Модели восстановления изнутри

Для меня возможность описать модель восстановления (Recovery Model) — одно из наиболее примечательных качеств SQL Server 2000 и одновременно одно из самых первых свойств SQL Server 2000, о которых я рассказывала в своих статьях. В то время продукт находился еще на стадии бета-тестирования, но было очевидно, что об этом новом свойстве сервера баз данных многие захотят узнать заранее, прежде чем выполнять обновление SQL-систем.

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

Что касается модели восстановления, то здесь администратору SQL Server необходимо выбрать между полной (Full Recovery) моделью и моделью с протоколированием массовых операций (Bulk-Logged Recovery). Третий вариант, упрощенное восстановление (Simple Recovery), подходит только для некоторых системных баз данных, для тестовых или учебных баз, которые нет нужды резервировать и которые в случае необходимости легко создаются заново.

Обсуждение выбора между моделью Full Recovery и Bulk-Logged Recovery вращается вокруг шести операций SQL Server: SELECT INTO, bulk copy program (bcp) и некоторых операций Data Transformation Services (DTS), где используется bcp, BULK INSERT, CREATE INDEX, WRITETEXT и UPDATETEXT. Если эти массовые операции не применяются, то никакой разницы между Full и Bulk-Logged нет, и не имеет значения, что выбрать. Если же любая из названных операций используется, приходится учитывать время выполнения операций, размер журнала транзакций, размер резервной копии журнала транзакций и возможность точного (актуального) восстановления базы данных. В модели Full журнал транзакций растет быстрее, любая массовая операция выполняется медленнее. В модели Bulk-Logged размер журнала транзакций меньше, но резервная копия журнала содержит все измененные данные (не только записанные), поэтому места на диске может понадобиться очень много. И, наконец, если выполняется любая массовая операция, то в модели Bulk-Logged невозможно восстановить базу на момент времени, который совпал со временем работы процедуры резервного копирования журнала транзакций; при восстановлении данных приходится работать с полным журналом транзакций.

Поскольку имеющиеся возможности SQL Server 2000 позволяют восстановить базу данных в любой момент, выполнение сценариев с SELECT INTO и bulk copy никогда не завершится неудачей и всегда будет возможность осуществлять массовые операции в любой модели. К сказанному надо добавить, что в моделях Full и Bulk-Logged всегда можно создать резервную копию журнала транзакций независимо от того, какие операции были выполнены — сценарии резервирования от этого не пострадают. Рамки настоящей статьи не позволяют дать полный анализ каждой модели, но основные различия моделей будут рассмотрены.

Разумный выбор

Модель полного восстановления гарантирует минимальный риск потерять результат проделанной работы в случае разрушения файла базы данных. Если база данных подчиняется этой модели, все операции представляют собой транзакции, т. е. SQL Server полностью записывает каждую строку, вставленную с помощью операции bcp или BULK INSERT в журнал транзакций. Журнал транзакций содержит все индексные строки, которые генерируются операцией CREATE INDEX, а SQL Server записывает в журнал целиком значения полей Text или Image, которые приложение вставляет или обновляет с помощью WRITETEXT или UPDATETEXT.

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

Модель Simple Recovery предусматривает использование быстрых массовых операций и выполнение резервирования и восстановления на основе простейшей стратегии. SQL Server может повторно задействовать секции журнала транзакций по мере того, как содержащиеся в журнале транзакции фиксируются или отменяются — для восстановления данных они уже больше не понадобятся. Таким образом, в этой модели предусмотрены только процедуры Full Backup и Differential Backup. Если попытаться создать резервную копию журнала транзакций в модели Simple Recovery, система выдаст сообщение об ошибке.

Последние исследования

Когда я готовил к выпуску книгу «Inside SQL Server 2000», один из разработчиков SQL Server в Microsoft сообщил мне, что запись транзакций в Simple Model происходит так же, как в Full Model. Другими словами, SQL Server рассматривает как транзакцию каждую строку данных, вставляемых при помощи операций SELECT INTO, bcp и BULK INSERT, в том числе и генерацию индекса строки. Мне было сказано, что основные различия в моделях Full и Simple состоят в том, что в Simple Model журнал транзакций постоянно сбрасывается, что аналогично использованию режима truncate log on checkpoint в более ранних версиях SQL Server. Такое устройство журнала значительно отличается от упрощенного алгоритма, реализованного

в прежних версиях, при котором никогда не протоколировалась операция CREATE INDEX; SQL Server лишь регистрировал сам факт создания индекса. В прежних версиях предлагалось также использовать SELECT INTO/BULKCOPY. Если эта установка включалась, то SQL Server мог выполнить операции SELECT INTO и Fast Bulkcopy, но при этом регистрировался только сам факт проведения подобных операций плюс некоторая вспомогательная информация.

Простейший тесты, которые я использовала, должны были помочь мне убедиться в правильности предположений о том, что Full Model и Simple Model похожим образом обрабатывают транзакции, и в том, что даже в Simple Model в журнал транзакций заносится огромный объем информации во время проведения массовых операций. Поскольку в тестах была продемонстрирована колоссальная активность SQL Server, я полагаю, что моя теория подтверждается.

Для просмотра строк журнала транзакций я воспользовалась недокументированной функцией работы с таблицами fn_dblog(). Эта функция включена в код сценария в Листинге 1, служащий примером теста, который я использовала для проверки своих предположений. Я не могу в точности объяснить, что возвращает данная функция; все, что делает мой сценарий, — отображает число строк, которое соответствует числу записей в журнале транзакций. В бета-версии SQL Server 2000, на котором запускался тест, число строк журналов в Simple Model и Full Model было практически одинаковым. В SQL Server 2000 Service Pack 3 (SP3) журнал содержал больше записей в модели Full Recovery, но в Simple Model строк по-прежнему оказалось больше, чем ожидалось. Однако в моих первоначальных тестах не сравнивалось число записей в журнале транзакций, генерируемых в Simple Model, с числом записей, генерируемых в Bulk-Logged Recovery Model, — я просто игнорировала размер файла журнала транзакций в модели Bulk-Logged. Я исправила эту ошибку, когда Дэн Гузман, SQL Server MVP, выслал мне свой вариант сценария.

Гузман не применял в своем сценарии никаких недокументированных команд; он просто создал базу данных MyDatabase с небольшим журналом транзакций фиксированного размера, а затем запустил один и тот же запрос SELECT INTO при всех трех моделях восстановления. Только в Full Model сервер SQL выдал ошибку, сообщив, что журнал заполнен; ни в модели Simple Model, ни в Bulk-Logged этой ошибки не было. В Листинге 2 показан сценарий Гузмана, который практически идентичен Листингу 1, за исключением того, что у Гузмана размер файла журнала транзакций имеет фиксированный максимум вместо использования (по умолчанию) файла неограниченного размера в сценарии в Листинге 1. Кроме того, у Гузмана тестируются все три модели, а не две, как у меня. Из результатов тестирования по Гузману со всей очевидностью следует, что SQL Server в модели Full Model регистрирует во много раз больше данных, чем в Bulk-Logged и Simple Model.

Читатели могут по своему усмотрению написать собственные тесты. Например, включить в код сценария Листинга 1 третий тест для Bulk-Logged. Помимо определения числа строк в журнале транзакций после операции SELECT INTO можно использовать следующую команду для отображения размера файла журнала транзакций:

DBCC sqlperf(logspace)

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

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

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

Кэлен Дилани — независимый консультант и инструктор по SQL Server. Имеет сертификаты MCT и MCSE. С ней можно связаться по адресу: kalen@sqlmag.com.

Листинг 1. Тесты с использованием fn_dblog() для сравнения моделей восстановления.

USE master
GO
CREATE DATABASE MyDatabase
ON (
	NAME=?MyDatabase?,
	FILENAME=?C:MyDatabase.mdf?,
	SIZE=20MB)
LOG ON (
	NAME=?MyDatabase_Log?,
	FILENAME=?C:MyDatabase_Log.ldf?,
	SIZE=5MB)
GO
USE MyDatabase
GO
—Simple Recovery SELECT INTO test
ALTER DATABASE MyDatabase SET RECOVERY Simple
GO
BACKUP DATABASE MyDatabase to disk = ?C:DBBACKUP.bak?
GO
RAISERROR(?Simple Recovery test?, 0, 1) WITH NOWAIT
SELECT TOP 10000 a.*
INTO MyTable
FROM master..sysobjects a
CROSS JOIN
master..sysobjects b
GO
SELECT count(*) FROM ::fn_dblog(null, null)
GO
USE master
GO
DROP DATABASE MyDatabase
GO
CREATE DATABASE MyDatabase
ON (
	NAME=?MyDatabase?,
	FILENAME=?C:MyDatabase.mdf?,
	SIZE=20MB)
LOG ON (
	NAME=?MyDatabase_Log?,
	FILENAME=?C:MyDatabase_Log.ldf?,
	SIZE=5MB)
GO
USE MyDatabase
GO
—Full Recovery SELECT INTO test
ALTER DATABASE MyDatabase SET RECOVERY FULL
CHECKPOINT
BACKUP DATABASE MyDatabase to disk = ?C:DBBACKUP.bak?
GO
RAISERROR(?Full Recovery test?, 0, 1) WITH NOWAIT
SELECT TOP 10000 a.*
INTO MyTable
FROM master..sysobjects a
CROSS JOIN
master..sysobjects b
GO
SELECT count(*) FROM ::fn_dblog(null, null)
SELECT * FROM ::fn_dblog(null, null)
GO
—cleanup
USE master
DROP DATABASE MyDatabase
GO
SELECT count(*) FROM ::fn_dblog(null, null)
GO

Листинг 2. Сценарий для тестирования возможностей работы с транзакциями.

SET NOCOUNT OFF
USE master
GO
CREATE DATABASE MyDatabase
ON (
	NAME=?MyDatabase?,
	FILENAME=?C:MyDatabase.mdf?,
	SIZE=20MB)
LOG ON (
	NAME=?MyDatabase_Log?,
	FILENAME=?C:MyDatabase_Log.ldf?,
	FILEGROWTH=0,
	SIZE=512KB,
	MAXSIZE=512KB)
GO
USE MyDatabase
GO
—Simple Recovery SELECT INTO test
ALTER DATABASE MyDatabase SET RECOVERY Simple
GO
RAISERROR(?Simple Recovery test?, 0, 1) WITH NOWAIT
SELECT TOP 100000 a.*
INTO MyTable
FROM master..sysobjects a
CROSS JOIN
master..sysobjects b
GO
—cleanup
DROP TABLE MyTable
CHECKPOINT
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
WAITFOR DELAY ?00:00:10?
GO
—Bulk_Logged Recovery SELECT INTO test
ALTER DATABASE MyDatabase SET RECOVERY BULK_LOGGED
CHECKPOINT
GO
RAISERROR(?BULK_LOGGED Recovery test?, 0, 1) WITH NOWAIT
SELECT TOP 100000 a.*
INTO MyTable
FROM master..sysobjects a
CROSS JOIN
master..sysobjects b
GO
—cleanup
DROP TABLE MyTable
CHECKPOINT
BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
WAITFOR DELAY ?00:00:10?
GO
—Full Recovery SELECT INTO test
ALTER DATABASE MyDatabase SET RECOVERY FULL
RAISERROR(?Full Recovery test?, 0, 1) WITH NOWAIT
SELECT TOP 100000 a.*
INTO MyTable
FROM master..sysobjects a
CROSS JOIN
master..sysobjects b
GO
—cleanup
USE master
DROP DATABASE MyDatabase
GO