New features of SQL Server 2022

Hello guys, hope you are doing well. In this blog I'm going to discuss about the "New features of SQL Server 2022" 

The release of the SQL Server 2022 is much anticipated by the audience since the announcement of it being in the works on November 2nd, 2021. Although the complete details of this brand-new version will be brought to light in due time as it is currently in private preview. However, Microsoft has already disclosed its key features that the audience should look forward to.

Although most of the product’s features are still private, the released information has introduced some unique new performance-based features in the SQL Server 2022. Here is a detailed list of what you’d be working with once you get your hands on the product: 

1. Performance Improvement: This feature enables SQL Server to build better execution plans and potentially multiple execution plans, the performance of which depends on the parameter values that are provided at runtime.

The Query Store can now help resolve performance issues related to the MAXDOP (maximum degree of parallelism) setting, memory grants, and the cardinality estimator using a feedback cycle to adapt and improve query execution plans. Finally, Query Store now supports availability group (AG) read-only replicas. 

2.  High Availability and Connection Improvements: In SQL Server 2022, you may construct a distributed AG between an on-premises SQL Server and an Azure SQL Managed Instance (MI) for use as a disaster recovery backup server or as a read-only replica for reporting workloads, making high availability even better. With a few clicks, you may manually failover to the MI and back to the on-prem SQL Server.

3. Security and Governance Improvements: Using "blockchain" technologies, the  new functionality creates an immutable record of data alterations throughout time. This safeguards data from tampering, which is important in some contexts and use cases, as well as providing benefits for internal and external audits. Microsoft's initial feature set has been developed into a more comprehensive data governance platform. Purview now has tighter integration with SQL Server and Azure SQL, allowing you to scan SQL Server for metadata, classify data using common classifier labels and tagging (such as PII or HIPAA data), and configure and control specific SQL Server access rights and privileges from a single Azure Purview console.

4.  Query Store and Intelligent Query Processing: The SQL Server 2022 ensures that the Query Store will be available on all new databases on the server. It addresses the common issue in the previous models by allowing write access from readable secondaries. This feature will enable transparency into activity on secondary replicas. However, if a database from the older version of SQL is accessed, the Query Store will have to be manually enabled.

5. Parameter Sensitive Plan Optimization: In older SQL Server versions, input parameters at the time of storage had an impact on certain stored processes. Parameter Sniffing was the popular name for this problem. One of the unique features of SQL Server 2022 is the Parameter Sensitive Plan Optimization, which allows you to cache multiple parameter-sensitive queries while procedures are being saved. This will assist you avoid having to alter your code by using the recompile option or allocating the values of the input parameter to variables within the procedure.

6.  TempDB and Latch: During SQL Service Performance Tuning in older SQL server versions, 1 out of 10 clients would struggle with TempDB and Latch. Although you can address it in multiple ways, the practice took valuable time and energy. The SQL Server 2022 addresses these issues by enhancements to the TempDB database and the Memory Optimized TempDB Metadata. Experts are hopeful that clients will not struggle with these issues with the release of this new version.

 

7.  Bidirectional HA/DR to Azure SQL Managed Instance: For enhanced data backup and security, SQL Server 2022 adds Bidirectional HA/DR to Azure SQL Managed Instance. It allows you to connect a database to the cloud in only a few clicks. This not only saves money, time, and labour by eliminating the technicalities of data storage via hardware and virtual machines, but it also encourages better backup and recovery methods. It creates an Availability Group (AG) between a SQL Server and an on-premises Azure SQL Managed Instance.

8.  Azure Synapse Link: The Azure Synapse Link helps move data from an on-premises SQL Server to Synapse without ETL. It captures the changes in the server and feeds them to Azure Synapse Analytics. The Synapse link puts minimal strain on operating systems when conducting real-time analysis and analytical processing. It fastens data transfer and increases the connectivity of the data to other robust analytic programs.

9.  SQL Server Ledger: In comparison to the competition, SQL Server has experienced fewer security concerns in the recent decade. The new SQL Server Ledger, on the other hand, secures data even further by maintaining an immutable record of any changes made to the data or stored processes. The ledger prevents unauthorized parties from tampering with or corrupting the data, lowering the chance of a security breach.

10.Multi-Write Replication: Previously, when rewritten data clashed with different clones of the same database, the dispute had to be manually resolved. SQL Server 2022, on the other hand, addresses this problem by adding the concept of 'last write wins.' As a result, if a conflict between duplicates arises, the most recent rewrite will take precedence and be copied to all copies.

 --By Harigovind Gupta (Software Engineer)

    Clota Technology

pg_restore

     pg_restore restores a PostgreSQL database from an archive file created by pg_dump.

     pg_restore [connection-option...] [option...] [filename]

     Example:

     We have dumped a database called mydb into a custom-format dump file:

     pg_dump -Fc mydb > db.dump

     To drop the database and recreate it from the dump:

     $ dropdb mydb

     $ pg_restore -C -d postgres db.dump

 

      The database named in the -d switch can be any database existing in the  cluster; pg_restore only uses it to issue the CREATE DATABASE command  for mydb. With -C, data is always restored into the database name that appears in the dump file.

   To reload the dump into a new database called newdb:

    $ createdb -T template0 newdb

    $ pg_restore -d newdb db.dump

 

    Notice we don't use -C, and instead connect directly to the database to be restored into. Also note that we clone the new database from template0 not template1, to ensure it is initially empty.

pg_switch_wal()

pg_switch_wal() is a system function which forces PostgreSQL to switch to a new WAL file.

    pg_switch_wal() returns the end LSN + 1 of the old WAL file.

    However, if there has been no activity which generates WAL since the last WAL file switch, a switch will not be carried out and the start location of the current WAL file will be returned.

    pg_switch_wal() can only be executed on a primary server (i.e. not a read-only standby).

    postgres=# SELECT pg_switch_wal();

    pg_switch_wal

    ---------------

    0/161E050

     If there has been no activity which generated WAL since the last WAL file  switch, a new WAL file will not be created:

 

    postgres=# SELECT pg_walfile_name(pg_switch_wal()), pg_walfile_name(pg_switch_wal());

     pg_walfile_name      |     pg_walfile_name     

--------------------------+--------------------------

 000000010000000200000086 | 000000010000000200000086

(1 

CAP Theorem

In theoretical computer science, the CAP theorem, also named Brewer's theorem after computer scientist Eric Brewer, states that it is impossible for a distributed data store to simultaneously provide more than two out of the following three guarantees:

Consistency

Every read receives the most recent write or an error.

Availability

Every request receives a (non-error) response, without the guarantee that it contains the most recent write.

Partition tolerance

The system continues to operate despite an arbitrary number of messages being dropped (or delayed) by the network between nodes.

 

When a network partition failure happens, it must be decided whether to

     •cancel the operation and thus decrease the availability but ensure consistency or to

    •proceed with the operation and thus provide availability but risk inconsistency.

The CAP theorem implies that in the presence of a network partition, one has to choose between consistency and availability.

  Today, NoSQL databases are classified based on the two CAP characteristics they support:

CP database: 

A CP database delivers consistency and partition tolerance at the expense of availability. When a partition occurs between any two nodes, the system has to shut down the non-consistent node (i.e., make it unavailable) until the partition is resolved.

AP database: 

An AP database delivers availability and partition tolerance at the expense of consistency. When a partition occurs, all nodes remain available but those at the wrong end of a partition might return an older version of data than others. (When the partition is resolved, the AP databases typically resync the nodes to repair all inconsistencies in the system.)

CA database: 

A CA database delivers consistency and availability across all nodes. It can’t do this if there is a partition between any two nodes in the system, however, and therefore can’t deliver fault tolerance.

             

--By Abhishek Yadav...

Stay In Touch:

Streaming Replication Step by step notes

1. On master, changed listener to * in postgreSQL.congif
2. Reboot master posstgres SQL
3. Created login for replication on master

CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD '******';

4. Modify pg_hba.conf on master with slave info…

echo "host replication replicator 192.168.0.126/32 md5" >> pg_hba.conf \

/u01/app/postgres/bin/psql -c "select pg_reload_conf()"

5. pg_basebackup make sure you take the backup from slave and PostgreSQL service must be in stop mode.

/u01/app/postgres/pgsql/bin/pg_basebackup -h 192.168.0.117 -U replicator -p 5432 -D /u05/pgsql/database -Fp -Xs -P -R

NOTE:-

In my case, I have installed PostgreSQL (PostgreSQL 12) in /u01/app/postgres/pgsql/bin/ directory and data directory  is /u05/pgsql/database