Нередко при выполнении определенных задач по обслуживанию или обновлению администратору приходится прерывать все соединения пользователей с базой данных. Иногда причиной тому правила эксклюзивности при обновлении или стремление защитить целостность данных и не влиять на работу клиентов при миграции, когда нужно обеспечить корректность изменений. Завершить соединения с базой данных можно несколькими способами.
Вариант 1. Простой, но неисчерпывающий подход: перевести базу данных в автономный режим
При использовании этого метода мы просто переводим базу данных в автономный режим, а затем возвращаем ее в оперативный режим. Этот процесс прост, но он не завершится до тех пор, пока не будут закончены все текущие транзакции и закрыты все сеансы. Это не лучший подход к переводу базы данных в монопольный режим, и я не рекомендую его использовать.
ALTER DATABASE [имя базы данных] SET OFFLINE;
Вы можете выполнить отмену всех открытых транзакций и закрыть сеансы с помощью дополнительного предложения ROLLBACK IMMEDIATE, но помните, что администратору базы данных следует избегать команд, негативно влияющих на работу конечных пользователей:
ALTER DATABASE [имя базы данных] SET OFFLINE WITH ROLLBACK IMMEDIATE;
За:
- Транзакции завершаются перед разрывом соединения, если не выдана команда ROLLBACK IMMEDIATE; простота выполнения.
Против:
- В зависимости от открытых транзакций вам, возможно, придется ждать завершения автономной команды, если не включить ROLLBACK IMMEDIATE.
Открытые сеансы без активных транзакций не завершаются и не закрываются, если не задействовано ROLLBACK IMMEDIATE, поэтому технически этот вариант не позволяет достичь цели.
Рекомендуется избегать этого метода, если только у вас нет полного понимания того, как приложения и пользователи работают с базой данных, и уверенности, что перечисленные выше недостатки не помешают вам получить монопольный доступ.
Вариант 2. Динамическая инструкция SQL для завершения всех пользовательских сеансов базы данных
Можно воспользоваться динамическим административным представлением sys.dm_exec_sessions для идентификации всех пользовательских сеансов для определенной базы данных — или всех баз данных, если применяемые изменения охватывают область сервера, — и создать динамическую инструкцию KILL для каждого сеанса, возвращаемого из запроса, код которого приведен в листинге.
За:
- Возможности этого программного кода несколько шире, чем у первого варианта, и теперь в вашем распоряжении есть код (благодаря этой статье).
Против:
- Существует проблема промежутка времени между выполнением запроса для получения динамической инструкции SQL и запуском этой динамической инструкции. В этот период создаются новые сеансы, которые будут вне действия динамической инструкции SQL или, хуже того, могут быть выполнены, а значения session_id завершаемых сеансов могут быть назначены сеансам, не имеющим никакого отношения к базе данных, с которой вы работаете.
- Во время применения KILL выполняется откат сеансов, и пользователи могут думать, что их транзакции зафиксированы, хотя на самом деле произошла их отмена.
Вариант 3. Изменение базы данных на SINGLE_USER или RESTRICTED_USER
Существует три различных режима подключения пользователей к базам данных: MULTI_USER, SINGLE_USER и RESTRICTED_USER. Обычно база данных находится в режиме MULTI_USER, то есть несколько пользователей могут подключаться одновременно. В режиме SINGLE_USER база данных может обслуживать один сеанс, и, когда этот сеанс открыт, для базы данных не может быть организовано никаких других сеансов. В режиме RESTRICTED_USER любой пользователь, который является участником роли базы данных db_owner или участником роли сервера sysadmin или dbcreator, может подключиться к базе данных, но все остальные пользователи лишаются этой возможности. При переключении, например, первого режима все открытые сеансы, не относящиеся к привилегированным ролям, должны завершить работу, прежде чем будет выполнена инструкция ALTER DATABASE.
Программный код для каждого режима:
ALTER DATABASE [имя базы данных] SET SINGLE_USER | RESTRICTED_USER;
Если приемлемо выполнить отмену всех открытых транзакций базы данных, можно усовершенствовать приведенную выше команду, но помните о проблемах, уже упомянутых в отношении WITH ROLLBACK IMMEDIATE:
ALTER DATABASE [имя базы данных] SET SINGLE_USER | RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
По окончании вернитесь к режиму MULTI_USER с помощью команды:
ALTER DATABASE [имя базы данных] SET MULTI_USER;
За:
- Транзакции могут завершиться, прежде чем разрываются подключения (если не включено предложение WITH ROLLBACK IMMEDIATE).
- У привилегированных пользователей по-прежнему остается возможность подключения через RESTRICTED_USER. Если вы корректно назначили права, то у вас не будет конечных пользователей с уровнем разрешений, который обеспечил бы им непрерывный доступ. Единственные пользователи, которым нужен такой уровень доступа, — это администраторы баз данных и ИТ-персонал, непосредственно ответственный за администрирование среды обработки данных и часто выполняющий процесс обновления или миграции, ради ознакомления с которым вы и читаете эту статью.
Против:
- В зависимости от открытых транзакций вам, возможно, придется ждать завершения автономной команды, если вы не используете предложение WITH ROLLBACK IMMEDIATE.
- Если применяется параметр SINGLE_
USER, рекомендуется вставить его в сценарий обновления в начале сценария. В противном случае после закрытия сеанса, выполнившего инструкцию ALTER DATABASE… SET SINGLE_USER, конечный пользователь может получить контроль над базой данных, и вам не удастся подключиться или изменить ее, пока этот сеанс не будет закрыт и при условии, что никто другой не предпримет попытки подключения.
Дополнительные соображения
В зависимости от особенностей использования баз данных настоятельно рекомендуется в первую очередь везде, где возможно, ограничить подключения приложений. Есть вероятность, что пользовательские соединения будут восстановлены, если не пресечь попытки пользователей вернуться к базе данных после принятия описанных выше мер.
Данная задача — еще одно напоминание о том, что, имея дело с технологиями, вы никогда не работаете индивидуально. Это непременно коллективные усилия, когда успех всего проекта зависит от слаженных действий нескольких групп.
/*Для определенной базы данных */
SELECT 'KILL ' + CAST(session_id AS VARCHAR(10))
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND database_id = DB_ID('database name');
/* Для всех баз данных */
SELECT 'KILL ' + CAST(session_id AS VARCHAR(10))
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;