When I was in elementary school we often had planned “fire drills”.  We would be told of the impending alarms, the alarms would go off, and we’d all exit the school in an orderly fashion using the prescribed path and exits.

Fast forward a hundred years and I’ve discovered why fire drills are important…  So that when the real deal happens we know exactly what to do and where to go.

We recently had a database server become over run with one or more queries that sent the server into a tail spin.  The classical definition of a good operating system, graceful degradation under heavy load, wasn’t working.  The server was in such bad shape that our admins weren’t able to log into the machine to determine what was going on.

“Normal” connections didn’t even allow an sp_who2 to return it’s simple result set.

The time to conduct your own DAC fire drill is BEFORE you really need it.  Otherwise, it’s easy to forget that it even exists when the red phones are going off and panicked IMs and emails are coming in from all corners of the business.

For starters, let’s clarify one point:  The DAC is not a specialized account.  It is a connection.  So there is no special login account and / or password you need to remember.  Nor is it a secret back door into SQL Server.  The purpose of the connection is to increase the probability (not guarantee) that a user with ADMIN rights can connect to the server and start KILL-ing off offending sessions that are causing the server to spike.

It ain’t pretty… but can be pretty darn useful.

There are two ways to use the DAC.

1.  When connecting to the DATABASE ENGINE (not server), precede “ADMIN:” before the server name.  My personal laptop has an instance of SQL Server called DJF001.  Here’s how my connection to the DAC looks:

You’ll know you have a DAC connection by observing your connection description at the lower right of you command window:

2.  An alternative method that uses fewer system resources is through SQLCMD.   When connecting from SQLCMD just use the “-A” switch “SQLCMD -A”.  Note:  The Storage Engine book indicates to use “/A”… this probably works in 2005 but did not work for me in 2008.

The default setting only allows local DAC connections.  If you want to be able to use the DAC from a remote host just set the REMOTE ADMIN CONNECTIONS configuration option.

The DAC is not intended as a general purpose connection and all commands, such as backup and restore, will not work.  It’s purpose is to allow an administrator to connect to the machine when all other connection options fail.

Configuring Remote DAC: Unless you are running SQL Express edition, DAC is ready to roll by default.  However, if you want to use the DAC from a remote host you’ll need to configure it.  To configure remote DAC enter the following on the server you want to setup:

sp_configure 'remote admin connections', 1

GO

RECONFIGURE

GO

Conclusion: The next time your server is running out of control think DAC and get connected.  In the meantime, run your own fire drill and make sure you know how to use it.

References:

Inside Microsoft SQL Server 2005: The Storage Engine, Kalen Delaney.  Microsoft Press. 2007.

Microsoft SQL Server 2005: Implementation and Maintenance, Solid Quality Learning, Microsoft Press, 2006.

MSDN, Connecting to the DAC.

Stay curious… Keep Learning…

Dave