Исправление конфигурации 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 будет сконфигурирована на минимально допустимый размер
Примечание. Режим минимальной конфигурации используется только для исправления ошибок конфигурации. После внесения правок сервер должен быть перезапущен без параметра -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:\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 в режиме минимальной конфигурации.
    запуск sql serve в минимальной конфигурации - с параметром f
  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;

sql management studio подключение к

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

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)

 SQL Serve Login failed Server is in single user mode. Only one administrator can connect

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

подключение к sql server в безопасном режиме

Теперь нам нужно изменить пути к испорченной базе 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, разбив его на несколько файлов данных и изменить путь хранения.

восстановление tempdb в sql server

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

настройки файлов tempdb в 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.

sqlservr запуск базы в безопасном режиме

Режим административного подключения (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

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

Совет. Убедитесь, что в фаерволе сервера создано правила для DAC порта. По умолчанию, это порт 1434 (отличается от стандартного порта экземпляра MSSQL) . Вы можете проверить, прослушивается ли этот порт на сервере:

netstat –aon|findstr 1434

sql server upd порт 1434 netstat


Есть два варианта соединение через DAC: с помощью SQL Server Management Studio и с помощью sqlcmd. Официально, DAC соединения не поддерживаются SSMS, и при попытке подключиться к серверу через DAC, вы получите ошибку:

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

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

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

sql management studio подключение в режиме Dedicated Admin Connection

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

Failed to connect to server ADMIN:servername\node

Failed to connect to server в mssql management studio

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

admin режим подключения к mssqlserver

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

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

sqlcmd -E -A -S testnode2\node2

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

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

sqlcmd DAC подключение

Чтобы убедиться, что вы используете 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'

tsql получить статус 'edicated Admin Connection

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


Предыдущая статья Следующая статья


Комментариев: 0 Оставить комментарий

Оставить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Я не робот( Обязательно отметьте)