Sunday 23 March 2014

Importance of DAC on SQL Server

Importance of DAC in SQL server .especially when SQL server is not responding or unable to connect to SQL server and SQL services are running...

DAC  (Dedicated Admin Connection) help us to connect and run basic troubleshooting queries in cases of serious performance problems.

Only members of the SQL Server sysadmin role can connect using the DAC.
Normally DAC allows database administrators to connect to a SQL Server Instance and to execute T-SQL commands to troubleshoot and fix issues rather than rebooting the SQL Server which could lead to database corruption or other problems. By default, the remote Dedicated Administrator Connection feature is disabled in SQL Server 2005 and later versions. It’s a good practice to enable the DAC feature once the SQL Server 2005 or SQL Server 2008 is installed on every instance as this will help you troubleshoot issues when regular connections are not responding. However, only one dedicated administrator connection is allowed at a time on SQL Server 2005 and later versions. 

By default DAC connection uses Port No..

By default, the DAC only listens on the loop-back IP address (127.0.0.1), port 1434. If TCP port 1434 is not available, a TCP port is dynamically assigned when the Database Engine starts up. When more than one instance of SQL Server is installed on a computer, check the error log for the TCP port number.
How to enable Remote DAC:

Using GUI:

Description: Description: DAC tree

This will open up View Facets window as shown in the snippet below. Here you need to select Surface Area Configuration facet as highlighted and then select the option as “True” for RemoteDacEnabled.
Description: Description: DAC settings

Enable Remote DAC using CMD:

Use master
GO
/* 0 = Allow Local Connection, 1 = Allow Remote Connections*/
sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO
Using DAC with SQLCMD

You need to be a member of the sysadmin fixed server role in order to use this feature. The “-A” switch is used to specify that the user is trying to connect to SQL Server using the Dedicated Administrator Connection.
The syntax to use DAC is mentioned below.
SQLCMD –S [SQL Server Name] –U [User Name] –P [Password] –A

Using DAC with GUI:

You need to specify “ADMIN:” before the SQL Server Instance name when trying to connect to an SQL Server Instance to using DAC feature as shown in the snippet below.
Description: Description: DAC login

Example queries to check the performance of the server and session being used and start troubleshooting and fix issues from SQL end.

-- Locking Information
SELECT * FROM sys.dm_tran_locks
GO
-- Cache Status
SELECT * FROM sys.dm_os_memory_cache_counters
GO
-- Active Sessions
SELECT * FROM sys.dm_exec_sessions
GO
-- Requests Status
SELECT * FROM sys.dm_exec_requests
GO


Please find the restrictions and more details in the below link.


My Friend added some more point to below info:

Just to add few things….

1>     DAC is always enabled by default  for the any instance of the SQL server, it’s just not enable for remote connection.
Means you can connect to any instance of SQL server using DAC if you are able to connect to the physical windows server (Using RDP).

Description: cid:image001.png@01CEA7D8.9AD92660
For local server the command for DAC is SQLCMD –A or SQLCMD  -S ServerName\InstanceName -A

If you want to connect to any remote server then  'remote admin connections' value should be 1 in SP_Configure.

And you can connect using SQLCMD –S ServerName –A

Note: No Need to provide any credential as long as your windows account is part of sysadmin in the instance, as SQLCMD uses Integrated authentication and DAC connection is only possible with sysadmin account.

2>     DAC is Mainly used when there is no any means to connect to the server (when SQL server is refusing all other connection). Because for all other cases you can troubleshoot using your normal connection.

3>     Please use DAC only through command prompt, so that it does not waste any resources (specially RAM) that is the whole point of giving the separate connection to the instance.
Since there is special thread running for this connection and the thread uses very less resource.

Note: Don’t use any resource intensive query like “ select * ” while using the DAC, it may throw errors depending on availability of the physical resources.   
             Only one thread assigned for DAC hence only one connection is allowed at any point of time in any instance, please close the DAC connection as soon as you finished with your work.

Let me know if this helps…….

No comments:

Post a Comment