Нередко при выполнении определенных задач по обслуживанию или обновлению администратору приходится прерывать все соединения пользователей с базой данных. Иногда причиной тому правила эксклюзивности при обновлении или стремление защитить целостность данных и не влиять на работу клиентов при миграции, когда нужно обеспечить корректность изменений. Завершить соединения с базой данных можно несколькими способами.
Вариант 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 или RESTRICTEDJJSER
Существует три различных режима подключения пользователей к базам данных: MULTI USER, SINGLE USER и RESTRICTED USER. Обычно база данных находится в режиме MULTI_ USER, то есть несколько пользователей могут подключаться одновременно. В режиме SINGLE_USER база данных может обслуживать один сеанс, и, когда этот сеанс открыт, для базы данных не может быть организовано никаких других сеансов. В режиме RESTRICTED_USER любой пользователь, который является участником роли базы данных dbowner или участником роли сервера sysadmin или dbereator, может подключиться к базе данных, но все остальные пользователи лишаются этой возможности. При переключении, например, первого режима все открытые сеансы, нс относящиеся к привилегированным ролям, должны завершить работу, прежде чем будет выполнена инструкция 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, конечный пользователь может получить контроль над базой данных, и вам не удастся подключиться или изменить ее, пока этот сеанс не будет закрыт и при условии, что никто другой не предпримет попытки подключения.
Дополнительные соображения
В зависимости от особенностей использования баз данных настоятельно рекомендуется в первую очередь везде, где возможно, ограничить подключения приложений. Есть вероятность, что пользовательские соединения будут восстановлены, если не пресечь попытки пользователей вернуться к базе данных после принятия описанных выше мер. Данная задача — еше одно напоминание о том, что, имея дело с технологиями, вы никогда не работаете индивидуально. Это непременно коллективные усилия, когда успех всего проекта зависит от слаженных действий нескольких групп.