Dedicated Administrator Connection (DAC)

Usually the sqlcmd tool is used for automation, but I also think of it as my last access to a running database instance when the dirt hits the fan. sqlcmd is a part of a database instance installation - you don't have to install management tools.
But when the resources of a Windows Server host or a database instance is consumed by a wild running process or session, then the Dedicated Administrator Connection (DAC) might be your last option - before shutting down the box.

SQLCMD usage
Usually it is possible to use the host name, but once I've experienced a situation where the DNS server(-s?) was down. In that case you have to enter the IP address of the host. This also gives the fastest connection to the host.

SQLCMD.EXE -S "Admin:" -E

SQLCMD.EXE -S "Admin:SANDBOX.sqladmin\SSDB42" -E

Please notice that the Browser Service must be running.

Management Studio (SSMS) usage
Use the value like the SQLCMD -S parameter.

TCP port number
The TCP port number of the DAC is shown in the Registry and the SQL Server Errorlog.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SSDB\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp\TcpDynamicPorts [REG_SZ]
2009-03-18 21:35:15.26 Server Dedicated admin connection support was established for listening locally on port 1434.

If a host has several database instances installed, each instance DAC has it own TCP port assigned. Then you have to look into several Registry branches to get the TCP port number.

The DAC TCP port number is unfortunately not available through SMO or the SQL Server WMI provider :-(

Remote DAC
By default the DAC is available only locally on the host, but it is possible to enable remote DAC.
At SQL Server 2005 it was possible to enable remote DAC with the Surface Area Configuration point-and-click tool. But this tool is not part of SQL Server 2008 where you have to enable Remote DAC by
sp_configure 'remote admin connections', 1;


MSDN library: Using a Dedicated Administrator Connection

Kalen Delaney et al: "Microsoft SQL Server 2008 Internals", pp 27.

No comments: