SQL Server dedicated Administrator connection (dedicated admin Connection (DAC))

SQL Server dedicated Administrator connection (dedicated admin Connection (DAC))

Only members of the SQL Server sysadmin role can use a DAC connection. By default, connections can only be established from clients running on the server.

Open SSMs, in the Connect to Server window, select Cancel, and then select the File menu, drop-down menu, select "New", "Database Engine Query".

650) this.width=650; "title=" clip_image001 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "Alt=" Clip_ image001 "src=" http://s3.51cto.com/wyfs02/M02/86/DB/wKiom1fNNI_SxP2iAABWVlqNeuk383.png "border=" 0 "height=" 392 "/ >

Enter "admin:." and click "Connect".

650) this.width=650; "title=" clip_image002 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "Alt=" Clip_ image002 "src=" http://s3.51cto.com/wyfs02/M01/86/DA/wKioL1fNNJCxmq-CAABafoX8NIo342.png "border=" 0 "height=" 309 "/ >

The network-connected DAC is not allowed by default, and the remote admin connections option needs to be configured via sp_configure.

Let's take a look at the default values for the configuration:

SELECT * from sys.configurations WHERE name = ' Remote admin connections '

Or

sp_configure ' remote admin connections '

650) this.width=650; "title=" clip_image003 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "Alt=" Clip_ image003 "src=" http://s3.51cto.com/wyfs02/M02/86/DA/wKioL1fNNJGBxzydAAARvsqnHNc781.png "border=" 0 "height=" 47 "/ >

Value defaults to 0, indicating that only local connections are allowed to use the DAC. A maximum of 1 indicates that only one remote management connection is running.

--Enable remote DAC connection sp_configure ' remote admin connections ', 1; Goreconfigure; GO

Output:

Configuration option ' remote admin connections ' changed from 0 to 1. Run The RECONFIGURE statement to install.

The SQL Server Browser service is then turned on, and the firewall allows access to TCP 1434 ports.

We establish a DAC query connection from SSMS on another server, select the File menu, and select "New" and "Database Engine query" from the drop-down menu.

650) this.width=650; "title=" clip_image004 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "Alt=" Clip_ image004 "src=" http://s3.51cto.com/wyfs02/M02/86/DB/wKiom1fNNJKhrFCxAABa4wNF93k039.png "border=" 0 "height=" 310 "/ >

Enter the domain name or IP.

When the DAC is connected in SSMs, it can only be opened by creating a query window. When SQL Server is unable to establish a database connection due to insufficient system resources or other exceptions, you can use the System-reserved DAC to connect to the database for troubleshooting and troubleshooting. A DAC can use only limited resources. Do not use a DAC to run a resource-intensive query, or you may have serious blocking.

Another way to open it is to provide support for the DAC by using a special administrator switch (-a) from sqlcmd at the command line interface.

Local DAC connection:

650) this.width=650; "title=" clip_image005 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "Alt=" Clip_ image005 "src=" http://s3.51cto.com/wyfs02/M02/86/DA/wKioL1fNNJPx585lAAAhn4garjs002.png "border=" 0 "height=" 283 "/ >

Remote DAC connection:

650) this.width=650; "title=" clip_image006 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "Alt=" Clip_ image006 "src=" http://s3.51cto.com/wyfs02/M02/86/DB/wKiom1fNNJPRSVA_AAAjDziNl68865.png "border=" 0 "height=" 281 "/ >

"SQL Server Internals" has the following words:

“

SQL Server maintains a set of tables that store information on all objects, data types, constraints,confguration option s, and resources available to SQL Server. In SQL Server, these tables is called the system base tables. Some of the system base tables exist only in the master database and contain system-wide information; Others exist in every database (including master) and contain information on the objects and resources belonging to tha T particular database. Beginning with SQL Server 2005, the system base tables aren "always visible by default" in master or any other database. You won ' t see them if you expand the Tables node in the Object Explorer in SQL Server Management Studio, and unless you is a system administrator, you won ' t see them if you execute the SP_HELP system procedure. If you log on as a system administrator and select from the catalog view called sys.objects (discussed shortly), you can s EE the names of all the system tables. For example, the FolloWing query returns all rows of output on my SQL Server instance:

Use master;

SELECT name from sys.objects

WHERE type_desc = ' system_table ';

But even as a system administrator, if you try to select data from one of the tables returned by the preceding query, you Get a 208 error, indicating the object name is invalid. The only-to-see the data in the system base tables are to make a connection using the dedicated Administrator conn Ection (DAC), which Chapter 2, "The Sqlos," explains in the section titled "The Scheduler." Keep in mind that the system base tables is used for internal purposes only within the Database Engine and aren ' t Intende D for general use. They is subject to the change, and compatibility isn ' t guaranteed. In SQL Server, three types of system metadata objects is intended for general use:compatibility views, Catalog View S, and Dynamic Management Objects.

”

For example, to connect a normal query connection in SSMs, enter:

SELECT * from Sys.sysrmtlgns;

Output:

MSG 208, Level A, State 1, line 1

Invalid object name ' Sys.sysrmtlgns '.

To establish a DAC connection, enter:

SELECT net_transport,auth_scheme,client_net_address from sys.dm_exec_connections WHERE [email protected] @spid; SELECT * from Sys.sysrmtlgns; SELECT * from Sys.syslnklgns;

650) this.width=650; "title=" clip_image007 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "alt=" clip_image007 "src=" Http://s3.51cto.com/wyfs02/M01/86/DA/wKioL1fNNJST0oAGAABoberZi8E493.png "border=" 0 "height=" 393 "/>

This article is from the SQL Server deep Dive blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1846482

SQL Server dedicated Administrator connection (dedicated admin Connection (DAC))