Life savior Dedicated Administrator Connection (DAC) in SQL Server 2008

DAC could become a real life savior for SQL Server DBA in some extreme critical situation when you are not able to connect to your SQL Server instance by any other way or you found you instance totally unresponsive, you can use DAC connection.

You shouldn’t practice to login via DAC connection in normal situation as it is special connection used to use as a rescue commando only. It intends to diagnose the issue, troubleshoot it and hopefully resolve it when you are not able to make connection to instance vial any other way.

BTW, you couldn’t make more than one connection with DAC at the same time and doing so will be resulted in an error. 

Before we see how to login with DAC, I would like to tell you few points which you should keep in mind before you try out DAC.

--) you must have sysadmin server role with your login to make DAC connection

--) there are few restrictions while you make connection with DAC, you can’t Backup/Restore database. Anyhow it is advisable not to use any heavy task while you are connected via DAC as it is designed to diagnose the problem and fix it only so you might want to look around to some catalog view or DMVs.

--) Generally DAC is available locally only by default, if you try to login via DAC from remote computer, you might see following error (however, you can enable DAC from remote computer too).

There are two ways to get connected with DAC. 1.) SQLCMD 2.) SSMS. All you need to do is, specify ADMIN: as a prefix of your SQL Server instance. Suppose my instance name is “SQLHub”, I should use “Admin:SQLHub”. Suppose my instance name is “SQLHub\SQL2K8R2”, I should use “Admin:SQLHub\SQL2K8R2”.

From SQLCMD, you can connect to DAC by specifying -A flag. Now, let us see, how we can do it via SSMS.

While opening SSMS, you will see login prompt “Connect to Server”, if you will give you credential of sysadmin server role along with admin:servername(instance), you will be greeted with an error given below.

TITLE: Connect to Server
------------------------------

Cannot connect to admin:WIN-7XRT6YL02S0.

------------------------------
ADDITIONAL INFORMATION:
Dedicated administrator connections are not supported. (ObjectExplorer)

------------------------------
BUTTONS:

OK
------------------------------
 

The reason is, only one DAC connection is possible as I have already written above, if you want to access DAC connection via SSMS, don’t try to login via object explorer, rather cancel that window and follow the given steps below.

File Menu->New->Database Engine Query

Give you credential of sysadmin account, keep admin: before your server name and you will be able to connect to DAC.

WIN-7XRT6YL02S0 is my server name of SQL Server 2008, you can see “Admin:” before my server name. I am going to use my windows administrator account as it has sysadmin server role, however you can use any of your login which has sysadmin server role permission.

Keep this information ready as you never know when you need it?

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com