Диагностическое соединение для администраторов баз данных

Статья
06/13/2017

SQL Server предоставляет специальное диагностическое подключение для администраторов, если стандартные подключения к серверу недоступны. Это диагностическое подключение позволяет администратору получить доступ к SQL Server для выполнения диагностических запросов и устранения неполадок, даже если SQL Server не отвечает на стандартные запросы на подключение.

Это выделенное подключение администратора (DAC) поддерживает шифрование и другие функции безопасности SQL Server. Выделенное административное соединение позволяет только изменять контекст пользователя на другого пользователя с правами администратора.

SQL Server выполняет каждую попытку успешного подключения DAC, но в чрезвычайных ситуациях она может быть не успешной.


Применимо к: SQL Server (SQL Server 2008 до текущей версии), База данных SQL версии 12.

Соединение с помощью выделенного административного соединения

По умолчанию, соединение разрешено только из клиента, запущенного на сервере. Сетевые подключения не разрешаются, пока они не настроены с помощью хранимой процедуры sp_configure с параметром remote admin connections.

Только члены роли системного администратора SQL Server могут подключаться с помощью DAC.

Выделенное административное соединение доступно и поддерживается через программу командной строки sqlcmd со специальным административным параметром (-A). Дополнительные сведения об использовании sqlcmd см. в разделе Использование программы sqlcmd с переменными скрипта. Вы также можете подключить префикс к admin:имени экземпляра в формате sqlcmd -Sadmin:<instance_name.> Кроме того, можно инициировать DAC из Редактор запросов SQL Server Management Studio, подключився к admin:<instance_name.>

Ограничения

Так как выделенное административное соединение существует только для диагностики проблем на сервере в редких обстоятельствах, у подключения есть некоторые ограничения.

Чтобы гарантировать наличие ресурсов, доступных для подключения, для каждого экземпляра SQL Server разрешено только один DAC. Если выделенное административное соединение уже активно, любой новый запрос на соединение через DAC отклоняется с ошибкой 17810.
Чтобы сохранить ресурсы, SQL Server Express не прослушивает порт DAC, если только не запущен флаг трассировки 7806.
Сначала выделенное административное соединение подключается к базе данных по умолчанию, связанной с именем входа. После успешного соединения можно подключиться к базе данных master. Если база данных по умолчанию находится в режиме вне сети или недоступна по другой причине, соединение вернет ошибку 4060. При этом соединение будет успешным, если вместо базы данных по умолчанию подключиться к базе данных master с помощью следующей команды:

sqlcmd -A -d master

Рекомендуется подключиться к базе данных master с помощью DAC, так как master гарантированно будет доступен, если запущен экземпляр ядро СУБД.
SQL Server запрещает выполнение параллельных запросов или команд с помощью DAC. Например, ошибка 3637 возникает при выполнении через выделенное административное соединение любой из следующих инструкций:
- RESTORE
- BACKUP
Через выделенное административное соединение гарантированно доступны только ограниченные ресурсы. Не используйте DAC для выполнения ресурсоемких запросов (например, сложного соединения в большой таблице) или запросов, которые могут блокироваться. Это позволяет обезопасить выделенное административное соединение от осложнения любыми существующими проблемами на сервере. Чтобы избежать сценариев, которые могут приводить к блокировке, следует при возможности запускать запросы, которые могут вызвать блокировку, на уровне изоляции моментального снимка. В противном случае следует установить уровень изоляции транзакций READ UNCOMMITTED и малое значение LOCK_TIMEOUT, например 2000 миллисекунд. Можно также использовать оба способа одновременно. Это позволит предотвратить блокировку сеанса выделенного административного соединения. Однако в зависимости от состояния, в который находится SQL Server, сеанс DAC может быть заблокирован на блоке. Возможно, удастся прекратить сеанс выделенного административного соединения с помощью комбинации клавиш CTRL-C, но это не гарантируется. В этом случае единственным вариантом может быть перезапуск SQL Server.
Чтобы гарантировать подключение и устранение неполадок с DAC, SQL Server резервирует ограниченные ресурсы для обработки команд, выполняемых в DAC. Этих ресурсов обычно хватает только для простых диагностических функций и устранения неполадок, которые приведены ниже.

Теоретически можно запустить любую инструкцию Transact-SQL, которая не должна исполняться параллельно через выделенное административное соединение, но корпорация Майкрософт настоятельно рекомендует ограничиться применением следующих команд диагностики и устранения неполадок.

Запрос таких динамических административных представлений (DMV) для базовой диагностики, как sys.dm_tran_locks для состояния блокировки, sys.dm_os_memory_cache_counters для проверки работоспособности кэша, а sys.dm_exec_requests и sys.dm_exec_sessions для активных сеансов и запросов. Старайтесь не использовать DMV, потребляющие много ресурсов (например, представление sys.dm_tran_version_store полностью проверяет хранилище версий, что может привести к резкому увеличению объема операций ввода-вывода) или использующие сложные соединения. Сведения о влиянии на производительность см. в документации к конкретному динамическому административному представлению.
Запрос представлений каталога.
Основные команды DBCC, например DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE, DBCC DROPCLEANBUFFERS, а также DBCC SQLPERF. Не выполняйте такие ресурсоемкие команды, как DBCC CHECKDB, DBCC DBREINDEX или DBCC SHRINKDATABASE.
Команда Transact-SQL KILL*<spid>*. В зависимости от состояния SQL Server команда KILL может не всегда выполняться; Затем единственным вариантом может быть перезапуск SQL Server. Рассмотрим несколько общих правил.
- С помощью запроса SELECT * FROM sys.dm_exec_sessions WHERE session_id = <spid>убедитесь, что SPID был действительно отключен. Если строки не возвращаются, значит, сеанс был остановлен.
- Если сеанс продолжается, проверьте с помощью запроса SELECT * FROM sys.dm_os_tasks WHERE session_id = <spid>наличие задач, назначенных для этого сеанса. Если задача присутствует, то, скорее всего, сеанс закрывается в настоящий момент. Заметьте, что это может занять немало времени и завершиться неуспешно.
- Если в sys.dm_os_tasks нет задач, связанных с данным сеансом, но сеанс остается в sys.dm_exec_sessions после выполнения команды KILL, это означает, что отсутствует доступный рабочий процессор. Чтобы освободить рабочий поток, выберите одну из текущих задач (задача в представлении sys.dm_os_tasks со значением sessions_id <> NULL) и остановите связанный с ней сеанс. Заметьте, что остановка одного сеанса может оказаться недостаточной: может потребоваться остановить несколько сеансов.

Порт выделенного административного соединения

SQL Server прослушивает DAC через TCP-порт 1434, если он доступен или tcp-порт динамически назначается при запуске ядро СУБД. Журнал ошибок содержит номер порта, на котором ожидается выделенное административное соединение. По умолчанию, выделенное административное соединение ожидается только на местном порте. Образец кода, активирующего удаленные административные соединения, см. в разделе Параметр конфигурации сервера "remote admin connections".

После настройки подключения удаленного администрирования прослушиватель DAC включен без необходимости перезапуска SQL Server, а клиент теперь может подключиться к DAC удаленно. Вы можете включить прослушиватель уровня данных для удаленного приема подключений, даже если SQL Server не отвечает, сначала подключився к SQL Server с помощью DAC локально, а затем выполнить хранимую процедуру sp_configure, чтобы принять подключение из удаленных подключений.

В кластерных конфигурациях выделенное административное соединение по умолчанию выключено. Обеспечить доступ к удаленным соединениям средству прослушивания DAC пользователи могут с помощью хранимой процедуры sp_configure с параметром remote admin connection. Если SQL Server не отвечает, а прослушиватель DAC не включен, может потребоваться перезапустить SQL Server для подключения к DAC. Поэтому корпорация Майкрософт рекомендует включать вариант конфигурации remote admin connections в кластеризованных системах.

Порт DAC назначается динамически SQL Server во время запуска. При подключении к экземпляру по умолчанию DAC избегает использования запроса протокола разрешения SQL Server (SSRP) к службе браузера SQL Server при подключении. Сначала выполняется попытка подключиться через TCP-порт 1434. В случае ошибки следует вызов SSRP на получение порта. Если браузер SQL Server не прослушивает запросы SSRP, запрос подключения возвращает ошибку. Обратитесь к журналу ошибок, чтобы найти номер порта, на котором ожидается выделенное административное соединение. Если SQL Server настроен для приема подключений удаленного администрирования, DAC необходимо инициировать с явным номером порта:

sqlcmd-Stcp: <server,port><>

Журнал ошибок SQL Server содержит номер порта для DAC, который по умолчанию равен 1434. Если SQL Server настроено только для принятия локальных подключений DAC, подключитесь с помощью адаптера loopback с помощью следующей команды:

sqlcmd-S127.0.0.1,1434

Пример

В этом примере администратор видит, что сервер URAN123 не отвечает, и пытается определить неполадку. Для этого пользователь активирует программу командной строки sqlcmd и подключается к серверу URAN123 с помощью ключа -A , чтобы обозначить выделенное административное соединение.

sqlcmd -S URAN123 -U sa -P <xxx> -A

Теперь администратор может запускать запросы для определения проблемы и, возможно, прекращения не отвечающих сеансов.

Использование программы sqlcmd с переменными скрипта

Программа sqlcmd

SELECT (Transact-SQL)

sp_who (Transact-SQL)

sp_lock (Transact-SQL)

KILL (Transact-SQL)

DBCC CHECKALLOC (Transact-SQL)

DBCC CHECKDB (Transact-SQL)

DBCC OPENTRAN (Transact-SQL)

DBCC INPUTBUFFER (Transact-SQL)