In this blog post I’m going to talk about the DAC and how you can enable it and how it may just save you from a very bad day if you ever have trouble connecting to your SQL Server.
So what is the DAC?
The dedicated admin connection, or DAC, allows an administrator to connect to an instance of SQL Server 2005 or higher in order to diagnose problems in the event other connections are not being accepted.
Typically this is useful when your server has a ‘run away’ query or process which is maxing out system resources and resulting in time out of connections. The DAC reserves a small portion of these system resources so that you will always be able to connect in and run any queries you need to get the instance back up and working as normal.As the name suggests, you have to be a member of the SQL sys admin group to connect using the DAC.
So how do we enable the DAC?
The DAC can be enabled by simply connecting to the SQL Server instance and running the following command (as either a sysadmin or serveradmin in SQL Server);
EXEC sp_configure ‘remote admin connections’,1
If you are not familiar with sp_configure it is basically used as a method to make configuration changes on your server and the RECONFIGURE command is used to apply the setting changes. Be very careful using it.
You should get a message back that states;
Configuration option ‘remote admin connections’ changed from 0 to 1. Run the RECONFIGURE statement to install.
The DAC is now enabled on the instance of SQL Server.
And how do we connect using the DAC?
So it’s time to connect to you server using the DAC, this can be done in one of two ways, either via SSMS or via the command line.
Connecting via both is easy, all you need to do is prefix your server name with ADMIN:
So assuming you server is called ServerA, in SSMS you would just put ADMIN:ServerA as the server name and via the command line you would just run;
SQLCmd –S ADMIN:ServerA
That’s it, but remember you can only have 1 DAC connection, this isn’t really a problem from the command line but in SSMS you need to do it via File>New>Database Engine Query and entering the server name there (with the ADMIN:) this makes sure only the query window connects and nothing else.
You can check that you are connected to the DAC by using the following DMV to look at the endpoints.
SELECT session_id FROM sys.dm_exec_connections WHERE endpoint_id = 1
The query should return one record in which the session_id matches yours.
I hope you found this post useful. As a standard practice we enable the DAC on our servers but we don’t use them unless absolutely necessary. The overhead is almost zero so to me it’s a no-brainer, get it enabled.