Dedicated Admin Connection (DAC):-
- DAC allows database administrators to perform T-SQL commands to connect to SQL Server instances and to troubleshoot problems instead of rebooting SQL Server, which can cause database corruption or other problems.
- By default, the remote server administrator connection feature is disabled in SQL Server 2005 and later versions.
- To Enable DDAC in SQL Server Using below query:-
Use master
GO
— 0 = TO Allow Local Connection, 1 = TO Allow Remote Connections
sp_configure ‘remote admin connections’, 1
GO
RECONFIGURE
GO
- To Enable Dedicated Administrator Connection (DAC) in SQL Server through SQL Server Management Studio:-
DAC can be anable by right clicking the SQL Server Instance and selecting the Facets option from the drop down list as shown in the below picture:-
Then Need to select Surface Area Configuration facet as highlighted and then select the option as “True” for RemoteDacEnabled:-
Click OK to save the configuration changes in the View Facets window.
Then connect SQL Server Database using DAC feature through SQL Server Management Studio:-
You need to specify “ADMIN:” before the SQL Server Instance name when trying to connect to an SQL Server Instance to using DAC feature as shown in the below
Once you will connect to SQL Server Instance using DAC then you are able to execute the code and troubleshoot the performance issue in you SQL databases.
Hope This will help you guys!!