• Статья

Вопросы и ответы по SQLОшибки ввода/вывода, зеркалирование баз данных и другое

Пол С. Рэндал (Paul S. Randal)

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

Ответ: старым фокусом для создания поврежденной базы данных в SQL Server® 2000 было ручное удаление строки из таблицы sysindexes тестовой базы данных. Но в случае SQL Server 2005 системную таблицу очень сложно повредить подобным образом. Лучшим способом собственноручного повреждения тестовой базы данных является использование шестнадцатеричного редактора для изменения файла данных, пока база данных закрыта. Вот что следует сделать:

  • Закройте базу данных, так чтобы файлы данных не были заблокированы. (Но постарайтесь не отсоединять базу данных, поскольку в случае повреждения не той страницы ее может оказаться невозможным присоединить снова.)
  • Выберите смещение в файле, скажем, более чем на 100 страниц в файл (как минимум 819200 байтов), но убедитесь, что оно выровнено по 8192-байтовой границе (границе страницы). Это позволяет избежать критических страниц метаданных и битовых матриц размещения, позволяя запустить базу данных и выполнить для нее DBCC CHECKDB.
  • Впишите в файл несколько байтов нулей на выбранном смещении. Использование этого приема практические гарантирует появление ошибок, связанных с повреждением заголовков страниц.

Но вообще, самый быстрый способ создать поврежденную тестовую базу данных – использовать базу, уже созданную другими. Примеры поврежденных баз данных SQL Server 2000 и SQL Server 2005 (с объяснениями) можно найти в моем блоге (по адресу go.microsoft.com/fwlink/?LinkId=115151).

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

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

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

ALTER DATABASE mydb SET PAGE_VERIFY CHECKSUM;

Совет. Измените порт SQL Server по умолчанию

По умолчанию настроенный порт экземпляров SQL Server – 1433. После того, как этот порт использован экземпляром, он не может быть занят другим экземпляром. Следовательно, если необходимо установить второй (именованный) экземпляр порта в сети с использованием tcp, ему понадобится другой порт. А в некоторых случаях администратор может пожелать изменить порт по соображениям намеренного усложнения (хотя такая форма усложнения незначительна и легко взламывается сканером портов). Само собой, после этого понадобиться настроить клиент на использование другого порта. Существуют три распространенных подхода к этому.

Во-первых, предполагая, что администратор сменил порт экземпляра на 5555, можно просто указать номер порта экземпляра внутри имени машины, к которой следует подключиться, используя синтаксис MyServername,5555. Если порт изменится снова, клиентам придется снова изменить свои строки подключения.

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

Третий вариант для именованных экземпляров, где пользователь знает лишь имя экземпляра и указывает имя используя MachineName\InstanceName внутри строки подключения, – это использование службы обозревателя SQL Server. Это уже применено в SQL Server 2000 как часть работающей службы. Однако в SQL Server 2005 служба обозревателя SQL Server была построена как отдельная служба. Помимо обнаружения экземпляров для компьютера, она также отвечает на входящие запросы протокола пользовательских датаграмм (User Datagram Protocol – UDP) на порте 1434, выдавая соответствующий номер порта для запрошенного экземпляра, что делает возможным перенаправление для клиента и поддерживает прозрачное подключение.

— Дженс К. Сьюссмейер (Jens K. Suessmeyer), консультант корпорации Майкрософт по базам данных

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

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

Когда индекс восстанавливается, перед удалением существующего индекса создается новая копия индекса. Эта процедура требует дополнительного места в файлах базы данных – обычно примерного такого же, что используется текущим индексом. В SQL Server 2000 дополнительное место также требовалось для сортировки строк индекса (примерно 20% размера индекса), но это требование было устранено для упрощения восстановления индекса в SQL Server 2005.

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

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

Наконец, можно подумать о сокращении частоты восстановления индексов. Можно даже попробовать использовать альтернативный метод, такой как старый DBCC INDEXDEFRAG, написанный мною для SQL Server 2000, или новый синтаксис ALTER INDEX REORGANIZE в SQL Server 2005 и SQL Server 2008.

Существует полезный технический документ, рассказывающий о фрагментации индекса и дающий рекомендации, когда следует удалять фрагментацию (его можно найти на go.microsoft.com/fwlink/?LinkId=115154). Хотя этот документ был написан для SQL Server 2000, концепции остаются прежними.

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

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

  • Сколько памяти имеется у основных и зеркальных экземпляров? (В идеале цифры должны быть одинаковы.)
  • Сколько вычислительной мощности выделено основным и зеркальным экземплярам? (Здесь цифры также должны быть одинаковы.)
  • Какова пропускная способность подсистемы ввода/вывода на зеркальном экземпляре? (И она должна быть такой же, как на основном.)
  • Какой объем журнала транзакций создается рабочей нагрузкой на каждой из баз данных?
  • Какова пропускная способность сети между основными и зеркальными экземплярами?

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

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

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

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

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

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

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

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

© 2008 Корпорация Майкрософт и CMP Media, LLC. Все права защищены. Запрещается воспроизведение статьи или ее части без разрешения.