By: Jeremy Kadlec | Comments (9) | Related: More > Dedicated Administrator Connection
Problem
In years and versions of SQL Server gone by, at times it was impossible to gain access to SQL Server when the server had a spinloop, maxed out resources or severe corruption. No matter how you tried to gain access to the server (i.e. Enterprise Manager, Query Analyzer, OSQL, ISQL, a third party IDE, etc.) it was not possible. With pressure mounting to get SQL Server back online a last resort was to reboot the server. At times, SQL Server was able to checkpoint the databases and shutdown gracefully other times your were not so lucky. No matter how patient you were with SQL Server on the shutdown and subsequent startup, the roll back process was long and at times yielded database corruption or access violations.
Solution
With SQL Server 2005, Microsoft offers a new feature, the dedicated administrator connection. This configuration is intended to serve as a last means of defense to troubleshoot and kill the offending SQL Server processes as opposed to rebooting the server possibly causing database corruption and/or access violations. The DAC allows you to connect to the server and issue T-SQL commands to troubleshoot and correct the problem.
To use the DAC you can either access it using SQLCMD from a command prompt
- sqlcmd -A -d master (the -A uses the DAC and the -d connects to the master database)
- sqlcmd -A -d master -E -S EDGESERV1\TEST1 (the -E uses Windows authentication and the -S connects the server and instance)
or by using SQL Server Management Studio with the ADMIN: option when connecting to the server. To use this option:
- launch SQL Server Management Studio (don't connect to an instance of SQL Server yet)
- select Database Engine Query (icon right next to "New Query")
- put "ADMIN:" in front of the server\instance name
- use your regular Authentication procedures to connect
- and select Connect
Once you have connected to the database engine you can now issue T-SQL commands to identify the problem and fix the problem. Note: there are no GUI tools so you need to be familiar with the T-SQL commands to use to troubleshoot the problem such as:
- sp_who2
- dbcc inputbuffer
- kill
- etc...
Note:
By default the DAC is only available from a client on the server. To enable remote clients to utilize the DAC an option needs to be set which can be done using sp_configure
-
sp_configure 'remote admin connections', 1;
or by using the SQL Server Surface Area Configuration. To do this:
- launch the SQL Server Surface Area Configuration.tool which can be found under SQL Server 2005 Configuration Tools in the SQL Server 2005 menus
- select "Surface Configuration Area for Features".
- check the box for "Enable remote DAC"
- select "Apply"
Next Steps
- Take the time to determine if the remote administrator only configuration setting should be enabled in your environment to troubleshoot a SQL Server issue if it arises.
- Be sure to deploy this configuration and verify it is configured as expected on each of the SQL Servers.
- If an issue arises, keep this configuration in the back of your mind as a possible means to correct the issue without gracefully shutting down SQL Server.
- Be sure you are up to speed on the T-SQL administrative commands to troubleshoot and rectify any issues
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
