Rajendra Gupta
SSMS connection

SQL Server Dedicated Administrator Connections for Azure SQL Database

March 29, 2022 by

This article explains SQL Server Dedicated Administrator Connections and how you can use it for Azure SQL Database.

Introduction

Suppose the users report performance and query time-out issues in a production environment. To begin an investigation, you try to connect the SQL Server using SQL Server Management Studio. You cannot connect to the SQL instance as your connection request takes longer, resulting in issues like the server not responding or timeout.

What actions did you take to resolve the performance bottleneck? If you cannot connect to DB and see what’s happening, you can only recycle the SQL Service. On a production system, it might be challenging to restart SQL Server, and the restart might also cause database recovery due to ongoing transactions before service restart.

How do you resolve the issue without restarting SQL Service?

If you think about the Azure SQL Database, you cannot restart SQL services, and Azure manages the services and their configuration. The SQL Server dedicated administrator connection might help you in this scenario.

This article will explore Azure SQL Database dedicated administrator connection(DAC) and how you can use it!

SQL Server Dedicated Administrator Connection (DAC)

The SQL Server Dedicated Administrator Connection (DAC) is a special diagnostic connection for the database administrators to access the database in critical performance issues. Sometimes, if SQL Server is under stress, it might not respond to standard user connections, and it might happen due to long-running queries, blocking sessions. In this case, the DBAs can connect using DAC and execute diagnostic queries for resolving issues. The administrator can access the SQL database, execute diagnostic queries, troubleshoot performance issues even when the server is not responding or in an abnormal state.

Note: SQL Server tries its best for a successful DAC connection; however, it might not work in extreme situations.

In the on-premises SQL Server, we have complete control over the infrastructure, DB engine services, instance. The DAC connection is available for the on-premises SQL versions. However, if you migrate to Azure SQL DB, a managed relational database service, do we support DAC connections?

By default, the DAC connection works on the loopback IP address 127.0.0.1 on port 1434. Therefore, the connection is permitted only from the client running on the server. It means you need to RDP SQL Server and connects database engine from inside the server.

However, you can configure the remote admin connections using the sp_configure in the on-prem SQL Server with the following script.

Azure SQL Database is a managed SQL database service. Therefore, you do not get complete control over SQL Services configurations. In the Azure SQL Database, you cannot run the sp_configure commands. If you try to do so, you get the following error message.

Sp_configure command

You can check the current value of remote admin connections using the sys.configurations.

Verify configurations

We cannot change the remote admin connections configuration in Azure SQL DB. In the next section, let’s see how you can connect to Azure SQL Database using a dedicated admin connection (DAC).

How to connect to SQL Server Dedicated Administrator Connection for Azure SQL Database

Azure SQL DB supports the DAC connections using SQLCMD or SSMS. To connect using the SSMS, add the prefix ADMIN: for your Azure server name (FQDN).

For example, in my case, Azure SQL Server is as shown below.

SQL Server Dedicated Administrator Connection

In the SSMS connection window, enter the server name as Admin:[Azure SQL Server].

SSMS connection

Click on Connect. It failed with the following error message.

Failure message

You should not try connecting to DAC using the object browser in SSMS because it creates multiple connections for getting the graphical view of an instance.

In the SSMS, go to File -> New -> Database Engine Query and try specifying the DAC connection. The Azure SQL DB also generates the error message, as shown below.

Database Engine Query

It requires additional configurations for Azure DB DAC connection.

  • Click on the connection properties and enter the Azure SQL Database name.
  • Put a check on the Encrypt Connection.

Specify DB name

Click on connect, and you can connect to Azure SQL DB with a DAC connection. In the connection bar, you can see, we are connected to Azure SQL Server using Admin: prefix, i.e. DAC connection.

Check connection bar

After connection, you can run DMV for viewing information such as active sessions, request status, locking information and cache status.

If you want to know, who is using the DAC connection, you can use the following query in the Azure SQL Database.

Check DAC connection

Connect Azure SQL Database using DAC connection with SQLCMD

SQLCMD is a command-line utility to connect, execute T-SQL statements, system procedures, and script files on the SQL Server databases. It is compatible with Azure SQL as well.

If you do not have SQLCMD command-line utility in your instance, you can download and install the latest version using the link https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15

SQLCMD install

You can access the Azure database using DAC with SQLCMD as well. In the SQLCMD, we add the following parameters.

  • -S: Specify the Azure SQL Server name. Do not add ADMIN: prefix for Azure server.
  • -U: Specify the SQL Server administrator username
  • -P : Enter the password of the admin user
  • – d: Enter the Azure SQL DB name. It is a must for connection using the DAC connection.
  • -A: It specified dedicated administrator connection

Verify SQL version

Note: To use the Azure Active Directory for authentication, use -G switch.

DAC Restrictions

  • You can have only one SQL Server dedicated administrator connection for Azure SQL Database. If a DAC connection is active, the additional DAC connections will be rejected.
  • The DAC connections are recommended to use for simple diagnostic and troubleshooting scenarios. You should not run resource-intensive queries that may cause blockings or high resource utilization on a DAC connection. For example, avoid running Do not run commands such as DBCC CHECKDB, DBCC DBREINDEX, or DBCC SHRINKDATABASE.
  • SQL Server does not execute queries in parallel mode on the DAC connection.
  • If you find any session causing performance issues while investigating, you can kill the SPID. The kill initiates the rollback of a transaction, and it may take time depending on the number of affected rows. Once you issue a kill command, you can use the following DMVs to track its status.
    • sys.dm_exec_sessions: Check the SPID status using the DMV sys.dm_exec_sessions. If it does not return any rows for a specific SPID, it shows that SPID is killed.
    • sys.dm_os_tasks: After running the KILL SPID, the session still exists in the sys.dm_exec_sessions; you can use sys.dm_os_tasks to see if there are any tasks associated. In case there is an existing task, it means transaction rollback is in progress.
    • If there are no tasks in the DMV sys.dm_os_tasks, and you have active sessions in the sys.dm_exec_sessions, it means there is no worker available. Therefore, run the T-SQL (Select * from sys.dm_os_tasks where sessions_id<>NULL) and kill sessions to free up the worker thread.

Conclusion

SQL Server dedicated administrator connection (DAC) is a way for DBA to connect the system during resource bottlenecks for troubleshooting purposes. It is supported on the Azure SQL Database as well as we explained in this article. You should be aware of the DAC connection, way to connect using DAC because it is required during critical production issues, and you might not have time to look for articles during that time. Therefore, try DAC connection for Azure SQL DB and be familiar with the process.

Rajendra Gupta
923 Views