Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Stop Forgetting the DAC

From time to time I hear of DBAs that had to restart the SQL Server service on a 2005 or 2008 server because it had stopped accepting new connections. The thing is that most times those servers did not have to be restarted. If the DBA had logged in via the Dedicated Administrator Connection (DAC) they would have been able to identify and either kill or track the progress of the offending query.

So what is the DAC? It is a special reserved connection that allows 1 connection from the local machine by appending “ADMIN:” to the beginning of the server or server\instance name. (Exact instructions are available here) The DAC is by default only available to clients connection from the machine that the instance is running on. There is a setting that allows people to connect over the network but in an emergency it is good to know who is using that connection. Forcing people to log onto the box to use the connection means you can see who might have the connection by looking at who is connected to the server. Remember you can’t connect to SQL Server to see who is connected if things are bad enough that the DAC is your only option.

The great part about the Dedicated Administrator Connection (DAC) is that it is always available for someone to connect. Even when normal connections to the server do not work the DAC is ready to accept a connection. Once connected via SSMS (Query Window not Object Explorer) or SQLCMD a DBA can quickly diagnose and resolve an issue.

We no longer have to blindly restart SQL, hoping that we are not setting up a situation where our most important database will be in recovery for minutes or hours.

We now have the tools to accurately diagnose a problem the first time it happens.

We just have to remember to use them.

Comments

Posted by Robert L Davis on 7 October 2010

DBA's aren't the only ones that forget the DAC!! I saw on the blog of a member of the SQL team that the reason they called Data-Tier Applications DAC was because the acronym DTA was already in use in SQL Server.

To date, I've only encountered 1 server that was hosed so bad that I couldn't even get in via the DAC. The DAC was a great addition.

Posted by David Levy on 7 October 2010

Yeah the DAC has saved my bacon more than once. In fairness though they did call it the "DACPAC". I have to admit that the adding "PAC" to the end did seem like a last minute swerve to avoid disaster.

Posted by Anonymous on 7 October 2010

Pingback from  Stop Forgetting the DAC - SQL Server Central Debt on Me

Posted by jpertell on 7 October 2010

Thanks for the article on the DAC. I always enable it on new servers and luckily I've never had to use it other than to test that it was available.  

Leave a Comment

Please register or log in to leave a comment.