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

Source: Internet
Author: User
Tags management studio sql server management sql server management studio

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.