Have you ever encountered performance and query timeout issues in your SQL Server production environment? Trying to connect to the SQL Server using SQL Server Management Studio can be a frustrating experience, especially when the server is not responding or timing out. In such situations, restarting the SQL Service might seem like the only solution, but it can be challenging and may cause database recovery due to ongoing transactions.

So, how can you resolve these performance bottlenecks without restarting the SQL Service? Enter the SQL Server Dedicated Administrator Connection (DAC). In this article, we will explore the concept of DAC and how it can be used with Azure SQL Database.

SQL Server Dedicated Administrator Connection (DAC)

The SQL Server Dedicated Administrator Connection (DAC) is a special diagnostic connection that allows database administrators to access the database during critical performance issues. When SQL Server is under stress and not responding to standard user connections, the DAC connection can be used to execute diagnostic queries and troubleshoot performance issues.

While the DAC connection is available for on-premises SQL Server versions, you might wonder if it is supported in Azure SQL Database, a managed relational database service. By default, the DAC connection works on the loopback IP address 127.0.0.1 on port 1434, allowing connections only from the client running on the server. However, in Azure SQL Database, you do not have complete control over SQL Service configurations, and running sp_configure commands is not possible.

Connecting to Azure SQL Database using DAC

Azure SQL Database supports DAC connections using SQLCMD or SQL Server Management Studio (SSMS). To connect using SSMS, you need to add the prefix “ADMIN:” to your Azure server name (FQDN). However, connecting to DAC using the object browser in SSMS is not recommended as it creates multiple connections for graphical views of the instance.

Instead, you can connect to Azure SQL Database using DAC by following these steps:

1. Open SSMS and go to File -> New -> Database Engine Query.
2. Specify the server name as “Admin:[Azure SQL Server]” and click on Connect.
3. If the connection fails, click on the connection properties and enter the Azure SQL Database name. Check the “Encrypt Connection” option and click on Connect.

Once connected, you can run diagnostic queries using DMVs to view information such as active sessions, request status, locking information, and cache status.

Using DAC with SQLCMD

If you prefer using SQLCMD, a command-line utility, to connect to Azure SQL Database using DAC, you can do so by specifying the necessary parameters:

-S: Specify the Azure SQL Server name (without the “ADMIN:” prefix).

-U: Specify the SQL Server administrator username.

-P: Enter the password of the admin user.

-d: Enter the Azure SQL Database name.

-A: Specify the dedicated administrator connection.

For example:

sqlcmd -S azuredemosqldemo.database.windows.net -U sqladmin -P ******** -d azuredemodatabase -A

DAC Restrictions and Best Practices

While DAC can be a powerful tool for troubleshooting, there are some restrictions and best practices to keep in mind:

• You can have only one SQL Server dedicated administrator connection for Azure SQL Database. Additional DAC connections will be rejected.
• Avoid running resource-intensive queries on a DAC connection, such as DBCC CHECKDB, DBCC DBREINDEX, or DBCC SHRINKDATABASE.
• SQL Server does not execute queries in parallel mode on the DAC connection.
• If you encounter a session causing performance issues, you can kill the SPID. Use DMVs like sys.dm_exec_sessions and sys.dm_os_tasks to track the status of the killed session.

Conclusion

The SQL Server Dedicated Administrator Connection (DAC) is a valuable tool for database administrators to troubleshoot critical performance issues. It is supported in Azure SQL Database, allowing DBAs to connect and execute diagnostic queries even when the server is not responding or in an abnormal state. By familiarizing yourself with DAC and its usage with Azure SQL Database, you can be better prepared to tackle production issues efficiently.