Dedicated connection DAC for SQL Server database administrator

dac:dedicated Admin Connection

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.

How to enable the DAC feature dedicated Administrator Connection feature and considerations

1. Only the system administrator (sysadmin) role member can use the DAC to connect to the SQL Server (local) on-premises connection

2. There can only be one DAC in a single execute.

3. Using a DAC is often a way for DBAs to look up and troubleshoot SQL Server problems (when it's not normal to connect to a single body),

4, like perform Sp_who2, Kill SPID, DBCC SQLPERF, DBCC dropcleanbuffers ... When using the DAC connection, do not perform commands that consume a lot of resources, such as DBCC CHECKDB, DBCC SHRINKDATABASE. such as

5, use the DAC login to modify the system table or view the system table, you can modify the system table before SQL2000, to the SQL2005 began to limit you

SQL to open DAC

1 Use Master
2 GO
3 sp_configure ' show advanced options ', 1
4 GO
5 sp_configure ' remote admin connections ', 1
6 GO
7 RECONFIGURE with OVERRIDE
8 GO
9
10
A SELECT * from sys.configurations WHERE name = ' remote admin connections ' can also be opened in the perimeter application Configurator

Log on using the DAC at the command line
Sqlcmd Plus/A option dedicated management connection
SQLCMD/S joe/e/A
1>DBCC dropcleanbuffers
2>go

SQL for troubleshooting and diagnostics

1 SELECT * from sys.dm_tran_locks
2 SELECT * from Sys.dm_os_memory_cache_counters
3 SELECT * from sys.dm_exec_requests
4 SELECT * from sys.dm_exec_sessions

For example, query sys.dm_tran_locks to understand the lock status

Query Sys.dm_os_memory_cache_counters, check cache count

Query sys.dm_exec_requests and sys.dm_exec_sessions to learn about active sessions and requests.
Avoid using a DMV that consumes a lot of resources (for example, Sys.dm_tran_version_store needs to scan the entire version store and can cause a lot of I/O) or a DMV that uses a complex join

When using a DAC connection in SQL Server Management Studio, to select a new query or database engine query, you cannot use the default login box to log the DAC connection, the

is connected to the database engine, the DAC connection is not supported if the database engine is connected with a DAC.

Here's the port number that the DAC listens on

To understand the port number that the DAC listens on, you can look at the SQL error log
SQL error Log
News
Dedicated admin connection support is established for listening remotely on port 1434.

Other messages about the DAC error log:

News
Could not connect because the maximum number of ' 1 ' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must is dropped, either by logging of F or ending the process. [Client: 127.0.0.1]

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

Local and remote connections for the DAC:

If you configure SQL Server to accept remote management connections, you must start the DAC with an explicit port number:

Sqlcmd–stcp:<server>,<port>

sqlcmd/stcp:192.168.1.100,1434/u sa/p Test

The SQL Server error log lists the port number of the DAC, which by default is 1434.

If SQL Server is configured to accept only local DAC connections, connect using the following command and the loopback adapter:

sqlcmd–s127.0.0.1,1434

Or

Sqlcmd Plus/A option dedicated management connection
SQLCMD/S joe/e/A

Or

Or

Or

Summary: After my experiment, found whether you are using sqlcmd or SSMS, local connection or remote connection, you have to use this way

Sqlcmd–stcp:<server>,<port>

Local: sqlcmd–s127.0.0.1,1434

Remote: sqlcmd/stcp:192.168.1.100,1434/u sa/p Test

Some articles on the internet say that without the port number, enable SQL Browser service, you can connect to SQL Server, in fact, without adding 1434 port number, is not using the DAC to

connection to SQL Server, no 1434 port number is used only normal connection

--------------------------------------------------------------------------------

2013-11-30 Supplement:

Decompile the DAC DLL

In the following public DLL path

The DAC function should be to call C:\Program Files\Microsoft SQL under this path server\100\sdk\assemblies\microsoft.sqlserver.management.dac.dll

With Ilspy This tool to decompile, in fact sqlsrver many functional components are written in. Net

Unless some of the core functional components are in C + + or C, you will find that many DLLs can be ilspy with this. NET Decompile tool for anti-compilation

The microemulsion does not confuse their code, and it is difficult to understand the hierarchy and meaning of the Code without developing the documentation.

Example of invoking a DAC in C #

1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using Microsoft.SqlServer.Management.Dac;
6 using Microsoft.SqlServer.Management.Smo;
7 using Microsoft.SqlServer.Management.Common;
8 using System.Data.SqlClient;
9
Ten namespace ConsoleApplication15
11 {
Class Program
13 {
The static void Main (string[] args)
15 {
Try
17 {
String connectionString = "Data source=xxxxx,1433;initial catalog=master;integrated security=false; User Id=sa; Password=sa; ";
SqlConnection SqlConnection = new SqlConnection (connectionString);
Serverconnection conn = new serverconnection (sqlConnection);
Server DestServer = new server (conn);
Console.WriteLine (destServer.Information.Version);
23
24
Dacextractionunit dacunit = new Dacextractionunit (DestServer, "DB2", "DB2", New Version ("1.0.0.1"));
26
Dacunit.extract (@ "E:\DB2.dacpac");
Console.WriteLine ("Finish");
29}
catch (Exception ex)
31 {
Console.WriteLine (ex);
33
34}
Console.WriteLine ("Press any key to close");
Console.ReadLine ();
37
38}
39}
40}

Dedicated connection DAC for SQL Server database administrator