search
HomeDatabaseMysql TutorialSql Server 2005 专用管理员连接 (DAC) 使用技巧

                                        Sql Server 2005 专用管理员连接 (DAC) 使用技巧

 

 

1 什么是专用管理员连接?
SQL Server 2005 为管理员提供了一种特殊的诊断连接,以供在无法与服务器建立标准连接时使用。

2 专用管理员连接有什么用?
即使在 SQL Server 不响应标准连接请求时,管理员也可以使用这种连接访问 SQL Server,以便执行诊断查询并解决问题。
管理员可以通过 DAC 访问正在运行的 SQL Server Database Engine 实例来排除服务器的故障(即使该服务器已停止响应其他客户端连接)。

3 专用管理员连接使用方法
(1)利用sqlcmd
sqlcmd -Sqiangguoods -A
sqlcmd -Sadmin:qiangguoods
(2)SQL Server Management Studio 查询编辑器启动 DAC
admin:
如:ADMIN:QIANGGUOODS

4 专用管理员连接使用限制
(1)为了保证有可用的连接资源,每个 SQL Server 实例只允许使用一个 DAC。如果 DAC 连接已经激活,则通过 DAC 进行连接的任何新请求都将被拒绝,并出现错误 17810。
(2)DAC 最初尝试连接到与登录帐户关联的默认数据库。连接成功后,可以连接到 master 数据库。如果默认数据库离线或不可用,则连接返回错误 4060。但是,如果使用以下命令覆盖默认数据库,改为连接到 master 数据库,则连接会成功:
sqlcmd –A –d master
由于只要启动数据库引擎 实例,就能保证 master 数据库处于可用状态,因此建议使用 DAC 连接到 master 数据库。
(4)SQL Server 禁止使用 DAC 运行并行查询或命令。例如,如果使用 DAC 执行以下任何命令,都会生成错误 3637。
RESTORE或BACKUP
(5)DAC 只能使用有限的资源。请勿使用 DAC 运行需要消耗大量资源的查询(例如,对大型表执行复杂的联接)或可能造成阻塞的查询。这有助于防止将 DAC 与任何现有的服务器问题混淆。为避免潜在的阻塞情况,当需要运行可能造成阻塞的查询时,
请尽可能在基于快照的隔离级别下运行查询;否则,请将事务隔离级别设置为 READ UNCOMMITTED 和/或将 LOCK_TIMEOUT 值设置为较短的值(如 2000 毫秒)。这可以防止 DAC 会话被阻塞。
但是,根据 SQL Server 所处的状态,DAC 会话可能会在闩锁上被阻塞。可以使用 CNTRL-C 终止 DAC 会话,但不能保证一定成功。如果失败,唯一的选择是重新启动 SQL Server。
为保证连接成功并排除 DAC 故障,SQL Server 保留了一定的资源用于处理 DAC 上运行的命令。通常这些资源只够执行简单的诊断和故障排除功能,如下所示。
为了保留资源,DAC 连接在 SQL Server 2005 Express Edition 中不可用。

5 如何启用远程DAC连接?
只有 SQL Server sysadmin 角色的成员可以使用 DAC 连接。默认情况下,只能从服务器上运行的客户端建立连接。除非通过 sp_configure 使用 remote admin connections 选项进行配置,否则不允许使用网络连接。
DAC 支持加密和 SQL Server 的其他安全性功能。DAC 只允许将用户上下文切换到其他管理用户。
默认情况下,DAC 仅侦听环回 IP 地址 (127.0.0.1) 端口 1434。
remote admin connections 设置的可能值如下:
0 - 指明仅允许本地连接使用 DAC
1 - 指明允许远程连接使用 DAC
--启用远程DAC连接
sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO
注意:配置远程管理连接之后,会立即启用 DAC 侦听器而不必重新启动 SQL Server,并且客户端可以立即远程连接到 DAC。

6 常用脚本
--利用sqlcmd
sqlcmd -Sqiangguoods -A
sqlc

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
MySQL: An Introduction to the World's Most Popular DatabaseMySQL: An Introduction to the World's Most Popular DatabaseApr 12, 2025 am 12:18 AM

MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.

The Importance of MySQL: Data Storage and ManagementThe Importance of MySQL: Data Storage and ManagementApr 12, 2025 am 12:18 AM

MySQL is an open source relational database management system suitable for data storage, management, query and security. 1. It supports a variety of operating systems and is widely used in Web applications and other fields. 2. Through the client-server architecture and different storage engines, MySQL processes data efficiently. 3. Basic usage includes creating databases and tables, inserting, querying and updating data. 4. Advanced usage involves complex queries and stored procedures. 5. Common errors can be debugged through the EXPLAIN statement. 6. Performance optimization includes the rational use of indexes and optimized query statements.

Why Use MySQL? Benefits and AdvantagesWhy Use MySQL? Benefits and AdvantagesApr 12, 2025 am 12:17 AM

MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.

Describe InnoDB locking mechanisms (shared locks, exclusive locks, intention locks, record locks, gap locks, next-key locks).Describe InnoDB locking mechanisms (shared locks, exclusive locks, intention locks, record locks, gap locks, next-key locks).Apr 12, 2025 am 12:16 AM

InnoDB's lock mechanisms include shared locks, exclusive locks, intention locks, record locks, gap locks and next key locks. 1. Shared lock allows transactions to read data without preventing other transactions from reading. 2. Exclusive lock prevents other transactions from reading and modifying data. 3. Intention lock optimizes lock efficiency. 4. Record lock lock index record. 5. Gap lock locks index recording gap. 6. The next key lock is a combination of record lock and gap lock to ensure data consistency.

What are common causes of poor MySQL query performance?What are common causes of poor MySQL query performance?Apr 12, 2025 am 12:11 AM

The main reasons for poor MySQL query performance include not using indexes, wrong execution plan selection by the query optimizer, unreasonable table design, excessive data volume and lock competition. 1. No index causes slow querying, and adding indexes can significantly improve performance. 2. Use the EXPLAIN command to analyze the query plan and find out the optimizer error. 3. Reconstructing the table structure and optimizing JOIN conditions can improve table design problems. 4. When the data volume is large, partitioning and table division strategies are adopted. 5. In a high concurrency environment, optimizing transactions and locking strategies can reduce lock competition.

When should you use a composite index versus multiple single-column indexes?When should you use a composite index versus multiple single-column indexes?Apr 11, 2025 am 12:06 AM

In database optimization, indexing strategies should be selected according to query requirements: 1. When the query involves multiple columns and the order of conditions is fixed, use composite indexes; 2. When the query involves multiple columns but the order of conditions is not fixed, use multiple single-column indexes. Composite indexes are suitable for optimizing multi-column queries, while single-column indexes are suitable for single-column queries.

How to identify and optimize slow queries in MySQL? (slow query log, performance_schema)How to identify and optimize slow queries in MySQL? (slow query log, performance_schema)Apr 10, 2025 am 09:36 AM

To optimize MySQL slow query, slowquerylog and performance_schema need to be used: 1. Enable slowquerylog and set thresholds to record slow query; 2. Use performance_schema to analyze query execution details, find out performance bottlenecks and optimize.

MySQL and SQL: Essential Skills for DevelopersMySQL and SQL: Essential Skills for DevelopersApr 10, 2025 am 09:30 AM

MySQL and SQL are essential skills for developers. 1.MySQL is an open source relational database management system, and SQL is the standard language used to manage and operate databases. 2.MySQL supports multiple storage engines through efficient data storage and retrieval functions, and SQL completes complex data operations through simple statements. 3. Examples of usage include basic queries and advanced queries, such as filtering and sorting by condition. 4. Common errors include syntax errors and performance issues, which can be optimized by checking SQL statements and using EXPLAIN commands. 5. Performance optimization techniques include using indexes, avoiding full table scanning, optimizing JOIN operations and improving code readability.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
WWE 2K25: How To Unlock Everything In MyRise
3 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment