SQL Server database dedicated administrator DAC connection method

Turn: http://www.cnblogs.com/kerrycode/archive/2010/09/18/1830071.html

 

SQL Server provides a dedicated administrator Link (DAC ). DAC allows administrators to access the running server to execute diagnostic functions or transact-SQL statements, or to troubleshoot problems on the server, even if the server is locked or runs abnormally.

 

DAC can only be used on the server by default, but you can use the SQL Server 2005 peripheral application configurator to allow client applications on the remote computer to use DAC, as shown in

 

 

You can also use the following sp_configure command to change the database server configuration.

-- 0-indicates that only local connections are allowed to use DAC.

-- 1-specify allow remote connection to use DAC

Sp_configure 'remote admin connections ';

Sp_configure 'remote admin connections', 1;
Go
Reconfigure with override;
Go

 

 

You can use SQL Server Management studio or sqlcmd to connect a DAC to a database server. However, note that if you enable SSMs to link to the server like that, an error will be reported (I have been here for a long time)

 

 

 

DAC only supports query windows and does not support object explorer. if you want to use DAC connection in SSMs, you should use a normal connection and use DAC to open the query window. DAC supports only one window. If you have already established a connection, an error is returned. The error 17810 is prompted on msdn, but the 64 errors I encountered on SQL Server 05 are shown in.

 

The command line interface (sqlcmd) is switched by using a special administrator (-A), Provides and supports this dedicated administrator connection (DAC ).

 

 

 

The following part of the DAC is from msdn.

Only members of the SQL Server SysAdmin role can use DAC to connect. By default, only clients running on the server can establish connections. Network connection is not allowed unless you use the remote admin connections option through sp_configure. DAC supports encryption and other SQL server security functions. DAC only allows you to switch user context to other management users.

Since DAC is only used to diagnose server problems in rare cases, there are some restrictions on connection:

• To ensure available connection resources, only one DAC can be used for each SQL server instance. If the DAC connection has been activated, any new requests connected through the DAC will be rejected with error 17810.

• The DAC initially attempted to connect to the default database associated with the login account. After 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, the connection will succeed:

  Sqlcmd-a-d master

  As you only need to start the database engine instance to ensure that the master database is available, we recommend that you use DAC to connect to the master database.

• SQL Server prohibits the use of DAC to run parallel queries or commands. For example, if you use DAC to execute any of the following commands, error 3637 is generated.
  • Restore

  • Backup
• DAC can only use limited resources. Do not use DAC to run queries that consume a large amount of resources (such as complex joins to large tables) or query that may cause blocking. This helps prevent confusion between DAC and any existing server issues. To avoid potential blocking, when you need to run a query that may cause blocking, try to run the query at the snapshot-based isolation level. Otherwise, set the transaction isolation level to read uncommitted and/or set the lock_timeout value to a shorter value (for example, 2000 ms ). This prevents DAC sessions from being blocked. However, depending on the status of the SQL Server, the DAC session may be blocked during the latch. You can use a CNTRL-C to terminate a DAC session, but it cannot be guaranteed to succeed. If it fails, the only option is to restart SQL Server.
• To ensure that the connection is successful and the DAC fault is eliminated, SQL Server reserves certain resources to process commands running on the DAC. Generally, these resources are sufficient for simple diagnosis and troubleshooting, as shown below.
• To retain resources, DAC connections are unavailable in SQL Server 2005 express edition.

Microsoft strongly recommends that you use only the following diagnostic and troubleshooting commands, although theoretically you can run any Transact-SQL statement without parallel execution on the DAC:

• Query the dynamic management view (DMV) for basic diagnosis, such as querying sys. dm_tran_locks to learn about the lock status and query sys. dm_ OS _memory_cache_counters checks the cache quality and queries sys. dm_exec_requests and SYS. dm_exec_sessions to learn about active sessions and requests. Avoid using DMV that consumes a large amount of resources (for example, SYS. dm_tran_version_store needs to scan the entire version storage zone, which may lead to a large number of I/O) or using DMV with complex connections. For more information about performance, see the specific DMV documentation.

• Query the directory view.
• Basic DBCC commands, such as DBCC freeproccache, DBCC freesystemcache, DBCC dropcleanbuffers, and DBCC sqlperf. Do not run commands that consume a large amount of resources, such as DBCC checkdb, DBCC dbreindex, or DBCC shrinkdatabase.
• The statement kill <spid>. Based on the SQL server status, the kill command is not always successful. If it fails, the only option is to restart SQL Server. The following are general guiding principles.
  • Queryselect * from sys.dm_exec_sessions where session_id = <spid>To verify whether the spid has been terminated. If no row is returned, the session is terminated.

  • If the session is still running, run the queryselect * from sys.dm_os_tasks where session_id = <spid>To verify whether a task is assigned to this session. If a task exists, it is likely that the session is being terminated. Note that this operation may last for a long time or fail at all.
  • If there are no tasks in the SYS. dm_ OS _tasks associated with this session, but the session still appears in SYS. dm_exec_sessions after the kill command is executed, there is no available working thread. Select a currently running task (listed in SYS. dm_ OS _tasks view)sessions_id <> NULLAnd terminate the associated session to release the working thread. Please note that terminating a single session may not be enough and multiple sessions may need to be terminated.

DAC Port

SQL server listens to DAC on a dedicated TCP/IP Port. The error log contains the port number of the DAC to be listened on. By default, the DAC listener only accepts connections on the local port. For sample code for Activating remote administrator connection, see the remote admin connections option.

After the remote management connection is configured, the DAC listener is enabled immediately without restarting SQL Server.