9.4 Dedicated Administrator Connection

9.4 Dedicated Administrator Connection

9.4.1 The concept of dedicated administrator connections

When SQL Server no longer responds to a standard connection request, the administrator needs to perform a diagnostic query and resolve the problem, but the SQL Server cannot be accessed through a normal connection request at this time.

SQL Server provides a dedicated administrator connection (dedicated Administrator Connection, referred to as a DAC). Even if the server stops responding to other client connections, the administrator can use the DAC to access the running instance of the SQL Server database engine to troubleshoot the server. SQL Server tries to make the DAC connection successful, but a connection failure may occur under very special circumstances.

To ensure that there are available connection resources, only one DAC is allowed per instance of SQL Server. If the DAC connection is already active, another connection that is trying to make the DAC attempt is rejected with error 17810.

Tips:

To preserve resources, SQL Server Express does not listen on the DAC port unless it is started with trace flag 7806.

9.4.2 opening the DAC

Start SSMS, skip the default "Connect to Database Engine" (do not connect to the database engine through this window, or you will get an error "The private administrator connection through SSMS is not supported because it establishes multiple connections by design").

There are 2 ways to open the DAC.

New query

Click the New Query button on the toolbar. In the Connect to Server dialog window, in the Server name box, enter the name of the admin:< instance >.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/6C/13/wKioL1U_WTzjO7JKAAHknLp1WdY153.jpg "title=" Connect to the server. png "alt=" wkiol1u_wtzjo7jkaahknlp1wdy153.jpg "/>

New Database Engine Query

From the File menu on the main menu, click New, and then select Database Engine query.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6C/17/wKiom1U_VSuQh2qLAAJBTUOSuhE471.jpg "title=" New query. png "alt=" wkiom1u_vsuqh2qlaajbtuosuhe471.jpg "/>

In the Connect to Database Engine dialog box, in the Server name box, enter the name of the admin:< instance >.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/6C/18/wKiom1U_WDmwzFZJAAIQ5kbIxNg372.jpg "title=" Connect to the database engine. png "alt=" wkiom1u_wdmwzfzjaaiq5kbixng372.jpg "/>

Best Practices:

The DAC initially attempted to connect to the default database associated with the login account. After a successful connection, you can connect to the master database. If the default database is offline or unavailable, the connection returns error 4060. The DAC is recommended to connect to the master database as long as the instance of the database engine is started to ensure that the master database is available.

Port of the 9.4.3 DAC

SQL Server listens on the DAC on the TCP1434 port, or listens for a DAC on a dynamically allocated TCP port when the database engine is started. The error log contains the port number on which the DAC is listening. For example:

2015-01-23 19:25:27.86 Server is listening on [:: 1 <ipv6> 1434]. 2015-01-23 19:25:27.86 Server is listening on [127.0.0.1 <ipv4> 1434]. 2015-01-23 19:25:27.86 Server Dedicated admin connection support is established for listening locally on port 1434.

When connected to the default instance, the DAC avoids accessing the SQL Server Browser service at connection time, but instead connects directly to the TCP 1434 port. If it fails, try accessing the SQL Server Browser service to get the port. If the SQL Server Browser service does not start, the connection request returns an error.

By default, the DAC listener only accepts connections on the local port. You can perform sp_configure modify the remote admin connection option so that the DAC listener can accept access to the remote connection.

sp_configure ' remote admin connections ', 1; GO RECONFIGURE; GO

This article from "SQLServer2014 series" blog, declined reprint!

9.4 Dedicated Administrator Connection