MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter






















































Trending SQL Server Tips













Different Ways to Enable Dedicated Administrator Connection in SQL Server

Read Comments   |   Related Topic: SQL Server Database Backup Options > Next Topic: SQL Server Disaster Recovery Tip


Introduction

Microsoft introduced Dedicated Administrator Connection (DAC) feature in SQL Server 2005. Using DAC feature a database administrator can connect to an SQL Server Instance when SQL Server stops responding to normal connections. During such scenarios using DAC feature a DBA can connect to the instance to troubleshoot and kill processes which are causing issues.

By default, Dedicated Administrator Connection feature is disabled in SQL Server. It is always a best practice to enable DAC on all SQL Server Instance so that during emergencies you can use this feature to troubleshoot the SQL Server Instance. At any given time only one DAC connection is allowed. Learn How to Enable Remote Admin Connections or DAC in SQL Server.

Enable Dedicated Administrator Connection in SQL Server Using TSQL

/*How to Enable Dedicated Administrative Connection in SQL 
Server Using TSQL*/
Use master
GO

sp_configure 'remote admin connections', 1
GO

RECONFIGURE WITH OVERRIDE
GO

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

DBA can also enable DAC feature using SQL Server Management Studio. Connect to SQL Server Instance using SSMS and then right click the Server and select FACETS from the drop down menu as shown in the snippet below.

Enable Dedicated Administrator Connection in SQL Server Using SSMS or Using Policy Based Management

In View Facets window you need choose the Facet as “Surface Area Configuration” as shown in the below snippet. Then, under Facet Properties choose value as “True” for RemoteDacEnabled as highlighted. Click OK to save the changes.

Remote DAC Enabled Facets in SQL Server

Conclusion

In this article you have seen different ways by which you can enable dedicated administrator connection in SQL Server.

Don't be Anti-Social

Thank the author by Share this article with your friends and colleagues; LIKE US ON FACEBOOK, FOLLOW US ON TWITTER and SIGN UP FOR OUR NEWSLETTER to get latest updates on SQL Server to excel in your career.





Last Updated On: Sept 18, 2013



Share this Article

Learn More...






Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: