Problem
One of the junior SQL Server Database Administrators in my company approached me yesterday with a dilemma. He want to know how to enabled the Dedicated Administrator Connection in SQL Server 2008. At first I started to tell him, but figured it would be smarter to document the options and share the information.  This tips shows you how you can enable this feature in SQL 2008.

Solution
In SQL Server 2005, Microsoft introduced a new feature called Dedicated Administrator Connection (DAC). Using this feature a SQL Server Database Administrator can connect to a SQL Server Instance when the database engine is not responding to regular connections. During such a scenario a DBA can connect to the SQL Server Instance to troubleshoot and to kill any of the SQL Server Processes which are causing the issues.

The DAC allows database administrators to connect to a SQL Server Instance and to execute T-SQL commands to troubleshoot and fix issues rather than rebooting the SQL Server which could lead to database corruption or other problems. By default, the remote Dedicated Administrator Connection feature is disabled in SQL Server 2005 and later versions. It’s a good practice to enable the DAC feature once the SQL Server 2005 or SQL Server 2008 is installed on every instance as this will help you troubleshoot issues when regular connections are not responding. However, only one dedicated administrator connection is allowed at a time on SQL Server 2005 and later versions. 

Use master
GO
sp_configure 'show advanced options' , 1
GO
/* 0 = Allow Local Connection, 1 = Allow Remote Connections*/ 
sp_configure 'remote admin connections', 1 
GO
RECONFIGURE
GO

Enable Dedicated Administrator Connection in SQL Server 2008 Using SQL Server 2008 Management Studio

Database Administrators can also enable Dedicated Administrator Connection Feature using SQL Server 2008 Management Studio. This can be done by right clicking the SQL Server Instance and selecting the Facets option from the drop down list as shown in the snippet below.

This will open up View Facets window as shown in the snippet below. Here you need to select Surface Area Configuration facet as highlighted and then select the option as “True” for RemoteDacEnabled.

The syntax to use DAC is mentioned below.

SQLCMD –S [SQL Server Name] –U [User Name] –P [Password] –A 

Once you are connected to SQL Server Instance using DAC, then you can execute code such as the code below to check the SQL Server health.

-- Locking Information
SELECT * FROM sys.dm_tran_locks
GO
-- Cache Status
SELECT * FROM sys.dm_os_memory_cache_counters 
GO
-- Active Sessions
SELECT * FROM sys.dm_exec_sessions 
GO
-- Requests Status
SELECT * FROM sys.dm_exec_requests
GO

Next Steps

• Make sure you have enabled Dedicated Administrator Connection Feature on all the SQL Server 2005 and on SQL Server 2008 servers.
• If at any point database engine is not allowing regular connections, then use this feature to troubleshoot issues without shutting down SQL Server.

Original link: http://www.mssqltips.com/tip.asp?tip=1801&home