In the past there have been many instances where the SQL Server (database engine) has become unresponsive which could result in users / administrators not being able to connect to the server to troubleshoot issues etc. SQL 2005 has a new feature called " Dedicated Administrator Connection (DAC)".
The Admin Connection (DAC) always uses a specific TCP end point which is always attached to a dedicated UMS. To ensure that resources will be available SQL Server only permits one DAC connection per instance. If there is an active DAC connection open all new requests to connect via DAC are denied. Only members with the SysAdmin role will be able to connect to the database engine using DAC.
Resources that could spawn multiple threads or resource intensive queries should not be run using a DAC connection.
A DAC connection is created in 2 ways
a. SQLCMD utility or
b. Using SSMS (SQL Server Management Studio)
you must use the -A switch when using SQLCMD
E.g.
D:\> SQLCMD -S <servername> -E (to use your NT authentication) or -U<usrname> -P <password> -A
you can also connect via SSMS using admin:<servername>
DAC feature can be turned on / off using either SSMS or the Surface Area Configuration Utility.
sp_configure 'remote admin connections' , '1'
go
reconfigure
with override
go
