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.
|
1 2 3 |
sp_configure 'remote admin connections', 1; |
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.
You can check the current value of remote admin connections using the sys.configurations.
|
1 2 3 4 |
SELECT * FROM sys.configurations WHERE NAME = 'remote admin connections' |
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.
In the SSMS connection window, enter the server name as Admin:[Azure SQL Server].
Click on Connect. It failed with the following error 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.
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.
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.
After connection, you can run DMV for viewing information such as active sessions, request status, locking information and cache status.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Active Sessions SELECT * FROM sys.dm_exec_sessions GO -- Requests Status SELECT * FROM sys.dm_exec_requests GO -- Locking Information SELECT * FROM sys.dm_tran_locks GO -- Cache Status SELECT * FROM sys.dm_os_memory_cache_counters GO |
If you want to know, who is using the DAC connection, you can use the following query in the Azure SQL Database.
|
1 2 3 4 5 6 7 |
SELECT session_id, host_name, program_name, client_interface_name, status FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_resource_governor_workload_groups AS wg ON s.group_id = wg.group_id WHERE wg.name = 'DACGroup'; |
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
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
|
1 2 3 |
>sqlcmd -S azuredemosqldemo.database.windows.net -U sqladmin -P ******* -d azuredemodatabase -A |
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023