Enable a dedicated administrator connection (DAC) for SQL Server 2008

Reference: http://technet.microsoft.com/zh-cn/library/ms178068 (v=sql.105). aspx

Problem:

A DBA who interned in our company asked me how to turn on the DAC for SQL Server 2008. At first I wanted to tell him directly, but the wiser option was to share the document options. This article will tell you how to open a DAC in SQL Server 2008.

Solution:

Microsoft added the new DAC feature in SQL Server 2005. The database administrator can use this attribute to connect to the database when the database is not responding to a normal connection. Once connected, DBAs can debug and end any problematic process.

The DAC allows DBAs to connect to a DB instance and execute SQL commands to debug and determine errors, in contrast, restarting the server may cause the server to crash or cause more problems. In SQL Server 2005 and later versions, the DAC is not turned on by default. It is a good practice to turn on the DAC in each of the installed instances of SQL Server 2005 or SQL Server 2008. This will help you debug the problem when you are not responding with a normal connection. However, only one DAC connection is allowed at a time for SQL Server 2005 and later versions.

Enable DAC with TSQL in SQL Server 2008

Execute the following SQL to enable remote use of the DAC:

Use mastergo/* 0 = Allow Local Connection, 1 = Allow Remote connections*/sp_configure ' remote admin Connections ', 1 gorec Onfigurego

Enable the DAC in SQL Server 2008 using SQL Server Management Studio

DBAs can also use SSMS to enable DAC features. Right-click the DB instance and select the Facet option from the drop-down menu.

The view facet panel is turned on, and the area options bar selects "Surface Area Configuration". Modify the "remotedacenabled" of the selection interface to True.

Finally click "OK" to make the changes take effect.

Microsoft SQL Server provides a dedicated administrator connection (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. Both the sqlcmd utility and the SQL Server Management Studio include a DAC. You can only establish a connection from a client running on the server, and you are not allowed to establish a network connection.

To use SQL Server Management Studio with a DAC, you need to connect the query Editor to an instance of the SQL Server database engine by typing ADMIN: in front of the server name. Object Explorer cannot connect using a DAC.

Connecting to a server using a DAC

1. In SQL Server Management Studio, without opening the other DAC, click Database Engine Queryon the toolbar.

2. In the connect to Database Engine dialog box, in the server name box, type the name of the ADMIN: and server instance. For example, to connect to a server instance named acct\payable , type ADMIN:acct\payable.

3. Complete the Authentication section (provide credentials for members of the sysadmin group), and then click Connect.

   The connection will be established.

   If the DAC is already in use, the connection fails with an error indicating that the connection cannot be made.