Модель восстановления SQL Server

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

Типы моделей восстановления

В Microsoft SQL Server существуют три основные модели восстановления: ПРОСТАЯ (SIMPLE), ПОЛНАЯ (FULL) и МОДЕЛЬ С НЕВЫПОЛНЯЕМЫМ ЖУРНАЛИРОВАНИЕМ (BULK_LOGGED). Каждая из них предназначена для определённых сценариев использования и предъявляет различные требования к операциям резервного копирования.

Простая модель восстановления (SIMPLE)

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

Особенности:

• Восстановление: Возможно только до момента последнего полного или разностного резервного копирования. Восстановление на конкретный момент времени (point-in-time recovery) недоступно.
• Резервное копирование: Требуется только резервное копирование базы данных (полное и, опционально, разностное). Резервное копирование журнала транзакций не производится.
• Риск потери данных: При сбое после последнего резервного копирования все изменения, внесённые после этого момента, будут потеряны.
• Производительность: Обеспечивает максимальную производительность операций записи, так как журнал не перегружается.
• Применение: Рекомендуется для баз данных, где потеря данных за короткий промежуток времени (например, за несколько часов или суток) допустима, а также для тестовых, разработочных и архивных баз данных. Часто используется в системах, где не требуется высокая степень защиты данных, например, в хранилищах отчётов, которые обновляются раз в сутки.

Полная модель восстановления (FULL)

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

Особенности:

• Восстановление: Поддерживает восстановление на любой момент времени (point-in-time), включая конкретные транзакции, при условии наличия непрерывной цепочки резервных копий журнала.
• Резервное копирование: Требуется регулярное резервное копирование журнала транзакций в дополнение к полному и разностному резервному копированию базы данных. Без резервного копирования журнала он будет неограниченно расти, что приведёт к заполнению дискового пространства.
• Риск потери данных: Минимальный. При правильной настройке и регулярном резервном копировании журнала потеря данных может быть сведена к секундам (например, к моменту последнего сбоя).
• Производительность: Может быть ниже, чем при простой модели, из-за более интенсивного ведения журнала, особенно при выполнении массовых операций.
• Применение: Используется для критически важных бизнес-приложений, где потеря данных недопустима, например, в системах электронной коммерции, банковских системах, системах управления взаимоотношениями с клиентами (CRM) и других OLTP-системах.

Модель с невыполняемым журналированием (BULK_LOGGED)

Модель с невыполняемым журналированием является компромиссом между полной и простой моделями. Она предназначена для оптимизации производительности массовых операций (например, BULK INSERT, SELECT INTO, создание или перестроение индексов). В этой модели для массовых операций журналируется только факт выполнения операции и информация о том, какие экстенты были изменены, но не каждая отдельная строка. Для обычных операций (вставка, обновление, удаление отдельных строк) журналирование ведётся полностью, как в полной модели.

Особенности:

• Восстановление: Восстановление на момент времени возможно только до момента начала массовой операции, если она была выполнена в этой модели. После массовой операции восстановление на конкретный момент времени внутри неё невозможно. Для восстановления после массовой операции требуется полная резервная копия, содержащая изменённые экстенты.
• Резервное копирование: Требуется резервное копирование журнала транзакций, как и в полной модели. Однако для восстановления после массовой операции может потребоваться резервная копия данных, содержащая изменённые экстенты.
• Риск потери данных: Выше, чем при полной модели, так как при сбое во время массовой операции может потребоваться её повторное выполнение.
• Производительность: Значительно повышает производительность массовых операций по сравнению с полной моделью, так как уменьшает объём записываемой в журнал информации.
• Применение: Используется временно при выполнении крупных массовых операций в базах данных, работающих в полной модели восстановления. После завершения массовой операции рекомендуется переключиться обратно на полную модель. Также может использоваться в хранилищах данных, где регулярно выполняются массовые загрузки.

Выбор модели восстановления

Выбор модели восстановления зависит от требований к уровню защиты данных, допустимому времени восстановления и объёму дискового пространства. Основные критерии:

• Допустимая потеря данных (RPO — Recovery Point Objective): Если потеря данных за последние несколько часов или суток допустима, подходит простая модель. Если требуется восстановление на любой момент времени с минимальными потерями, необходима полная модель.
• Время восстановления (RTO — Recovery Time Objective): Восстановление из полной модели может занимать больше времени, чем из простой, из-за необходимости применения нескольких резервных копий журнала.
• Объём журнала транзакций: В полной модели журнал может расти до значительных размеров, если не выполняется его регулярное резервное копирование. В простой модели журнал остаётся небольшим.
• Тип операций: Если база данных выполняет много массовых операций, временное переключение на модель BULK_LOGGED может значительно повысить производительность.

Переключение между моделями

Переключение между моделями восстановления выполняется с помощью команды ALTER DATABASE или через графический интерфейс SQL Server Management Studio. Важно учитывать, что переключение с полной модели на простую приводит к разрыву цепочки резервных копий журнала, и восстановление на момент времени, предшествующий переключению, становится невозможным. Переключение с простой модели на полную требует выполнения полного резервного копирования базы данных для начала ведения журнала в полном объёме.

Практические рекомендации

• Для большинства критически важных производственных баз данных рекомендуется использовать полную модель восстановления.
• Для тестовых, разработочных и архивных баз данных, где потеря данных допустима, целесообразно применять простую модель.
• Модель BULK_LOGGED следует использовать временно при выполнении крупных массовых операций, после чего возвращаться к полной модели.
• Регулярное резервное копирование журнала транзакций является обязательным условием для полной модели восстановления, чтобы предотвратить его неограниченный рост и обеспечить возможность восстановления на момент времени.
• При выборе модели восстановления необходимо учитывать не только технические требования, но и бизнес-требования к уровню защиты данных.

Источники

• Документация Microsoft SQL Server: «Модели восстановления (SQL Server)»
• Книга «Microsoft SQL Server 2019. Справочник администратора» (авторы: Уильям Ассоф, Питер Шиндлер)
• Книга «SQL Server 2019. Администрирование» (авторы: Дмитрий Григорьев, Илья Варламов)