Предисловие
Достаточно нередко можно встретить задачу об обеспечении отказоустойчивости СУБД MS SQL Server. Еще интереснее становится, когда нет лицензии Enterprise, а только Standard.
Сразу отметим, что рассматриваться лицензия Express не будет, т к по данному экземпляру есть существенные ограничения. Да, некоторые из них можно обойти. Например, максимальный размер БД в 10 ГБ легко решается путем декомпозиции большой БД на более маленькие (например, по какому-то признаку создавать новую БД, а в главной БД в представлениях объединять выборки из одних таблиц разных БД). Но отказоустойчивость в Express будет достигаться либо силами системного администратора, либо использованием собственно разработанного (или стороннего) ПО.
В данной статье будут вкратце разобраны все существующие стандартные технологии отказоустойчивости для MS SQL Server 2017 и будет рассмотрен пример реализации отказа наиболее подходящего унифицированного из них в лицензии Standard.
Краткий обзор
- AlwaysOn: распределение нагрузки среди всех участников, все участники должны быть по своим характеристикам максимально похожи между собой.
В синхронном режиме обеспечивается максимальная надежность передачи данных, однако скорость работы будет равняться скорости работы самого медленного участника. В асинхронном режиме обеспечивается максимальное быстродействие, однако могут возникать рассогласованности данных между участниками, что ведет к более сложной поддержке и вероятности потерять последние изменения в случае сбоя основного участника.Быстрота переключения в синхронном режиме-практически мгновенно и не требует вмешательства системного администратора и DBA, в асинхронном-зависит от текущего состояния БД-дублей, но обычно в среднем до 5 минут (также можно автоматизировать переключение силами одного DBA без привлечения системного администратора).
Признана Microsoft рекомендуемой технологией для БД. Доступна только с лицензией Enterprise от 2012 версии и выше.
- Кластеризация: несмотря на простоту настройки, данное решение ненадежно в виду узкого места в виде единого для всех хранилища данных. В случае выхода из строя хранилища данных, восстановление займет достаточно длительный промежуток времени-более 1 часа.
Доступна с лицензией Standard до 2008 версии и выше. - Репликация: любая репликация подразумевает создание системных триггеров на каждую таблицу-участницу, а репликация моментальных снимков будет достаточно сильно нагружать основную БД. Поэтому репликацию моментальных снимков можно делать только в минимальные часы нагрузки БД (например, ночью), что неприемлемо, т к необходим горячий резерв. Репликация слиянием сложна в сопровождении для некоторых системы (например, CRM, NAV), также она не подходит для 1С в виду частого изменения структур БД.
Репликация транзакциями возможна, но при наличии Enterprise-лицензии.
Доступна с лицензией Standard (слиянием и снимков БД) и Enterprise (транзакциями) до 2008 версии и выше. - Зеркалирование: возможна в любом режиме, однако как и при AlwaysOn, синхронный режим обеспечивает максимальную надежность и быстрое переключение, а асинхронный режим дает максимальную скорость работы с основной БД, но возможны рассогласованности данных между всеми участниками, а также переключение не будет мгновенным. Здесь переключение на уровне БД обеспечивает следящий сервер автоматически (при например, нагрузке ЦП более, чем в 50% на основном сервере) или средствами DBA. Подключение же к другому серверу обеспечивается силами системного администратора. Резервная БД при любом типе зеркалирования находится в режиме постоянного восстановления, в следствие чего к ней невозможно обратиться.
Режим восстановления БД-полный.Признана Microsoft устаревшей технологией для БД.
Доступна с лицензией Standard (в синхронном режиме) и Enterprise (в асинхронном режиме) до 2008 версии и выше. - Доставка журналов транзакций: есть 2 режима-постоянное восстановление на резервном сервере или восстановление с отсрочкой.
Первый режим переводит резервную БД (как и при зеркалировании) в режим постоянного восстанавления и к ней невозможно обратиться.
Второй же режим переводит резервную БД в режим восстановления периодически в момент накатывания обновлений (между накатываниями обновлений резервная БД доступна, но это возможно при условии, что экземпляры MS SQL Server одной версии).
Принцип работы прост:
- Периодически делается резервная копия журнала транзакций БД на источнике в общедоступную папку как источнику, так и резервному скулю (настраивается путь и расписание, по умолчанию-каждые 15 минут).
- Резервный скуль периодически копирует получившуюся резервную копию журнала транзакций БД себе в локальную доступную папку (настраивается путь и расписание, по умолчанию-каждые 15 минут).
- Резервный скуль восстанавливает журнал транзакций из скопированной резервной копии журнала транзакций (настраивается расписание, по умолчанию – каждые 15 минут).
Переключение можно автоматизировать на уровне БД-силами DBA, а на уровне подключений к серверу-на уровне системного администратора.
Также стоит отметить, что данный метод работает всегда в асинхронном режиме. Можно настроить несколько резервных БД.
Режим восстановления БД-полный или с неполным протоколированием
Доступна с лицензией Standard до 2008 версии и выше.
Вывод
Из выше описанных вариантов, больше подходит доставка журналов транзакций в лицензии Standard, т к его удобно использовать для плавного перехода с одного сервера на другой (например, при обновлении среды). Также доставка журналов транзакций достаточна проста в поддержке и проста в работе, а также всегда работает в асинхронном режиме, что не так сильно (как синхронный режим зеркалирования) нагружает БД. В любом случае зеркалирование приемлемо, если возможно настроить собственное автоматическое переключение, иначе возможны ложные переключения (например, когда ЦП основного сервера будет загружено более чем на 50%).
Для Enterprise – однозначно предлагается технология AlwaysOn.
Настройка отработки отказа при доставке журналов транзакций
Подробно по настройке доставки журналов транзакций описано здесь . Также данный процесс можно автоматизировать, написав собственную утилиту для повторного многократного использования, а также для возвращения на основной сервер после его починки в случае отработки отказа.
Разберем теперь один из возможных вариантов отработки отказа при доставке журналов транзакций на уровне СУБД.
Важно отметить, что данный метод подходит только для сервера, который является резервным только для одного экземпляра MS SQL Server, т к для нескольких возникает проблема в определении какие задания выполнять, а какие нет.
Опишем сначала саму последовательность действий:
- Выполнить все задания по копированию последних файлов с источника (при продуманной архитектуре, путь должен быть доступен даже при падении основного сервера)
- Выключить все задания по копированию файлов с источника
- Выполнить все задания по восстановлению БД по последним файлам с источника
- Выключить все задания по восстановлению БД по последним файлам с источника
- Сделать БД восстановленными и основными по доставкам журнала, но без получателя
- Создать полные резервные копии для БД
- Создать задания для создания резервных копий журналов транзакций БД
Ниже приведем пример реализации выше описанной последовательности в виде хранимой процедуры.
Стоит отметить, что здесь важно настроить логин (желательно доменный), под которым будут запускаться созданные задачи для создания резервных копий журналов транзакций.
CREATE PROCEDURE [srv].[RunLogShippingFailover]
@isfailover bit=1,
@login nvarchar(255)=N'ЛОГИН', --вход (лучше доменный), под которым будут выполняться созданные задачи по созданию резервных копий журналов транзакций БД
@backup_directory nvarchar(255)=N'ПУТЬ'--общедоступный путь для пересылки резервных копий журналов транзакций между экземплярами MS SQL Server (например, 'D:Shared')
AS
/*
Переводит резервный сервер в основной режим при потере боевого сервера при @isfailover=1 полностью автоматизировано
при @isfailover=0 ничего не проиходит-здесь нужно создать заново журнал доставки журналов с резервного на основной
, а затем сделать переход на основной сервер и далее уже там вновь настроить доставку журналов транзакций заново.
Считается, что данный резервный сервер принимает только от одного сервера резервные копии журналов транзакций
*/
BEGIN
--если совершается переход на резервный сервер, то выполнить все задания по копированию последних файлов с источника
if(@isfailover=1)
begin
select [job_id]
into #jobs
from [msdb].[dbo].[sysjobs]
where [name] like 'LSCopy%';
declare @job_id uniqueidentifier;
while(exists(select top(1) 1 from #jobs))
begin
select top(1)
@job_id=[job_id]
from #jobs;
begin try
EXEC [msdb].dbo.sp_start_job @job_id=@job_id;
end try
begin catch
end catch
delete from #jobs
where [job_id]=@job_id;
end
drop table #jobs;
end
--выключить все задания по копированию файлов с источника при переходе на резервный сервер
--включить все задания по копированию файлов с источника при возврате на боевой сервер
update [msdb].[dbo].[sysjobs]
set [enabled]=case when (@isfailover=1) then 0 else 1 end
where [name] like 'LSCopy%';
--если совершается переход на резервный сервер, то выполнить все задания по восстановлению БД по последним файлам с источника
if(@isfailover=1)
begin
select [job_id]
into #jobs2
from [msdb].[dbo].[sysjobs]
where [name] like 'LSRestore%';
while(exists(select top(1) 1 from #jobs2))
begin
select top(1)
@job_id=[job_id]
from #jobs2;
begin try
EXEC [msdb].dbo.sp_start_job @job_id=@job_id;
EXEC [msdb].dbo.sp_start_job @job_id=@job_id;
end try
begin catch
end catch
delete from #jobs2
where [job_id]=@job_id;
end
drop table #jobs2;
end
--выключить все задания по восстановлению БД по последним файлам с источника при переходе на резервный сервер
--включить все задания по восстановлению БД по последним файлам с источника при возврате на боевой сервер
update [msdb].[dbo].[sysjobs]
set [enabled]=case when (@isfailover=1) then 0 else 1 end
where [name] like 'LSRestore%';
--при переходе на резервный сервер, делаем БД восстановленными и основными по доставкам журнала, но без получателя
if(@isfailover=1)
begin
select [secondary_database] as [name]
into #dbs
from msdb.dbo.log_shipping_monitor_secondary
where [secondary_server]=@@SERVERNAME;
declare @db nvarchar(255);
while(exists(select top(1) 1 from #dbs))
begin
select top(1)
@db=[name]
from #dbs;
begin try
RESTORE DATABASE @db WITH RECOVERY;
end try
begin catch
end catch
delete from #dbs
where [name]=@db;
end
drop table #dbs;
select [secondary_database] as [name]
into #dbs2
from msdb.dbo.log_shipping_monitor_secondary
where [secondary_server]=@@SERVERNAME;
declare @jobId BINARY(16);
declare @command nvarchar(max);
declare @dt nvarchar(255)=cast(YEAR(GetDate()) as nvarchar(255))
+'_'+cast(MONTH(GetDate()) as nvarchar(255))
+'_'+cast(DAY(GetDate()) as nvarchar(255))
+'_'+cast(DatePart(hour,GetDate()) as nvarchar(255))
+'_'+cast(DatePart(minute,GetDate()) as nvarchar(255))
+'.trn';
declare @backup_job_name nvarchar(255);
declare @schedule_name nvarchar(255);
declare @disk nvarchar(255);
declare @uid uniqueidentifier;
while(exists(select top(1) 1 from #dbs2))
begin
select top(1)
@db=[name]
from #dbs2;
set @disk=@backup_directory+N''+@db+N'.bak';
set @backup_job_name=N'LSBackup_'+@db;
set @schedule_name=N'LSBackupSchedule_'+@@SERVERNAME+N'_'+@db;
set @command=N'declare @disk nvarchar(max)='+N''''+@backup_directory+N''+@db+'_'+@dt+N''''
+N'BACKUP LOG ['+@db+'] TO DISK = @disk
WITH NOFORMAT, NOINIT, NAME = '+N''''+@db+N''''+N', SKIP, NOREWIND, NOUNLOAD, STATS = 10;';
set @uid=newid();
begin try
BACKUP DATABASE @db TO DISK = @disk
WITH NOFORMAT, NOINIT, NAME = @db, SKIP, NOREWIND, NOUNLOAD, STATS = 10;
EXEC msdb.dbo.sp_add_job @job_name=@backup_job_name,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=@login, @job_id = @jobId OUTPUT;
EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=@backup_job_name,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=@command,
@database_name=N'master',
@flags=0;
EXEC msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1;
EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=@backup_job_name,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=5,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20171009,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=@uid;
EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)';
end try
begin catch
end catch
delete from #dbs2
where [name]=@db;
end
drop table #dbs2;
end
END
Для возврата на основной сервер необходимо заново настроить доставку журналов транзакций с резервного сервера на основной, а затем сделать отработку отказа (после этого основной сервер снова станет боевым). После этого настроить доставку журналов транзакций с боевого сервера на резервный.
Настройка автокоррекции мониторинга доставки журналов транзакций
Для мониторинга доставки журналов транзакций используется на сервере-мониторе задание LSAlert_<НАЗВАНИЕ_ЭКЗЕМПЛЯРА> и отчет (Правой кнопкой мыши по экземпляру сервера-монитора, далее-ОтчетыСтандартный отчетСостояние доставки журналов транзакций).
Достаточно часто со временем сервер-монитор (в случае, если он не является боевым) неправильно берет последнее время создания резервной копии журнала транзакций БД по боевому серверу. В следствии чего возникают ложные предупреждения.
Решить проблему можно следующим простым скриптом:
CREATE PROCEDURE [srv].[AutoCorrectMonitorLogShipping]
AS
BEGIN
/*
Коррекция мониторинга за доставкой журналов транзакций
*/
SET NOCOUNT ON;
update t2
set
t2.[last_backup_date]=t1.[BackupFinishDate]
,t2.[last_backup_date_utc]=DateAdd(hour,-DateDiff(hour,GetUTCDate(),GetDate()),t1.[BackupFinishDate])
,t2.[last_backup_file]=
RIGHT(t1.[PhysicalDeviceName], CHARINDEX('',REVERSE(t1.[PhysicalDeviceName]),1)-1)
from [НАЗВАНИЕ_БОЕВОГО_ЭКЗЕМПЛЯРА].[SRV].[inf].[vServerLastBackupDB] as t1
inner join [msdb].[dbo].[log_shipping_monitor_primary] as t2 on t1.[DBName] collate SQL_Latin1_General_CP1_CI_AS=t2.[primary_database] collate SQL_Latin1_General_CP1_CI_AS
where t1.[BackupType]=N'log';
END
Вызов хранимой процедуры можно автоматизировать по времени. Например, каждые 5 минут, создав соответствующую задачу в Агенте. Естественно, что боевой сервер необходимо связать на резервном (Объекты сервераСвязанные сервера).
Здесь используется представление [inf].[vServerLastBackupDB] в БД SRV, которое определяет последние резервные копии БД:
CREATE VIEW [inf].[vServerLastBackupDB] as
with backup_cte as
(
select
bs.[database_name],
backup_type =
case bs.[type]
when 'D' then 'database'
when 'L' then 'log'
when 'I' then 'differential'
else 'other'
end,
bs.[first_lsn],
bs.[last_lsn],
bs.[backup_start_date],
bs.[backup_finish_date],
cast(bs.[backup_size] as decimal(18,3))/1024/1024 as BackupSizeMb,
rownum =
row_number() over
(
partition by bs.[database_name], type
order by bs.[backup_finish_date] desc
),
LogicalDeviceName = bmf.[logical_device_name],
PhysicalDeviceName = bmf.[physical_device_name],
bs.[server_name],
bs.[user_name]
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
)
select
[server_name] as [ServerName],
[database_name] as [DBName],
[user_name] as [USerName],
[backup_type] as [BackupType],
[backup_start_date] as [BackupStartDate],
[backup_finish_date] as [BackupFinishDate],
[BackupSizeMb], --размер без сжатия
[LogicalDeviceName],
[PhysicalDeviceName],
[first_lsn] as [FirstLSN],
[last_lsn] as [LastLSN]
from backup_cte
where rownum = 1;
Результат
В данной статье был рассмотрен краткий обзор всех возможных вариантов отказоустойчивости и быстрой доступности в MS SQL Server 2017, а также разобраны примеры реализаций отработки отказа и автокоррекции мониторинга доставки журналов транзакций.
Источники:
» msdb
» Сравнение выпусков SQL Server 2017
» AlwaysOn
» Кластеризация
» Репликация
» Зеркалирование
» Доставка журналов транзакций
» Настройка доставки журналов
» When was the last time your SQL Server database was restored
Автор: jobgemws
