Pull to refresh

Реализация отказа в MS SQL Server 2017 Standard

Reading time 10 min
Views 30K

Предисловие


Достаточно нередко можно встретить задачу об обеспечении отказоустойчивости СУБД MS SQL Server. Еще интереснее становится, когда нет лицензии Enterprise, а только Standard.

Сразу отметим, что рассматриваться лицензия Express не будет, т к по данному экземпляру есть существенные ограничения. Да, некоторые из них можно обойти. Например, максимальный размер БД в 10 ГБ легко решается путем декомпозиции большой БД на более маленькие (например, по какому-то признаку создавать новую БД, а в главной БД в представлениях объединять выборки из одних таблиц разных БД). Но отказоустойчивость в Express будет достигаться либо силами системного администратора, либо использованием собственно разработанного (или стороннего) ПО.

В данной статье будут вкратце разобраны все существующие стандартные технологии отказоустойчивости для MS SQL Server 2017 и будет рассмотрен пример реализации отказа наиболее подходящего унифицированного из них в лицензии Standard.

Краткий обзор


  1. AlwaysOn: распределение нагрузки среди всех участников, все участники должны быть по своим характеристикам максимально похожи между собой.
    В синхронном режиме обеспечивается максимальная надежность передачи данных, однако скорость работы будет равняться скорости работы самого медленного участника. В асинхронном режиме обеспечивается максимальное быстродействие, однако могут возникать рассогласованности данных между участниками, что ведет к более сложной поддержке и вероятности потерять последние изменения в случае сбоя основного участника.

    Быстрота переключения в синхронном режиме-практически мгновенно и не требует вмешательства системного администратора и DBA, в асинхронном-зависит от текущего состояния БД-дублей, но обычно в среднем до 5 минут (также можно автоматизировать переключение силами одного DBA без привлечения системного администратора).

    Признана Microsoft рекомендуемой технологией для БД. Доступна с лицензией Enterprise от 2012 версии и выше. Доступна с ограничениями с лицензией Standard (подробно в статье Top 5 Questions about Basic Availability Groups).
  2. Кластеризация: несмотря на простоту настройки, данное решение ненадежно в виду узкого места в виде единого для всех хранилища данных. В случае выхода из строя хранилища данных, восстановление займет достаточно длительный промежуток времени-более 1 часа.
    Доступна с лицензией Standard до 2008 версии и выше.
  3. Репликация: любая репликация подразумевает создание системных триггеров на каждую таблицу-участницу, а репликация моментальных снимков будет достаточно сильно нагружать основную БД. Поэтому репликацию моментальных снимков можно делать только в минимальные часы нагрузки БД (например, ночью), что неприемлемо, т к необходим горячий резерв. Репликация слиянием сложна в сопровождении для некоторых системы (например, CRM, NAV), также она не подходит для 1С в виду частого изменения структур БД.

    Репликация транзакциями возможна, но при наличии Enterprise-лицензии.
    Доступна с лицензией Standard (слиянием и снимков БД) и Enterprise (транзакциями) до 2008 версии и выше.
  4. Зеркалирование: возможна в любом режиме, однако как и при AlwaysOn, синхронный режим обеспечивает максимальную надежность и быстрое переключение, а асинхронный режим дает максимальную скорость работы с основной БД, но возможны рассогласованности данных между всеми участниками, а также переключение не будет мгновенным. Здесь переключение на уровне БД обеспечивает следящий сервер автоматически (при например, нагрузке ЦП более, чем в 50% на основном сервере) или средствами DBA. Подключение же к другому серверу обеспечивается силами системного администратора. Резервная БД при любом типе зеркалирования находится в режиме постоянного восстановления, в следствие чего к ней невозможно обратиться.
    Режим восстановления БД-полный.

    Признана Microsoft устаревшей технологией для БД.
    Доступна с лицензией Standard (в синхронном режиме) и Enterprise (в асинхронном режиме) до 2008 версии и выше.
  5. Доставка журналов транзакций: есть 2 режима-постоянное восстановление на резервном сервере или восстановление с отсрочкой.

    Первый режим переводит резервную БД (как и при зеркалировании) в режим постоянного восстанавления и к ней невозможно обратиться.

    Второй же режим переводит резервную БД в режим восстановления периодически в момент накатывания обновлений (между накатываниями обновлений резервная БД доступна, но это возможно при условии, что экземпляры MS SQL Server одной версии).

    Принцип работы прост:

    1. Периодически делается резервная копия журнала транзакций БД на источнике в общедоступную папку как источнику, так и резервному скулю (настраивается путь и расписание, по умолчанию-каждые 15 минут).
    2. Резервный скуль периодически копирует получившуюся резервную копию журнала транзакций БД себе в локальную доступную папку (настраивается путь и расписание, по умолчанию-каждые 15 минут).
    3. Резервный скуль восстанавливает журнал транзакций из скопированной резервной копии журнала транзакций (настраивается расписание, по умолчанию – каждые 15 минут).

    Переключение можно автоматизировать на уровне БД-силами DBA, а на уровне подключений к серверу-на уровне системного администратора.

    Также стоит отметить, что данный метод работает всегда в асинхронном режиме. Можно настроить несколько резервных БД.

    Режим восстановления БД-полный или с неполным протоколированием
    Доступна с лицензией Standard до 2008 версии и выше.

Вывод


Из выше описанных вариантов, больше подходит доставка журналов транзакций в лицензии Standard, т к его удобно использовать для плавного перехода с одного сервера на другой (например, при обновлении среды). Также доставка журналов транзакций достаточна проста в поддержке и проста в работе, а также всегда работает в асинхронном режиме, что не так сильно (как синхронный режим зеркалирования) нагружает БД. В любом случае зеркалирование приемлемо, если возможно настроить собственное автоматическое переключение, иначе возможны ложные переключения (например, когда ЦП основного сервера будет загружено более чем на 50%).

Для Enterprise – однозначно предлагается технология AlwaysOn.

Настройка отработки отказа при доставке журналов транзакций


Подробно по настройке доставки журналов транзакций описано здесь . Также данный процесс можно автоматизировать, написав собственную утилиту для повторного многократного использования, а также для возвращения на основной сервер после его починки в случае отработки отказа.

Разберем теперь один из возможных вариантов отработки отказа при доставке журналов транзакций на уровне СУБД.

Важно отметить, что данный метод подходит только для сервера, который является резервным только для одного экземпляра MS SQL Server, т к для нескольких возникает проблема в определении какие задания выполнять, а какие нет.

Опишем сначала саму последовательность действий:

  1. Выполнить все задания по копированию последних файлов с источника (при продуманной архитектуре, путь должен быть доступен даже при падении основного сервера)
  2. Выключить все задания по копированию файлов с источника
  3. Выполнить все задания по восстановлению БД по последним файлам с источника
  4. Выключить все задания по восстановлению БД по последним файлам с источника
  5. Сделать БД восстановленными и основными по доставкам журнала, но без получателя
  6. Создать полные резервные копии для БД
  7. Создать задания для создания резервных копий журналов транзакций БД

Ниже приведем пример реализации выше описанной последовательности в виде хранимой процедуры.

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

Пример реализации отработки отказа доставки журналов транзакций
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, которое определяет последние резервные копии БД:

Пример реализации представления vServerLastBackupDB
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, а также разобраны примеры реализаций отработки отказа и автокоррекции мониторинга доставки журналов транзакций.

Благодарности


Спасибо NoOne за конструктивные комментарии об AlwaysOn.

Источники:


» msdb
» Сравнение выпусков SQL Server 2017
» AlwaysOn
» Кластеризация
» Репликация
» Зеркалирование
» Доставка журналов транзакций
» Настройка доставки журналов
» When was the last time your SQL Server database was restored
» Top 5 Questions about Basic Availability Groups
Tags:
Hubs:
+9
Comments 15
Comments Comments 15

Articles