SQL в вопросах и ответах: Восстановление в случае аварии и зеркальное отображение баз данных

  • Статья

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

Пол С. Рэндал

Временное решение

Вопрос Я читал массу противоречивых советов о том, сколько файлов данных на сервере нужно конфигурировать в БД tempdb для сокращения конкуренции PAGELATCH. Не могли бы вы прояснить ситуацию?

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

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

Один из способов ослабления последствий конкуренции — включить флаг трассировки 1118 (подробнее об этом можно узнать в моем блоге SQLskills.com blog в записи по адресу). Более эффективный способ — создание нескольких файлов данных tempdb. При наличии многих файлов данных SQL Server выделяет (или освобождает) страницы в файлах по модели циклического обслуживания. При этом число битовых карт выделения увеличивается (по одной или несколько на каждый файл данных), а общая конкуренция в системе снижается.

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

Но потом на конференции SQL PASS в конце 2011 года Бол Уард (Bob Ward) из отдела поддержки продуктов Microsoft представил более элегантную формулу определения необходимого числа файлов. Если на сервере меньше восьми логических ядер, число файлов данных tempdb должно равняться числу этих ядер. Если на сервере больше восьми логических ядер, начните с восьми файлов данных tempdb, а если конкуренция не снизится, добавьте еще четыре файла.

Имейте в виду, что это очень общий совет. Существует как минимум три случая, когда для устранения конкуренции серверам с 64 ядрами нужны 128 файлов данных tempdb — по два на каждое ядро. Вполне возможно, что в вашем случае ситуация может отличаться.

Идеальный план

Вопрос Недавно я проверял наши планы аварийного восстановления и обнаружил, что мы не выполняем регулярного архивирования наших системных баз данных. Считаете ли вы, что это нужно делать? Что плохого может произойти, если этого не делать?

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

При планировании или тестировании процедуры восстановления многие администраторы БД забывают о системных базах данных (master, model, msdb и всех базах данных репликации). Это очень большая ошибка. Эти базы критически важны для ваших экземпляров SQL Server. Их нужно защитить и проверять их целостность так же, как и с пользовательскими базами данных.

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

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

Критически важно архивировать базу данных msdb, потому что она содержит все задания агента SQL Server (такие как проверка архивов и целостности), уведомления агента SQL Server (об очень важных ошибках и ранних предупреждениях о нарушении работы подсистемы ввода/вывода), пакеты SSIS и таблицы истории архивирования. Если у вас есть какие-либо автоматизированные системы, генерирующие набор инструкций RESTORE для облегчения процедуры восстановления базы данных в случае аварии, скорее всего для выполнения этой задачи они используют таблицы истории архивирования в msdb. Если нет копии msdb (в случае полной утраты информации в подсистеме ввода/вывода), придется вручную собирать инструкции RESTORE, а это сложная задача, удлиняющая время простоя.

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

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

Для начала почитайте следующие страницы Электронной документации по SQL Server:

Все больше, больше и больше

Вопрос Мы никак не можем понять, почему наш журнал транзакций постоянно растет, хотя мы и сжимаем вручную. Мы выполняем фиксацию внутренних транзакций и архивируем журналы, так почему же журнал все растет?

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

BEGIN TRAN; Выполнение какой-то работы … BEGIN TRAN; Выполнение какой-то работы … COMMIT TRAN Продолжение выполнения какой-то работы …

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

Это означает, что при выполнении команды COMMIT TRAN для вложенной транзакции ничего не происходит кроме увеличения значения @@TRANCOUNT, потому что никакой вложенной транзакции нет. Ничего не фиксируется, пока не будет зафиксирована исходная транзакция, что сбросит значение @@TRANCOUNT к нулю. Именно поэтому растет журнал транзакций. На самом деле у вас одна долгоиграющая транзакция.

Кроме того, не нужно регулярно выполнять операцию уменьшения размера журнала. При каждом увеличении журнала транзакций новую порцию журнала нужно инициировать нулями. То, что раньше являлось частью NTFS-тома, перезаписывается нулями. Это нужно, чтобы возможная операция восстановления после сбоя не потерпела сбой (подробнее см. запись в моем блоге на сайте SQLskills.com по адресу).

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

Лучше вообще избегать авторасширения журнала транзакций, если это возможно. Если журнал транзакций расширяется каждый раз после того, как вы его ужимаете, лучше его вообще не трогать. Ясно, что он должен быть больше, чем то прокрустово ложе, в которое вы его пытаетесь загнать.

Свет мой зеркальце…

Вопрос Мы только что реализовали зеркальное отображение базы данных и обнаружили, что больше не в состоянии выполнять перестроение индексов в некоторых наших таблицах. Огромный объем журнала транзакций перегружает сеть и зеркальное отображение базы данных замедляется. Почему это происходит и как избавиться от этой проблемы?

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

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

Объем дополнительных записей в журнале транзакций при перестроении индекса в модели полного восстановления может быть действительно большим и может переполнять сетевые связи между основным и зеркальным серверами баз данных. Если это происходит, может увеличиваться очередь отправки SEND в основной базе данных. Это может приводить к задержкам обработки транзакций в приложениях.

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

Альтернативный вариант обслуживания индексов заключается в использовании ALTER INDEX … REORGANIZE вместо ALTER INDEX … REBUILD. Реорганизация индексов решает только проблему существующей фрагментации индексов. Ее можно прервать, не потеряв никакой выполненной работы. С другой стороны, операция перестроения индекса всегда строит новый индекс независимо от существующего уровня фрагментации. Если прервать ее, вы ничего не получите. Будет выполнен полный откат.

Для более крупных индексов, которые неразумно перестраивать, выполните следующие операции:

День первый Во время, предназначенное для обслуживания сервера, запустите команду ALTER INDEX … REORGANIZE. Пусть поработает час или чуть больше. Завершите команду принудительно. Отката не произойдет, а вы получите некоторое снижение фрагментации индекса.

День второй Снова запустите реорганизацию. Операция «не знает» о первом дне, но быстро увидит выполненное за первый день и начнет устранять фрагментацию следующей порции индекса. Через час или больше снова принудительно завершить операцию.

После дня второго Повторяйте описанную процедуру, пока уровень фрагментации не снизится до требуемого или просто повторяйте описанную последовательность неопределенно долго.

Это позволяет ограничить объем, генерируемый в журнале транзакций (и переносимый при зеркальном отображении) в процессе регулярного обслуживания индексов.  Если вы хотите действовать более интеллектуально и не прекращать задачу по времени, можно отлеживать сгенерированный объем и завершать задачу при достижении определенного порога (подробнее см. запись в блоге SQLskills.com по адресу).

Paul S. Randal

Пол С. Рэндал (Paul S. Randal) — носит звание SQL Server MVP и занимает посты исполнительного директора SQLskills.com и регионального директора Microsoft. Пол работал в команде ядра хранения SQL Server в Microsoft с 1999 до 2007 года. Рэндал написал DBCC-инструкцию CHECKDB /repair для SQL Server 2005 и отвечал за ядро хранения при разработке SQL Server 2008. Он является экспертом по восстановлению после аварий, высокой доступности и обслуживанию баз данных и регулярно выступает с докладами на конференциях в разных странах. Адрес его блога — SQLskills.com/blogs/paul, а также его можно найти на Twitter по адресу Twitter.com/@PaulRandal.