DAC - Dedicated Admin Connection in SQL Server 2005

In mids of loads of new features introduced in SQL Server 2005, this is one feature that interests the DBA's the most. If you work with large number of servers and large number of databases you might want to have immense control the way you monitor and maintain SQL Server instances. In the past I've seen times when people say the server has frozen and I am not able to connect. To eliminate such problems "Welcome SQL Server 2005 - DAC"

What is DAC ?

Dedicated Admin Connection allows user to connect to SQL server when normal connection attempts fail, for example, when server is hanging, out of memory or other bad states that it's not responding to connection requests. DAC is achieved by pre-allocating dedicated resources during server startup, including memory and scheduler etc. Only available in SQL Server 2005.

Ways to use DAC ?

There are two modes of usage. You can use the sqlcmd which is the command promt version and the osql version in SQL Server 2005. We have a new option -A that enables the connection to be as Admin connection.


More often than not, I am sure you would like to have this with the Enterprise Manager way i.e. the SQL Server Management interface for SQL Server 2005. To enable the admin connection from SSMS you need to use the ADMIN: before your server's name. Hence the connection property would look like:



I am sure this is a nifty feature as DBA's we would like to capitalize on. This proves at any point in time the DBA has a window of control ALWAYS available to him. I think we need to use it, test it and experience the difference that this feature brings to the table.