Dedicated Administrator Connection (DAC)

The dedicated administrator connection (DAC) allows an administrator to connect to an SQL Server instance to run diagnostic functions or T-SQL statements even if the server is not responding to normal connections. This feature was introduced in SQL Server 2012 and by default this option is disabled. 

You can enable DAC using below two methods;

​​

Using SSMS

1. Open 
   ​​
   SSMS
   ​.​
   
   
2. In object explorer, connect to the SQL Server instance that you want to enable 
   ​​
   DAC
   ​.​
   
   
3. Right click on Server instance and select 
   ​​
   Facets
   ​.​
   
   
4. In 
   ​​
   View Facets window select 
   ​​
   Server Configuration using the drop down box. See below figure
   ​:​

Using T-SQL

sp_configure 'show advanced options',1;

GO

RECONFIGURE;

GO

sp_configure 'remote admin connections', 1; 

GO 

RECONFIGURE; 

GO​

How to connect via DAC

​​

Once you enabled the DAC for an SQL Server instance, it is pretty easy to connect to the Server via DAC. You just need to mention ADMIN: in front of the server name that your connecting. See below figure:

Note that you can establish a single successful DAC connection to a SQL Server. If you try another DAC connection attempt, you get the following error message;

"A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)"

There is a possibility that you will get the above error when you try to login to a server via DAC to perform some urgent troubleshooting task. That means someone else has already logged into the server using remote DAC and you may immediately do to panic mode. In such cases, it is very important to know who is using DAC connection. 

Use the below T-SQL code to get the information of already established DAC connection. 

SELECT CASE WHEN ses.session_id= @@SPID THEN 'It''s me! ' ELSE '' END + 
       COALESCE(ses.login_name, '???') AS WhosGotTheDAC, 
       ses.session_id, 
       ses.login_time, 
       ses.status, 
       ses.original_login_name 
FROM   sys.endpoints AS en 
       JOIN sys.dm_exec_sessions ses 
         ON en.endpoint_id = ses.endpoint_id 
WHERE  en.NAME = 'Dedicated Admin Connection'

If your using the server name when connecting, make sure SQL Server Browser service is running. Otherwise you will receive the most common log on error which is "A Network-related or instance-specific error occurred…" which basically says it cannot find the server. 

General guideline

It is always a good practice to enable

​D​

AC in all production servers. This option is extremely useful when you want to troubleshoot server issues. 

Further reading:

remote admin connections Server Configuration Option

Cheers!

Persistent Log Buffer with SQL Server 2016 SP1

You can get significant performance gain by just moving the existing applications to SQL Server 2016. There are several case studies to prove this story. You can even get more performance gain (2X - 4X) with combination of SQL Server 2016 SP1 and Windows Server 2016 (not yet released). This performance gain achieved through Persistent (non-volatile) Log Buffer technique. This performance enhancement has been released to SQL Server with SP1. SQL Server utilizes Windows Server 2016 direct memory access (DAX). With this new feature SQL Server can write transaction log data in memory access speed over disk IO speed earlier. With the new type of memory which is persistent, the moments SQL Server writes to log cache (memory area for t-log), the data is persistent. 

For more details:

Transaction Commit latency acceleration using Storage Class Memory