The DAC

The Dedicated Admin Connection – aka The DAC

The Dedicated Admin Connection or DAC can be a real life saver when it comes to a sql server that just isn’t playing ball !

Let’s just say your server has it’s cpu pegged at 100%, and you can’t connect to the instance via the usual methods of SSMS or RDP to examine what the problem is, and your only hope, or so it would seem, is to bounce the server and along with that the chance to actually see what was happening, then step away from that on/off button (I know that fixes EVERYTHING but trust me on this…..) and reach out for the DAC (queue music)…..oh but wait….you forgot to enable it for remote access ! Re-boot and when the server comes back enable the DAC for remote connections – Here’s how, and your gonna kick yourself but hey you probably deserve it !!!

Open SSMS and run the following query – this doesn’t require a server restart so can be done on a live Production box without any problems.

 

EXEC sp_configure ‘remote admin connections’, 1;
GO
RECONFIGURE
GO

 

So what Exactly is the DAC ?

The DAC is a single thread that  sql server reserves for members of the sysadmin group that allows them to connect in times of trouble. It will keep this thread free so no matter what resources have been consumed on your sql box you will always be able to use this single thread (and I stress the word single again) to connect and run some basic queries (no parallel queries here or funky maintenance routines), to query the dmv’s and get the low down on the slow down !!

By default remote access to the DAC is disabled – so make sure you enable it for remote access by running the above query.

You can see the DAC by running the below code – the highlighted result set shows the dedicated admin account in the Status column as being ‘VISIBLE ONLINE (DAC)’. This was run on my test laptop so the top result of  VISIBLE ONLINE is the cpu scheduler that will service my sql server processes and queries and the HIDDEN ONLINE are system reserved. This topic of cpu schedulers will covered in an other post.

 

SELECT * FROM SYS.DM_OS_SCHEDULERS

 

In Times of Trouble….

When things go horribly wrong on your server and you can’t connect via RDP, and lets face it, thats probably the last thing you want to do if resources are running at ’11’ (sorry i’ve just been watching Spinal Tap and the famous amp scene volume 1 to 11 !!) , or even your trusted SSMS won’t connect, here’s how you can (maybe) save the day.

Open up your SSMS from your workstation and hit the new query button (I always find that completely closing down SSMS first, then reopening it seems to work best). In the Server name box prefix with ADMIN: as shown below. Here I am connecting to a named instance called SQL2.

I am using Windows Authentication (remember the account has to be a member of the sysadmin group), but sql authentication is supported via the DAC providing it is configured on your server

 

Quite often you may get this error at this point…..

 

This is a somewhat confusing message. Basically from what I understand of this is that the connection tried to connect via object explorer (which isn’t allowed via the DAC) and so an error is displayed – it makes it sound like the whole of SSMS is not supported via the DAC when infact this is NOT true. I always close down object explorer and try and again – the connection eventually succeeds. You will be able to verify the connection by looking at the bottom right of SSMS – it should have the ADMIN:server name as shown below:

Now you can run some diagnostic queries to see if anything looks out of the ordinary. Its worth having a plan in your mind of what steps you should take once connected so you can follow a set procedure, as your already going to be under pressure – thats why your connecting with the DAC right ?

This is something I might blog about in the future

 

And Finally…..

Don’t forget to close your connection from the DAC when you finish. If you don’t close the connection then the next time you try and connect you will receive an error – which defeats the purpose of having the DAC in the first place !!