What's new in SQL Server 2022 (16.x)

SQL Server 2022 (16.x) builds on previous releases to grow SQL Server as a platform that gives you choices of development languages, data types, on-premises or cloud environments, and operating systems.

https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2022?view=sql-server-ver16

Feature highlights

Administrative:

Azure Synapse Link for SQL:  near real-time analytics With integration between SQL Server 2022 (16.x) and Azure Synapse Analytics dedicated SQL pools, Azure Synapse Link for SQL enables you to run analytics scenarios on your operational data with minimum impact on source databases.

Object storage integration:  introduces new object storage integration with S3-compatible object storage, in addition to Azure Storage. The first is backup to URL and the second is Data Lake Virtualization.

Link to Azure SQL Managed Instance: Connect your SQL Server instance to Azure SQL Managed Instance.

Contained availability group: Create an Always On availability group that:

        - Manages its own metadata objects (users, logins, permissions, SQL Agent jobs etc.) at the availability                     group level in addition to the instance level.
        - Includes specialized contained system databases within the availability group.

Distributed availability group: - Now using multiple TCP connections for better network bandwidth utilization across a remote link with long tcp latencies.

Improved backup metadata: system table returns last valid restore time.

Azure Active Directory authentication: Use Azure Active Directory (Azure AD) authentication to connect to SQL Server.

Always encrypted with secure enclaves: Support for JOIN, GROUP BY, and ORDER BY, and for text columns using UTF-8 collations in confidential queries using enclaves.

Query Store on secondary replicas: Query Store on secondary replicas enables the same Query Store functionality on secondary replica workloads that is available for primary replicas. 

Manage Azure extension for SQL Server:Use SQL Server Configuration Manager to manage Azure extension for SQL Server service. 

Shrink database WAIT_AT_LOW_PRIORITY:In previous versions, shrinking databases and database files to reclaim space often leads to concurrency issues. SQL Server 2022 adds WAIT_AT_LOW_PRIORITY as an additional option for shrink operations (DBCC SHRINKDATABASE and DBCC SHRINKFILE). 

When you specify WAIT_AT_LOW_PRIORITY, new queries requiring Sch-S or Sch-M locks aren't blocked by the waiting shrink operation, until the shrink operation stops waiting and begins executing

Developers:

CREATE INDEX

WAIT_AT_LOW_PRIORITY with online index operations clause added.

SELECT ... WINDOW clause

Determines the partitioning and ordering of a rowset before the window function, which uses the window in OVER clause is applied. See SELECT - WINDOW.

IS [NOT] DISTINCT FROM	Determines whether two expressions when compared with each other evaluate to NULL, and guarantees a true or false value as the result. For more information, see IS [NOT] DISTINCT FROM (Transact-SQL).
Time series functions	You can store and analyze data that changes over time, using time-windowing, aggregation, and filtering capabilities. - DATE_BUCKET () - GENERATE_SERIES () The following adds support to IGNORE NULLS and RESPECT NULLS: - FIRST_VALUE () - LAST_VALUE ()
JSON functions	- ISJSON () - JSON_PATH_EXISTS () - JSON_OBJECT () - JSON_ARRAY ()
Aggregate functions	- APPROX_PERCENTILE_CONT () - APPROX_PERCENTILE_DISC ()
T-SQL functions	- GREATEST () - LEAST () - STRING_SPLIT () - DATETRUNC () - LTRIM () - RTRIM () - TRIM ()
Bit manipulation functions	- LEFT_SHIFT () - RIGHT_SHIFT () - BIT_COUNT () - GET_BIT () - SET_BIT ()

Creating Alias name for MSSQL Server Instance

Recently we shifted our production database server to new SQL Server instance. Once that activity completed we face a lot of issues to change connection string for running application from one server to other. One the major application is SharePoint because you can’t open Central administration from SharePoint server unless your instance name will be same as before.

After doing some research we find a way around for this problem. We can create database server Alias and Alias name will be same as previous database instance name so that all applications will work as they are working before. Other possible uses to create aliases are database server security in order to hide you database server name or you can create it when Named instance is there to make it easy.

In following scenario, we have Database server with Named Instance. We created Alias to qualify simple name and smooth application execution without changing much. Following are the steps to create Alias.

Open SQL Server configuration manager and go to SQL Native Client Configuration. Click to Aliases as shown in diagram

--

You need to provide following information

Alias name: Name that you want to set for Database server

Port number: Write dynamic or specific port to connect

Protocol: It can be TCP/IP preferably or Name pipe. Selected protocol must be enabled in client protocols.

Server Name: For named instance you need to mention machine\instance name, For default instance we can mention local host.

You can mention specific port number or you can find dynamic port from SQL Server Network Configuration. Open TCP/IP properties and go all the way down you will see IPAll.

--

If you have 64 bit operating system you need to configure Aliases in SQL Native Client 11.0 configuration and SQL Native Client 11.0 Configuration (32bit).

--

In the following screen shot. You can see both Instances are connected.

--

Changing and verifying SQL Server Status from Linux (Ubuntu)

Recently we learn how to install SQL Server on Linux and how to use it. As a database administrator we need to check current database status as well as need to enable and disable it on demand. In the following post we will learn how to check current database status and change it using Linux command shell.

Once you install MSSQL Server in Ubuntu, You need to restart Linux machine in order to finish installation. SQL Server engine will automatically start as “Active” after restart. In the following post we will learn how to verify and change its status like Start, Stop, Disable and Enable.

1-      Verify running status for MSSQL Server using following command

“ systemctl status mssql-server”

You can see the status in green color.

2-      We can change MSSQL Server status using following sudo command

“sudo systemctl stop mssql-server”

To verify current database status you will repeat same step as above “ systemctl status mssql-server”.

3-      Using following command We can disable MSSQL Server engine using following sudo command

“sudo systemctl disable mssql-server” and can verify by executing as “ systemctl status mssql-server”.

4-      To enable MSSQL Server engine we can using same sudo command

“sudo systemctl enable mssql-server” and can verify by executing as “ systemctl status mssql-server”

5-      Finally MSSQL Server is up and active.