SQL Server 2008: бэкапим с умом. Часть 1: Теория

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


Первые шаги



Если так уж сложилось, что по долгу своей службы вы отвечаете за сохранность баз данных и бесперебойное функционирование SQL-сервера, то наверняка первой вещью, о которой вы задумаетесь, будут бэкапы — банально по той причине, что если вдруг с вашими базами данных случается какая-нибудь беда, а у вас нет бэкапа, вас ждет не самое приятное будущее. Но вместе со светлой мыслью о необходимости бэкапов приходит и миллион самых разнообразных вопросов: с чего начать? резервировать все базы вместе, или лучше каждую отдельно? делать полный бэкап? А может, лучше резервировать только логи? Что ж, давайте попробуем разобраться.

В первую очередь следует определиться с двумя фундаментальными вопросами:

  1. Насколько велика роль каждой отдельной базы данных для компании? и
  2. Потери данных за какой промежуток времени можно считать допустимыми?

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

А вот со вторым вопросом все не так гладко. Хотя бы потому, что в ответ абсолютное большинство руководителей расскажет вам о недопустимости потери данных за любой промежуток времени. Между тем, все мы прекрасно понимаем, что организация отказостойчивой инфраструктуры со сведенным к нулю шансом потери данных — дело сложное, затратное и неподходящее для небольших и средних компаний. Постарайтесь объяснить свою позицию и добиться какого-то более определенного ответа — день, час, полчаса, пять минут или любая другая цифра, с которой вы сможете работать.

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

Full Backup


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

Главный вопрос, связанный с полными бэкапами — как часто их стоит делать? Универсального ответа нет. Понятно, что полное резервирование должно выполняться по меньшей мере раз в неделю. Если у вас не слишком большие базы данных и нет особых проблем с местом под бэкапы, возможно, резервирование раз в день будет более подходящим вариантом. С другой стороны, слишком частое полное резервирование будет нагружать ваш сервер и потребует неоправданно много дискового пространства для хранения бэкапов. Точная цифра зависит от размера ваших баз данных, производительности и загруженности сервера, ответа на «фундаментальный вопрос номер два» и того, как часто вы будете делать бэкапы других типов, например, бэкап лога или дифференцированный бэкап.

Log backup


Бэкап лога отличается от полного бэкапа тем, что в него входят исключительно изменения базы данных (то есть операции INSERT, UPDATE и DELETE) с момента последнего бэкапа, будь то полный бэкап, дифференцированный или предыдущий бэкап лога. Поскольку объем сохраняемых данных крайне мал, такой тип резервирования намного быстрее, требует меньше ресурсов и занимает меньше места на диске. Недостатков, увы, тоже хватает. В первую очередь, бэкап лога бесполезен, если у вас нет хотя бы одного полного бэкапа. Объясняется это тем, что в таком логе не сохраняется никакая информация о таблицах, индексах, хранимых процедурах и так далее. Вторым существенным недостатком является то, что если с момента последнего полного бэкапа вы успели сделать сотню бэкапов лога, а потом случилась беда, то прежде, чем вы восстановите заветный сотый, вам потребуется восстановить не только полный бэкап, но и предыдущие девяносто девять бэкапов лога, да к тому же в правильном порядке. Согласитесь, приятного в такой перспективе не очень много. Еще одна немаловажная особенность заключается в том, что бэкап лога доступен только для тех баз данных, у которых указан FULL или BULK LOGGED режим восстановления.

Для того, чтобы можно было создавать бэкапы лога, ведение этого самого лога должно быть включено. Хотя данная опция работает по умолчанию и предоставляет немало замечательных возможностей, есть ситуации, когда разумнее будет отказаться от ее использования. Во-первых, лучше отключить ведение лога, если вы собираетесь добавить в базу или изменить в ней очень много данных. Такая операция может занять не один час, но с отключенным протоколированием выполнится намного быстрее. Как только данные будут добавлены, можно смело делать полный бэкап БД и включать режим ведения лога. Вторым пикантным моментом является то, что за логом нужно очень пристально следить — если память, которая доступна для записи лога, заканчивается, система останавливает все выполняющиеся транзакции до тех пор, пока память не будет освобождена. Чтобы избежать такой печальной ситуации, требуется делать бэкап лога как можно чаще — тогда как только вы делаете более свежий бэкап, система помечает сохраненную порцию лога как доступную для перезаписи, тем самым восстанавливая занятое дисковое пространство.

Как часто делать бэкапы лога? Опять же, универсального ответа нет. С одной стороны, чем чаще вы делаете бэкап лога, тем большую гибкость получаете при восстановлении и тем меньше данных теряете в случае каких-то происшествий. С другой стороны, если вы делаете полный бэкап раз в сутки, а бэкап лога раз в минуту, то при худших раскладах вам придется восстановить почти полторы тысячи бэкапов, прежде чем база вернется к тому состоянию, которое можно считать последним рабочим. Мало того, что такая перспектива сама по себе не вдохновляет на подвиги, так еще и в это время над вами будут стоять руководители, директора и все, кому не лень, спрашивая о том, когда же всё будет готово и почему так долго. Думаю, бэкап лога чаще, чем раз в пять минут, практически всегда окажется плохой идеей. Существует мнение, что бэкап лога реже, чем раз в час, тоже не имеет смысла, но это заявление кажется мне несколько спорным. В любом случае, если вы будете держаться в этих пределах, скорее всего, это станет неплохим решением.

Differential Backup


Дифференцированный бэкап — это что-то среднее между полным бэкапом и бэкапом лога. В нем сохраняются изменения, сделанные с момента последнего полного бэкапа по настоящее время. Главным его преимуществом по сравнению с бэкапом лога является то, что для полного восстановления базы данных вам достаточно восстановить только лишь полный и последний дифференцированный бэкапы. Недостатком же является то, что вы не можете восстановить промежуточное состояние базы данных на какой-то момент времени — история изменения БД в дифференцированном бэкапе не сохраняется. Как и бэкап лога, такой бэкап бесполезен, если у вас нет полной копии базы данных.

Вместо постскриптума


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

  • Используйте DBCC CHECKDB для проверки каждой базы данных перед копированием, это своевременно предупредит вас о надвигающихся проблемах.
  • Используйте опцию BACKUP WITH CHECKSUM, чтобы убедиться, что все прошло хорошо. Недостатком такого решения является то, что для больших баз данных проверка контрольной суммы может серьезно загрузить систему.
  • Если у вас проблемы с дисковым пространством, доступным для хранения бэкапов, используйте опцию BACKUP WITH COMPRESSION. Сжатие позволяет уменьшить итоговый размер файла с бэкапом в несколько раз, обычно в 3-5. Как и в случае с проверкой контрольной суммы, такая операция очень серьезно влияет на производительность, особенно для больших баз данных. Помните, что опция сжатия доступна только для Enterprise-версии SQL Server 2008 и Enterprise и Standart-версий 2008R2. Все другие версии, в том числе девелоперские, не смогут ни сжать бэкап при резервировании, ни «разжать» при восстановлении.
  • Создавайте отдельный файл для каждого бэкапа, не храните все бэкапы в одном файле. В таком случае, если с этим файлом что-то случится, вы потеряете один бэкап, а не все.
  • Естественно, не храните бэкап на том же диске, на котором хранится ваша БД.
  • Если существует угроза, что кто-то посторонний будет иметь доступ к вашим бэкапам, используйте парольную защиту или шифрование.
  • Автоматизируйте процесс создания бэкапов. Это просто, и вы будете уверены, что у вас всегда есть свежая копия базы данных.
  • Бэкапы бесполезны, если вы не можете их использовать. Поэтому регулярно тренируйтесь в восстановлении баз данных, чтобы при необходимости вы могли в стрессовой ситуации сделать все быстро и безошибочно.

На этом, пожалуй, всё. Хочу сразу заметить, что в рамках данной статьи я намеренно не рассматривал такие моменты, как бэкап файлов, бэкап без очистки лога и многое другое. В следующий раз я постараюсь сделать более «практическую» статью, рассказать о различных путях автоматизации создания резервных копий и привести примеры кода. Надеюсь, вы почерпнули что-то новое и полезное для себя. Have a nice day.
Поделиться публикацией
Похожие публикации
AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама
Комментарии 17
  • +1
    Добавлю по поводу опции 'WITH CHECKSUM'.
    При бэкапе бызы скриптом или через gui, эта опция доступна для использования.
    Если же настраивается Maintenance Plan, то там просто напросто нет подобной опции.
    Для 2008-го сервера можно в плане включить сжатие (WITH COMPRESSION), которое, по словам Microsoft, повлечет за собой активацию проверки контрольной суммы.
    Для 2005-го сервера включить в плане 'WITH CHECKSUM' не представляется возможным. Поэтому пришлось писать отдельную процедуру под это дело.
    • 0
      Прошу прощения, ссылка битая получилась.
      Вот правильная.
      • 0
        Да, действительно, для WITH COMPRESSION по умолчанию активируется проверка контрольной суммы. Изменить это можно, принудительно указав NO_CHECKSUM в списке параметров. Для резервирования без сжатия проверка контрольной суммы по умолчанию отключена.
    • 0
      Спасибо за статью. Получилась: «Бэкапы для самых маленьких» :)
      P. S. у нас используется, обычно, такая схема: бэкап лога раз в час, инкрементал раз в день (ночью, когда никто не работает), фулл — по выходным. Пока, вроде, никто не жаловался.
      • 0
        В вашей схеме удобнее будет делать не инкремент а дифф, тогда в любой день для восстановления надо будет FULL + DIFF + DAILY; в отличии от пятницы в текущем варианте: FULL + INC * 5 + DAILY.
        • 0
          DIFF больше места и времени занимает все же
        • 0
          Я решил пойти по пути «от простого к сложному». В следующий раз буду ориентироваться на возраст от трёх до десяти =)
        • НЛО прилетело и опубликовало эту надпись здесь
          • 0
            Что требует развернутого второго SQL Server со всем необходимым (сервер, ОС, etc.)
            И всё равно, требования к оперативности возобновления сервиса (вами помянутые repl & log shipping) совершенно не отменяют необходимости делать резервные копии.
          • 0
            Ну и если у вас совсем плохо с местом под бэкапы, а версия SQL Server не поддерживает сжатие, то вы можете просто включить NTFS-сжатие на каталог где хранятся бэкапы. Но следует трезво взвесить ЗА и ПРОТИВ такого решения. Если базы не особо большие (меньше 10Гб), может оказаться что проще и дешевле купить любой iSCSI NAS.
            • 0
              Юзаем DPM. Срезы (точки восстановления) каждые 15 минут. Процесс автоматизирован до нельзя. Восстанавливается в пару кликов либо туда же, либо в новую базу, либо в отдельный файл. Главное поглядывать на логи DPM-а, хотя на все с уровнем выше информационного шлет уведомления. В общем настроил и забыл.
              • 0
                DPM — хорошая штука, но не без недостатков. Как минимум — это отдельный продукт, который стоит отдельных денег, если я ничего не путаю.
                • 0
                  Формально говоря, сам DPM бесплатен, но на каждый элемент защиты нужно купить лицензию. Так что да. Он стоит денег и это, пожалуй, единственный его недостаток.
              • +1
                Есть ещё третий фундаментальный вопрос: допустимое время простоя.
                • 0
                  Пожалуй, да. Другое дело, что он очень тесно коррелирует с вопросом номер два.
                • 0
                  Как раз сегодня искал информацию по этой теме. Весь день по кусочкам собирал инфу, а тут — все в одном месте.

                  Надеюсь, темы автоматизации бекапов вы так же коснетесь в продолжении?

                  Увидел, что люди часто сталкиваются с проблемой огромнейшего лога при FULL бекапе и потом не знают как его уменьшить, ибо после бекапа он меньше на становится. Об этом я бы тоже с удовольствием почитал.
                  • 0
                    Да, думаю, к выходным допишу и выложу вторую часть, как раз практическую. Тему с логом тоже постараюсь осветить, спасибо за наводку =)

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