DAC

In Earlier versions of SQL Server,it was impossible to gain access to SQL Server when the server had a spinloop, maxed out resources or severe corruption. No matter how you tried to gain access to the server (i.e. Enterprise Manager, Query Analyzer, OSQL, ISQL, a third party IDE, etc.) it was not possible.To get SQL Server back online a last optiont was to reboot the server. If you reboot, SQL Server was able to checkpoint the databases and shutdown gracefully, other times your were not so lucky.The roll back process was long and defer database corruption or access violations.

With SQL Server 2005, Microsoft offers a new feature, the dedicated administrator connection.

DAC is used to defense to troubleshoot and kill the offending SQL Server processes as opposed to rebooting the server possibly causing database corruption and/or access violations.

You can connect to the DAC using the command-line tool SQLCMD and Through SSMS.Here Iam providing sample syntax to connect DAC.

SQLCMD:
In the below picture I tried to connect DAC by using SQLCMD through default Instance

If you want connect DAC through named instance:

C:\>SQLCMD -S servername\Named Instance -A -d master

 -A is for the DAC
-d is for the database
-S is for the Server name

The DAC is a special-purpose connection designed for diagnosing problems in SQL Server and possibly resolving them. It is not meant to be used as a regular user connection. Any attempt to connect using the DAC when there is already an active DAC connection results in an error.The message returned to the client says only that the connection was rejected,it does not state explicitly that it was because there already was an active DAC.However,a message is written to the error log indicating the attempt (and failure) to get a second DAC connection.

2010-09-02 19:59:34.40 Logon       Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]
2010-09-02 19:59:34.48 Logon       Error: 17810, Severity: 20, State: 2.
2010-09-02 19:59:34.48 Logon       Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]

You can check whether a DAC is in use by running the following query.If there is an active DAC, the query will return the SPID for the DAC; otherwise, it will return no rows. 

SELECT s.session_id 
FROM sys.tcp_endpoints as e JOIN sys.dm_exec_sessions as s  
   ON e.endpoint_id = s.endpoint_id
WHERE e.name='Dedicated Admin Connection'; 

Please keep the following points in mind about using the DAC:

• By default, the DAC is available only locally. However,an administrator can configure SQL Server to allow remote connection by using the configuration option called Remote Admin Connections.
• The user logon to connect via the DAC must be a member of the sysadmin server role.
• There are only a few restrictions on the SQL statements that can be executed on the DAC.(For example, you cannot run BACKUP or RESTORE using the DAC.) However,it is recommended that you do not run any resource-intensive queries that might exacerbate the problem that led you to use the DAC.The DAC connection is created primarily for troubleshooting and diagnostic purposes.
• A special thread is assigned to the DAC that allows it to execute the diagnostic functions or queries on a separate scheduler.This thread cannot be terminated. You can kill only the DAC session, if needed. The DAC scheduler always uses the scheduler_id value of 255,and this thread has the highest priority. There is no lazywriter thread for the DAC, but the DAC does have its own IOCP, a worker thread, and an idle thread.

You can also connect DAC through SSMS

open management studio-click on connect Database Engine-then type admin:Servername or Servername\Instance (if it is a named instance).

By default, no DAC resources are reserved on SQL Server Express.If you want to enable DAC on Express Edition you need to set trace flag 7806 on startup parameters.

DAC Ports:
when you installed SQL server setup creates an Additional Endpoint.SQL server listens on that port.The Default port for DAC is 1434. It can be changed by using REGEDIT.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

Here MSSQL.X Indicates that Default Instance or Named Instance.

DAC KNOWLEDGE MEASURE :

1. Default DAC port listens on?
2. How to Check Which port DAC was using?
3. How to change port number for default instance or Named Instance?
4. DAC is having an additional endpoint?
5. Express Edition supports DAC?
6. How to Enable DAC in Express Edition?
7. By default DAC allow local or remote Connections?
8. How to Enable remote connections for DAC?
9. DAC is having own Schedular ID if yes what is the Scheduler ID number?
10. which is the trace flag used in express edition to enable DAC?