Dedicated Administrator Connection (DAC)

As a Database Administrators you may come across a scenario where you are unable to access a SQL Server instance, especially when the CPU or Memory utilization on the server is very high. To allow database administrators to troubleshoot such scenarios, Microsoft introduced the Dedicated Administrator Connection (DAC) in SQL Server 2005; this is a special diagnostic connection for database administrators when standard connections to the SQL Server are not possible. SQL Server will make every possible attempt to successfully connect using DAC feature; however in some extreme conditions it may not be successful. The Dedicated Administrator Connection feature is also available in SQL Server 2008. Database Administrators need to keep it in mind that only one  DAC connection can be established to a SQL Server Instance. Once the connection is established using DAC you can access SQL Server and execute queries to troubleshoot performance issues. In this article you will see how to configure and enable a Remote Dedicated Administrator Connection and you will also see how you can use DAC with SQL Server Management Studio and the SQLCMD command line utility.

Enabling the Remote Dedicated Administrator Connection
By default, the DAC can only be run on the server. A Remote DAC is not possible until it is configured by the database administrator using the sp_configure system stored procedure with the remote admin connections option. To enable a Remote DAC, execute the code below:

USE master
GO
sp_configure 'show advanced options', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
/* 0 = Allow Local Connection, 1 = Allow Remote Connections*/
sp_configure 'remote admin connections', '1'
GO
RECONFIGURE WITH OVERRIDE
GO

In SQL Server 2005 you can also enable remote computes to access a DAC by using the SQL Server Surface Area Configuration Tool. However in SQL Server 2008, the SQL Server Surface Area Configuration Tool is not available.

To access the Area Configuration Tool in SQL Server 2005  select Start | All Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Surface Area Configuration



In SQL Server 2005, the Surface Area Configuration screen you need to select is the Surface Area Configuration for Features.  This will open the screen below:



Click on DAC under Database Engine and select the Enable remote DAC option and click OK to save the configuration changes.

TCP/IP Port Used by Dedicated Administrator Connection
The Dedicated Administrator Connection requires a dedicated TCP/IP port which gets assigned dynamically when the Database Engine starts up. By default the DAC listener accepts connections on the local port, i.e., for a default instance of SQL Server, DAC uses TCP/IP port 1434. Once the remote administrator connection is configured then the DAC listener is enabled without requiring a restart of the SQL Server service. You can check the port which was assigned for the DAC in SQL Server error log.

Using DAC with SQLCMD Command Line Utility
It is advised to use the SQLCMD command line utility to use the DAC feature, especially when SQL Server is facing high CPU or Memory utilization issues. The reason to use SQLCMD is because it is a light weight command line utility and it uses very little server resources such as Memory and CPU when connected locally or remotely. In scenarios when the server is not responding to standard SQL Server connections this is the best approach. You need to be a member of sysadmin fixed server role to connect and use the DAC. The (-A) is the special administrator switch which needs to be used when connecting to a SQL Server 2005 or aSQL Server 2008 instance using DAC with the SQLCMD command line utility.

SQLCMD –S AKMEHTA –U UserA –P UserA$ –A 

Explanation for Command Line Options which we have used in SQLCMD:

-S <Provide SQL Server Instance>
-U <User Name>
-P <Password>
-A Logs in to SQL Server with a Dedicated Administrator Connection (DAC).



Once connected using SQLCMD, database administrators can use the SQL Server Diagnosis queries to troubleshoot performance issues.

 
 

• Query Dynamic Management Views (DMV) like sys.dm_tran_locks, sys.dm_exec_sessions, sys.dm_exec_requests etc
  
• Execute Basic DBCC Commands like DBCC SQLPERF(LOGSPACE), DBCC DROPCLEANBUFFERS etc
  
• Run KILL 'SPID' etc
  
• 
  

Using DAC with SQL Server Management Studio
1. In SQL Server Management Studio, Press CTRL + N or click Database Engine Query

2. In Connect to Server dialog box, type ADMIN: followed by the name of SQL Server Instance in the Server name textbox. You can see that in the below example to get connect to a SQL Server instance named AKMEHTA, we have provided the Server name value as ADMIN:AKMEHTA

3. In the Authentication drop down list, there will be two options, namely Windows Authentication and SQL Server Authentication. In this example I will be using SQL Server Authentication. I have provided the credentials of a member of the sysadmin group and then clicked Connect to establish the connection using DAC. 


 
4. If there is no other dedicated administrator connection in use then the attempt will be successful. Otherwise the connection will fail with an error indicating it cannot establish the connection.

5. In the new query window which has opened up, you can type the queries below which will help you quickly diagnosis performance issues.
 
SELECT * FROM sys.dm_tran_locks
SELECT * FROM sys.dm_exec_sessions
SELECT * FROM sys.dm_exec_requests

 

Limitations when using DAC

 
 

• Only one DAC connection is allowed per instance of SQL Server. This limitation is there in both SQL Server 2005 and SQL Server 2008
  
• You will be receive the error below when a user tries to connect using DAC and another DAC connection is active
  
• 
  

Could not connect because the maximum number of dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process.

 
 

• It is not possible to take a database backup or restore a database when you have connected using a DAC
  
• It is advised not to run resource intensive queries when connected to an SQL Server Instance using a DAC
  
• You need to be a member of the ysadmin fixed server role to use a DAC
  
• It is likely that if your database engine is running you will be able to access master database and then diagnosis the performance issues on the SQL Server
  

Conclusion
The Dedicated Administrator Connection feature which was introduced in SQL Server 2005 is a great help for Database Administrators especially in scenarios when SQL Server is not responding to standard user connections. Using the DAC, an administrator can get access to an instance of SQL Server and execute queries to diagnose performance issues.