SQL 2005 Tips for using a dedicated administrator connection (DAC) and ways to modify system tables _mssql2005

Server 2005 Dedicated Administrator connection (DAC) tips for using
1 What is a dedicated administrator connection?
SQL Server 2005 provides an administrator with a special diagnostic connection for use when it is not possible to establish a standard connection with the server.
2 What is the use of a dedicated administrator connection?
Even when SQL Server does not respond to standard connection requests, administrators can use this connection to access SQL Server to perform diagnostic queries and resolve problems.
The administrator can access the running SQL Server Database Engine instance through the DAC to troubleshoot the server (even if the server has stopped responding to other client connections).
3 Dedicated Administrator Connection usage method
(1) Using sqlcmd
Sqlcmd-sqiangguo\ods-a
Sqlcmd-sadmin:qiangguo\ods
(2) SQL Server Management Studio Query Editor start DAC
admin:< Instance Name >
such as: Admin:qiangguo\ods
4 Dedicated Administrator Connection usage restrictions
(1) To ensure that there are available connection resources, each instance of SQL Server is allowed to use only one DAC. If the DAC connection is active, any new requests that are connected through the DAC will be rejected with error 17810.
(2) The DAC initially attempted to connect to the default database associated with the login account. Once 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 overwrite the default database with the following command and connect to the master database instead, the connection succeeds:
Sqlcmd–a–d Master
Since the master database is guaranteed to be available as long as the database Engine instance is started, it is recommended that you use the DAC to connect to the master database.
(4) SQL Server prohibits running parallel queries or commands using the DAC. For example, if you use the DAC to execute any of the following commands, you will generate error 3637.
Restore or Backup
(5) The DAC can only use limited resources. Do not use the DAC to run queries that consume large amounts of resources (for example, perform complex joins to large tables) or queries that may cause blocking. This helps prevent the DAC from being confused with any existing server issues. To avoid potential blocking situations, when you need to run a query that might cause blocking,
If possible, run the query at the isolation level based on the snapshot; otherwise, set the transaction isolation level to READ uncommitted and/or set the LOCK_TIMEOUT value to a shorter value, such as 2000 milliseconds. This prevents the DAC session from being blocked.
However, depending on the state of SQL Server, the DAC session may be blocked on the latch. You can use CNTRL-C to terminate a DAC session, but there is no guarantee that it will succeed. If it fails, the only option is to restart SQL Server.
To ensure a successful connection and troubleshoot DAC failures, SQL Server reserves the resources to handle commands running on the DAC. Typically these resources are only sufficient to perform simple diagnostics and troubleshooting functions, as shown below.
To retain resources, the DAC connection is not available in SQL Server Edition Express.
5 How do I enable a remote DAC connection?
Only members of the SQL Server sysadmin role can use the DAC connection. By default, connections can only be established from clients running on the server. Network connections are not allowed unless configured by sp_configure using the remote admin connections option.
The DAC supports encryption and other security features of SQL Server. The DAC only allows the user context to be switched to other administrative users.
By default, the DAC listens only for loopback IP address (127.0.0.1) port 1434.
The possible values for remote admin connections settings are as follows:
0-Indicates that only local connections are allowed to use the DAC
1-Indicates allowing remote connections to use the DAC
--Enable remote DAC connections
sp_configure ' remote admin connections ', 1;
Go
Reconfigure;
Go
Note: After you configure a remote management connection, the DAC listener is enabled immediately without restarting SQL Server, and the client can connect to the DAC immediately remotely.
6 Common scripts
--Using sqlcmd
Sqlcmd-sqiangguo\ods-a
Sqlcmd-sadmin:qiangguo\ods
--Query Dynamic view
SELECT * FROM Sys.dm_os_memory_cache_counters
SELECT * FROM sys.dm_exec_requests
SELECT * FROM sys.dm_exec_sessions
--End session
KILL <spid>
SELECT * from sys.dm_exec_sessions where session_id = <spid>
SELECT * from sys.dm_os_tasks where session_id = <spid>
7 Common errors
Question (1)
C:\Documents and Settings\guoqiang>sqlcmd-s qiangguo\ods,1434
HResult 0x274d, Level 16, State 1
TCP provider: Unable to connect because the target machine is actively rejecting.
Sqlcmd: Error: Microsoft SQL Native Client: An error occurred while establishing a connection to the server. Connect to
The fact that SQL Server does not allow remote connections while SQL Server 2005 may cause failure.
。
Sqlcmd: Error: Microsoft SQL Native Client: The logon timeout has expired.
Solve:
Question (2)
Title: Connecting to the server------------------------------
Unable to connect to Admin:qiangguo\ods.
------------------------------
Additional Information:
Private administrator connections are not supported. (Objectexplorer)
Solve:
Question (3)
C:\Documents and Settings\guoqiang>sqlcmd-sadmin:qiangguo\ods
Sqlcmd: Error: Microsoft SQL Native Client: Due to delay during server connection opening, cannot
Complete the login process.
Solution: Try not to connect with DAC, use normal connection after successful, then use DAC to connect.
Such as:
C:\Documents and Settings\guoqiang>sqlcmd-s qiangguo\ods-a
Sqlcmd: Error: Microsoft SQL Native Client: Due to delay during server connection opening, cannot
Complete the login process.
C:\Documents and Settings\guoqiang>sqlcmd-s Qiangguo\ods
1> quit
C:\Documents and Settings\guoqiang>sqlcmd-s qiangguo\ods-a
1>
Question (4)
Title: Connecting to the server
------------------------------
Unable to connect to Admin:qiangguo\ods.
------------------------------
Additional Information:
A connection was made to the server successfully, but an error occurred during the logon process. (PROVIDER:TCP provider, error:0-the remote host forced the shutdown of an existing connection.) )
(Microsoft SQL Server, error: 10054)

Resolved: A DAC connection has been established, please close it before connecting again.
Article Source: http://www.diybl.com/course/7_databases/sql/sqlServer/200863/120812.html
================================================================ Author: Lansz | Can be reproduced, reprinted must be in the form of hyperlinks to indicate the original source of the article and author information and copyright notice
Links: http://www.lansz.com/html/2008/05/system_catalog_change_in_sql2005.html Questions:
When using SQL Server 2000, you may already be accustomed to using the following statement to modify the system table
EXEC sp_configure ' allow updates ', 1
Reconfigure with OVERRIDE;

--do some modification to system tables

EXEC sp_configure ' allow updates ', 0
Reconfigure with OVERRIDE; but if you still want to modify the system table by running the above statement in SQL Server 2005, you are wrong, although there is no error in running the above statement in SQL Server 2005. But when you try to modify the system tables, you get the following error message:
MSG 259, level, State 1, line 1
The Ad hoc updates to system catalogs are is not allowed. At this point, you can just check out the Bol allow in SQL Server 2005 and you'll find the following instructions:
Allow updates Option

Updated:14 April 2006

This option is still present in the sp_configure stored procedure, although it functionality is unavailable in Microsoft SQL Server (the setting has no effect). In SQL Server, direct updates to the system tables are not supported. It seems that in SQL Server 2005 allow updates is just a device, it doesn't work, and The system table cannot be modified directly either. But what if you really need to modify the system tables?
Fortunately, Microsoft is still not done, although allow updates can not be used, but there are other ways to modify the system table, that is: first in Single-user mode to start the SQL Server 2005 instance, and then use the DAC (dedicated Administrator connection/a dedicated admin connection) connects to the SQL instance and then can modify the system table directly without running any sp_configure.
The basic steps:
1. Boot to Single user mode
"C:\Program Files\Microsoft SQL Server\mssql.1\mssql\binn\sqlservr.exe"-m2, open SQL Server Management Studio, connect to Server dialog box, enter Admin:instancename in the server name, so you can use the DAC to log on to the startup SQL Server instance.
Friendly tips:
In addition, SQL Server 2005 strengthens the dictionary management function, if you want to modify the operation of the system table can have stored procedures to support the case must use stored procedures, do not directly modify the system table to maintain the consistency of the data dictionary.
Resources:
1, allow updates Option
2, Using a dedicated Administrator Connection
3. Starting SQL Server in Single-user Mode

================================================================
how to log on to a server using the SQL Server 2005 dedicated Administrator connection (DAC)

One what is DAC SQL Server 2005 provides an administrator with a special diagnostic connection for use when a standard connection cannot be established with the server. Even when SQL Server does not respond to standard connection requests, administrators can use this connection to access SQL Server to perform diagnostic queries and resolve problems. The command line Interface (SQLCMD) provides and supports this dedicated administrator connection (DAC) by using a special administrator switch (-a). How to use DAC to log on to server 1 native DAC logon command line execute SQLCMD-A-S SQL server name 2 remote DAC login 1 open Remote DAC option '-> ' Sql Server2005 '-> ' Configuration tool '-> ' Sq The perimeter configuration '-> ' DataBase Engine '-> ' DAC '-> ' Enable remote DAC ' 2 for the server Perimeter Application Configurator '-> ' feature to log on to a remote server sqlcmd-a-s 192.168.0.1 -U sa-p 1234563 logon, you can query the dynamic management view to diagnose the problem