Исправление конфигурации SQL Server в монопольном режиме

В этой статье мы рассмотрим запуск MS SQL Server в режиме минимальной конфигурации (аналог Safe Mode), использование режима выделенного административного подключения DAC (Dedicated Admin Connection), а также сценарий запуска SQL Server без tempdb (исходная база повреждена или недоступна) и ручное восстановление этой базы данных.

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

Запуск SQL Server с минимальной конфигурацией, параметр -f

Если SQL Server не запускается из-за ошибок конфигурации:

• Отсутствует или повреждена база tempdb;
• Ошибка в триггере или процедуре, из-за которой невозможно попасть на SQL Server;
• Любые другие ошибки, связанные с конфигурацией SQL Server.

В этом случае необходимо запустить SQL Server в режиме минимальной конфигурации или Safe Mode. Для этого нужно добавить параметр -f в строку запуска экземпляра.

В этом режиме MSSQL запускается с определенной конфигурацией:

• Сервер запускается в однопользовательском (монопольном) режиме single user
• Хранимые процедуры, настроенные на запуск при старте сервера не запускаются
• База tempdb будет сконфигурирована на минимально допустимый размер

В монопольном режиме вы можете восстановить повреждённую базу данных master и другиен системные базы данных.

Восстановление служебной базы данных tempdb через safe mode

Для примера мы возьмем сценарий, когда файлы базы tempdb на дисковом хранилище полностью утеряны и SQL Server не запускается.

В логах MSSQL при отсутствующей tempdb будут ошибки вида:

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘F:\DB\Tempdb.mdf’

Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

1. По RDP соединитесь с сервером, где находится нужный вам экземпляр MSSQL;
2. Откройте SQL Server Configuration Manager, в менеджере зайдите в свойства экземпляра -> Startup Parameters и добавьте параметр –f;
   Также можно использовать команду NET START MSSQLSERVER /f для запуска SQL Server в режиме минимальной конфигурации.
   
3. Перезагрузите экземпляр SQL Server;
   Совет. После перезагрузки экземпляра, сервер будет работать только с одним соединением. Чтобы это соединение ничего не заняло, выключите SQL Server Agent, ограничьте доступ к SQL Server через фаервол, а также убедитесь, что его не занимают другие администраторы или другие локальные приложения. Вы также можете использовать параметр -m с указанием приложения, с которого вы будете соединяться, например, -mSQLCMD или -m"SQL Server Management Studio" . Поскольку в SQL Server Configuration Manager невозможно задать параметры с кавычками, параметр -m"SQL Server Management Studio" , вы сможете использовать его только запуская SQL Server из командной строки.
4. Запустите SQL Server Management Studio, слздайте новый запрос через кнопку New Query;

Если вы получите подобную ошибку, значит кто-то уже занял соединение.

Cannot connect to testnode\node2
Login failed for user username. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

Если всё сделано правильно, вы получите доступ к серверу.

Теперь нам нужно изменить пути к испорченной базе tempdb. Выполните следующие T-SQL команды:

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'E:\templog.ldf');
GO

Это задаст новую конфигурацию для tempdb. Файлы tempdb будут созданы на диске E:\.

The file tempdev/templog has been modified in the system catalog. The new path will be used the next time the database is started.

Удалите параметр -f из диспетчера конфигураций SQL Server и перезагрузите экземпляр.

С помощью этого режима вы также можете поправить другие проблемы с конфигурацией, например, отключить триггер или процедуру.

Также для запуска SQL Server в безопасном режиме можно использовать командную строку:

"C:\Program Files\Microsoft SQL Server\MSSQL15.NODE2\MSSQL\Binn\sqlservr.exe" –c -f -sNODE2"

Это запустит экземпляр NODE2 в safe mode. Параметр -с ускоряет загрузку SQL Server при запуске из командой строки. Вы также можете указать параметр -m"SQL Server Management Studio" для ограничения подключений только из SQL студии или -mSQLCMD для подключения только из sqlcmd .

Режим административного подключения (Dedicated Admin Connection) в SQL Server

Dedicated Admin Connection – это соединение в SQL Server, которое резервируется для подключения администратора. DAC используется только в том случае, если сервер находится под массивной нагрузкой, соединиться обычными способами не получается. для выхода их такой ситуации приходилось перезапускать сервер с потерей всех текущих сеансов пользователей.

По умолчанию, соединиться с помощью DAC можно только с компьютера, на котором запушен SQL Server. Разрешить удаленные DAC соединения можно через T-SQL (можно сделать это сразу после установки SQL Server).

Для начала проверим настройку remote admin connections:

SELECT value
FROM sys.configurations
where name like '%remote admin connections%'

Если в результате вы получили value = 0, то значит, удаленный DAC выключен. Чтобы включить его, выполните:

EXEC sp_configure 'remote admin connections', 1;
GO
RECONFIGURE
GO

Перезагружать экземпляр не надо, конфигурация применяется динамически.

Dedicated administrator connections are not supported via SSMS as it established multiple connections by design.

Но есть обходной путь, подключение через New Query. Для этого в SQL Studio нажмите на New Query и введите адрес сервера в формате admin:server\instance . “admin:” перед адресом сервера значит, что будет использоваться DAC подключение.

После того как вы нажмете Connect, вы можете получить подобную ошибку:

Failed to connect to server ADMIN:servername\node

Проигнорируйте эту ошибку. Нужно обратить внимание на строку состояния запроса в SSMS

Если вы видите состояние Connected на сервер с префиксом ADMIN:, значит вы соединились через DAC.

Чтобы соединиться с вашим сервером SQL через выделенное административное подключение в sqlcmd, используйте команду:

sqlcmd -E -A -S testnode2\node2

• -E – использование trusted connection (Windows аутентификация);
• -A – использовать DAC подключение;
• -S имя инстанса sql в формате server\instance

Если всё сделано правильно, вы соединитесь с сервером

Чтобы убедиться, что вы используете DAC, можно выполнить T-SQL запрос

SELECT
COALESCE(ses.login_name, '???') AS 'DACUser',
ses.session_id,
ses.login_time AS 'Login time',
ses.status,
ses.original_login_name AS 'Login name'
FROM sys.endpoints AS en
JOIN sys.dm_exec_sessions ses
ON en.endpoint_id = ses.endpoint_id
WHERE en.name = 'Dedicated Admin Connection'

В результате вы получите логин, id сессии и время логина пользователя, использующего DAC.