MS SQL Server 2005 provides new feature for DBAs - DAC or Dedicated Administrator Connection. This new type of connection can be used when sql server does not respond to standard connection. Even though SQL Server does everything to make DAC connection attempts successful this type of connection can also fail if SQL Server is completely screwed up.

What is DAC

DAC is privileged connection type in SQL Server (starting from SQL Server 2005). SQL Server reserves special scheduller for this type of connection. Run the query

select * from sys.dm_os_schedulers

 and notice scheduler with scheduler_id = 255. Its status is VISIBLE ONLINE (DAC). This is scheduler for DAC connection.

There are some restrictions of DAC:

• You must be a member of sysadmin role to use DAC
• There is only one Dedicated Admin Connection allowed per sql server instance. Any DAC attempts to the server which already has an active DAC will fail
• Since DAC resources are guaranteed they are limited. You can't run BACKUP/RESTORE using DAC. You can't run heavy queries.

You should use DAC only to query dynamyc management views/functions (DMV), run basic DBCC commands and KILL abusive processes.
Dedicated admin connection port is assined during SQL Server startup so if you enabled remote DAC connections you need to enable SQL Server Browser service. Otherwise you will need to specify port number explicitly within your connection string.

How to use DAC

To connect to the server with the name servername via SQL Server Management Studio (SSMS) using DAC you need only to write admin: prefix before server name in "Connect to Server" window in Server Name edit box

Remember one thing connecting from SMSS using DAC: You cannot connect using DAC to a server using Object Browser pannel in yor SMSS becase only one dedicated admin connection is allowed but SMSS object explorer opens two. The only way to connect from SMSS using DAC is to press New Query button when there is no active connections in Object Browser panel. Otherwise you will get error

Cannot connect to admin:servername.

Dedicated administrator connections are not supported. (ObjectExplorer)
 

Another way to establish Dedicated Administrator Connection - via sqlcmd using -A parameter -A dedicated admin connection. For instance

 sqlcmd -E -S servername -A

This command will establish connection to servername using Windows authenticationwith DAC.

 When to use DAC

You should use Dedicated Administrator Connection only in emergency cases. You should not everytime connect to the server via DAC just because you can use it. There are two major cases when you need use DAC:

• SQL Server does not accept standard connections
• There is a server level trigger that prohibits connections for instance. You need to connect to the server using DAC and drop/modify the trigger.

How to enable or disable DAC

Only local DAC is enabled by default.

To enable Remote DAC connections you can use SQL Server Surface Area Configuration tool (Start->Programs->Microsoft SQL Server 2005->Configuration tools->SQL Server Surface Area Configuration) chose Surface are Configuration for Features and then mark Enable Remote DAC checkbox

Since SQL Server Surface Area Configuration tool is gone in SQL Server 2008 you will need to run script

exec sp_configure 'remote admin connections', 1

reconfigure

to enable remote DAC on your SQL Server 2008 instance.