Ashish MehtaMicrosoft introduced Dedicated Administrator Connection (DAC) feature in SQL Server 2005 and higher versions. 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.
Dedicated Administrator Connection [DAC] SQL Server
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.
How to Enable Dedicated Administrator Connection (DAC) in SQL Server
There are two different methods by which a DBA can enable DAC feature in SQL Server. First method is to use the TSQL Script. Second method by using SQL Server Management Studio.
How to Enable Dedicated Administrator Connection in SQL Server Using sp_configure
/*How to Enable Dedicated Administrative Connection in SQL Server Using TSQL*/
Use master
GO
sp_configure 'remote admin connections', 1
GO
RECONFIGURE WITH OVERRIDE
GOIt is always a Best Practice to enable Dedicated Administrator Connection (DAC) on all SQL Server Instance so that during emergencies you can use this feature to troubleshoot the SQL Server Instance.
Only members of the SQL Server sysadmin role can connect using the DAC
Trending SQL Server Disaster Recovery Articles and Tips
- Improved Startup Parameters in SQL Server 2012
- SQL Server Database Engine Service Startup Options
- How to Configure TempDB on Local Disk in SQL Server 2012/2014 Failover Cluster to Improve Performance
- Database Backup Encryption in SQL Server 2014 a Step by Step Implementation Guide
- How to Fix “BACKUP detected corruption in the database log” Error in SQL Server
- How to Repair Database in Suspect Mode in SQL Server
- How to Start SQL Server without TempDB Database
How to Enable Dedicated Administrator Connection in SQL Server Using SQL Server Management Studio
DBA can also enable DAC feature using SQL Server Management Studio.
Step 1: 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.
Step 2: 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 RemoteDacEnabledas highlighted. Click OK to save the changes.
Conclusion
In this article you have seen different ways by which you can enable dedicated administrator connection in SQL Server.
Chetna Bhalla