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

  • Статья

Применимо к:база данных SQL ServerAzure SQL

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

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

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

Подключение к DAC

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

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

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

Установка DAC из SQL Server Management Studio:

  • Отключите все соединения со связанным экземпляром SQL Server, включая обозреватель объектов и все открытые окна запросов.

  • В меню выберите элементы Файл > Создать > Запрос ядра СУБД.

  • В диалоговом окне подключения в поле "Имя сервера" введите admin:<server_name> при использовании экземпляра по умолчанию или admin:<server_name>\<instance_name> при использовании именованного экземпляра.

Ограничения

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

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

  • Для экономии ресурсов SQL Server Express прослушивает порт выделенного административного соединения только при запуске с флагом трассировки 7806.

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

    sqlcmd -A -d master

    Рекомендуется подключаться к базе данных master через выделенное административное соединение, так как база данных master будет в любом случае доступна, если запущен экземпляр компонента ядра СУБД.

  • SQL Server запрещает выполнение параллельных запросов или команд через выделенное административное соединение. Например, ошибка 3637 возникает при выполнении через выделенное административное соединение любой из следующих инструкций:

    • RESTORE...

    • BACKUP...

  • Через выделенное административное соединение гарантированно доступны только ограниченные ресурсы. Не используйте выделенное административное соединение для запуска ресурсоемких запросов (например, сложного соединения для большой таблицы) или запросов, которые могут блокироваться. Это позволяет обезопасить выделенное административное соединение от осложнения любыми существующими проблемами на сервере. Чтобы избежать сценариев, которые могут приводить к блокировке, следует при возможности запускать запросы, которые могут вызвать блокировку, на уровне изоляции моментального снимка. В противном случае следует установить уровень изоляции транзакций READ UNCOMMITTED и малое значение LOCK_TIMEOUT, например 2000 миллисекунд. Можно также использовать оба способа одновременно. Это позволит предотвратить блокировку сеанса выделенного административного соединения. Но в зависимости от состояния SQL Server сеанс выделенного административного соединения может быть заблокирован с помощью кратковременной блокировки. Возможно, удастся завершить сеанс DAC с помощью клавиш CTRL+C, но это не гарантируется. В таком случае единственным вариантом остается перезапуск SQL Server.

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

Теоретически можно запустить любую инструкцию 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.

  • Команда KILL <spid> Transact-SQL: В зависимости от состояния 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) и остановите связанный с ней сеанс. Остановка одного сеанса может оказаться недостаточной: может потребоваться остановить несколько сеансов.

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

SQL Server для выделенных административных соединений прослушивает TCP-порт 1434, если он доступен, или TCP-порт, динамически назначаемый при запуске компонента Компонент Database Engine . Журнал ошибок содержит номер порта, на котором ожидается выделенное административное соединение. По умолчанию, выделенное административное соединение ожидается только на местном порте. Образец кода, активирующего удаленные административные соединения, см. в разделе Параметр конфигурации сервера "remote admin connections".

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

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

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

sqlcmd -S tcp:<server>,<port>

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

sqlcmd -S 127.0.0.1,1434

Совет

При подключении к База данных SQL Azure с помощью DAC необходимо указать в строке подключения имя базы данных, используя параметр -d.

Пример

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

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

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

Аналогичный пример для подключения к База данных SQL : здесь используется команда с параметром -d для указания базы данных.

sqlcmd -S serverName.database.windows.net,1434 -U sa -P <StrongPassword> -d AdventureWorks

См. также раздел