“The SQL Guy” Post #16: Using the Special Admin Connection to SQL Server

There are times when SQL Server may be so busy processing requests that it can no longer allocate memory or processor resources to even allow an SA to connect. This has been an issue with older versions of SQL Server.

SQL Server solves this problem by introducing a feature called "Dedicated Administrator Connection” (DAC).

DAC uses a specific TCP endpoint in a SQL Server instance that is always attached to a dedicated UMS (User Mode Scheduler). DAC provides a connection that can be used by a member of the sysadmin role to access a SQL Server instance, thereby guaranteeing that an administrator could not be locked out of SQL Server due to resource allocation issues.

DAC connection is similar to a regular SQL Server connection except that it has only one worker thread to serve requests. It cannot run any queries that require parallel work/multiple threads such as BACKUP or parallel query plan. It will use the regular system memory and in case of failure, it will use its reserved memory. Only a single connection of DAC is allowed at a time and if a connection is already being used, any subsequent connections are refused.

Due to the limitation in available resources (One thread and limited amount of memory), you must be very careful of the types of queries you run. You MUST use DAC connection exclusively for diagnostic purposes to resolve a condition that cannot normally be resolved through a regular connection.

You can create a connection to DAC in two ways:

1) Using SQLCMD from the Command Prompt

2) Using SQL Server Management Studio

*You must use -A parameter when using SQLCMD to use Dedicated Administrator Connection.

THINGS TO KEEP IN MIND:

1. DO NOT USE DAC FOR EVERYDAY WORK. IT IS EXCLUSIVELY RESERVED FOR EMERGENCY PURPOSES ONLY.

2. ONLY ONE DAC CONNECTION IS ALLOWED PER INSTANCE. IF SOMEONE ELSE IS CONNECTED TO SQL SERVER USING DAC CONNECTION OR HAS LEFT THE CONNECTION OPEN, YOU WILL NOT BE ABLE TO CONNECT TO SQL SERVER USING DAC UNLESS THE OTHER CONNECTION IS CLOSED.

3. BY DEFAULT, DAC CONNECTION IS ALLOWED ONLY ON A LOCAL SERVER CONNECTION. YOU MUST ENABLE IT FOR REMOTE CONNECTION IF YOU WISH TO USE IT REMOTELY. (BY DEFAULT IT IS NOT ON FOR A CLUSTERED INSTANCE)