Советы по эффективному обслуживанию баз данных SQL Server - Обнаружение повреждений

 

Обнаружение повреждений

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

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

Подавляющее большинство повреждений вызываются «оборудованием». Почему оно в кавычках? Ну, оборудование здесь – это на самом деле условное обозначение для «что-то в подсистеме ввода-вывода, под SQL Server». Подсистема ввода/вывода состоит из таких элементов, как операционная система, драйверы файловой системы, драйверы устройств, контроллеры RAID, кабели, сеть и сами диски. Масса мест, где могут возникнуть (и возникают) неполадки.

Одной из наиболее распространенных проблем является сбой питания в момент, когда диск ведет запись на страницу базы данных. Если диск не сможет завершить запись, прежде чем у него кончится электричество (или если операции записи кэшируются и резервного источника питания не хватит для очистки кэша диска), результатом может стать незавершенный образ страницы на диске. Это может произойти, поскольку 8-килобайтная страница базы данных на деле состоит из 16 смежных 512-байтных секторов диска. Неполная запись могла записать некоторые из секторов из новой страницы, но оставить некоторые из секторов из образа предыдущей страницы. Такая ситуация называется разорванной страницей. Как можно обнаружить, когда это случается?

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

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

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

ALTER DATABASE MyDatabase SET PAGE_VERIFY CHECKSUM; 

Чтобы включить обнаружение разорванных страниц для SQL Server 2000, используйте следующий оператор:

ALTER DATABASE MyDatabase SET TORN_PAGE_DETECTION ON; 

Эти механизмы позволяют обнаружить наличие повреждений на странице, но только при чтении страницы. Как можно легко организовать чтение всех распределенных страниц? Лучшим методом для выполнения этого (и обнаружения повреждений любого другого рода) является использование команды DBCC CHECKDB. Вне зависимости от указанных вариантов эта команда всегда будет читать все страницы в базе данных, таким образом заставляя проверять все контрольные суммы страниц или обнаружение порванных страниц. Следует также установить предупреждения, чтобы можно было узнать, когда пользователи сталкиваются с повреждениями при выполнении запросов. Пользователь также может быть уведомлен о всех вышеописанных проблемах, используя предупреждение о ошибках уровня серьезности 24 (рис. 5).

Рис. 5. Установка предупреждения для всех ошибок серьезности 24

Так что другой хорошей рекомендацией является регулярное выполнение DBCC CHECKDB на базах данных для проверки их целостности. Существует много вариантов этой команды и вопросов о том, как часто ее следует выполнять. Увы, технического документа, в котором это бы обсуждалось, сейчас не существует. Однако, поскольку DBCC CHECKDB была основной частью кода, которой я написал SQL Server 2005, я много писал о ней в блогах. См. категорию моего блога "CHECKDB From Every Angle" («CHECKDB со всех сторон»)(sqlskills.com/blogs/paul), в которой имеется много подробных статей, посвященных проверке целостности, рекомендациям и практическим советам. Для невольных администраторов баз данных хорошим правилом является выполнение DBCC CHECKDB после каждого полного резервного копирования базы данных. Я рекомендую выполнять следующую команду:

DBCC CHECKDB ('MyDatabase') WITH NO_INFOMSGS,      ALL_ERRORMSGS; 

Если эта команда что-то выдает, DBCC нашел повреждения в базе данных. Тогда вопрос превращается в: «Что делать, если DBCC CHECKDB находит повреждения?». Здесь-то на сцене и появляются резервные копии.

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

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

Во-первых, следует регулярно выполнять полное резервное копирование базы данных. Это дает единое состояние на момент времени, до которого затем можно восстанавливать. Полную резервную копию базы данных можно сделать, используя команду BACKUP DATABASE. Примеры имеются в электронной документации. Для дополнительной защиты можно использовать параметр WITH CHECKSUM, который проверяет контрольные суммы (если они есть) читаемых страниц и вычисляет контрольную сумму для всей резервной копии. Следует выбрать частоту, отражающую потерю данных или работ, которую можно себе позволить. Например, резервное копирование всей базы данных раз в день означает, что в случае сбоя может быть потеряна дневная работа в данных. В случае использования лишь полного резервного копирования базы данных следует быть в модели восстановления SIMPLE (обычно именуемой режимом восстановления), чтобы избежать сложностей, связанных с управлением ростом журнала транзакций.

Во-вторых, держите резервные копии по нескольку дней на случай, если одна из них будет повреждена – старая резервная копия лучше, чем никакой. Также следует проверять целостность своих резервных копий, используя команду RESTORE WITH VERIFYONLY (опять же, см. электронную документацию). Если при создании резервной копии был использован параметр WITH CHECKSUM, при использовании команды проверки будет проверено, верна ли еще контрольная сумма резервной копии, а также контрольные суммы страниц внутри нее.

В-третьих, если ежедневное полное копирование базы данных может привести к недопустимому для компании уровню потерь данных/сделанной работы, возможно, стоит заглянуть в разностные резервные копии базы данных. Разностная резервная копия базы данных основана на полной резервной копии базы данных и содержит запись всех изменений с момента создания последней полной копии (часто считается, что разностные резервные копии являются добавочными – это не так). В качестве примера стратегии можно привести ежедневное снятие полной резервной копии с созданием разностной копии каждые четыре часа. Разностная копия предоставляет вариант восстановления к состоянию на дополнительный момент времени. В случае использования только полного и разностного резервного копирования баз данных также следует использовать модель восстановления SIMPLE.

Наконец, наилучшие возможности по восстановлению предоставляет резервное копирование журналов. Оно доступно лишь в моделях восстановления FULL (и BULK_LOGGED) и предоставляет резервные копии всех записей журнала, созданных с момента последнего резервного копирования журнала. Поддержание набора резервных записей журнала, а также периодическое проведение полного (и, возможно, разностного) резервное копирования позволяет восстановить состояние на любой момент времени с точностью до минуты. Платить за это приходится тем, что журнал транзакций растет, пока его не «освобождают», создав резервную копию. В качестве примера стратегии здесь можно привести ежедневное снятие полной резервной копии с созданием разностной копии каждые четыре часа и резервной копии журнала каждые полчаса.

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

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

• Удалите избыточною фрагментации файла журнала транзакций.
• Верно установите автоматическое увеличение.
• Отключите любые запланированные операции сжатия.
• Включите мгновенную инициализацию файлов.
• Создайте регулярный процесс обнаружения и удаления фрагментации индекса.
• Включите AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS и создайте регулярный процесс для обновления статистики.
• Включите контрольные суммы страниц (или, как минимум, обнаружение порванных страниц на SQL Server 2000).
• Убедитесь в наличии регулярного процесса для выполнения DBCC CHECKDB.
• Убедитесь в наличии регулярного процесса для выполнения полного резервного копирования базы данных, а также разностного резервного копирования и копирования журнала для восстановления состояния на момент времени.

Я привел в статье команды T-SQL, но многое можно сделать и из Management Studio. Надеюсь, что я дал вам кое-какие полезные указания по эффективному обслуживанию баз данных. Если у вас есть комментарии или вопросы, отправьте их мне —Адрес электронной почты защищен от спам-ботов. Для просмотра адреса в вашем браузере должен быть включен Javascript..

Автор: Пол С. Рэндал
Иcточник: TechNet Magazine
Опубликована - 13.08.2008