On about day one of being a DBA I was told this was an option that should always be enabled.  It took me much longer to understand what it achieved, and over 5 years before I had to use the dedicated administrator connection ‘in anger’.  But if you use it once and save having to reboot a server, you will come to appreciate it very quickly.

Option Name:  remote admin connection

Usage:  sp_configure ‘remote admin connection’, 1

Default: 0

Maximum: 1

Requires Restart:  No

What it does:  SQL Server keeps a single scheduler dedicated to allow an administrator to connect.  To do this you connect as normal, but prefix your servername with ADMIN: – now even if SQL Server is completely tied up – this allows you to connect and work out what’s going on.  And that’s a very valuable thing to be able to do.  However, when SQL Server gets itself tied up into the sort of knots that would normally require using the dedicated admin connection, you’ll often find the server so slow to respond that trying to do anything on the instance itself is nearly impossible.  I’m assuming you’re not going to have your server hooked up to a mouse, monitor and keyboard on your desk(and if you do……another day perhaps).  So you are most likely going to need to RDP into the server.  When you RDP into a server you are actually adding even more load as your profile gets loaded and your remote session is maintained.  If the server is dying you don’t want to make it work harder.  If this option is enabled the dedicated admin connection can be used from a remote version of management studio or sqlcmd.

When should I use it:  Always.  This should be a standard deployment.  You may get objections about it being a security risk but seriously – you already have to be a system administrator to use it, there’s no danger there…or at least no additional danger.  If you have dodgy sysadmins then you have bigger problems than this setting.

What else should you know:   Be aware that you have one thread to play with if you connect to the DAC.  It’s meant as a method to get in, run quick diagnosis on what has put the server into the state it is in, and resolve the issue.  It’s not meant to run complicated query, there’s no chance of parellelism(Because it’s just ONE thread) and it’s really only there as a last ditch option to prevent you having to restart the server.

Also be aware that when used locally the DAC is listening on the loopback address.  If you are using the DAC remotely you are going to need to make sure all the necessary firewall ports are open.