Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Проблема монопольного доступа к базе при RESTORE.  [new]
Начинучка
Guest
Всем привет!

Для обеспечения себе монопольного доступа к базе на период её восстановления выполняю команду: ALTER DATABASE [my_db_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
После чего выполняю восстановление. Иногда проходит успешно, но иногда кто-то успевает вклинится и захватить single-сессию.

Собственно, вопрос: как надежно заблокировать возможность подключения к базе, чтобы никто не успел вклинится между командами ALTER и RESTORE и захватить единственно-доступное соединение?

Восстановление выполняется примерно по такому сценарию:
use [master]
ALTER DATABASE [my_db_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [my_db_name]
   FROM DISK = N'....bak'
   with replace
      , recovery 
      , move 'db_file_name' to '....mdf'
      , move 'db_log_file' to '....ldf'
ALTER DATABASE [my_db_name] SET MULTI_USER


Сервер MS SQL Server 2012 SE
29 сен 17, 06:30    [20830656]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
aleks222
Guest
[quot Начинучка]

Собственно, вопрос: как надежно заблокировать возможность подключения к базе, чтобы никто не успел вклинится между командами ALTER и RESTORE и захватить единственно-доступное соединение?

Восстановление выполняется примерно по такому сценарию:
use [master]
ALTER DATABASE [my_db_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [my_db_name]
   FROM DISK = N'....bak'
   with replace
      , recovery 
      , move 'db_file_name' to '....mdf'
      , move 'db_log_file' to '....ldf'
ALTER DATABASE [my_db_name] SET MULTI_USER


Очевидно же, Ватсон!
use [my_db_name]
ALTER DATABASE [my_db_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
29 сен 17, 08:12    [20830728]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
Начинучка
Guest
aleks222
Очевидно же, Ватсон!
use [my_db_name]
ALTER DATABASE [my_db_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Если бы всё было так просто - вопроса бы не возникло... Это первое, что я попробовал. Но получил отлуп:
RESTORE cannot process database 'my_db_name' because it is in use by this session. It is recommended that the master database be used when performing this operation.
29 сен 17, 09:00    [20830787]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5162
Начинучка, я использую следующую команду:

alter database [...] set emergency with rollback immediate


отключает всех пользователей, кроме настырных и настойчивых db_owner-ов.
Если они своими коннектами мешают восстановлению, то блокирую логины на время восстановления и отстреливаю их сессии.
29 сен 17, 09:41    [20830861]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
iii2
Member

Откуда:
Сообщений: 202
Начинучка
aleks222
Очевидно же, Ватсон!
use [my_db_name]
ALTER DATABASE [my_db_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Если бы всё было так просто - вопроса бы не возникло... Это первое, что я попробовал. Но получил отлуп:
RESTORE cannot process database 'my_db_name' because it is in use by this session. It is recommended that the master database be used when performing this operation.

Гы. А я прибиваю всех перед подключением к базе.
SET DEADLOCK_PRIORITY HIGH;

SET ANSI_NULLS ON;

SET QUOTED_IDENTIFIER ON;

SET ANSI_PADDING ON;

DECLARE @query NVARCHAR(max)

SELECT @query = (
			SELECT N';Kill ' + CAST(p.spid AS NVARCHAR(10)) [text()]
			FROM master.dbo.sysprocesses p(NOLOCK)
			JOIN master..sysdatabases d(NOLOCK) ON p.[dbid] = d.[dbid]
			WHERE d.[name] = N'myBase'
			FOR XML PATH(N''), type
			).value('(./text())[1]','nvarchar(max)')
Execute sp_executesql @query 
;

Впрочем, всё равно некоторые ухитряются пролезть сразу после момента отключения.
29 сен 17, 10:34    [20831009]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
запустить в цикле и ждать пока звёзды сойдутся :)
29 сен 17, 10:40    [20831039]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
o-o
Guest
komrad
Начинучка, я использую следующую команду:


alter database [...] set emergency with rollback immediate


отключает всех пользователей, кроме настырных и настойчивых db_owner-ов.
Если они своими коннектами мешают восстановлению, то блокирую логины на время восстановления и отстреливаю их сессии.

а emergency-то зачем?
если надо оставить только овнеров, то restricted_user.
а emergency никак не db_user_access_option
29 сен 17, 10:48    [20831067]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
komrad
Member

Откуда: Msk -> Utrecht
Сообщений: 5162
o-o
komrad
Начинучка, я использую следующую команду:


alter database [...] set emergency with rollback immediate


отключает всех пользователей, кроме настырных и настойчивых db_owner-ов.
Если они своими коннектами мешают восстановлению, то блокирую логины на время восстановления и отстреливаю их сессии.

а emergency-то зачем?
если надо оставить только овнеров, то restricted_user.
а emergency никак не db_user_access_option

мне так нравится
29 сен 17, 11:21    [20831177]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
o-o
Guest
базы такие розовенькие делаются, прикольно, да :)
29 сен 17, 11:37    [20831251]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
xgsdhkjt
Guest
CREATE TRIGGER ON ALL SERVER FOR LOGON
ROLLBACK

https://docs.microsoft.com/ru-ru/sql/relational-databases/triggers/logon-triggers
http://sqlhints.com/2016/03/09/logon-triggers-in-sql-server/
https://sqlandme.com/2011/07/13/sql-server-login-auditing-using-logon-triggers/
29 сен 17, 11:56    [20831319]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
xgsdhkjt
CREATE TRIGGER ON ALL SERVER FOR LOGON
ROLLBACK

https://docs.microsoft.com/ru-ru/sql/relational-databases/triggers/logon-triggers
http://sqlhints.com/2016/03/09/logon-triggers-in-sql-server/
https://sqlandme.com/2011/07/13/sql-server-login-auditing-using-logon-triggers/

праивльно, пусть весь сервер стоит, пока одну базу ресторят
29 сен 17, 11:57    [20831327]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
o-o
Guest
это не только козлов.нет, это форум баранов.есть.йа.йа
29 сен 17, 11:59    [20831334]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
xgsdhkjt
Guest
o-o,

победить в скорости захвата базы у сервиса выдающего тысячи запросов в секунду - это как выиграть в лотерею

TaPaK,

sys.dm_exec_sessions, sys.dm_exec_connections - дают широкие возможности фильтрации кого отправить в ROLLBACK
29 сен 17, 12:13    [20831366]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
xgsdhkjt
o-o,

победить в скорости захвата базы у сервиса выдающего тысячи запросов в секунду - это как выиграть в лотерею

TaPaK,

sys.dm_exec_sessions, sys.dm_exec_connections - дают широкие возможности фильтрации кого отправить в ROLLBACK

и получить вечную тугосерю на логоне, алилуя, пятница на baranov.net
29 сен 17, 12:15    [20831367]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
invm
Member

Откуда: Москва
Сообщений: 9127
alter database ... set offline with rollback immediate;
restore database ...;
29 сен 17, 12:18    [20831372]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
Начинучка
Guest
invm,

а это мысль!!! :)


2ALL
не думал, что разведу тут пятничный срач и холивар... сорри :) ;)
29 сен 17, 12:31    [20831409]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30813
invm
alter database ... set offline with rollback immediate;
restore database ...;
Да, хорошее решение, как то в голову не приходило.
29 сен 17, 13:23    [20831590]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
xgsdhkjt
Guest
в редких, но всё же бывает, случаях и это не прокатывает за приемлемое время.
какая-нибудь долгая открытая транзакция, rollback для которой идёт NN часов
29 сен 17, 14:50    [20831890]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
o-o
Guest
xgsdhkjt
в редких, но всё же бывает, случаях и это не прокатывает за приемлемое время.
какая-нибудь долгая открытая транзакция, rollback для которой идёт NN часов

ну запейте с горя что ли...
будет вам уроком: не стОит бэкапить, когда люди массово переливают данные.
29 сен 17, 15:50    [20832104]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
xgsdhkjt
Guest
o-o,

тестовый сервер, никто в выходные не работает, кроме тестовых сервисов программистов...

Рабочий вариант: триггером отключить logon сервисов (по IP), рестартануть SQL для быстрого rollback (вдруг есть долгая транзакция), накатить боевую базу на тестовый сервер.

И все довольны в понедельник.
29 сен 17, 15:57    [20832135]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
o-o
Guest
xgsdhkjt
рестартануть SQL для быстрого rollback (вдруг есть долгая транзакция)
И все довольны в понедельник.

да надо же.
т.е. если кто-то терабайт данных менял в одной транзакции и вы выдали серверу with rollback immediate,
то будет откатываться сутки, ибо терабайт он и есть терабайт,
а вот если рестартануть, то за пару секунд терабайт данных откатит?
заклинание-то какое надо произнести, чтобы сработало? или оно так только в полнолуние бывает?
козлов.есть, баранов.есть, xgsdhkjt.есть
29 сен 17, 16:04    [20832157]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
sparrow
Member

Откуда: Россия, Красноярск.
Сообщений: 21759
Надо сначала удалить базу, там есть флажёк, отцепить всех пользователей.

Оно и скриптуется на раз.
29 сен 17, 17:02    [20832323]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
o-o
Guest
sparrow
Надо сначала удалить базу, там есть флажёк, отцепить всех пользователей.

Оно и скриптуется на раз.

Гавриленко, не пора ли ету ветку форума переименовать в ПТ-2?
чтобы название точнее отражало содержимое.
флажёк.ёк.ёк.
29 сен 17, 17:10    [20832341]     Ответить | Цитировать Сообщить модератору
 Re: Проблема монопольного доступа к базе при RESTORE.  [new]
sparrow
Member

Откуда: Россия, Красноярск.
Сообщений: 21759
Надо сначала удалить базу, там есть флажёк, отцепить всех пользователей.

Оно и скриптуется на раз.
29 сен 17, 17:48    [20832400]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить