В предыдущей статье мы рассмотрели некоторые аспекты резервирования баз данных, входящих в состав групп доступности AlwaysOn. В дополнение к общему обзору функции sys.fn_hadr_backup_is_preferred_replica () я упомянул о том, что ее интегрирование в процесс резервирования под управлением планов обслуживания SQL Server сопряжено с большими трудностями, чем те, что характерны для работы с другими типами резервных копий.
Поэтому в данной статье я кратко изложу аргументы за и против интегрирования проверок if/else в планы обслуживания SQL Server (применительно к резервным копиям), а также — раз уж речь зашла об этом — общие соображения о пакетах SSIS. Это не бог весть какая премудрость, но существует ряд обстоятельств, о которых вам придется постоянно помнить. Начну я с нескольких общих соображений относительно возможности использования планов обслуживания SQL Server.
Планы обслуживания
Мы обсудим их в широком контексте — в чем состоят их достоинства и недостатки. В целом я полагаю, что инфраструктура, на которой строились планы обслуживания, достаточно эффективна. Иначе говоря, поскольку в основу планов обслуживания SQL Server положены службы интеграции SQL Server Integration Services (SSIS), администраторы баз данных выигрывают от того, что получают возможность расчленять сложные операции на небольшие дискретные задания, отделять их друг от друга операторами предшествования и вообще решать огромное количество проблем с использованием весьма мощной среды.
Однако дело том, что, хотя архитектуру, предназначенную для управления планами обслуживания, пожалуй, можно считать функционально полной, «задания», предлагаемые в планах обслуживания, были составлены либо стажерами, либо глубоко невежественными людьми. Если такая аттестация кажется вам слишком строгой, познакомьтесь с заданиями Reorganize and Rebuild Index Tasks, которые поставляются с планами обслуживания. Оба задания позволяют указывать минимальное число настроек (какие базы данных следует считать целевыми, а также нужно ли сортировать результаты в базе данных tempdb или перестраивать индексы онлайн или офлайн, когда дело доходит до задания Rebuild Task), но не дают возможности определять спецификации относительно того, какие индексы необходимо перестраивать либо реорганизовывать. Следовательно, эти задания просто перестраивают или реорганизуют каждый отдельный индекс в целевой базе данных вне зависимости от того, требуется ли в данном случае выполнение операций по обслуживанию или нет.
В свете такого серьезного недостатка — вкупе с другими — вполне понятно, почему так много опытных администраторов баз данных с нескрываемым пренебрежением относятся к планам обслуживания SQL Server. Что касается меня, то я не вполне разделяю точку зрения тех, кто ни во что не ставит эти планы, ибо, на мой взгляд, задание по резервному копированию баз данных можно с легкостью использовать для создания вполне надежных планов резервирования в тех случаях, когда организации не располагают решениями для резервного копирования от сторонних поставщиков. И еще один аргумент: планы обслуживания обеспечивают администраторам возможность размещать резервные копии заданных баз данных в специальных папках; мне такая возможность представляется весьма важной.
С учетом сказанного в ситуациях, когда клиенты хотят задействовать в своих сетях планы обслуживания SQL Server, я обычно рекомендую, чтобы они:
а) практически не использовали их для решения задач, не связанных с резервным копированием;
б) если клиенты действительно используют их для создания резервных копий, чтобы они прилагали дополнительные усилия для ограничения сферы использования этих планов снятием резервных копий (а не пытались при случае применять их дополнительно для решения других задач).
По этой причине, не являясь ярым противником применения планов обслуживания SQL Server, я в то же время не отношусь к числу их горячих сторонников, хотя должен сказать, что заключил с ними нечто вроде перемирия в том, что касается работы с резервными копиями.
В иных случаях, когда дело доходит до использования планов обслуживания (для создания резервных копий) или до применения пакетов SSIS для обработки баз данных групп доступности, следует выполнять несколько ключевых требований. В частности, необходимо определять, когда нужно запускать задание (выяснять, размещается ли оно на основной реплике), а также обеспечивать синхронизацию деталей задания на различных серверах.
Как работать с логикой планов обслуживания
В предыдущей статье я отмечал, что в большинстве случаев при работе с резервными копиями нужно просто отыскать код, ответственный за рассылку команд по созданию резервных копий, и включить его в логику if/else, построенную на базе процедуры sys.fn_hadr_backup_is_preferred_replica (). Таким образом мы сможем определить, следует ли запускать резервные копии на текущем хосте. Но если мы имеем дело с планами обслуживания SQL Server, у нас просто нет возможности ни просматривать код, созданный планом обслуживания, ни модифицировать его. В этом случае заданные команды, детали и настройки помещаются в пакет службы интеграции SSIS в качестве компонентов этого пакета, который выполняется на сервере по расписанию и далее генерирует команды в процессе исполнения. Эти команды он впоследствии направляет на сервер.
Соответственно, когда дело доходит до управления резервными копиями с помощью планов обслуживания, в вашем распоряжении остается два основных метода применения проверок if/else.
- Воздержаться (To Not To), как выразился герой одного фильма. Когда этого персонажа спросили, как ему рекомендовали взаимодействовать с определенной группой людей, он ответил, что ему посоветовали «воздержаться». Эту рекомендацию можно использовать и применительно к планам обслуживания — в том смысле, что, если вы используете их только для создания резервных копий (а это единственное задание, для выполнения которого я рекомендую их применять), задача проще всего решается, когда дело доходит до управления резервными копиями баз данных вашей группы доступности, с помощью неиспользования планов обслуживания. Как вы сможете убедиться, работать с ними намного сложнее, чем хотелось бы.
- Вставляйте в пакет SSIS логику SSIS IF/ELSE. Иначе вам придется редактировать свои пакеты SSIS, дабы применять в них условную логику. Сама по себе эта операция несложная, но в результате ее выполнения пакеты становятся чуть более усложненными. И не то что бы это отражалось на планах обслуживания, однако, если в процессе выполнения «пакетных» или иных заданий вам придется иметь дело с большим числом пакетов SSIS, у вас, возможно, возникнут проблемы.
Если при организации процесса управления резервными копиями вы захотите вместо логики плана обслуживания SQL Server использовать то или иное альтернативное решение, взгляните на сценарий в листинге. В функциональном отношении он позволяет добиться тех же показателей, что и резервные компоненты, реализованные в планах обслуживания. Иначе вам придется организовывать проверки if/else в службах интеграции SQL Server.
Проверки на выполнение условий по установкам реплик AlwaysOn внутри пакетов SSIS
Проводить такие проверки довольно просто. Но, к сожалению, при этом вам придется «засорять» свои пакеты рядом проверок if/else, в результате чего такие пакеты будут становиться чуть более сложными.
Для решения задачи вам фактически понадобится всего лишь внести два дополнительных изменения в пакеты SSIS или в планы обслуживания SQL Server:
- Добавьте новую задачу Execute T-SQL Statement Task. Включите проверку на выполнение условия, которая возвратит или инициирует ошибку, если соответствующий сервер не является вашей целью.
- Проследите за тем, чтобы в оставшейся части потока работ использовался примененный в этом задании оператор предшествования on success. Поскольку таким образом вы, в сущности, получите гарантию того, что в случае, если вы не находитесь на сервере, с которого хотите или должны выполнять операцию, ваше задание T-SQL возвратит ошибку и логика не будет «ветвиться» до конца выполнения пакета.
Чтобы получить более четкое представление о сказанном, взгляните на экран 1. Так план обслуживания может выглядеть до того, как вы внесете в него изменения.
.jpg) |
| Экран 1. План обслуживания до внесения изменений |
Тот же план после внесения изменений показан на экране 2.
.jpg) |
| Экран 2. План обслуживания после внесения изменений |
Обратите внимание, что оператор предшествования настроен на выполнение только в случае успешного завершения предыдущего шага. Для полноты картины я привожу экран 3 с самим заданием Execute T-SQL Statement Task. На нем показано, как мой код возвращает ошибку, если на сервере, о котором идет речь, не размещается предпочтительная реплика.
.jpg) |
| Экран 3. Ошибка с размещением реплики |
Разумеется, в данном случае мы имеем дело с планами обслуживания SQL Server или с резервными копиями. Если вы работаете с пакетами SSIS, которые запускаются или выполняются как пакетные задания, у вас, возможно, возникнет желание не определять, размещается ли на сервере ваша предпочтительная реплика, а воспользоваться иной логикой. Поэтому в качестве отправной точки в поисках ответа на вопрос, как определить серверы, с которых будут запускаться пакеты SSIS, вы можете взять одну из логических конструкций, описанных в статье о выявлении владельца главной реплики. Хотя для полной ясности нужно отметить, что в данном случае мы говорим о необходимости планирования выполнения пакетов SSIS, а не о том, как определять, какой из хостов должен быть целевым при выполнении операций по обработке данных, поскольку все действия по выявлению целевых объектов должны осуществляться применительно к прослушивателю группы доступности.
Наконец, поскольку вы дополнили свои планы этой проверкой на выполнение условия или поскольку вы ввели эти изменения в пакеты SSIS, которые используются для обработки пакетных заданий, вам нужно будет выполнить еще одно задание: синхронизировать эти изменения по всем серверам, на которых размещаются базы данных вашей группы доступности. В следующей статье серии мы рассмотрим некоторые проблемы, связанные процессом синхронизации, и обоснуем, почему применение планов обслуживания для работы с резервными копиями имеет больше недостатков, чем достоинств, когда дело касается баз данных групп доступности.
/*
-- Эта хранимая процедура заменяет резервные копии заданий
по обслуживанию SQL Server.
-- Для ее выполнения необходимо указать путь, список
подлежащих резервированию баз данных и тип резервной копии...
-- а также метку времени для подлежащих удалению объектов
старше X.
-- Примечание: на серверах Express и на Web-северах...
выполнение сжатия не поддерживается.
-- Резервные копии системных баз данных:
DECLARE @olderThan datetime;
SET @olderThan = DATEADD(dd, -3, GETDATE());
EXEC dbo.dba_DatabaseBackups
@BackupType = 'FULL',
@DatabasesToBackup = '[SYSTEM_DBS]',
@BackupDirectory = 'D:\SQLBackups\System',
@OlderBackupDeletionTime = @olderThan;
GO
-- Полное резервирование всех пользовательских баз данных:
DECLARE @olderThan datetime;
SET @olderThan = DATEADD(hh, -48, GETDATE());
EXEC dbo.dba_DatabaseBackups
@BackupType = 'FULL',
@DatabasesToBackup = '[USER_DBS]',
@BackupDirectory = 'D:\SQLBackups\User',
@OlderBackupDeletionTime = @olderThan;
GO
-- Полное резервирование всех пользовательских баз данных:
DECLARE @olderThan datetime;
SET @olderThan = DATEADD(hh, -48, GETDATE());
EXEC dbo.dba_DatabaseBackups
@BackupType = 'FULL',
@DatabasesToBackup = '[USER_DBS]',
@BackupDirectory = 'D:\SQLBackups\User',
@OlderBackupDeletionTime = @olderThan;
GO
-- Полное резервирование указанных пользовательских баз данных:
DECLARE @olderThan datetime;
SET @olderThan = DATEADD(hh, 25, GETDATE());
EXEC dbo.dba_DatabaseBackups
@BackupType = 'FULL',
@DatabasesToBackup = 'meddling,ssv2',
@BackupDirectory = 'D:\SQLBackups\User',
@OlderBackupDeletionTime = @olderThan;
GO
-- разностное резервное копирование указанных баз данных:
DECLARE @olderThan datetime;
SET @olderThan = DATEADD(hh, -48, GETDATE());
EXEC dbo.dba_DatabaseBackups
@BackupType = 'DIFF',
@DatabasesToBackup = 'meddling,ssv2',
@BackupDirectory = 'D:\SQLBackups\User',
@OlderBackupDeletionTime = @olderThan;
GO
-- Резервные копии T-Log всех пользовательских баз данных:
DECLARE @olderThan datetime;
SET @olderThan = DATEADD(hh, -36, GETDATE());
EXEC dbo.dba_DatabaseBackups
@BackupType = 'LOG',
@DatabasesToBackup = '[USER_DBS]',
@BackupDirectory = 'D:\SQLBackups\User',
@OlderBackupDeletionTime = @olderThan;
GO
*/
USE master;
GO
IF OBJECT_ID('dbo.dba_DatabaseBackups','P') IS NOT NULL
DROP PROC dbo.dba_DatabaseBackups;
GO
CREATE PROC dbo.dba_DatabaseBackups
@BackupType sysname,
@DatabasesToBackup nvarchar(1000),
@BackupDirectory sysname,
@OlderBackupDeletionTime datetime,
@PrintOnly bit = 0
AS
SET NOCOUNT ON;
DECLARE @jobStart datetime;
SET @jobStart = GETDATE();
-- проверяем
IF UPPER(@BackupType) NOT IN ('FULL', 'DIFF','LOG') BEGIN
PRINT 'Usage: @BackupType = FULL|DIFF|LOG';
RAISERROR('Invalid @BackupType Specified.', 16, 1);
END
IF @OlderBackupDeletionTime >= GETDATE() BEGIN
RAISERROR('Invalid @OlderBackupDeletionTime - greater than
or equal to NOW.', 16, 1);
END
-- определяем базы данных:
DECLARE @targetDatabases TABLE (
database_name sysname NOT NULL
);
IF UPPER(@DatabasesToBackup) = '[SYSTEM_DBS]' BEGIN
INSERT INTO @targetDatabases (database_name)
SELECT 'master' UNION SELECT 'msdb' UNION SELECT 'model';
END
IF UPPER(@DatabasesToBackup) = '[USER_DBS]' BEGIN
IF @BackupType = 'LOG'
INSERT INTO @targetDatabases (database_name)
SELECT name FROM sys.databases
WHERE recovery_model_desc = 'FULL'
AND name NOT IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY name;
ELSE
INSERT INTO @targetDatabases (database_name)
SELECT name FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb','tempdb')
ORDER BY name;
END
IF (SELECT COUNT(*) FROM @targetDatabases) <= 0 BEGIN
-- осуществляем десериализацию списка подлежащих
резервированию баз данных:
SELECT TOP 400 IDENTITY(int, 1, 1) as N
INTO #Tally
FROM sys.columns;
DECLARE @SerializedDbs nvarchar(1200);
SET @SerializedDbs = ',' + REPLACE(@DatabasesToBackup, ' ', '') + ',';
INSERT INTO @targetDatabases (database_name)
SELECT SUBSTRING(@SerializedDbs, N + 1, CHARINDEX(',', @
SerializedDbs, N + 1) - N - 1)
FROM #Tally
WHERE N < LEN(@SerializedDbs)
AND SUBSTRING(@SerializedDbs, N, 1) = ',';
IF @BackupType = 'LOG' BEGIN
DELETE FROM @targetDatabases
WHERE database_name NOT IN (
SELECT name FROM sys.databases WHERE
recovery_model_desc = 'FULL'
);
END
ELSE
DELETE FROM @targetDatabases
WHERE database_name NOT IN (SELECT name FROM
sys.databases);
END
-- удостоверяемся в том, что у нас что-то есть
IF (SELECT COUNT(*) FROM @targetDatabases) <= 0 BEGIN
PRINT 'Usage: @DatabasesToBackup = [SYSTEM_DBS]|
[USER_DBS]|dbname1,dbname2,dbname3,etc';
RAISERROR('No databases for backup.', 16, 1);
END
-- нормализуем путь:
IF(RIGHT(@BackupDirectory, 1) = ‘\’)
SET @BackupDirectory = LEFT(@BackupDirectory,
LEN(@BackupDirectory) - 1);
-- Начинаем выполнять резервные копии:
DECLARE backups FAST_FORWARD FOR
SELECT
database_name
FROM
@targetDatabases
ORDER BY
database_name;
DECLARE @currentDB sysname;
DECLARE @backupPath sysname;
DECLARE @backupStatement nvarchar(2000);
DECLARE @backupName sysname;
DECLARE @now datetime;
DECLARE @timestamp sysname;
DECLARE @extension sysname;
DECLARE @offset sysname;
DECLARE @verifyStatement nvarchar(2000);
DECLARE @Errors TABLE (
ErrorID int IDENTITY(1,1) NOT NULL,
[Database] sysname NOT NULL,
ErrorMessage nvarchar(2000)
);
DECLARE @ErrorMessage sysname;
OPEN backups;
FETCH NEXT FROM backups INTO @currentDB;
WHILE @@FETCH_STATUS = 0 BEGIN
SET @backupPath = @BackupDirectory + N'\' + @currentDB;
-- удостоверяемся в том, что подкаталог существует:
IF @PrintOnly = 1 BEGIN
PRINT 'Verify/Create Directory: ' + @backupPath;
END
ELSE
EXECUTE master.dbo.xp_create_subdir @backupPath;
-- создаем имя резервной копии:
SET @extension = ‘.bak’;
IF @BackupType = 'LOG'
SET @extension = '.trn';
SET @now = GETDATE();
SET @timestamp = REPLACE(REPLACE(REPLACE(CONVERT
(sysname, @now, 120), '-','_'), ':',''), ' ', '_');
SET @offset = RIGHT(CAST(CAST(RAND()
AS decimal(12,11)) AS varchar(20)),7);
SET @backupName = @currentDB + '_backup_' +
@timestamp + '_' + @offset + @extension;
-- основное отличие данной резервной копии и резервных
копий плана обслуживания состоит в: CHECKSUM...
SET @backupStatement = 'BACKUP ' + QUOTENAME
(@currentDB, '[]') + ' TO DISK = N''' + @backupPath + '\' +
@backupName + '''
WITH NOFORMAT, NOINIT, NAME = N''' + @backupName + ''',
SKIP, REWIND, NOUNLOAD, CHECKSUM, STATS = 10;'
IF @BackupType IN ('FULL', 'DIFF') BEGIN
SET @backupStatement = REPLACE(@backupStatement,
'', 'DATABASE');
IF @BackupType = 'DIFF'
SET @backupStatement = REPLACE
(@backupStatement, '', 'DIFFERENTIAL,');
ELSE
SET @backupStatement = REPLACE(@backupStatement, '', '');
END
ELSE BEGIN -- log file backup
SET @backupStatement = REPLACE(@backupStatement, '', 'LOG');
SET @backupStatement = REPLACE(@backupStatement, '', '');
END
SET @verifyStatement = ‘RESTORE VERIFYONLY FROM DISK
= N’’’ + @backupPath + ‘\’ + @backupName +
‘’’ WITH NOUNLOAD, NOREWIND;’;
BEGIN TRY
IF @PrintOnly = 1 BEGIN
PRINT @backupStatement;
PRINT @verifyStatement;
END
ELSE BEGIN
EXEC sp_executesql @backupStatement;
EXEC sp_executesql @verifyStatement;
END
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE();
INSERT INTO @Errors ([Database], ErrorMessage)
VALUES (@currentDB, @ErrorMessage);
END CATCH
FETCH NEXT FROM backups INTO @currentDB;
END;
CLOSE backups;
DEALLOCATE backups;
-- Удаление любого/всех файлов по мере необходимости
DECLARE @deleteStatement sysname;
SET @deleteStatement = 'EXECUTE master.dbo.xp_delete_file 0,
N''' + @BackupDirectory + ''', N''' + REPLACE(@extension, '.','') +
''', N''' + REPLACE(CONVERT(nvarchar(20),
@OlderBackupDeletionTime, 120), ' ', 'T') + ''', 1;';
BEGIN TRY
IF @PrintOnly = 1
PRINT @deleteStatement
ELSE
EXEC sp_executesql @deleteStatement;
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE();
INSERT INTO @Errors ([Database], ErrorMessage)
VALUES ('File Deletion', @ErrorMessage);
END CATCH
IF (SELECT COUNT(*) FROM @Errors) > 0 BEGIN
PRINT 'The Following Errors were Detectected: ';
DECLARE errors FAST_FORWARD FOR
SELECT [Database],[ErrorMessage]
FROM @Errors
ORDER BY ErrorID;
OPEN errors;
FETCH NEXT FROM errors INTO @currentDB, @ErrorMessage;
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT 'DATABASE/OPERATION: ' + @currentDB + ' -> ' +
@ErrorMessage;
FETCH NEXT FROM errors INTO @currentDB, @ErrorMessage;
END
CLOSE errors;
DEALLOCATE errors;
-- Вызывается ошибка, чтобы проинформировать нас
о наличии проблем:
RAISERROR('Unexpected errors executing backups -
see output.', 16, 1);
END
RETURN 0;
GO