Pages

Monday, 30 July 2012

SQL Server : Dedicated Administrator Connections

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
GO

RECONFIGURE


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.

DAC port number








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 

6 comments:

  1. Good Post Nelson. Keep up the good work

    ReplyDelete



  2. I loved the way you discuss the topic great work thanks for the share, Let me share this, dotnet training in pune

    ReplyDelete
  3. شركة نقل عفش
    اهم شركات مكافحة حشرات بالخبر كذلك معرض اهم شركة مكافحة حشرات بالدمام والخبر والجبيل والخبر والاحساء والقطيف كذلك شركة رش حشرات بالدمام ومكافحة الحشرات بالخبر
    شركة مكافحة حشرات بالدمام
    شركة تنظيف خزانات بجدة الجوهرة من افضل شركات تنظيف الخزانات بجدة حيث ان تنظيف خزانات بجدة يحتاج الى مهارة فى كيفية غسيل وتنظيف الخزانات الكبيرة والصغيرة بجدة على ايدى متخصصين فى تنظيف الخزانات بجدة
    شركة تنظيف خزانات بجدة
    شركة كشف تسربات المياه بالدمام
    شركة نقل عفش واثاث

    ReplyDelete
  4. شركة نقل عفش بالرياض وجدة والدمام والخبر والجبيل اولقطيف والاحساء والرياض وجدة ومكة المدينة المنورة والخرج والطائف وخميس مشيط وبجدة افضل شركة نقل عفش بجدة نعرضها مجموعة الفا لنقل العفش بمكة والخرج والقصيم والطائف وتبوك وخميس مشيط ونجران وجيزان وبريدة والمدينة المنورة وينبع افضل شركات نقل الاثاث بالجبيل والطائف وخميس مشيط وبريدة وعنيزو وابها ونجران المدينة وينبع تبوك والقصيم الخرج حفر الباطن والظهران
    شركة نقل عفش بجدة
    شركة نقل عفش بالمدينة المنورة
    شركة نقل اثاث بالرياض
    شركة نقل عفش بالدمام
    شركة نقل عفش بالطائف

    ReplyDelete