SQL Server - однопользовательский режим - сохранить, какой пользователь

Question

Вопрос

Есть ли способ при переводе базы данных в однопользовательский режим указать этого пользователя (в моем случае это текущий пользователь) и гарантировать, что даже после того, как этот пользователь отключится, один сеанс будет зарезервирован для этого определенного пользователя; то есть, это действительно однопользовательский, а не однопользовательский?

Фон

Чтобы дать некоторый контекст...

У нас есть несколько скриптов для обновления данных в наших тестовых средах. Недавно произошла ошибка с сообщением о том, что учетная запись пользователя, под которой запускаются сценарии, не имеет доступа к базе данных. При расследовании пользователь был системным администратором в базе данных; так что это явно не так. Тем не менее, я обнаружил, что скрипт помещает базу данных в Single User Режим. Глядя на активные сеансы в БД, я вижу, что один пользователь отличается от ожидаемой учетной записи (скорее это другая учетная запись службы, принадлежащая системе, которая периодически опрашивает эту базу данных). Мое предположение следующее произошло:

• Скрипт запускается как пользователь А
• Скрипт переводит БД в однопользовательский режим
• Скрипт выполняет некоторые запросы
• Скрипт закрывает текущее соединение во время выполнения некоторых других задач
• Пользователь B пытается подключиться к БД; успех, так как сеанс UserA теперь закрыт
• Сеанс UserB остается открытым (так как UserB - это сервис с пулами подключений, даже после завершения операции сеанс остается открытым).
• Пользователь A пытается восстановить соединение с БД; доступ запрещен, так как один сеанс занят пользователем B

Существуют различные возможные решения.

• Перепишите сценарий, чтобы сохранить постоянное соединение (потенциально много усилий, особенно из-за того, что я не знаком с кодом)
• Отключите все службы, которые могут пытаться подключаться с перебоями (что отрицательно сказывается на использовании однопользовательского режима / также много дополнительных усилий для изучения всех учетных записей, которые могут подключаться, и для поддержания этого)
• Добавьте в скрипт перехват для уничтожения конкурирующих SPID и, таким образом, восстановите однопользовательский сеанс (мне не нравится убивать SPID, так как это влияет на целостность транзакций)
• использовать магию (то есть увидеть принятый ответ на этот вопрос; надеюсь)

Обновить

Код использует PowerShell Invoke-SqlCmd, который создает и сбрасывает соединения для каждой команды запуска; поэтому, как только база данных переведена в однопользовательский режим, соединение сбрасывается. Я посмотрел на параметры этой команды, чтобы увидеть, есть ли варианты для объединения / сохранения соединений, но самое близкое, что я мог найти, было DedicatedAdministratorConnectionо котором, мне кажется, следует избегать ( http://www.brentozar.com/archive/2011/08/dedicated-admin-connection-why-want-when-need-how-tell-whos-using/) несмотря на мои первоначальные надежды.

Answer 1

Извините, нет магии. Там нет нативного решения для того, что вам нужно. SQL Server гарантирует, что сеанс, который задает однопользовательский режим, является сеансом, который сохраняется. После завершения сеанса (отключение пользователя, разрыв сети и т. Д.) "Слот" теперь доступен для любого другого соединения, включая определенные фоновые рабочие потоки или обозреватель объектов SSMS. ЦАП не является хорошей идеей для чего-либо кроме спасательных операций.

Вы можете использовать sqlcmd для запуска сценариев или вызвать файл сценария из PS (invoke-sqlcmd -inputfile "c:\mysqlfile.sql" -serverinstance "имя_сервера \serverinstance" -database "mydatabase"), поэтому он выполняется скорее за один сеанс чем отдельные сеансы от PS.

Есть много других способов запретить пользователям подключаться к БД (триггеры, безопасность и т. Д.), Но они обычно рискованны тем, что вам нужно быть очень осторожным при реализации блоков и убедиться, что блоки безопасно и правильно удалены при сделанный. Это нетривиально, так как вам нужно учитывать потерю соединения или потерю экземпляра и то, как бороться с состоянием после возобновления или восстановления. Становится очень грязно, очень быстро, поэтому действуйте осторожно.