SQL Server 2005 Dedicated Administrator connection (DAC) tips for using

SQL Server 2005 Dedicated Administrator connection (DAC) tips for using

1 What is a dedicated administrator connection?
SQL Server 2005 provides an administrator with a special diagnostic connection for use when it is not possible to establish a standard connection with the server.

2 What is the use of a dedicated administrator connection?
Even when SQL Server does not respond to standard connection requests, administrators can use this connection to access SQL Server to perform diagnostic queries and resolve problems.
The administrator can access the running SQL Server Database Engine instance through the DAC to troubleshoot the server (even if the server has stopped responding to other client connections).

3 Dedicated Administrator Connection usage method
(1) Using sqlcmd
Sqlcmd-sqiangguoods-a
Sqlcmd-sadmin:qiangguoods
(2) SQL Server Management Studio Query Editor start DAC
admin:< Instance Name >
such as: Admin:qiangguoods

4 Dedicated Administrator Connection usage restrictions
(1) To ensure that there are available connection resources, only one DAC is allowed per instance of SQL Server. If the DAC connection is active, any new requests that are connected through the DAC will be rejected with error 17810. The
(2) DAC initially attempted to connect to the default database associated with the login account. Once the connection is successful, you can connect to the master database. If the default database is offline or unavailable, the connection returns error 4060. However, if you use the following command to overwrite the default database and connect to the master database instead, the connection succeeds:
Sqlcmd–a–d Master
can guarantee that the master database is available as long as the database Engine instance is started, so it is recommended to use the DAC Connect to the master database.
(4) SQL Server prohibits running parallel queries or commands using the DAC. For example, if you use the DAC to execute any of the following commands, you will generate error 3637. The
Restore or Backup
(5) DAC can only use limited resources. Do not use the DAC to run queries that consume large amounts of resources (for example, perform complex joins to large tables) or queries that may cause blocking. This helps prevent the DAC from being confused with any existing server issues. To avoid potential blocking situations, when you want to run a query that can cause blocking,
try to run the query at the snapshot-based isolation level if possible, or set the transaction isolation level to READ uncommitted and/or set the LOCK_TIMEOUT value to a shorter value, such as 2000 milliseconds). This prevents the DAC session from being blocked.
However, depending on the state of SQL Server, the DAC session may be blocked on the latch. You can use CNTRL-C to terminate a DAC session, but there is no guarantee that it will succeed. If it fails, the only option is to restart SQL Server.
to ensure a successful connection and troubleshoot DAC failures, SQL Server reserves the resources to handle commands running on the DAC. Typically these resources are only sufficient to perform simple diagnostics and troubleshooting functions, as shown below.
to preserve resources, the DAC connection is not available in SQL Server Express Edition.

5 How do I enable a remote DAC connection?
Only members of the SQL Server sysadmin role can use the DAC connection. By default, connections can only be established from clients running on the server. Network connections are not allowed unless configured by sp_configure using the remote admin connections option.
The DAC supports encryption and other security features of SQL Server. The DAC only allows the user context to be switched to other administrative users.
By default, the DAC listens only for loopback IP address (127.0.0.1) port 1434.
The possible values for remote admin connections settings are as follows:
0-Indicates that only local connections are allowed to use the DAC
1-Indicates allowing remote connections to use the DAC
--Enable remote DAC connections
sp_configure ' remote admin connections ', 1;
Go
Reconfigure;
Go
Note: After you configure a remote management connection, the DAC listener is enabled immediately without restarting SQL Server, and the client can connect to the DAC immediately remotely.

6 Common scripts
--Using sqlcmd
Sqlcmd-sqiangguoods-a
Sqlc

<