Автоматизация обслуживания SQL Server (ola hallengren)

Ссылка на сайт создателя

Backup, проверка целостности, обслуживание индексов и статистики

Данное решение работает начиная с SQL Server 2005 до SQL Server 2016. Решение основано на хранимых процедурах, утилите sqlcmd и заданий SQL Server Agent. Система проектировалась для критически важных систем и протестирована на множестве серверов по всему миру, так же имеет множество наград в номинации «Best Free Tool».

Приступим

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

Для понимания как работает система, изучите:

• DatabaseBackup: SQL Server Backup
• DatabaseIntegrityCheck: SQL Server проверка целостности
• IndexOptimize: SQL Server обслуживание индексов и статистики

При желании вы можете оформить подписку на все изменения данного решения

Работа с Availability Groups

Одна из наиболее желаемых возможностей на протяжении долгого времени, наконец я добавил и её:

EXECUTE dbo.DatabaseBackup
@AvailabilityGroups = 'AG1',
@Directory = 'C:\Backup',
@BackupType = 'FULL'

EXECUTE dbo.DatabaseBackup
@AvailabilityGroups = 'AG1, AG2',
@Directory = 'C:\Backup',
@BackupType = 'FULL'

EXECUTE dbo.DatabaseBackup
@AvailabilityGroups = 'ALL_AVAILABILITY_GROUPS, -AG1',
@Directory = 'C:\Backup',
@BackupType = 'FULL'

Теперь, если вы хотите выбрать все пользовательские БД, которые не в availability groups, то вам необходимо передать дополнительную запись в параметр @Databases:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES, -AVAILABILITY_GROUP_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL'

В других заданиях система отрабатывает так же.

Backup to Azure Blob Storage

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES',
@URL = 'https://myaccount.blob.core.windows.net/mycontainer',
@Credential = 'mycredential',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y'

Вы так же можете включить шифрование для вашего backup:

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES',
@URL = 'https://myaccount.blob.core.windows.net/mycontainer',
@Credential = 'MyCredential',
@BackupType = 'FULL',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'MyCertificate',
@Verify = 'Y'

Несколько копий Backup

Вы можете создать несколько копий backup на разные хранилища:

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@MirrorDirectory = 'D:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@Verify = 'Y',
@CleanupTime = 24,
@MirrorCleanupTime = 48

SQL Server 2014

Добавлена поддержка шифрования backup, low priority locks, онлайн перестроение секций индексов и обновление статистики на таблицах в памяти.

Активация шифрования на backup:

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'MyCertificate'

Low priority locks:

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@WaitAtLowPriorityMaxDuration = 1,
@WaitAtLowPriorityAbortAfterWait = 'NONE'

Секции перестраивается онлайн по-умолчанию.

Умное обновление Индексов

Система обновляет только те индексы, которые нуждаются в этом:

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

В данном примере сильно фрагментированные индексы будут перестраиваться онлайн, если это возможно, индексы с меньшим уровнем фрагментации будут реорганизованы, а индексы, которым не требуется обслуживания, не будут обновляться.

Обновление статистики

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

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

Разбиение Backup на множество частей

Чтобы увеличить скорость backup, вы можете воспользоваться разбиением backup на множество частей. Все эти части являются одной резервной копией БД:

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup, D:\Backup, E:\Backup, F:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@BufferCount = 50,
@MaxTransferSize = 4194304,
@NumberOfFiles = 4,
@CleanupTime = 24

Запуск проверки целостности на больших БД

Вы можете выполнять проверку целостности на всей БД, только на определённых файловых группах или на конкретных таблицах:

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKDB',
@PhysicalOnly = 'Y'