Виправлення конфігурації SQL Server в монопольному режимі

У цій статті ми розглянемо запуск MS SQL Server в режимі мінімальної конфігурації (Аналог Safe Mode), використання режиму виділеного адміністративного підключення ЦАП (виділене адміністративне підключення), А також сценарій запуску 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 буде налаштована на мінімально допустимий розмір

Примітка. Режим мінімальної конфігурації використовується тільки для виправлення помилок конфігурації. Після внесення правок сервер повинен бути перезапущений без параметра -f.

У монопольному режимі ви можете відновити пошкоджену базу даних 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:DBTempdb.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 через фаєрвол, а також переконайтеся, що його не займають інші адміністратори або інші локальні програми. Ви також можете використовувати параметр -м із зазначенням додатки, з якого ви будете з’єднуватися, наприклад, -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 testnodenode2
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.

замітка. Я розміщую tempdb на цей диск тимчасово, так як найчастіше після аварійного відновлення роботи сервера ви захочете переналаштувати tempdb, розбивши його на кілька файлів даних і змінити шлях зберігання.

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

За допомогою цього режиму ви також можете поправити інші проблеми з конфігурацією, наприклад, відключити тригер або процедуру.

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

"C:Program FilesMicrosoft SQL ServerMSSQL15.NODE2MSSQLBinnsqlservr.exe" –c -f -sNODE2"

Це запустить примірник NODE2 в safe mode. параметр -з прискорює завантаження SQL Server при запуску з командного рядка. Ви також можете вказати параметр -m"SQL Server Management Studio" для обмеження підключень тільки з SQL студії або -mSQLCMD для підключення тільки з sqlcmd.

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

Виділене адміністративне підключення – це з’єднання в 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

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

Є два варіанти з’єднання через DAC: за допомогою Студія управління SQL Server і за допомогою sqlcmd. Офіційно, DAC з’єднання не підтримуються SSMS, і при спробі підключитися до сервера через DAC, ви отримаєте помилку:

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

Але є обхідний шлях, підключення через New Query. Для цього в SQL Studio натисніть на New Query і введіть адресу сервера в форматі admin:serverinstance. “Admin:” перед адресою сервера значить, що буде використовуватися DAC підключення.

Після того як ви натиснете Connect, ви можете отримати подібну помилку:

Failed to connect to server ADMIN:servernamenode

Проігноруйте цю помилку. Потрібно звернути увагу на рядок стану запиту в SSMS

Якщо ви бачите стан Connected на сервер з префіксом ADMIN :, значить ви з’єдналися через DAC.

Щоб з’єднатися з вашим сервером SQL через виділене адміністративне підключення в sqlcmd, використовуйте команду:

sqlcmd -E -A -S testnode2node2

• -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.