Предотвращение повреждения баз данных по-прежнему остается актуальной задачей. Но хотя знать возможности предназначенных для этого технологий, безусловно, полезно, какая-то одна выбранная нами технология редко обеспечивает всеобъемлющее решение. Необходимо принимать во внимание много важных факторов, чтобы построить готовую к сбоям стратегию, в том числе учесть, какая модель восстановления базы данных (database recovery model, DRM) больше всего подходит для конкретных условий. Выбор DRM может иметь решающее значение при определении того, какие данные удастся восстановить после сбоя – и самое неприятное, если в разгар мероприятий по восстановлению выяснится, что модель восстановления выбрана неверно.
В SQL Server 2000 введены три модели DRM: полная (Full), журналирование массовых операций (Bulk_Logged) и простая (Simple). Создавая эти модели, разработчики Microsoft в первую очередь были озабочены увязыванием вместе концепций использования транзакций, восстановления информации и производительности работы системы. Однако многие DBA неверно понимают перечисленные DRM просто как работу с такими режимами как SELECT INTO/Bulk Copy и Trunc. Log on Chkpt, которые уже были в более ранних версиях SQL Server. Хотя определенное сходство между DRM и указанными режимами существует, прямой корреляции все же нет. В Таблице 1 перечислены модели восстановления SQL Server 2000 и поясняются общие отличия каждой модели с точки зрения параметрических возможностей, реализованных в предыдущих версиях SQL Server. Например, Full Recovery Model и отсутствие настроек SELECT INTO/Bulk Copy и Trunc. Log on Chkpt – это не дно и то же. При использовании данной модели информация регистрируется по-новому, для некоторых операций более детально. Судить об этом можно хотя бы по работе процедур, на выполнение которых потребуется больше времени и больше места для файла журнала транзакций, чем раньше, в предыдущих версиях SQL Server. Но, вероятно, самая сложная и пока не оцененная по достоинству модель - Bulk_Logged. Рабочие характеристики системы при ее использовании в чем-то схожи с установкой SELECT INTO/Bulk Copy, однако возможности восстановления - иные. Давайте попробуем прояснить ситуацию с работой по модели Bulk_Logged и понять, почему иногда есть смысл переключиться с модели Full на модель Bulk_Logged, а затем обратимся к недостаткам Bulk_Logged, о которых тоже нельзя забывать.
Первое знакомство с Bulk_Logged Recovery Model
При работе по модели Bulk_Logged накладные расходы на исполнение операций минимальны, что позволяет в некоторых случаях обрабатывать их более эффективно, чем при полном резервировании, когда каждая операция досконально регистрируется. Bulk_Logged записывает в журнал только модифицированные экстенты, поддерживая небольшой размер активного журнала и предоставляя возможность обходиться меньшим размером транзакционного журнала, чем в случае с Full Recovery Model. При необходимости что-то восстановить нужно немедленно вернуть операции по журналу транзакций после выполнения операции bulk_logged.
В режиме Bulk_Logged сервер SQL Server создает резервную копию журнала транзакций в два приема. Сначала – и это важное отличие использования Bulk_Logged Recovery Model от Full Recovery Model – SQL Server создает резервные копии всех экстентов, измененных в результате групповой операции. А затем SQL Server создает резервную копию журнала транзакций аналогично тому, как это происходит в режиме Full Recovery Model. Такая двушаговая процедура чем-то напоминает Differential Backup, за исключением того, что в Bulk_Logged Recovery Model сервер SQL резервирует только модифицированные экстенты.
Модель Bulk_Logged позволяет очень быстро выполнить некоторые операции с минимальными расходами на заполнение журнала (поддерживается только битовый образ измененных экстентов), но и возможности восстановления ограничены. Во-первых, если выполняется групповая операция, резервная копия журнала транзакций не дает возможности остановиться на произвольной временной отметке при восстановлении. Во-вторых, если какой-то участок данных не читается (например, сбой на диске), работать с журналом транзакций после групповой операции становится невозможно. При использовании Bulk_Logged Recovery Model минимально регистрируются следующие групповые операции:
- Построение и перестройка индексов;
- Групповая загрузка данных (быстрая загрузка), включая (но не ограничиваясь) BULK INSERT, Data Transformation Services (DTS) Bulk Load, а также операции bcp;
- Операции SELECT INTO, когда создаются постоянные таблицы;
- Операции WRITETEXT и UPDATETEXT для манипуляции с объектами Binary Large Object (BLOB).
Переключение
Технически вы еще в состоянии выполнить поминутное восстановление или восстановление к определенному моменту времени при работе в Bulk_Logged Recovery Model, но это возможно только при условии, что со времени проведения последнего резервного копирования журнала транзакций не выполнялось ни одной операции типа bulk_logged. Не стоит всегда использовать Bulk_Logged Recovery Model, лучше менять модели – с Full Recovery Model на Bulk_Logged Recovery Model, например, с помощью пакетных процедур - это может оказаться наилучшей стратегией. Переключаясь между различными моделями, можно принудительно создавать резервные копии в самое подходящее время, сводя потенциальные потери данных к минимуму.
По-прежнему после пакетной операции необходимо сразу же выполнить резервное копирование журнала, обеспечив тем самым восстанавливаемость данных. Рассмотрим следующий сценарий:
- 12:00 A.M. Transaction-Log Backup (происходит каждый час).
- 12:10 A.M. Начало пакетной операции
- 12:20 A.M. Завершение пакетной операции
- 12:47 A.M. База данных оказывается в состоянии suspect из-за сбоя на диске
- 12:50 A.M. Вы узнаете об этом. При попытке обратиться к последним записям журнала транзакций появляются следующие сообщения об ошибках:
Server: Msg 4216, Level 16, State 1, Line 1
Minimally logged operations cannot be backed up when the
database is unavailable.
Server: Msg 3013, Level 16, State 1,
Line 1 BACKUP LOG is terminating abnormally.
В 12:50 A.M. все, что вы могли сделать – это восстановить базу данных и журналы транзакций, сформированные к 12:00 A.M. Если бы резервная копия журнала была создана в 12:20 A.M., база не оказалась бы в состоянии bulk-logged (независимо от того, была ли установлена модель Bulk_Logged). Вы можете зарезервировать остаток журнала транзакций при работе в модели Bulk_Logged только в том случае, если не было ни одной групповой операции. Создавая резервную копию журнала транзакций сразу же после групповой операции, вы фактически переустанавливаете состояние bulk_logged, и копии журналов транзакций могут быть обработаны без обращения к соответствующим данным базы. Поэтому если бы база данных не была в состоянии bulk-logged в 12:50 A.M., вы смогли бы создать копию остатка журнала транзакций. Если остаток журнала доступен, можно выполнить поминутное восстановление данных и не было бы никаких потерь. Но в нашем случае оказалось, что потеряна вся работа, начиная с 12:00 A.M.
Будем учитывать сказанное, рассматривая другой сценарий. Предположим, база оказалось поврежденной в 12:15 A.M., в середине выполнения пакетной операции. Вы уже знаете, что остаток журнала транзакций недоступен, поскольку все произошло во время групповой операции при работе в режиме Bulk_Logged Recovery Model. Как уже говорилось, было утеряно все, что происходило после 12:00 A.M. Но вы могли бы попытаться хотя бы частично предотвратить потерю данных. Создав резервную копию журнала транзакций в 12:10 A.M., пока база данных была еще доступна (непосредственно перед началом групповой операции), вы, по крайней мере, сохраните данные к моменту 12:10 A.M., т.е. к самому началу запуска групповой операции. Если кроме этой операции с базой в период с 12:10 A.M. по 12:15 A.M. (т.е. к моменту разрушения базы) не проводилось никаких других, вы могли бы воспользоваться резервной копией журнала транзакций для восстановления базы данных по состоянию на 12:10 A.M. Восстановив базу к 12:10 A.M., всегда можно после ликвидации последствий сбоя запустить групповую операцию заново и продолжать работать дальше.
Принципиально важно, чтобы резервная копия журнала транзакций была создана непосредственно перед пакетной операцией и сразу же после ее завершения. Это сведет к минимуму потенциальные потери данных в случае сбоя. Помните, что если база данных находится в состоянии Bulk_Logged Recovery Model и выполняется групповая операция, вы не сможете обратиться к остатку журнала, даже если сам файл журнала транзакций доступен. Если групповая операция не выполнялась, зарезервировать журнал транзакций удастся. Поскольку резервные копии Log Backup, скорее всего, записаны в состоянии Bulk_Logged Recovery Model, кто-то может посчитать, что необходимо всегда работать в режиме Bulk_Logged. Однако в этом случае существует опасность, что вы утратите контроль над проведением восстановительных процедур. Выполнение групповых операций не обязательно является привилегией только DBA или системных администраторов. Всякий владелец таблицы вправе создавать и перестраивать индексы своей таблицы, любой пользователь с разрешением Create Table может задействовать SELECT INTO для создания постоянной таблицы, а любой пользователь, у которого есть доступ к текстовым данным, может манипулировать ими с помощью WRITETEXT и UPDATETEXT.
Поскольку вы не в состоянии отследить, кто и когда запускает групповые операции, очень важно понимать, когда система регистрирует все изменения полностью, а когда – в минимальном объеме. Если по долгу службы вы отвечаете за восстановление данных, и потери данных в организации неприемлемы, единственный способ свести потери к минимуму – работать в режиме Full Recovery Model и взять под строгий контроль внесение изменений в режиме Bulk_Logged. В таком случае переключаться в Bulk_Logged Recovery Model можно будет только тогда, когда это допускается условиями работы. В некоторых организациях такое переключение недопустимо.
Если база данных не обрабатывает транзакции круглосуточно или если риск потерять часть работы оправдан достижением лучшей производительности пакетных операций, можно рассмотреть сценарий временного переключения в Bulk_Logged Recovery Model. Лучше всего предусмотреть возможность периодически переходить на режим Bulk_Logged Recovery Model через пакетную процедуру, и делать это после окончания обычного рабочего дня. Такой подход гарантирует, что окно потенциальных потерь данных ограничено строгими временными рамками. Когда переход на режим Bulk_Logged выполняется из пакетной процедуры, непосредственно перед переключением нужно выполнить Log Backup, после завершения групповой операции снова установить Full Recovery Model и опять выполнить Log Backup. Чтобы посмотреть, как программным путем оптимально переключаться с одной модели на другую во время проведения групповой операции, загрузите со странички статьи сценарий DB Alter for Batch Operation.sql (ссылка Download the Code).
Кроме того, можно разбить большой и сложный пакетный сценарий на несколько более мелких и простых, сдерживая тем самым чрезмерный рост журнала транзакций. Чтобы минимизировать потенциальные потери данных (из-за невозможности зарезервировать остаток транзакций при сбое и переходе базы в состояние suspect), подумайте о возможности выполнить Log Backup во время процесса обработки пакетного задания или в перерывах между отдельными этапами групповой операции. Чем мельче дробится большое и сложное пакетное задание, тем больше вариантов при восстановлении данных будет доступно.
Тестирование модели восстановления
Чтобы посмотреть, как выбранная модель восстановления влияет на размер активного журнала транзакций, размер резервной копии журнала транзакций и скорость выполнения операций, проведем простой тест, в котором будем использовать операцию SELECT INTO для создания новой таблицы TestTable (за основу этой таблицы взяты данные из дебетовой таблицы в другой базе данных). В оригинальной таблице 800 000 записей, объем данных – около 40 Мбайт.
Как видно из Таблицы 2, модели Simple и Bulk_Logged показывают приблизительно одинаковую производительность и близкие по значению размеры журнала транзакций. Однако модели восстановления оказывают влияние не на все операции. Во втором тесте я выполнял операцию UPDATE для 800 000 записей в TestTable. Как видно из Таблицы 3, по сравнению с SELECT INTO операция UPDATE привела для всех трех моделей существенный рост размера журнала транзакций, но продолжительность выполнения UPDATE и размер резервной копии журнала транзакций оказался одинаковым для всех трех моделей.
Глядя на результаты тестов в Таблицах 2 и 3, вы можете подумать, что Bulk_Logged – лучшая из имеющихся моделей восстановления, так как вроде бы допускает работу с резервными копиями Log Backup и работает быстрее, нежели Full Recovery Model. Однако нужно помнить, что при работе в Bulk_Logged Recovery Model журнал транзакций не всегда оказывается пригодным для создания резервной копии журнала. Если устройство, на котором размещены данные, при проведении сервером SQL Server резервного копирования Log Backup недоступно, SQL Server не сможет выполнить задание резервирования, и в результате данные будут потеряны. Таким образом, поминутное восстановление не всегда доступно в режиме Bulk_Logged. Уделите время изучению работы SQL Server в различных режимах восстановления , учитывая при этом все имеющиеся компромиссы и характер влияния моделей на скорость, работу с транзакциями и возможности восстановления промышленных баз данных.