(Возврат на основную страницу)

Содержание номера за Январь 2009 год

SQL Server
для администраторов

Январь 2009
№ 1 (31)

Рон Толмейдж

Способы переноса баз данных

По материалам статьи Пола Ибисона

Сравнение передачи журналов и репликации

По материалам статьи Александра Чигрика

Пошаговое руководство по установке репликации слиянием SQL Server 2000

Сверка данных в репликации слиянием

По материалам статьи Пола Ибисона

Репликация слиянием: ручное управление диапазоном identity

По материалам статьи Майкла Р. Хотека

Очистка метаданных в репликации слиянием

По материалам статьи Ларри Чесната

Зеркальное отображение базы данных и репликация слиянием

Джеймс Ри

Знакомство с журналом транзакций (Transaction Log). Часть 1

Способы переноса баз данных

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

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

Перенос базы данных

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

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

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

•    отсоединение и копирование;

•    перевод базы данных в автономный режим и копирование ее файлов;

•    резервное копирование базы данных и восстановление ее на целевом сервере.

Далее мы обсудим преимущества и недостатки, присущие каждому из этих способов.

Я считаю, что для переноса базы данных лучше всего подходят отсоединение/присоединение и резервное копирование/восстановление. Оба способа имеют как преимущества, так и недостатки, но любой из них позволяет решить нашу задачу. Главное — подготовить путь к отступлению на случай, если что­то пойдет не так!

Сравнение передачи журналов и репликации

Многие статьи о репликации и доставке журналов (Log shipping) в SQL Server 2000 сконцентрированы в основном на том, как их настраивать и конфигурировать. В этой же статье описаны различия между ними. Доставку журналов и репликацию часто сравнивают с кластеризацией, однако кластеризация — это на самом деле технология, созданная, чтобы обеспечить отказоустойчивость системы. Здесь имеются некоторые ограничения: расстояние между узлами ограничено и данные системы физически расположены в одном месте, поэтому нет возможности распределить запросы, чтобы, например, использовать один из серверов для отчетности. Также кластеризацию довольно трудно устанавливать и настраивать, и необходимо иметь лицензию, которая стоит недешево. Поэтому многие DBA используют свой накопленный опыт применения методологии организации и поддержки т. н. резервного сервера, использование которой для поддержания отказоустойчивости требует ручной настройки, но при этом появляется возможность использовать один из серверов для отчетности.

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

Табл. 1

Пошаговое руководство по установке репликации слиянием SQL Server 2000

В этой статье рассматриваются некоторые важные темы организации репликации Microsoft SQL Server: топология репликации, типы и агенты репликации. Также обсуждается репликация слиянием (Merge): как создать необходимые условия для этого типа репликации и как резервировать и восстановить базы данных при таком сценарии репликации. Во время иллюстрации этой концепции предлагается пошаговое руководство по установке и настройке репликации слиянием.

Сверка данных в репликации слиянием

Microsoft SQL Server 7.0/2000 позволяет вам сверять изменяемые репликацией слиянием данные. Вы можете сверять только число строк для проверки данных статьи или сверять число строк и их контрольные суммы.

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

Если в процессе сверки были обнаружены расхождения, программа агента слияния (Merge Agent) посылает системное сообщение с номером 20574 или системное сообщение с номером 20575, если сверка проходит успешно. Программа агента слияния будет продолжать тиражировать подписчикам изменения, даже если сверка будет показывать, что подписчик с издателем не синхронен. Вы можете изучить содержание журнала Windows NT или Windows 2000, содержащего сообщения об ошибках приложений (Application log), чтобы увидеть там результаты отражения работы сверки данных (системные сообщения с номерами 20574 и 20575 будут записаны в системный операционный журнал «Appli­cation log»). Также вы можете так настроить предупреждения на системные сообщения 20574 и 20575 (отнеся их к категории репликации — Replica­tion Alert), чтобы при обнаружении расхождений сверки администратору по электронной почте посылалось письмо, сообщение на пейджер или уведомление по сети.

Репликация слиянием: ручное управление диапазоном identity

Для репликации транзакций и репликации моментальных снимков закономерно, что свойство identity, существующее на изданной таблице, не передается подписчику. Просто это не требуется, поскольку подписчик не предназначен для того, чтобы добавлять строки самостоятельно. Репликация слиянием, однако, предназначена для независимого, автономного добавления данных, и в этом случае свойство identity будет передаваться. Поэтому возникает вопрос — как управлять диапазонами identity и гарантировать, что не будет наложений в значениях identity при синхронизации. Существует два варианта: автоматическое и ручное управление диапазонами нумерации. SQL Server может автоматически управлять диапазонами identity в репликации слиянием, но этот способ имеет плохую репутацию, так как может повлечь за собой некоторые проблемы. Хотя верно то, что когда выделенный диапазон заканчивается раньше, чем произойдет синхронизация, могут быть осложнения, в большинстве случаев их можно просто избежать: свойство identity накладывается на колонку с целочисленным типом данных «int», который принимает значения в диапазоне от -2 147 483 648 до 2 147 483 647, тогда действительно, если у вас нет невероятно большого числа подписчиков, выбор большого диапазона значений на подписчике должен гарантировать, что выделенный диапазон не закончится. Однако в форумах часто задается вопрос — как вручную администрировать диапазоны identity и таким образом брать управление ими на себя. Эта статья представляет собой пошаговую инструкцию применения подобного алгоритма на практике.

Очистка метаданных в репликации слиянием

Таблица MSmerge_contents может стать проблемой для любого администратора баз данных, который обслуживает репликацию слиянием (Merge) достаточно много времени. Проблема состоит в том, что MSmerge_contents будет постоянно расти, если этим не управлять. Репликация слиянием очень интенсивно использует MSmerge_contents, что и вызывает проблемы, поскольку ее размер неуклонно растет. Это обычно проявляется в виде взаимоблокировок и существенного замедления обмена изменений в репликации. Происходит это потому, что время поиска искомых строк в таблице метаданных увеличивается вместе с увеличением ее размера.

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

Главными таблицами для репликации слиянием являются: MSmerge_contents, MSmerge_tombstone и MSmerge_genhistory. В дополнение к ним есть набор таблиц на распространителе, которые не существенны для репликации слиянием, т. к. просто содержат сообщения о состоянии агентов (запуск, остановка, повтор и т. д.). Эти таблицы на распространителе можно не резервировать (если у вас только репликация слиянием), т. к. при потере их достаточно просто обновить. MSmerge_tombstone содержит по строке для каждой записи, которая была удалена в издаваемой таблице. Каждая публикация имеет в настройках период задержки. Задание на очистку удаляет все, что старше этого периода задержки. Это относится только к сообщениям в базе распространителя и к строкам в таблице MSmerge_tombstone. Больше нигде очистка этим заданием не производится. Когда происходит транзакция на издаваемой таблице, срабатывает триггер, который создает записи в таблицах метаданных слияния. Вставки и изменения отражаются в MSmerge_contents. Все новые строки получают поколение 0 (нуль). Таблица MSmerge_genhistory — краеугольный камень к системе репликации слиянием и от нее зависит производительность и гибкость репликации слиянием. Она содержит по записи для каждой строки, изменяемой в каждой статье и в каждой публикации. Когда запущен агент слияния, он считывает все строки с поколением ноль. Потом он просматривает таблицу MSmerge_genhistory и получает самое большое значение для поколения. После этого он вычисляет следующее значение поколения на основании значений, которые он получил из MSmerge_genhistory, и пересылает строки между издателем и подписчиком. Как только они приняты, запись для каждой строки в разрезе статей, посланной в этом поколении, вставляется в таблицу MSmerge_genhistory на сервере получателе. Если запись по этой строке уже присутствует в MSmerge_genhistory, значение поколения увеличивается (на самом деле, это упрощение реальной схемы, которая немного сложнее, но это упрощение достаточно близко к реальности, чтобы можно было понять то, что происходит и почему).

Последний столбец в таблице MSmerge_genhistory назван «coldate». Именно эта дата используется для управления метаданными репликации слиянием. Колонка «coldate» говорит о том, когда в последний раз было сделано изменение каждой из строк, задействованных в каждой из статей. Если в MSmerge_genhistory зафиксировано 500 изменений одной строки, в таблице MSmerge_contents будет содержаться только одна строка для всех 500 транзакций. Строка в таблице MSmer­ge_genhistory будет содержать поколение и время последнего изменения. MSmerge_contents всегда будет содержать только одну строку для всех 500 изменений.

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

•    поиск самого большого значения поколения;

•    обновление значения поколений до самого большого;

•    передача изменений между издателем и подписчиком;

•    поиск в MSmerge_genhistory соответствующих уникальных идентификаторов для строк, которые были переданы, и их обновление;

•    вставка строк в MSmerge_genhistory для всех строк, которые были затронуты впервые;

•    вставка строк в MSmerge_contents для всех строк, затронутых впервые;

•    обновление поколений и порождений для всех строк, которые были затронуты до этого.

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

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

Автор статьи пробовал уменьшить размер MSmer­ge_contents до нескольких сотен тысяч строк с одновременным сокращением числа записей в таблице MSmer­ge_genhistory. Этого он добивался, оставляя в таблицах метаданные о поколениях изменений только за последние две недели. Сценарий, который приводится в конце статьи, содержит таблицы и хранимые процедуры, которые использовались автором для обслуживания таблиц MSmerge_contents и MSmer­ge_genhistory, и позволяющий оптимизировать продолжительность сеансов репликации слиянием.

Зеркальное отображение базы данных и репликация слиянием

На прошлой неделе, когда я проводил очередное занятие, мне задали несколько интересных вопросов, на которые я не нашелся что сразу ответить. Поэтому мы договорились, что я опубликую вопросы и ответы на них в своем блоге. Один из вопросов был про зеркальное отображение базы данных и репликацию слиянием, где на подписчиках установлен SQL Server 2000. Если произойдет переключение ролей, то будут ли подписчики автоматически переключены на резервный сервер?

Будет ли достаточным установить на подписчиках SQL Server 2005 SNAC (SQL Native Access Compo­nents) или потребуется установить MDAC 2.9 в полном объеме? Кроме того, так как репликация слиянием подразумевает, что подписчики могут отключаться от сети и выключать свои ноутбуки, а следовательно, полагаться исключительно на кэш MDAC нельзя, как прописать на подписчиках данные резервного сервера?

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

Ответ на сегодняшний день звучит так: зеркальное отображение баз данных этот сценарий не поддерживает*. Как общее правило, зеркальное отображение баз данных не поддерживает объекты уровня сервера, такие как задания (job) и имена входа (login). Также, зеркальное отображение баз данных не обновляет метаданные репликации и не меняет имя сервера после переключения на резервный сервер. Нельзя сказать, что все это нельзя преодолеть, например, реализовав реакцию на соответствующее уведомление о событии (event notification). Но в рамках этой статьи я хотел бы обратить ваше внимание на то, что эта работа не делается для вас автоматически. Вы должны сами решить насколько эта функциональность подходит для вашей системы и готовы ли вы взять на себя дополнительную работу по ее доработке.

Зеркальное отображение базы данных предназначено для обеспечения высокого уровня доступности базы данных. Если вам нужно добиться высокого уровня доступности на уровне сервера, то вы должны рассмотреть возможность внедрения решения на базе отказоустойчивых кластеров (failover clustering).

Есть несколько важных моментов, которые нужно помнить, если вы собираетесь использовать инструменты обеспечения высокого уровня доступности на уровне базы данных (например, зеркальное отображение баз данных). Во­первых, те имена входа (login), которые нужны для базы данных, должны быть созданы как на основном, так и на резервном сервере, причем их SID должны совпадать. Иначе после переключения на резервный сервер, пользователи в базе данных утратят связь с именем входа (login). Во­вторых, не забудьте, что задания (job), созданные на основном сервере, также должны быть созданы и на резервном сервере. Но как создать задания, когда зеркальная база данных недоступна? Кроме того, после создания, эти задания должны быть отключены и оставаться в таком состоянии вплоть до момента, когда произойдет переключение на резервный сервер. Затем задания должны быть включены обратно. Но как в нужный момент автоматически включить все эти задания? Подразумевается, что вы сами должны запрограммировать соответствующую реакцию на событие переключения на резервный сервер. Звучит как потенциальная работа для Service Bro­ker. И конечно, как я писал раньше, зеркальное отображение баз данных и регулярные (например, по расписанию) BCP/Bulk­вставки, особенно если речь идет о действительно больших объемах данных, плохо сочетаются вместе.

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

Я хочу обратить ваше внимание на то, что между зеркальным отображением баз данных и отказоустойчивыми кластерами есть существенная разница. В случае зеркального отображения баз данных, хранилище данных (data storage) не является общим. Использование отказоустойчивых кластеров помогает, в случае, например, если процессор или память выйдет из строя или произойдет серьезная ошибка в операционной системе. Но как часто вы видите на своей системе «синий экран»? Вероятно, не часто. Зеркальное отображение баз данных может защитить вас еще и от неисправности дисковой подсистемы, так как в этом случае поддерживается полная отдельная копия данных. Мне видится, что вероятность отказа диска или дискового контроллера больше, чем вероятность отказа какой­либо другой подсистемы сервера. Замену отказавшего диска и восстановление дискового массива обычно можно выполнить относительно быстро. Так что время работы на зеркальном сервере, вероятно, также будет не очень большим. Кроме того, в пользу зеркального отображения баз данных говорит тот факт, что в отличие от кластеров, переключение обратно на основной сервер происходит очень быстро (обычно несколько секунд). Это достигается за счет того, что зеркальная база данных постоянно поддерживается в готовности. В случае кластеров процесс восстановления (recovery) базы данных может стать серьезной проблемой.

Еще раз подчеркну, что зеркальное отображение баз данных — это решение для обеспечения высокого уровня доступности на уровне базы данных. Если ваши требования шире, то вы должны либо самостоятельно реализовывать недостающую функциональность, либо задействовать решение на базе кластера. В последнее время в аппаратный лист совместимости (Hardware Compatibility List, HCL) было внесено много самых последних разработок в этой области от разных производителей (например, такого как HP/Compaq). Поэтому внедрение решения на базе отказоустойчивого кластера становится сейчас проще и дешевле, чем когда бы то ни было.

Однако если вы решили пойти по этому пути, то желательно использовать только «полные» кластерные решения. То есть не просто сервер, который сертифицирован для работы в кластере, а сервер, снабженный специальным BIOS, соответствующим SCSI/HBA­адаптером. Должны быть установлены специальные драйвера. Оптический коммутатор (fiber switch) должен быть протестирован и сертифицирован для работы с этой HBA­картой и т. д.

*Статья была написана на основе версии SQL Server 2005 Beta 2. На момент подготовки журнала при использовании зеркального отображения баз данных поддерживается переход репликации на зеркальную базу данных (при сбое основной базы данных) только для баз данных публикации. Переход на резервную базу данных для базы данных распространителя и базы данных подписки не поддерживается. Подробнее см. «Репликация и зеркальное отображение базы данных».

Знакомство с журналом транзакций (Transaction Log). Часть 1

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

Зачем нужен журнал транзакций?

Историю возникновения журнала транзакций проследить сложно и существуют некоторые противоречия, основанные на различиях реализации в разных приложениях. Примером может послужить методика восстановления баз данных по журналам транзакций, в основу которой легла проверенная методика, используемая в Microsoft Access, которая в свою очередь была взята из других приложений и стандартов. Это значит, что не существует какого­то определенного документа, который бы точно определял историю развития журнала транзакций и большинство современной документации частично основано на личном опыте, знаниях и/или истолкованиях. (Теоретическую статью на эту тему можно найти по адресу http://www.sai.msu.su/~megera/postgres/gist/papers/concurrency/p94­mohan.pdf. Можно предполагать, что именно на этой теории, сформулированной специалистами IBM, выросла текущая реализация журналов транзакций.)

Я не буду делать попыток определить истинное происхождение журналов транзакций. Я просто хочу указать, что журналы транзакций во многом основываются на методиках хранения информации для транзакций в Microsoft Access. Для нас важно знать это, т.к. частым заблуждением стало мнение о том, что использование команды TRUNCATE должно не только удалить старую информацию о транзакции, но и уменьшить (SHRINK) файл. Это безусловно не так! Я расскажу об этом подробней в этой статье. Во­первых, для того чтобы понять, почему TRUNCATE не уменьшает физический размер журнала, вы должны понять как транзакции записываются в журнал, и физически, и логически.

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

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

Примером того, как можно использовать эти журналы для аудита безопасности, является возможность просмотра журнала транзакций для выяснения проведенных транзакций, а также их создателей (пользователя или системную учетную запись); и если вы знаете свою базу данных хорошо, вы можете быстро определить, получал ли кто­либо доступ к тем областям базы данных, к которым не должен иметь доступа; или даже хуже, имел ли неавторизованный пользователь доступ к базе данных! Рассмотрение технологий аудита безопасности с помощью журналов транзакций выходит за рамки данной статьи; приведенные выше примеры использованы для предоставления более широкого понимания методов, которые используются для получения нужной информации. Вы можете найти информацию о множестве различных способов аудита журналов и получения дополнительной полезной информации на таких сайтах, как MSDN, SQLServerCentral.com и Google. Также имеет смысл отметить, что существуют приложения сторонних разработчиков, которые используют журналы транзакций для обеспечения аудита безопасности, миграции данных и многих других целей. Как обычно, по возможности внимательно изучайте любые приложения сторонних разработчиков; выполняйте тестирование на тестовых серверах до того, как использовать новые методы или приложения в производстве.

См документацию: Transaction Log Physical Archi­tec­ture, http://msdn.microsoft.com/en­us/library/ms179355.aspx или http://msdn.microsoft.com/ru­ru/lib­rary/ms179355.aspx.

(Возврат на основную страницу)