Вопросы и ответы по SQL: Секционирование, проверки согласованности и другое

В: Я случайно присоединил производственную базу данных к серверу SQL Server® 2005 и теперь пытаюсь присоединить ее к правильному серверу, который работает под управлением SQL Server 2000. Я попробовал просто отсоединить базу данных и присоединить ее к SQL Server 2000, потом попробовал сделать то же самое через восстановление резервной копии, но оба способа не сработали. Почему это не работает на сервере SQL Server 2000? Эта копия базы данных у меня единственная.

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

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

Например, одно из главных изменений, вносимых при обновлении базы данных SQL Server 2000 до SQL Server 2005, происходит со структурой системных каталогов (также называемых системными таблицами или метаданными), в которых хранятся данные о таблицах, индексах, колонках, распределениях и прочие сведения о физической структуре базы данных.

И с каждой операцией обновления увеличивается номер версии. Например, у баз данных SQL Server 7.номер версии 515, у баз данных SQL Server 2000 номер версии 539, а у баз данных SQL Server 2005 номер версии 611 (или 612, если включен тип vardecimal). По номеру версии SQL Server узнает, какой была последняя операция обновления, произведенная над базой данных.

SQL Server предыдущих версий не могут читать базы данных, обновленные до самых последних версий (например, SQL Server 2000 не может читать базы данных, обновленные до SQL Server 2005). Причина в том, что в старых версиях просто нет программного кода, который способен работать с обновленными структурами баз данных. В этом и состоит проблема вашей базы данных, которая была обновлена до SQL Server 2005: ее невозможно присоединить к SQL Server 2000.

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

Чтобы написать сценарий выгрузки базы данных SQL Server 2005, нужно воспользоваться обозревателем объектов SQL Server Management Studio (щелкните базу данных правой кнопкой мыши, выберите «Задания», затем пункт меню «Создать сценарий»). Интуитивно понятный мастер создаст сценарий, который выгрузит все объекты, индексы, ограничения, триггеры и так далее.

В: Недавно мы перепроектировали базу данных так, чтобы для главной таблицы использовать секционирование — нам сказали, что это поможет увеличить производительность. Наша база данных хранится на одном жестком диске объемом 120 Gb, а таблица полностью помещается в одну файловую группу. Скользящих окон создано не было, просто каждую неделю добавляются новые секции. Необходимо, чтобы все данные были доступны в сети. Большинство наших запросов обрабатывает данные в пределах одной недели, и только некоторые работают с данными за год. Использовать одну файловую группу показалось проще. Прав ли я, или что-то было пропущено?

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

В классическом примере использования секционирования таблица продаж делится на 12 разделов, каждый из которых содержит данные за один месяц текущего года. В конце каждого месяца отключается (и архивируется или удаляется) самый старый раздел и подключается новый. Это и есть то скользящее окно, о котором вы писали. Раздел текущего месяца доступен и для записи, и для чтения; данные предыдущих месяцев доступны только для чтения. Каждый раздел хранится в отдельной файловой группе. В данной схеме используются все преимущества секционирования, но в некоторых условиях она может быть неоптимальной.

Моя жена Кимберли придумала изменение описанной выше схемы, приводящее к более эффективному индексированию. Разделим таблицу продаж на две: одна будет хранится на разделе, доступном для записи и чтения, а другая — на 11 разделах только для чтения. Также поверх обеих таблиц создадим секционированное представление.

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

Кроме того, запросам к данным для чтения и записи не придется обрабатывать данные только для чтения. В SQL Server 2005 удаление разделов из планов запросов несовершенно, особенно для сложных предикатов, однако в SQL Server 2008 оно значительно улучшено. Дополнительные сведения по этому вопросу находятся в записи в блоге Кимберли sqlskills.com/blogs/kimberly/default,month,2007-10.aspx#a71c70243-3d57-4940-9af7-a802b73f2f93.

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

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

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

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

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

В: Недавно мы сильно перепугались за один из наших современных серверов баз данных — неисправная планка памяти приводила к повреждениям данных. Мы обнаружили это, когда в приложении стали появляться произвольные данные. Мы запустили DBCC CHECKDB и обнаружили все типы повреждения данных. К сожалению, резервные копии тоже были испорчены, так что нам пришлось вручную избавляться от плохих данных.

Короче говоря — мы заменили оборудование и включили проверку контрольных сумм страницы. Нам бы хотелось делать регулярные проверки целостности, но у нас немного времени на обслуживание базы данных, а 2,4 TB проверяются долго. Что мы можем сделать?

О: Вопрос о том, как проводить проверку целостности и прочее обслуживание на VLDB (очень больших базах данных) становится все более и более популярным. Многие администраторы опускают руки, видя, что DBCC CHECKDB занимает больше времени, чем позволяет время запланированного обслуживания. (В некоторых случаях базы данных используются круглосуточно и без выходных, и времени для существенного повышения нагрузки на процессор и систему ввода-вывода, которыми сопровождается проверка целостности, просто нет).

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

Использование параметра WITH PHYSICAL_ONLY программы DBCC CHECKDB Обычный запуск DBCC CHECKDB осуществляет большое количество проверок логической целостности данных, сильно нагружая процессор (в общем, именно эта операция и является процессорнозависимой). Параметр WITH PHYSICAL_ONLY ограничивает проверки запуском очень быстрого DBCC CHECKALLOC, который сначала проверяет целостность битового массива распределений, а затем читает и проверяет каждую распределенную страницу базы данных вместе с контрольными суммами. Таким образом, DBCC CHECKDB становится приложением, зависящим от ввода-вывода, с гораздо меньшим временем исполнения (иногда такая проверка на порядок быстрее полной проверки целостности, в результате чего экономится существенное время).

Разделить рабочую нагрузку проверки целостностиЭтот способ предусматривает разбивку таблиц в базе данных на группы равного размера (проще всего разбивать по количеству страниц) и запускать DBCC CHECKTABLE для проверки целостности всех таблиц в одной группе. Например, если сделать семь групп и проверять их по одной в день, используя DBCC CHECKALLOC и DBCC CHECKCATALOG, то будет достигнут результат DBCC CHECKDB, только растянутый на неделю.

Использовать секционирование таблиц с несколькими файловыми группамиСамы большие таблицы в VLDB можно разбить на несколько файловых групп. В примерной схеме проверки целостности можно использовать ежедневный запуск DBCC CHECKFILEGROUP на файловую группу с таблицами, использующимися для записи и чтения, и еженедельный запуск DBCC CHECKFILEGROUPs для файловых групп, содержащих разделы, открытые только для чтения. Логика здесь в том, что данные только для чтения полностью скопированы в резервные копии и не участвуют в ежедневной обработке. Таким образом, повреждение этих данных некритично и, следовательно, их целостность можно проверять реже.

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

Как видите, существуют варианты, позволяющие DBA осуществлять проверки целостности VLDB и одновременно снизить количество дополнительных ресурсов, требующихся для полного DBCC CHECKDB. Как я упоминал раньше, я лично помогал клиентам использовать все четыре подхода. Думаю, и вы найдете подходящий вам вариант.

Совет. Использование триггеров для реализации логики на стороне сервера

В некоторых вариантах моделирования требуется реализовать логику на стороне сервера при помощи триггеров. При этом нобходимо избегать некоторых ошибок. Вот несколько важных моментов.

• Триггеры выполняются по операторам, а не по записям. Таким образом, в триггере должна содержаться дополнительная логика на случай обработки нескольких записей или их отсутствия (триггеры выполняются по операторам, даже если не задействована ни одна запись). Измененные оператором данные находятся в виртуальных таблицах операторов языка модификации данных (DML). Эти таблицы можно соединять, тем самым позволяя работать с данными.
• Триггеры выполняются синхронно внутри транзакции. Каждый раз, когда вам захочется вызвать внешнее приложение или обратиться к внешним данным, помните, что нельзя быть уверенным в приемлемом времени ответа. Например, если вы выполняете над таблицей оператор Update и этот оператор вызвал срабатывание триггера, то транзакция (неявная в случае оператора Update) не закончится до сих пор, пока не отработает вся логика триггера. Если внешнее приложение или процесс возвращают ошибку, SQL server может отменить транзакцию и откатить изменения (в зависимости от того, какая ошибка возникла и как определена ее обработка). Таким образом, если вам требуется что-то сделать внутри триггера с внешним ресурсом и это некритично для транзакции (или не должно выполняться в той же области видимости), то эту задачу нужно передать другому процессу, получая данные асинхронно. В SQL Server 2005 представлен SQL Server Service Broker, который делает выполняет это действие асинхронно.
• Ошибку, вызванную оператором внутри триггера, очень сложно найти. Если в транзакции участвуют несколько таблиц, убедитесь в том, что проверили обработку ошибок в триггерах и, если необходимо, дописали ее. Если в базе данных меняется схема, убедитесь в том, что понимаете, как в таком случае будут работать триггеры, — иначе маленький триггер может сильно ухудшить производительность и стабильность. Другие особенности изменения схемы можно проверить, используя Visual Studio® для специалистов по базам данных. Он автоматически проверяет схему во время редактирования проекта и осуществляет статический анализ кода для выявления нессответствий.