SQL в вопросах и ответах: Перенос, настройка производительности, резервное копирование и зеркалирование баз данных

  • Статья

Пол С. Рэндал

Переход на новый дисковый массив

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

Ответ: Спешу вас обрадовать — запрет на публикацию базы данных без повторной инициализации репликации транзакций или непосредственного изменения системных таблиц существовал только в SQL Server 2000 (и более ранних версиях).

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

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

ALTER DATABASE MyDatabaseName SET OFFLINE;

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

ALTER DATABASE MyDatabaseName 
MODIFY FILE
   (NAME = N'LogicalFileName',
   FILENAME = N'pathname\filename');

После того, как все файлы физически скопированы и их положение обновлено в SQL Server, переведите базу данных обратно в оперативный режим:

ALTER DATABASE MyDatabaseName SET ONLINE;

Кратковременные блокировки страниц

Вопрос: Я не могу разобраться с некоторыми вещами, касающимися настройки производительности. Я читал несколько раз что нужно избегать «кратковременных блокировок страниц». Я не понимаю, что означает «страница» или «блокировка» или почему кратковременная блокировка страницы может быть проблемой. Не могли бы вы объяснить все это поподробнее?

Ответ: Все данные в базе данных SQL Server хранятся в файлах данных. Внутренняя структура этих файлов такова, что они организованы в виде последовательностей блоков длиной 8 КБ, называемых страницами. Страница — основная единица хранения и ввода-вывода в системе управления сервера SQL Server. Страницы обычно хранятся в файлах данных на диске, а перед тем как обработать запрос, SQL Server должен считать и разместить соответствующие страницы в своем кеше (еще его называют буферный пул).

SQL Server использует различные виды страниц для хранения различных видов реляционных данных (например, строки таблицы, строки некластеризованного индекса, текстовых или LOB-данных). Есть также страницы, которые хранят части внутренних структур данных, необходимых SQL Server для организации и получения доступа к страницам хранения реляционных данных.

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

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

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

Подробное объяснение, как наблюдать и решать проблему кратковременной блокировки страниц, выходит за рамки этой рубрики, а за более подробной информацией обращайтесь к следующим источникам:

Листая моментальные снимки баз данных

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

Ответ: Все не так — снимки базы данных никак не заменяют всеобъемлющей стратегии восстановления на случай сбоя: это непрактично и неразумно. Моментальный снимок базы данных не обеспечивает возможностей полного восстановления после сбоя, предоставляемых резервным копированием журнала транзакций. Моментальный снимок не содержит копию всех страниц базы данных, а только измененных с момента первого его создания. Иначе говоря, если база данных повредится, снимок базы данных будет бесполезен без основной базы данных. Это просто набор разрозненных страниц из базы данных, которые не годятся для полного восстановления.

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

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

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

Моментальные снимки баз данных не предназначены для замены частых резервных копий журнала. Более подробные сведения вы найдете в документе Database Snapshot Performance Considerations under I/O-Intensive Workloads» (анализ производительности моментальных снимков базы данных в условиях интенсивной нагрузки на систему ввода-вывода).

Кроме того, если используется модель полного восстановления и резервного копирования журналов транзакций, важно восстановление до состояния на момент, непосредственно предшествующий сбою, и/или на определенный момент времени. (Пояснения по этому поводу даны в моих статьях за август и декабрь 2009 года соответственно: Understanding SQL Server Backups” and “SQL Server: Recovering from Disasters Using Backups.)

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

Вопрос: Меня попросили создать зеркало нашей базы данных, но я боюсь, что зеркальное отображение базы данных не решит нашу проблему. Мы испытывали проблемы с искажением данных в сети устройств хранения (SAN), поэтому для страховки планируем создать зеркальную базу данных. Не будут ли все искажения автоматически попадать на зеркало? Как зеркалирование базы данных поможет решить эту проблему?

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

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

Тем не менее при зеркальном отображении базы данных этого не происходит, потому что из базы в базу страницы не копируются. При зеркальном отображении копируются записи журнала транзакций. Эти записи описывают физические изменения, внесенные в страницы баз данных и не содержат самих страниц. (За подробным объяснением записей журнала транзакций, ведения записей и их восстановления обратитесь к мартовскому выпуску колонки за 2009 год: “Understanding Logging and Recovery in SQL Server.”)

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

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

Примечание редактора: спасибо Кимберли Л. Трипп (Kimberly L. Tripp) из SQLskills.com за техническое рецензирование этой статьи.

Пол С. Рэндал (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.

Материалы по теме