Надежное обслуживание баз MS SQL Server для занятых08.09.2015 15:48

Вероятно, вы знаете, что обслуживание баз данных это целый комплекс процедур: создание бэкапов, проверка целостности, обслуживание индексов, статистики и т.д. На просторах сети (да и на Хабре в том числе) на эту тему написано множество статей и рекомендаций. Однако занимаясь внедрением »1С: Предприятие», нам частенько приходится сталкиваться с тем, что обслуживание баз данных настраивается либо неправильно, либо по очень упрощённой схеме. Например, чтобы не заморачиваться с управлением журналами транзакций, для «боевых» баз устанавливается Простая модель восстановления (Simple Recovery model). И это несмотря на то, что потеря информации за пару часов уже критична для компании. Иногда задача по сжатию файлов БД включается в регулярное обслуживание («шобы не росло»), или после обновления индексов идёт уничтожение статистики и прочие подобные ляпы. Так происходит потому, что чаще всего в компаниях нет опытного администратора БД и обслуживанием приходится заниматься кому-то из сотрудников ИТ-службы — «невольному» администратору баз данных (DBA). При этом такой DBA не всегда осознаёт все риски и возложенную на него ответственность.

Для обслуживания баз Microsoft предлагает планы обслуживания (Maintenance Plan) в SQL Server Management Studio (SSMS). Однако как показывает практика, создать и настроить качественный и надёжный план обслуживания может только опытный DBA. Отмечу, что надёжное обслуживание максимально автоматизировано и не требует регулярного ручного мониторинга администратором, а также гарантирует, что данные удастся восстановить в случае сбоя.

Сторонние программы, которые имеются на рынке и способны облегчить жизнь, в основном автоматизируют создание бэкапов. Выбор таких программ очень широк. Они позволяют делать бэкапы сжатые и шифрованные, на FTP/GoogleDrive/Amazon и так далее. Бэкапы тут можно сравнить с креветками, о которых говорил Бабба в картине «Форест Гамп»:»… их можно жарить, варить, печь, тушить, можно приготовить шашлык из креветок, креветки по-креольски, креветки гамбо, поджаренные с рисом …».

Однако как было сказано, настройка бэкапов это далеко не всё, поэтому такие программы закрывают лишь часть вопросов.

В итоге «невольному» DBA приходится читать статьи, разбираться с SSMS, разрабатывать стратегию резервного копирования, искать скрипты, настраивать уведомления. Времени уходит много, но всегда есть, что обматерить… А хочется жить безмятежно!  Так, чтобы один раз сделал и забыл.

В этой статье я хотел бы сделать обзор нашей программы Quick Maintenance & Backup (QMB), которая поможет вам просто и быстро настроить обслуживание баз данных на Microsoft SQL Server. Бесспорно, что для больших и высоконагруженных баз данных не обойтись без опытного DBA и индивидуального тюнинга производительности, но если вам приходится иметь дело с множеством небольших баз (как правило до 50–80 Гб), то данная утилита будет полезна как новичкам, так и продвинутым пользователям.

В коротком видеоролике ниже показан сквозной пример настройки обслуживания с помощью QMB. Последующее описание дополняет видеоролик и рассказывает о некоторых особенностях программы.

Концепция: доступно новичкам, удобно профессионалам

С одной стороны, мы старались сделать программу доступной новичкам и реализовать наиболее распространённые сценарии обслуживания. С другой стороны, мы хотели сделать так, чтобы программа была удобна продвинутым пользователям и помогала им настроить самые разнообразные сценарии, в том числе объединить операции обслуживания баз средствами Transact SQL с другими регламентными процедурами ваших приложений. Например, в QMB можно сделать сценарий, который вначале загрузит данные в »1С: Предприятие», а только потом сделает бэкап и выполнит остальное обслуживание. В итоге получился планировщик, предоставляющий свой фреймворк для исполнения T-SQL скриптов и пакетных файлов (с возможностью хранения результатов их исполнения).

Архитектура

Программа имеет три компонента: GUI клиента, службу QMB Service и файловую базу для хранения своих данных. При установке QMB устанавливаются все три компонента программы. Планы обслуживания не создаются, поэтому не требуется наличия службы агента SQL Server. Подробнее об архитектуре читайте здесь.

Политика обслуживания, сценарии и задачи

Как было сказано выше, QMB не создаёт планов обслуживания на SQL Server. Вместо этого создаётся Политика обслуживания, которая сохраняется в локальном хранилище (файловой базе данных). По сути, политика — это группировка баз данных со схожими свойствами, которые обслуживаются по одним правилам. Политика содержит список баз данных, настройки хранения и копирования бэкапов. В политику входит один или несколько сценариев обслуживания. Сценарий содержит набор задач, исполняемых последовательно для каждой (включенной в политику) базы данных. Если проводить аналогию с планами обслуживания, то сценарий можно сравнить с вложенными Планами обслуживания (Maintenance Plan).

Задача в QMB может иметь один из пяти типов:

• Скрипт T-SQL
• Создание архивной копии (скрипт T-SQL)
• Восстановление архивной копии (динамический скрипт T-SQL)
• Произвольный скрипт (не T-SQL)
• Копирование архивных копий (используется в одноименной системной задаче)

В программе имеется два набора встроенных задач. Первый набор задач базируется на T-SQL скриптах, полученных из открытых источников и созданных разработчиками QMB. Второй набор базируется на скриптах Ола Халенгрэн (администратор баз данных из Швеции), который разработал три популярные хранимые процедуры для обслуживания баз данных. Процедуры Ола устанавливаются автоматически в системную базу данных master, при создании политики из шаблона.

Обслуживание больших и маленьких баз данных. Шаблоны политик

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

• Моделью восстановления баз данных. 5 политик с полной моделью восстановления (Full recovery model) и 2 с простой (Simple recovery model).
• Порядком обслуживания индексов. Для небольших баз дефрагментация индексов выполняется каждую ночь, а обновление изменившейся статистики в течении дня; для больших баз дефрагментация индексов выполняется раз в неделю.
• Набором используемых задач в сценариях. Для обслуживания используются задачи/скрипты Ола Халенгрэн или QMB.
  

Для рабочих баз данных, с ежедневным оперативным вводом информации (OLTP-базы), рекомендуется выбирать политику с Полной моделью восстановления — например, для баз »1С: Предприятие», в которые ежедневно вводятся данные. Такая модель позволяет восстанавливать базу данных на актуальный или на произвольный момент времени.

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

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

Подробнее о различиях в шаблонах можно посмотреть в справке.

Задачи

Как было сказано выше, в QMB имеется 5 типов задач — ниже описаны некоторые их особенности.

Исполнение скриптов

Большинство системных задач это T-SQL скрипты. Сам скрипт можно просмотреть в форме задачи:

Тексты скриптов (T-SQL, CMD, VBS, PowerShell и других) могут содержать маркеры, которые будут заменены на соответствующие значения, перед его исполнением. Например, маркер ? DataBaseName? будет заменён на имя базы данных, а маркер ? BackupDirectory? – на путь к каталогу архивных копий, указанному в политике. Полный список маркеров можно посмотреть в справке.

Оптимизация окна обслуживания
Бывает, что в ограниченное временное окно необходимо уместить не только обслуживание баз средствами SQL Server, но и исполнение других регламентных операций вашего приложения. Например, тестирование и исправление баз 1С, выгрузку средствами платформы »1С: Предприятие», проведение обмена и т.п. Обычно для этого используется планировщик заданий Windows или планировщик »1С: Предприятие». Однако при этом приходится разносить процедуры во времени с хорошим запасом — так, чтобы они гарантированно не пересекались. В итоге задачи могут не уложиться в имеющееся временное окно.

С QMB можно максимально эффективно использовать окно обслуживания, объединив в сценарии исполнение T-SQL скриптов и пакетных файлов на языках VBS, JavaScript, CMD, PowerShell и других. Ниже показан простой пример задачи альтернативного копирования бэкапов с помощью утилиты Robocopy:

Нужно отметить, что пакетный файл может выполняться как на машине, где установлена программа, так и на стороне SQL Server. Это позволяет оперировать файлами бэкапов на стороне SQL Server. Например, можно написать скрипт, который будет архивировать последний бэкап и выкладывать его в любое облачное хранилище или реализовать собственный алгоритм копирования. В следующих статьях я планирую подробнее рассказать об этой возможности и привести скрипты для работы с базами »1С: Предприятие 8».

Вывод сообщений и журнал обслуживания
Все сообщения, выводимые при исполнении скрипта, перенаправляются в журнал обслуживания программы. Это касается сообщений, выводимых командами print, raiserror для T-SQL скриптов, а также сообщений, выводимых в консоль командами echo, для прочих CMD-скриптов и пакетных файлов. И это здорово! Потому что читабельные и понятные логи — это колоссальная экономия времени, а в качестве бонуса — текст ошибок отправляется в email-уведомлении.

Автоматизированная проверка бэкапов через восстановление

Наличие бэкапов ещё не означает, что удастся восстановить данные при сбое — восстановление может завершаться ошибкой по самым различным причинам. Например, может случится так, что цепочка архивных копий будет прервана, а вы даже не будете знать об этом, пока не попробуете восстановить данные. Именно поэтому лучшие практики говорят, что хороший DBA должен регулярно проверять созданные архивные копии, выполняя восстановление из них. Другого 100% способа просто не существует. Microsoft также рекомендует хотя бы единожды протестировать все архивные копии. Задачи для автоматизированного восстановления в SSMS нету, а ежедневно проверять бэкапы вручную желающих найдётся не много.

В QMB имеется специальная задача, которая последовательно восстановит всю цепочку бэкапов для каждой базы данных политики: Full backup –> Differential backup –> Transaction log backup. Восстановление выполняется во временную тестовую базу, которая удаляется после проверки её целостности.

Например, у нас в компании на виртуальном SQL Server имеется около 60 небольших баз данных, общим объёмом под 100 Гб. QMB каждую ночь выполняет проверку возможности восстановления всех баз. Проверка занимает около полутора часов и это даёт нам гарантию того, что все резервные копии проверены. Если цепочка бэкапов будет прервана, то придёт уведомление с примерно такой ошибкой:

1. Задача 'Восстановление из арх. копий во временную БД с последующей проверкой целостности' (база данных: Buh_Oazis)
Message: 4305, Level: 16, State: 1, Line: 21
Журнал в этом резервном наборе данных начинается с номера LSN 5235000000291100001, который еще не может применяться к базе данных. Может быть восстановлена более ранняя резервная копия журналов, включающая номер LSN 5228000000281600001.

Message: 3013, Level: 16, State: 1, Line: 21
RESTORE LOG прервано с ошибкой.

Message: 50000, Level: 16, State: 1, Line: 119
В процессе восстановления возникла ошибка

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

Советы тем, кто захочет настроить подобную проверку:

1. Операция восстановления ресурсоемкая, поэтому её следует включать в сценарии исполняемые только в нерабочее время.
2. Для создания временной тестовой базы и восстановления бэкапов в неё требуется запас дискового пространства, равный как минимум самой большой базе данных в политике + 10% от её объёма.
3. Восстановление больших баз может занимать значительное время. Не включайте задачу по проверке, если не уверены, что операция успеет завершиться в отведённое окно обслуживания.
4. Правильно размещайте задачу в сценарии. Учитывайте, что восстановление выполняется на актуальный момент времени, т.е. на момент исполнения задачи. Например, если задачу проверки бэкапов разместить сразу после создания полной резервной копии, то будет протестирован только последний бэкап, т.к. его будет достаточно для восстановления базы на актуальный момент времени.
5. Если на проверку бэкапов всех баз политики не хватает окна обслуживания, можно проверять бэкапы только определённых баз данных. Либо распределить задачи по дням недели. Например, сегодня ночью проверить бэкапы баз А и B, а завтра — баз C и D.
6. Не рекомендуется делать бэкапы в сетевую папку, т.к. при восстановлении приходится «тащить» файлы бэкапов по сети, что значительно увеличивает время восстановления. Правильнее будет настроить создание бэкапов на локальный диск с ежедневным копированием в сетевую папку.

Автоматизированная поддержка копий баз в актуальном состоянии

С помощью программы можно поддерживать копии баз данных в актуальном состоянии. Например, для разработчиков 1С можно каждую ночь актуализировать тестовую базу. Для этого нужно создать задачу, аналогичную встроенной «Восстановление из арх. копий во временную БД». В задаче необходимо указать базу-источник бэкапов и базу, в которую будет выполняться восстановление. И уже потом разместить задачу в ночном сценарии. На рисунке ниже показана задача, которая выполняет восстановление бэкапов базы Accounting в базу данных AccountingCopy. Причём если на SQL Server нет базы данных AccountingCopy, то она будет создана автоматически.

Во время процедуры восстановления база AccountingCopy будет переведена в однопользовательский режим с отключением всех пользовательских соединений.

Копирование файлов бэкапов

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

Ниже я хотел бы акцентировать внимание на нескольких особенностях копирования бэкапов c помощью QMB:

1. Частота копирования определяется расписанием сценария, содержащего задачу «Копирование архивных копий». Задачу можно разместить в одном или нескольких сценариях.
2. Копируются только новые и изменённые файлы бэкапов — это снижает нагрузку на сеть и позволяет выполнять частое копирование. Например, можно копировать каждый раз после создания нового бэкапа журнала транзакций.
3. Для сетевой папки можно задать срок хранения файлов. Таким образом, на локальном диске SQL Server можно хранить бэкапы, например, за 1 неделю, а в сетевой папке за 1 месяц.
4. Возможно настроить копирование бэкапов только для избранных баз политики.

Восстановление баз данных

Восстановить базу данных можно в стандартной консоли SSMS. Однако в QMB есть аналог с более простыми настройками:

Команда «Восстановление из архивной копии» позволяет:

• Восстанавливать базу данных из бэкапов на указанный момент времени с автоматическим подбором цепочки бэкапов (если бэкапы создавались на этом же SQL Server).
• Восстанавливать базу данных из файла с полной резервной копией.
• Восстанавливать бэкапы одной базы данных в другую базу данных, в том числе новую.
• Выполнять проверку целостности базы данных после её восстановления.
  

Оповещения на Email

Если вы когда-нибудь использовали оповещения по электронной почте в SSMS, то наверняка знаете, что сообщения компонента DataBase Mail содержат минимум информации. Например, в случае ошибки будет отправлено подобное сообщение:

ЗАДАНИЕ ВЫПОЛНЯЕТСЯ:
«Рабочие базы данных.ВложенныйПлан_1» началось в 19.05.2015 17:00:00
ДЛИТЕЛЬНОСТЬ:
0 час., 0 мин., 5 сек.
СОСТОЯНИЕ:
Ошибка
СООБЩЕНИЯ:
Не удалось завершить задание.  Запуск задания был произведен Расписание 9 (MaintenancePlan). Последним выполнявшимся шагом был шаг 1 (Бэкап журнала транзакций).

По такому сообщению можно понять, что произошла ошибка, но для того, чтобы понять, какая именно (и оценить степень её серьёзности), нужно смотреть логи сервера. Кроме того, Database Mail будет отправлять уведомление каждый раз, когда появляется ошибка — не исключена ситуация, когда у вас в почте окажутся сотни однотипных сообщений.

В отличие от Database Mail, QMB отправляет в уведомлении первые 15 строк текста ошибки. Обычно этого бывает достаточно, чтобы понять причину и предпринять нужные действия. Посмотреть полный лог можно в журнале обслуживания программы. Пример сообщения с ошибкой:

Сценарий 'Ресурсоемкие задачи для средних OLTP баз (каждую ночь)' был выполнен с ошибками на сервере 'Srv05'.

Старт сценария: 06.06.2015 1:00
Окончание работы: 06.06.2015 1:29
Длительность: 00:29:28

Всего задач: 7
Выполнено задач: 7
С ошибками: 1

1. Задача 'Восстановление из арх. копий во временную БД с последующей проверкой целостности' (база данных: IPGor)
Message: 4305, Level: 16, State: 1, Line: 21
Журнал в этом резервном наборе данных начинается с номера LSN 5235000000291100001, который еще не может применяться к базе данных. Может быть восстановлена более ранняя резервная копия журналов, включающая номер LSN 5228000000281600001.

Message: 3013, Level: 16, State: 1, Line: 21
RESTORE LOG прервано с ошибкой.

Message: 50000, Level: 16, State: 1, Line: 119
В процессе восстановления возникла ошибка

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

Лицензионная политика и стоимость

Полную версию программы можно скачать на нашем сайте. Есть пробный период (30 дней с момента первой регистрации SQL Server), после которого на каждый SQL Server необходимо приобрести лицензию. Однако QMB позволяет бесплатно (с некоторыми ограничениями) обслуживать базы данных на SQL Express. Также для SQL Express есть недорогие коммерческие лицензии от 1560 руб. На текущий момент стоимость Профессиональной лицензии для российских компаний составляет 7100 руб. Характеристики и цены можно посмотреть тут.

Лицензии вечные и не ограничены по времени. При необходимости лицензию можно легко перенести с одного сервера на другой.

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

Заключение

Иногда я сталкиваюсь с мнением, что сторонние программы к SQL Server рассматривают исключительно как «костыли». Что, якобы, бэкапы или обслуживание, настроенное с помощью таких программ, по определению хуже, чем с использованием штатного агента SQL Server. В таком случае мне приходится объяснять, что SQL Server понимает только инструкции Transact SQL и ему совершенно всё равно, кто именно отправит эту инструкцию — агент SQL Server или другая программа. Например, чтобы проверить целостность базы данных ему нужно отправить команду DBCC CHECKDB, а чтобы сделать бэкап — BACKUP DATABASE. Очевидно, что результат всегда будет идентичным, вне зависимости от того, кто отправит эту команду.

Надеюсь, что этот обзор оказался вам полезен. Помните, что низкая производительность и внезапные остановки SQL Server наносят вред репутации всей службе ИТ, в то время как потеря данных в большинстве случаев приводит к ещё более серьёзным последствиям. Если у вас создан план обслуживания, но нет уверенности в его надёжности, то вы сидите на мине замедленного действия — я настоятельно советую предотвратить ЧП заранее, чем расхлёбывать его последствия.

Спасибо за внимание, готов ответить на ваши вопросы в комментариях.

© Habrahabr.ru