Think about a scenario : People are complaining about your production database server.Many users are getting continuous time out error. Rest of the users are not able to connect database server.When you tried to run your diagnostic query , either you are not able to connect or it is not returning the result for your diagnostic queries. At this moment most of us will tend to restart the SQL server for the quicker resolution. The dedicated administrator connection (DAC) will help us in this kind of situation.
How DAC helps in this situation ?
Sql server will not not respond to queries if there is scheduler dead lock or all the resource are utilized by another connections. There will not be enough free resource to process your request and requests will be keep waiting for resources. As explained in the earlier post, in this scenario all scheduler will have longer queue,but in sql server we have dedicated scheduler only to process the request comes through the dedicated admin connection.SQL server provides DAC as special diagnostic connection for administrator when standered connection to the server are not possible.This should be considered as the last resort like 'Fire exit'. Do not tempted to misuse this privilege. This is just one thread and there is no parallelism for queries running under the DAC connection. DAC is not designed for high performance, so do not try to schedule any job or to run your day to day queries.
How to enable DAC ?
By default DAC is enabled to connect through local connection. That means you can connect to the local instance of SQL server using DAC with out making any changes. To connect the SQL server using DAC from remote machine using TCP/IP, we have to enable the 'remote admin connections' using sp_configure. We can enable the remote DAC connection using the below query.
EXEC sp_CONFIGURE 'remote admin connections',1
GORECONFIGURE
By default SQL server listen to DAC connection on port number 1434. If the port number 1434 is not available ,SQL server dynamically assign a port number during the start up and this can be found in the SQL server error log as given below.
if you have firewall between remote machine and SQL server , we might need to open the DAC port in the firewall to establish the DAC connection from the remote machine.
Limitation of of using DAC connection
- Only one DAC connection is allowed per instance.If a DAC connection is already open, new connection request will be denied with error 17810.
- You can't connect SSMS object explorer using the DAC connection, but you can connect a query window.
- SQL server prohibits running parallel queries or commands on DAC connection.Error 3637 is generated if you try to perform a backup or restore operation.
- Only login with sysadmin rights can establish the DAC connections.
How to establish DAC ?
You can use either SQL server management studio or SQLCMD to establish a DAC connection.If SQL browser is running, you can use
ADMIN:SERVERNAME\INSTANCENAME
in the server name of SSMS. The prefix ADMIN prompt the SQL server browser service to find out the DAC port of that instance. if your are aware about the port number used by the DAC you can use SERVERNAME\INSTANCENAME,1435
where 1435 is the port number where SQL server listen to the DAC connection. This number might change from instance to instance. You can find out the port number that listen to DAC from the SQL server error log as mentioned earlier.
In the same way to connect using the SQLCMD
SQLCMD -S SERVERNAME\INSTANCENAME -U sa -P Password12 -A
Where -A prompt the SQLCMD to connect using the DAC port. If you know the DAC port of the instance, you can use
SQLCMD -S SERVERNAME\INSTANCENAME,1435 -U sa -P Password12
If you liked this post, do like my page on FaceBook
www.PracticalSqlDba.com
