Different Ways to Enable Dedicated Administrator Connection in SQL Server
Sept 18, 2013
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*/
sp_configure 'remote admin connections', 1
RECONFIGURE WITH OVERRIDE
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.
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.
In this article you have seen different ways by which you can enable dedicated administrator connection in SQL Server.
Geeks who read this article also read…
- Performance Dashboard Reports in SQL Server 2012
- How to Backup an Analysis Services Database Using SQL Server Management Studio
- How to Restore an Analysis Services Database Using SQL Server Management Studio
- Automate Backup of Analysis Services Database Using SQL Server Agent Job
- How to Attach Analysis Services Database in SQL Server
- How to Detach Analysis Services Database in SQL Server
- How to Backup Database in SQL Server
- How to Restore Database in SQL Server
- How to Attach Database Without a Transaction Log File in SQL Server
- New Features in SQL Server 2012 Reporting Services for Developers
- New Features in SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Read More SQL Server Articles…