Tags

, ,


Using Dedicated Administrator Connection (DAC) database administrators can connect to the SQL Server Instance and can check the health and troubleshoot when the SQL Server is not responding and is not accepting any new regular connections. By default, remote DAC connection is disabled from SQL Server 2005 onwards which means DAC is only available from a client on the server.

As best practice, I always make sure remote DAC is always enabled in every SQL Server that I manage.

Please remember that only single DAC connection is allowed at a time.

We can enable Remote Admin Connections by SSMS GUI and by TSQL.

Lets first see how to enable it by TSQL:

USE MASTER
GO
/* 0 = Allow Local Connection, 1 = Allow Remote Connections*/
SP_CONFIGURE ‘remote admin connections’, 1
GO
RECONFIGURE
GO

Enabling Remote DAC by GUI:

Right click the SQL Server Instance and select the Facets option from the drop down list as shown below:

This will open up View Facets window . Here we 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.

Once the Dedicated Administrator Connection is enabled we can connect to SQL Server instance using either SQL Server Management Studio or using SQLCMD.

Thanks
Subhro Saha

Advertisements