ЗАКАЗАТЬ

Обсудить книгу можно тут: http://www.sql.ru/forum/actualthread.aspx?tid=643700

Пример статьи из сборника:

Высокая доступность в репликации SQL Server 2008 с зеркалированием и доставкой журналов

Автор Александр Юрьевич Гладченко

Статья написана по мотивам технического документа Майкрософт: "SQL Server Replication: Providing High Availability using Database Mirroring" и описания в электронной документации SQL Server 2008 Books Online (далее BOL): "Репликация и зеркальное отображение базы данных".
В этой статье мы рассмотрим новые возможности обеспечения высокой доступности тиражируемых данных, используя для этого Репликацию транзакций, Доставку журналов и зеркальные копии баз данных.
Основанная на Репликации транзакций распределённая система хранения данных может обеспечить высокую устойчивость к отказам серверов баз данных. Подобные решения позволяют достичь высокой степени доступности, за счёт поддержки избыточных копий данных. Кроме Репликации, избыточность на уровне баз данных способны обеспечить несколько механизмов SQL Server 2008. Это такие возможности, как резервное копирование с последующим восстановлением, Доставка журналов и Зеркальное отображение базы данных. Причём, Зеркальное отображение является единственным механизмом, который поддерживает точную копию защищаемой базы данных практически в реальном масштабе времени, и гарантирует отсутствие потерь данных.
В этой статье на примерах мы посмотрим, как можно использовать Зеркальное отображение реплицируемой базы данных для повышения её доступности. Мы рассмотрим как Репликация и Зеркальное отображение влияют друг на друга, а также, как Зеркальное отображение совместимо с Доставкой журналов и как Доставка журналов совместима с Репликацией. Кроме того, в этой статье мы коснёмся возможностей использования для первоначальной синхронизации баз данных механизмов Доставки журналов, и вкратце рассмотрим принципы работы инициализации подписчика, основанной на логических номерах виртуальных журналов (LSN), которая позволяет сократить время восстановления после отказа при наличии зеркальной копии базы данных Подписчика.

Совместимость Доставки журналов с Зеркальным отображением и Репликацией

В настоящей статье мы не ставим целью полностью раскрыть тему совмещения Доставки журналов с Зеркальным отображением баз данных и Репликацией. Эта тема достаточно подробно раскрыта в BOL. Здесь мы рассмотрим только те аспекты Доставки журналов, которые нам пригодятся для первоначальной синхронизации основной базы данных Зеркального отображения и баз данных Подписчиков в топологии репликации. Коротко будет сказано и о том, как можно использовать Доставку журналов в целях резервного копирования участвующих в Репликации баз данных.
Целью Доставки журналов SQL Server 2008 является обеспечение автоматической синхронизации баз данных, которая осуществляется путём резервного копирования журналов транзакций в базе данных Источника, и последующей доставке и восстановление копий журнала транзакций в базе данных Получателя. Сервер, который обслуживает базу данных Получателя, выступает в роли резервного сервера или сервера отчётов. Сервер отчётов предоставляет возможность обработки запросов пользователей на чтение данных. Одна база данных Источника может синхронизироваться с одной или с несколькими базами данных Получателей. Доставка журналов применима к тем базам данных, которые используют полную модель восстановления или модель восстановления с неполным протоколированием.
Давайте договоримся, что сервер, первоначально обслуживающий базу данных Источника, будем называть сервером источника (термин "основной сервер" был бы удобнее, но он используется в Зеркальном отображении, и мы его не станем использовать, чтобы не было путаницы), а сервер, первоначально обслуживающий базу данных Получателя, называть резервным сервером. После настройки, сервер источника обслуживает базу данных Источника, но может сменить роль и обслуживать базу данных Получателя. Изменение роли приводит к одновременному изменению настроек на всех участвующих в Доставке журналов серверах, поскольку сервер источника может быть только один.
В Доставке журналов предусмотрены средства мониторинга и оповещения обо всех штатных и нештатных состояниях участников процесса Доставки журналов. Начиная с SQL Server 2008, поддерживается сжатие резервных копий, и такие копии можно использовать для Доставки журналов. Копии журналов создаются и доставляются с помощью заданий автоматизации службы SQL Server Agent, всего таких заданий четыре: задание резервного копирования Источника, задание копирования файлов Получателю, задание восстановления копий на Получателе и задание рассылки предупреждений.
Формат хранения данных на диске для SQL Server x64 и x86 одинаков, т.е. в Доставке журналов могут участвовать сервера баз данных под управлением 32-х и 64-х разрядных операционных систем. Доставка журналов поддерживается следующими редакциями SQL Server 2005: Enterprise Edition, Standard Edition и Workgroup Edition. Серверы, задействованные в доставке журналов, должны иметь одинаковые параметры сортировки, а базы данных источника и получателей могут использовать только модель полного восстановления (Full) или модель восстановления с неполным протоколированием (Bulk Logged).
Доставка журналов может быть средством тиражирования данных, однако, в этой статье мы рассмотрим возможность использования Доставки журналов для первоначальной синхронизации баз данных в Зеркальном отображении и Репликации.
Из BOL мы знаем, что Доставку журналов допустимо совмещать с Зеркальным отображением. В таком случае база данных Источника должна являться основной базой данных Зеркального отображения. Причём, Зеркальное отображение может быть настроено в любом из трёх режимов его работы. Базы данных Получателей не должны находиться на том же экземпляре сервера, что и база источника или на экземпляре Зеркального отображения базы Источника. Поскольку Зеркальное отображение позволяет делать резервные копии только базы данных основного сервера, использование в качестве базы данных Источника зеркальной копии основной базы невозможно. Настраивать Доставку журналов и Зеркальное отображение на основном сервере можно в любом порядке.
Допустимо совмещать Доставку журналов с Репликацией, и это справедливо для издаваемой базы данных, баз данных Подписок, и для базы данных Распространителя. Следует учитывать, что процесс репликации будет прерван в случае перехода на резервный сервер, поскольку агенты репликации не умеют правильно реагировать на изменение ролей серверов в Доставке журналов, и это приведёт к тому, что транзакции перестанут тиражироваться подписчикам. После того, как роль базы данных Источника вернётся в первоначальное состояние, репликация возобновляется, и все те транзакции, которые были скопированы Доставкой журналов с резервного сервера на сервер источник, реплицируются подписчикам. В случае безвозвратной потери базы данных Источника, достаточно переименовать Получателя, чтобы возобновить процесс репликации.
Первоначальная инициализация базы данных Получателя в процессе настройки Доставки журналов осуществляется путём восстановления полной резервной копии базы данных источника с параметрами NORECOVERY или STANDBY. Для организации доставки журналов необходимо создать сетевой ресурс, в котором будут создаваться резервные копии журналов транзакций и который будет доступен всем участвующим в Доставке журналов серверам. В нашем примере в качестве такого ресурса будет использоваться каталог сервера источника C:\MSSQL\LogShip\.
Существует возможность синхронизировать процесс доставки журналов с тиражированием данных в репликации транзакций. Такая синхронизация устанавливается с помощью системной хранимой процедуры sp_replicationdboption, которая позволяет для публикуемой базы данных и базы данных Распространителя установить опцию "sync with backup". Когда эта опция установлена для базы данных Распространителя, это гарантирует, что транзакции в журнале публикуемой базы данных не будут усечены до тех пор, пока не будет создана их резервная копия в базе данных Распространителя. Усечение журнала транзакций публикуемой базы данных откладывается до завершения резервного копирования усекаемых транзакций в базе данных Распространителя. Установка этой опции позволяет управлять точкой усечения базы данных Публикации. Новое значение вступает в силу после очередного запуска Агента чтения журнала или по истечении заданного параметром агента -MessageInterval интервала времени, если Агент чтения журнала работает в непрерывном режиме. За счёт того, что в базе данных распространителя не будет транзакций, которых нет в резервной копии публикуемой базы данных, можно не боятся рассогласованности издаваемой базы данных и базы данных Распространителя в случае восстановления издаваемой базы данных из резервной копии. Однако, следует помнить, что такой способ синхронизации добавляет задержку в процесс тиражирования транзакций и не гарантирует отсутствия потерь данных, в случае повреждения журнала транзакций. Кроме того, для Доставки журналов опция важна только для издаваемой базы данных.
В рамках этой статьи мы не будем рассматривать совместную работу Доставки журналов и Репликации. Доставка журналов нами будет использоваться только для целей первоначальной синхронизации баз данных подписчиков и Зеркального отображения базы данных Издателя. Поэтому сценарии включения Доставки журналов будут использоваться в примерах с демонстрацией включения Зеркального отображения и Репликации.
Дополнительную информацию о Доставке журналов можно получить в следующих статьях BOL:

• Развертывание доставки журналов
• Репликация и доставка журналов
• Зеркальное отображение баз данных и доставка журналов
• Резервное копирование и восстановление из копий реплицируемых баз данных
• Стратегии резервного копирования и восстановления из копии репликации моментальных снимков и репликации транзакцийСовместимость Зеркального отображения и Репликации

Не все базы данных в топологии Одноранговой репликации транзакций допускают Зеркальное отображение (зеркалирование). Например, это невозможно для базы данных Распространителя. В Репликации транзакций зеркалирование базы данных подписчика требует учёта некоторых ограничений, которых нет для издаваемой базы или в одноранговой топологии. Возможность использования в репликации зеркалирования зависит от того, насколько будут способны задействованные агенты репликации отрабатывать состояние отказа основной базы данных и автоматически переключаться на зеркальную копию. Так, например, ни один из соответствующих агентов репликации не может правильно отреагировать на отказ базы данных Распространителя и переключиться на работу с зеркальной копией этой базы данных. Для обеспечения высокой доступности Распространителя его следует вынести на выделенный компьютер и отдать его под управление отказоустойчивого кластера. Однако, те агенты репликации, которые соединяются с публикуемой базой данных, умеют переключаться на зеркальную копию, и в случае отказа способны подключиться автоматически, обеспечив непрерывность процесса репликации.
С подписчиками всё не так просто, как с издателем. К сожалению, ни один из работающих с базами на подписчике агентов репликации не предназначен для автоматического переключения в случае отказа. Самым естественным путём переключения потока данных репликации является удаление подписки и создание её заново.
Дополнительные сведения можно получить в следующей статье BOL: "Репликация и зеркальное отображение базы данных".

Влияние зеркалирования на работу Агента чтения журнала

Зеркалирование публикуемой базы данных влияет на поведение Агента чтения журнала, его состояние становится зависимым от состояния зеркалирования. Агент чтения журнала зеркалируемой базы данных будет копировать из журнала вначале записи тех транзакций, которые были до этого скопированы и завершены в журнале регистрации транзакций зеркальной базы данных (процесс, с помощью которого пишутся записи в журнал транзакций зеркальной базы данных, в документации называют hardening - закрепление). Т.е. реплицироваться будут только записи с таким LSN, который больше LSN последней закреплённой в журнале транзакций зеркала.
Это позволяет выставить из топологии основной сервер (зеркало доступно, но существуют такие записи в журнале транзакций, которые еще не были закреплены на зеркале) или изолировать его (когда зеркало недоступно). В обоих случаях, пока основной сервер работоспособен и его база данных доступна, любые изменения в его базе данных не будут реплицированы, пока соответствующие записи журнала транзакций не будут закреплены на зеркале.
Такое поведение добавляет задержки в поток репликации, и если произойдёт отказ зеркалирования, будет гарантировано, что записи в журнале Подписчика не обгонят фиксацию в основной базе данных.
Ели Агент чтения журнала вынужден ждать закрепления записей в журнале транзакций зеркальной базы данных, в хронологии работы Агента чтения журнала будут появляться уведомления следующего вида: "Replicated transactions are waiting for next Log backup or for mirroring partner to catch up".

Изменение поведения Агента чтения журнала при установке флага трассировки 1448

В редких случаях, эффект задержек из-за зеркалирования для логики работы использующих репликацию приложений может оказаться не приемлем. Для решения этой проблемы добавлен новый флаг трассировки 1448, который предписывает репликации продолжаться даже в тех случаях, когда основная база данных выставлена или изолирована. Обычно, Агента чтения журнала ждет, пока не будут закреплены записи в журнале регистрации транзакций зеркальной базы данных, после чего он копирует их в базу данных Распространителя. Когда сервер запущен с флагом трассировки 1448, это ожидание исключается, и Агента чтения журнала может сразу копировать изменения, независимо от состояния зеркалирования.
Этот флаг трассировки можно применять в SQL Server 2008, а для SQL Server 2005 он появился в составе Cumulative update package 2 для SQL Server 2005 Service Pack 2. Подробности о флаге трассировки №1448 можно узнать в статье базы знаний Майкрософт №937041.
Обратите внимание на потенциальную опасность исключения задержек тиражирования в репликации. Для приложений отсутствие задержек может иметь решающее значение, и это может послужить причиной установки флага трассировки №1448 при использовании зеркалирования с репликацией. Надо понимать, что существует вероятность того, что использование этого флага приведёт к проблемам в случае отказа Зеркального отображения. Эти проблемы будут рассмотрены ниже.

Влияние отказа зеркалирования на работу Агента чтения журнала

Когда происходит автоматическая или ручная отработка отказа Зеркального отображения, Агент чтения журнала должен автоматически соединиться с новым основным сервером и продолжить копировать транзакций (если параметр -PublisherFailoverPartner был установлен правильно, как это будет описано ниже). Есть два случая, когда этого может не произойти. Первый, это когда отказ не может быть отработан правильно, потому что зеркальный сервер по каким либо причинам не может стать основным сервером. Второй случай может произойти только тогда, когда включён флаг трассировки №1448, о котором только что упоминалось выше.
Когда Зеркальное отображение не работает в режиме высокой доступности или основной сервер был выставлен или изолирован, может оказаться, что на основном сервере есть завершённые транзакции, которых еще не были закреплены на зеркале. Если основная база данных станет недоступной, и произойдёт передача её роли зеркалу, может случиться потеря тех транзакций, которые были завершены на основном сервере, но еще не были закреплены на зеркале. Если к тому же включён флаг трассировки №1448, некоторые из завершённых транзакций основного сервера могут быть скопированы, но ещё не закреплены на зеркале. Это приведёт к тому, что состояние метаданных о тиражируемых транзакциях на Распространителе будет опережать реальное состояние транзакций на принявшем роль основной базы данных зеркале. Т.е. Агент чтения журнала уже передавал Распространителю некоторое количество транзакций. После обнаружения таких расхождений, Агент распространителя выдаст ошибку: "The process could not execute 'sp_repldone/sp_replcounters' on ' GLADCHENKO-TEST'". Кроме этого сообщения об ошибке, будут и детализирующие ситуацию сообщения, из которых можно узнать, какой номер виртуального журнала пытался считать Агент чтения журнала. Исправить положение можно запустив с помощью системной хранимой процедуры sp_replrestart принудительную синхронизацию метаданных Издателя и Распространителя. Однако, велика вероятность того, что из-за расхождений в метаданных часть не синхронизированных в результате отказа основного сервера транзакций уже может попасть в базы данных Подписчиков. Это приведёт к тому, что Агент распространителя тоже прекратит свою работу, выдав сообщение об ошибке синхронизации данных. Поскольку в результате сбоя синхронизации Распространитель просто повторит изменения, передаваемые не прошедшими синхронизацию транзакциями, для преодоления ошибки работы Агента распространителя достаточно сменить его профиль на стандартный профиль с именем: "Continue on data consistency errors".

Как влияет нарушение зеркалирования на работу Агента чтения журнала

Как уже отмечалось, в случае отказа Зеркального отображения Агент чтения журнала станет работать с журналом транзакций зеркала основной базы данных. Возможность переключения на сменивший свою роль новый основной сервер обеспечивается параметром запуска агента -PublisherFailoverPartner. Пока между обоими участниками Зеркального отображения действует партнёрство, т.е. они продолжают работать по схеме основная база - зеркало основной базы, в случае отказа основной базы данных Агент чтения журнала подключится к принявшей роль основной, зеркальной базе данных.
Если основной сервер будет недоступен очень долго, лучше всего удалить зеркалирование, что позволит избежать проблем с ростом журнала транзакций зеркальной базы данных. Если отказ приведёт к тому, что будет нарушено партнёрство основной и зеркальной баз данных, Агент чтения журнала будет продолжать работать с зеркалом, пока ситуация с партнёрством не восстановится или зеркальное отображение не будет удалено. Если потом агента перезапустить, он попробует соединяться с основной базой данных, которая больше не является издаваемой базой данных, что приведёт к сбою репликации. В хронологии работы Агента чтения журнала появятся сообщения такого типа: "User-specified agent parameter values: -Publisher GLADCHENKO-TEST'". Также будут более детальные сообщения об отказе подключения. Для того чтобы решить подобную проблему, и заставить Агент чтения журнала продолжить свою работу, создайте на Распространителе псевдоним действующего сервера издателя.

Настройка периода хранения Распространителя

По умолчанию на Распространителе значение периода хранения транзакций установлено в ноль. Это означает, что транзакции очищаются сразу же, как только они будут доставлены всем Подписчикам. При использовании Зеркального отображения, в момент, когда из-за отказа Агент распространителя переключается на работу с зеркальной копией базы данных, нужно чтобы в базе данных распространителя ещё оставались транзакции, которые уже доставлены всем Подписчикам. Такое случается, когда зеркалирование работает в высокопроизводительном режиме, т.е. передача транзакций зеркалу идёт асинхронно и состояние зеркала может сильно отставать от состояния основной базы данных. Тогда, при необходимости добавления нового Подписчика, которого нежелательно инициализировать, у Агента распространителя должна существовать возможность догрузить новому Подписчику те транзакции, которые на момент добавления подписчика ещё не успели закрепиться в зеркале. Другой такой случай может произойти при отказе сервера Издателя, когда данные в базе данных публикации больше не доступны, и это не позволяет корректно инициализировать новую базу данных подписчика. В таком случае, при отсутствии транзакций на Распространителе, может произойти потеря данных.
Чтобы избегать потери данных или длительного простоя в системе с зеркалированием базы данных подписчика, рекомендуется установить адекватный период задержки транзакций у распространителя.
В наших примерах мы будем использовать следующий вариант настройки параметра задержки распространения:

USE distribution
EXEC sp_changedistributiondb N'distribution', N'min_distretention', 3
EXEC sp_helpdistributiondb;
GO

Типы ручной синхронизации Подписчика

Репликация не поддерживает автоматический переход на зеркальное отображение подписанной на публикацию базы данных, т.е. хотя зеркалирование и возможно, Агент распространителя не сможет переключиться на зеркало после отказа основной базы данных Зеркального отображения. Работа Агента распространителя закончится ошибкой сразу, как только он поймёт, что не может подключиться к базе данных, являющейся основной в партнёрстве Зеркального отображения. Зеркалирование Подписчика можно настраивать как до, так и после подписки. Однако, если планируется инициализация Подписчика моментальным снимком Публикации, эффективнее будет настраивать Зеркальное отображение после инициализации базы данных Подписчика.
В случае отказа Зеркального отображения базы данных Подписчика, необходимо перенаправить Репликацию на зеркальный сервер, туда, где теперь располагается новая база данных Подписчика, на новом основном сервере. Хотя этот метод требует заново оформить подписку, полной её инициализации не потребуется, и это благодаря новому типу синхронизации SQL Server 2008.
Все методы ручной инициализации Подписчика основаны на том, что база данных приводится в согласованное с Издателем состояние, и это становится отправной точкой тиражирования последовавших за этим состоянием транзакций Издателя. После создания Подписки с ручной синхронизацией, Агент распространителя должен иметь информацию о том, какие транзакции нужно тиражировать, не потеряв при этом новые данные или изменения в имеющихся данных. В качестве такой отправной точки используется тот номер LSN, который применим к соответствующему методу инициализации. Например, в методе с указанием параметра "replication support only" определение LSN происходит автоматически. Когда же инициализация осуществляется восстановлением из резервной копии, LSN берётся из заголовка файла резервной копии. В случае инициализации по LSN, необходимый номер виртуального журнала задаётся пользователем. Этот номер нужно указывать аккуратно, т.к. относящиеся к нему транзакции должны присутствовать на этот момент в базе данных Распространителя. Вот почему так важна задержка транзакций у Распространителя, это очень помогает в случае отказа.

Примеры

Для демонстрации новых возможностей обеспечения доступности Репликации SQL Server 2008 нам потребуется сервер Распространитель, который в представленном ниже сценарии создания Распространителя называется GLADCHENKO-VHD:

-- Сценарий создания Распространителя
use master
exec sp_adddistributor @distributor = N'GLADCHENKO-VHD'
, @password = N'' -- безопасность репликации не тема этой статьи,
                  -- оставляем уязвимой
GO
exec sp_adddistributiondb
  @database = N'distribution'
, @data_folder = N'C:\MSSQL\DATA'
, @log_folder = N'C:\LOG\DATA'
, @log_file_size = 2
, @min_distretention = 0
, @max_distretention = 72
, @history_retention = 48
, @security_mode = 1
GO

use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties'
    and type = 'U '))
    create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder'
    , 'user', 'dbo', 'table', 'UIProperties', null, null)))
    EXEC sp_updateextendedproperty
  N'SnapshotFolder'
    , N'C:\MSSQL\ReplData'
    , 'user'
    , dbo
    , 'table'
    , 'UIProperties'
else
    EXEC sp_addextendedproperty
  N'SnapshotFolder'
    , N'C:\MSSQL\ReplData'
    , 'user'
    , dbo
    , 'table'
    , 'UIProperties'
GO

exec sp_adddistpublisher
  @publisher = N'GLADCHENKO-VHD'
, @distribution_db = N'distribution'
, @security_mode = 1
, @working_directory = N'C:\MSSQL\ReplData'
, @trusted = N'false'
, @thirdparty_flag = 0
, @publisher_type = N'MSSQLSERVER'
GO

Зеркальное отображение издаваемой базы данных

Мы будем использовать сервер GLADCHENKO-TEST в качестве основного сервера в зеркале и сервера издателя в Репликации. Публиковаться будет база данных MIR, и она же будет иметь своё Зеркальное отображение. Публикация тоже будет называться MIR. Сервер, на котором будет размещаться зеркало, называется GLADCHENKO-VHD и этот же сервер является в нашей тестовой топологии репликации Распространителем. В качестве сервера Подписчика будет выступать экземпляр GLADCHENKO-A\SUB, на котором база данных подписки будет называться MIR. Хотя репликацию можно настроить с помощью SQL Server Management Studio, мы будем её настраивать с помощью системных процедур Transact-SQL.
Для простоты демонстрации, все учётные записи, от имени которых будут запускаться службы, и имена входа, в контексте безопасности которых будут выполняться работы на серверах, будут сведены к одной, доменной учётной записи пользователя AG@troika.ru. Этот пользователь является локальным администратором на всех серверах, и включён в серверную роль sysadmins всех указанных выше серверов баз данных.

Шаг 1. Создание Публикации

Публикуемая база данных MIR состоит из четырёх таблиц: PerformanceCounter, PerformanceSignature, PerformanceSignatureData и PerformanceSignatureHistory. На самом деле, эти таблицы тиражируются средствами репликации транзакций с сервера, на котором размещена база данных System Center Operations Manager 2007 SP1. Сделано это для того, чтобы эмулировать работу "живого" приложения, поскольку данные мониторинга инфраструктуры серверов поступают в эти таблицы постоянно и без длительных перерывов. Именно этот поток реплицируемой информации мы и будет реплицировать с сервера GLADCHENKO-TEST на сервер GLADCHENKO-A\SUB.
Для получения более подробной информации о настройке Распространителя, смотрите следующие темы в SQL Server 2008 Books Online:

• Как настроить публикацию и распространение (программирование репликации на языке Transact-SQL)
• Как настроить публикации и распространение (среда SQL Server Management Studio)

Ниже представлен сценарий создания Публикации.

Вначале, нужно разрешить на Распространителе ещё одного Издателя. Для этого на сервере GLADCHENKO-VHD выполните следующий сценарий:

exec sp_adddistpublisher
@publisher = N'GLADCHENKO-TEST'
, @distribution_db = N'distribution'
, @security_mode = 1
, @working_directory = N'C:\MSSQL\ReplData'
, @trusted = N'false'
, @thirdparty_flag = 0
, @publisher_type = N'MSSQLSERVER'
GO

После этого можно приступить к созданию Публикации, для чего на сервере GLADCHENKO-TEST нужно выполнить следующий сценарий:

use master
-- Указываем Распространителя
exec sp_adddistributor @distributor = N'GLADCHENKO-VHD'
, @password = N'*********' -- тут нужно указать заданный
                           -- на Распространителе пароль
-- В ответ должны получить подтверждение:
-- "You have updated the Publisher property 'active' successfully"
GO

use [MIR]
exec sp_replicationdboption @dbname = N'MIR'
, @optname = N'publish'
, @value = N'true'
GO

-- Добавляет Публикацию
exec sp_addpublication
  @publication = N'MIR'
, @description = N'Публикация БД MIR в репликации
транзакций, на сервере GLADCHENKO-TEST'
, @sync_method = N'native'
, @retention = 0
, @allow_push = N'true'
, @allow_pull = N'true'
, @allow_anonymous = N'false'
, @enabled_for_internet = N'false'
, @snapshot_in_defaultfolder = N'true'
, @compress_snapshot = N'false'
, @ftp_port = 21
, @ftp_login = N'anonymous'
, @allow_subscription_copy = N'false'
, @add_to_active_directory = N'false'
, @repl_freq = N'continuous'
, @status = N'active'
, @independent_agent = N'true'
, @immediate_sync = N'true'
, @allow_sync_tran = N'false'
, @autogen_sync_procs = N'false'
, @allow_queued_tran = N'false'
, @allow_dts = N'false'
, @replicate_ddl = 1
, @allow_initialize_from_backup = N'true'
, @enabled_for_p2p = N'false'
, @enabled_for_het_sub = N'false'
, @p2p_conflictdetection = N'false'
, @p2p_originator_id = 1
-- В ответ должны получить сообщение:

-- Job 'GLADCHENKO-TEST-MIR-1' started successfully.

-- "Warning: The logreader agent job has been implicitly created
-- and will run under the SQL Server Agent Service Account"
GO

-- Создаём инициализирующий моментальный снимок
exec sp_addpublication_snapshot @publication = N'MIR'
, @frequency_type = 1
, @frequency_interval = 1
, @frequency_relative_interval = 1
, @frequency_recurrence_factor = 0
, @frequency_subday = 8
, @frequency_subday_interval = 1
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 0
, @active_end_date = 0
, @job_login = null
, @job_password = null
, @publisher_security_mode = 1
GO
-- Добавляем наши таблицы в виде статей публикации
exec sp_addarticle @publication = N'MIR'
, @article = N'PerformanceCounter'
, @source_owner = N'dbo'
, @source_object = N'PerformanceCounter'
, @type = N'logbased'
, @description = null
, @creation_script = null
, @pre_creation_cmd = N'drop'
, @schema_option = 0x000000000803509F
, @identityrangemanagementoption = N'manual'
, @destination_table = N'PerformanceCounter'
, @destination_owner = N'dbo'
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboPerformanceCounter'
, @del_cmd = N'CALL sp_MSdel_dboPerformanceCounter'
, @upd_cmd = N'SCALL sp_MSupd_dboPerformanceCounter'
GO
exec sp_addarticle @publication = N'MIR'
, @article = N'PerformanceSignature'
, @source_owner = N'dbo'
, @source_object = N'PerformanceSignature'
, @type = N'logbased'
, @description = null
, @creation_script = null
, @pre_creation_cmd = N'drop'
, @schema_option = 0x000000000803509F
, @identityrangemanagementoption = N'manual'
, @destination_table = N'PerformanceSignature'
, @destination_owner = N'dbo'
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboPerformanceSignature'
, @del_cmd = N'CALL sp_MSdel_dboPerformanceSignature'
, @upd_cmd = N'SCALL sp_MSupd_dboPerformanceSignature'
GO
exec sp_addarticle @publication = N'MIR'
, @article = N'PerformanceSignatureData'
, @source_owner = N'dbo'
, @source_object = N'PerformanceSignatureData'
, @type = N'logbased'
, @description = null
, @creation_script = null
, @pre_creation_cmd = N'drop'
, @schema_option = 0x000000000803509F
, @identityrangemanagementoption = N'manual'
, @destination_table = N'PerformanceSignatureData'
, @destination_owner = N'dbo'
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboPerformanceSignatureData'
, @del_cmd = N'CALL sp_MSdel_dboPerformanceSignatureData'
, @upd_cmd = N'SCALL sp_MSupd_dboPerformanceSignatureData'
GO
exec sp_addarticle @publication = N'MIR'
, @article = N'PerformanceSignatureHistory'
, @source_owner = N'dbo'
, @source_object = N'PerformanceSignatureHistory'
, @type = N'logbased'
, @description = null
, @creation_script = null
, @pre_creation_cmd = N'drop'
, @schema_option = 0x000000000803509F
, @identityrangemanagementoption = N'manual'
, @destination_table = N'PerformanceSignatureHistory'
, @destination_owner = N'dbo'
, @vertical_partition = N'false'
, @ins_cmd = N'CALL sp_MSins_dboPerformanceSignatureHistory'
, @del_cmd = N'CALL sp_MSdel_dboPerformanceSignatureHistory'
, @upd_cmd = N'SCALL sp_MSupd_dboPerformanceSignatureHistory'
GO
-- настраиваем и запускаем Агента чтения журнала
exec [MIR].sys.sp_addlogreader_agent
  @job_login = null
, @job_password = null
, @publisher_security_mode = 1
GO
exec [MIR].sys.sp_addqreader_agent
@job_login = null
, @job_password = null
, @frompublisher = 1
GO
-- Формируем лист доступа к публикации (PAL)
-- Сюда нужно даобавить все задействованные агентами логины
exec sp_grant_publication_access
  @publication = N'MIR'
, @login = N'sa'
GO
exec sp_grant_publication_access
  @publication = N'MIR'
, @login = N'NT AUTHORITY\SYSTEM'
GO

Шаг 2. Настройка Доставки журналов для инициализации Подписчика

На этом шаге мы должны подготовить базу данных на сервере Подписчика, для того, чтобы она могла быть инициализирована из резервной копии. Поскольку поток транзакций в базе данных нашего Издателя не прерывается 24 часа в сутки, 7 дней в неделю и 365 дней в году, нам будет удобно задействовать для этого механизм Доставки журналов.
Для включения Доставки журналов нужно инициализировать базу данных Получателя путем восстановления полной резервной копии базы данных Источника. В нашем случае, резервным сервером, а заодно и сервером Подписчика будет компьютер GLADCHENKO-A\SUB.
Делаем полную копию издаваемой базы данных, для чего, на сервере GLADCHENKO-TEST выполняем следующий сценарий:

BACKUP DATABASE [MIR]
TO DISK = N'C:\MSSQL\BACKUP\MIR.bak'
WITH NOFORMAT, INIT
, NAME = N'Полная копия БД MIR'
, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Дожидаемся получения подтверждения успешности этой операции, в окне результатов появится приблизительно такой текст:

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 18104 pages for database 'MIR', file 'MIR' on file 1.
100 percent processed.
Processed 3 pages for database 'MIR', file 'MIR_log' on file 1.
BACKUP DATABASE successfully processed 18107 pages in 4.394 seconds (32.193 MB/sec).

После того, как копия успешно создана, нужно её скопировать на сервер Источника. В нашем случае, нужно взять файл с компьютера GLADCHENKO-TEST из папки C:\MSSQL\BACKUP\MIR.bak и скопировать его на компьютер GLADCHENKO-A, в папку: C:\MSSQL\BACKUP\MIR.bak.
После того, как файл резервной копии окажется на сервере Получателя, нужно восстановить базу данных Получателя, что мы и сделаем на экземпляре GLADCHENKO-A\SUB, используя следующий сценарий:

RESTORE DATABASE [MIR]
FROM DISK = N'D:\MSSQL\BACKUP\MIR.bak'
WITH FILE = 1
, MOVE N'MIR' TO N'C:\MSSQL\DATA\MIR.mdf'
, MOVE N'MIR_log' TO N'D:\MSSQL\LOG\MIR_1.ldf'
, NOUNLOAD, REPLACE, STATS = 10, NORECOVERY
GO

После успешного восстановления базы данных, приступаем к настройке Доставки журналов между публикуемой базой данных на Издателе, и только что восстановленной базой на Подписчике. Подробное описание настройки Доставки журналов можно найти в статье электронной документации: "Как включить доставку журналов (Transact-SQL)". Создадим папку для обмена резервными копиями журналов транзакций: C:\MSSQL\LogShip. На эту папку у учётных записей служб должен быть полный доступ.
Далее, настраивает базу данных Источника вместе с заданием резервного копирования, а также записями локального и удаленного мониторов:

DECLARE @LS_BackupJobId AS uniqueidentifier
DECLARE @LS_PrimaryId AS uniqueidentifier

EXEC master.dbo.sp_add_log_shipping_primary_database
 @database = N'MIR'
,@backup_directory = N'C:\MSSQL\LogShip'
,@backup_share = N'\\GLADCHENKO-TEST\lsbackup'
,@backup_job_name = N'LSBackup_MIR'
,@backup_retention_period = 1440
,@backup_compression = 0
,@monitor_server = N'GLADCHENKO-A'
,@monitor_server_security_mode = 1
,@backup_threshold = 60
,@threshold_alert_enabled = 1
,@history_retention_period = 1440
,@backup_job_id = @LS_BackupJobId OUTPUT
,@primary_id = @LS_PrimaryId OUTPUT
,@overwrite = 1
GO

Значения идентификаторов получились соответственно следующие:

@LS_BackupJobId = DDCD7E77-F045-4649-A9F1-CF8FE843C711
@LS_PrimaryId = 3346F94D-3DB6-4A56-BE99-DF2E60E0A25F

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

c:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe" -Backup 3346F94D-3DB6-4A56-BE99-DF2E60E0A25F -server GLADCHENKO-TEST'

Создаём расписание для задания резервного копирования журналов транзакций базы данных MIR:

DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule
 @job_id=N'DDCD7E77-F045-4649-A9F1-CF8FE843C711'
,@name=N'1'
,@enabled=1
,@freq_type=4
,@freq_interval=1
,@freq_subday_type=4
,@freq_subday_interval=10
,@freq_relative_interval=0
,@freq_recurrence_factor=1
,@active_start_date=20081202
,@active_end_date=99991231
,@active_start_time=0
,@active_end_time=235959
,@schedule_id = @schedule_id OUTPUT
SELECT @schedule_id
GO

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

EXEC master.dbo.sp_add_log_shipping_primary_secondary
 @primary_database = N'MIR'
,@secondary_server = N'GLADCHENKO-A\SUB'
,@secondary_database = N'MIR'
,@overwrite = 1
GO

После этого, на сервере Получателя (Подписчик, в нашем примере это компьютер GLADCHENKO-A\SUB) нужно запустить процедуру, которая создаст все необходимые задания по применению резервных копий на базе данных Получателя.

DECLARE @LS_Secondary__CopyJobId AS uniqueidentifier
DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier
DECLARE @LS_Secondary__SecondaryId AS uniqueidentifier

EXEC master.dbo.sp_add_log_shipping_secondary_primary
 @primary_server = N'GLADCHENKO-TEST'
,@primary_database = N'MIR'
,@backup_source_directory = N'\\GLADCHENKO-TEST\lsbackup'
,@backup_destination_directory = N'\\GLADCHENKO-A\D$\MSSQL\LogShip'
,@copy_job_name = N'LSCopy_GLADCHENKO-TEST_MIR'
,@restore_job_name = N'LSRestore_GLADCHENKO-TEST_MIR'
,@file_retention_period = 4320
,@monitor_server = N'GLADCHENKO-A'
,@monitor_server_security_mode = 1
,@overwrite = 1
,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT
,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT
,@secondary_id = @LS_Secondary__SecondaryId OUTPUT
GO

Получаем следующий набор идентификаторов:

@LS_Secondary__CopyJobId = 338E7DF6-F266-40CD-902E-86F424E12E6C
@LS_Secondary__RestoreJobId = 6C0FE890-5FC5-4BA7-BD89-EC13214514E8
@LS_Secondary__SecondaryId = DC7800CE-767C-4F2B-9A42-70DC5F807184

После этого, среди заданий экземпляра GLADCHENKO-A\SUB появятся два новых задания, для которых определены шаги, но ещё не настроены расписания их работы. Первое задание с именем: LSCopy_GLADCHENKO-TEST_MIR. У этого задания только один шаг и он выполняет следующий сценарий:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe -Copy DC7800CE-767C-4F2B-9A42-70DC5F807184 -server GLADCHENKO-A\SUB

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

EXEC msdb.dbo.sp_add_schedule
 @schedule_name =N'DefaultCopyJobSchedule'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20081203
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT
,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT

EXEC msdb.dbo.sp_attach_schedule
@job_id = '338E7DF6-F266-40CD-902E-86F424E12E6C'
,@schedule_id = @LS_SecondaryCopyJobScheduleID
GO

Второе задание называется LSRestore_GLADCHENKO-TEST_MIR и предназначено для восстановления скопированных с Источника резервных копий на базе данных Получателя. Единственный шаг этого задания выполняет следующую инструкцию:

С:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe -Restore DC7800CE-767C-4F2B-9A42-70DC5F807184 -server GLADCHENKO-A\SUB

Для добавления этому заданию расписания запуска необходимо на сервере GLADCHENKO-A\SUB выполнить следующий сценарий:

EXEC msdb.dbo.sp_add_schedule
 @schedule_name =N'DefaultRestoreJobSchedule'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20081203
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT
,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT

EXEC msdb.dbo.sp_attach_schedule
 @job_id = '6C0FE890-5FC5-4BA7-BD89-EC13214514E8'
,@schedule_id = @LS_SecondaryRestoreJobScheduleID
GO

И теперь осталось только добавить в Доставку журналов базу данных Получателя, что можно сделать, выполнив следующий сценарий на сервере GLADCHENKO-A\SUB:

EXEC master.dbo.sp_add_log_shipping_secondary_database
 @secondary_database = N'MIR'
,@primary_server = N'GLADCHENKO-TEST'
,@primary_database = N'MIR'
,@restore_delay = 0
,@restore_mode = 0
,@disconnect_users = 0
,@restore_threshold = 45
,@threshold_alert_enabled = 1
,@history_retention_period = 1440
,@overwrite = 1
GO

Следующим действием в настройке Доставки журналов будет подготовка сервера мониторинга, в качестве которого будет выступать экземпляр GLADCHENKO-A. На этом экземпляре нужно выполнить следующий сценарий:

EXEC msdb.dbo.sp_processlogshippingmonitorprimary
 @mode = 1
,@primary_id = N'3346F94D-3DB6-4A56-BE99-DF2E60E0A25F'
,@primary_server = N' GLADCHENKO-TEST'
,@monitor_server = N'GLADCHENKO-A'
,@monitor_server_security_mode = 1
,@primary_database = N'MIR'
,@backup_threshold = 60
,@threshold_alert = 14420
,@threshold_alert_enabled = 1
,@history_retention_period = 1440
GO

После этого, нужно включить те задания обслуживания Доставки журналов, которые были отмечены как выключенные. Это должно привести к тому, что Доставка журналов начнёт свою работу и будет поддерживать актуальность базы данных MIR на сервере GLADCHENKO-A\SUB в автоматическом режиме.
Теперь, когда придёт время создавать Подписку, достаточно будет ненадолго отключить клиентов от базы данных Издателя, дождаться или запустить вручную поочерёдно все задания Доставки журналов на Источнике и потом на Получателе, отключить все эти задания, и подготовить базу данных Получателя к настройке Подписки, выполнив этот сценарий:

RESTORE DATABASE MIR WITH RECOVERY
GO

Шаг 3. Настройка Подписки

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

use [MIR]
exec sp_addsubscription @publication = N'MIR'
, @subscriber = N'GLADCHENKO-A\SUB'
, @destination_db = N'MIR'
, @sync_type = N'initialize with backup'
, @backupdevicetype = N'disk'
, @backupdevicename = 'LogShip\MIR_20081203195807.trn'
, @fileidhint = 1
, @subscription_type = N'pull'
, @update_mode = N'read only'
GO

Все последующие действия по оформлению Подписки нужно выполнять на сервере GLADCHENKO-A\SUB. Следующий сценарий создаёт Подписку по запросу:

use [MIR]
exec sp_addpullsubscription @publisher = N'GLADCHENKO-TEST'
, @publication = N'MIR'
, @publisher_db = N'MIR'
, @independent_agent = N'True'
, @subscription_type = N'pull'
, @description = N''
, @update_mode = N'read only'
, @immediate_sync = 1

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

exec sp_addpullsubscription_agent @publisher = N'GLADCHENKO-TEST'
, @publisher_db = N'MIR'
, @publication = N'MIR'
, @distributor = N'GLADCHENKO-VHD'
, @distributor_security_mode = 1
, @distributor_login = N''
, @distributor_password = null
, @enabled_for_syncmgr = N'False'
, @frequency_type = 64
, @frequency_interval = 0
, @frequency_relative_interval = 0
, @frequency_recurrence_factor = 0
, @frequency_subday = 0
, @frequency_subday_interval = 0
, @active_start_time_of_day = 0
, @active_end_time_of_day = 235959
, @active_start_date = 20081203
, @active_end_date = 99991231
, @alt_snapshot_folder = N''
, @working_directory = N''
, @use_ftp = N'False'
, @job_login = null
, @job_password = null
, @publication_type = 0
GO

Когда все необходимые действия по оформлению подписки завершены, на сервер GLADCHENKO-A\SUB можно полностью удалить Доставку журналов и позаботиться об удалении созданных там заданий Доставки журналов.

Шаг 4. Зеркалирование публикуемой базы данных

В репликации транзакций существует очень опасная точка отказа, это Издатель, об обеспечении высокой доступности которого раньше было трудно заботиться. Теперь, для повышения доступности Издателя, можно создать Зеркальное отображение его базы данных и обеспечить автоматическое переключение на зеркальную копию в случае отказа. Для примера, мы попробуем настроить Зеркальное отображение базы данных Публикации на сервере Издателя и имитируем отказ с переключением Агента чтения журнала на зеркальную копию публикуемой базы данных.
В электронной документации BOL есть несколько статей, которые подробно рассматривают вопросы, связанные с настройкой Зеркального отображения баз данных. Вот список названий некоторых из них:

• Настройка зеркального отображения базы данных.
• Как настроить сеанс зеркального отображения базы данных (среда SQL Server Management Studio).
• Подготовка зеркальной базы данных к зеркальному отображению.
• Пример. Настройка зеркального отображения базы данных с помощью проверки подлинности Windows (язык Transact-SQL)
• Зеркальное отображение базы данных ALTER DATABASE (Transact-SQL)

В последней из перечисленных выше статей говориться, что предварительно на зеркальном сервере должна быть восстановлена резервная копия основной базы данных, и все последующие резервные копии журнала транзакций, восстановленные с использованием предложения WITH NORECOVERY. Как уже отмечалось выше, лучше всего подготовку синхронной копии зеркалируемой базы данных позволяет сделать Доставка журналов. Поскольку в настоящей статье мы уже уделили много внимания настройке Доставки журналов, повторение этой процедуры для инициализации копии основной базы данных мы опустим, будем считать, что все эти процедуры уже выполнены. Копия базы данных на момент начала настройки Зеркального отображения должна находиться в актуальном по отношению к основной базе данных состоянии.
В нашем примере, основной экземпляр и зеркало настраиваются так, чтобы использовать одного и того же Распространителя. В конфигурации Агента чтения журнала мы изменим настройки, чтобы получить нужное нам поведение агента в случае отказа сервера Издателя. Для этого на сервере Распространителя GLADCHENKO-VHD нужно установить значение для параметра запуска Агент чтения журнала -PublisherFailoverPartner, например так:

exec sp_add_agent_parameter
  @profile_id = 1 -- ID Агента моментальных снимков
, @parameter_name = N'-PublisherFailoverPartner'
, @parameter_value = N'GLADCHENKO-VHD' -- сервер зеркала
GO

exec sp_add_agent_parameter
  @profile_id = 2 -- ID Агента чтения журнала
, @parameter_name = N'-PublisherFailoverPartner'
, @parameter_value = N'GLADCHENKO-VHD' -- сервер зеркала
GO

Для принятия изменений профиля, нужно перезапустить задание Агента чтения журнала.
Зеркалирование публикуемой базы данных начнём с создания конечных точек зеркалирования. В первую очередь создадим конечную точку на Издателе, это у нас компьютер GLADCHENKO-TEST:

CREATE ENDPOINT [MirroringEndpoint]
    AUTHORIZATION [AG@troika.ru]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING
( ROLE = PARTNER
    , AUTHENTICATION = WINDOWS KERBEROS
    , ENCRYPTION = REQUIRED ALGORITHM RC4
)
GO

На сервере Зеркального отображения GLADCHENKO-VHD (это у нас Распространитель) тоже нужна конечная точка:

CREATE ENDPOINT [MirroringEndpoint]
    AUTHORIZATION [AG@troika.ru]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING
( ROLE = ALL
    , AUTHENTICATION = WINDOWS KERBEROS
    , ENCRYPTION = REQUIRED ALGORITHM RC4
)
GO

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

SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints

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

GRANT CONNECT ON ENDPOINT:: MirroringEndpoint TO AG@troika.ru GO

После раздачи прав, нужно объявить участников Зеркального отображения. На сервере Зеркальной копии GLADCHENKO-VHD выполним следующую команду:

ALTER DATABASE [MIR] SET PARTNER = 'TCP://GLADCHENKO-TEST.troika.ru:5022'

После этого, с основного сервера GLADCHENKO-TEST запустим симметричную команду:

ALTER DATABASE [MIR] SET PARTNER = 'TCP://GLADCHENKO-VHD.troika.ru:5022'

После этого должна произойти синхронизация основной и зеркальной баз данных. Зеркальное отображение должно быть запущено. Если этого не произошло, попробуйте внимательно изучить сообщение об ошибке. Чаще всего, причина в несогласованности баз, т.е. недостаёт последних изменений, которые могли быть не перенесены Доставкой журналов, или нечто подобное. Если причина не в этом, попробуйте отменить роль Зеркала и повторить настройку сначала. Сценарий отмены роли Зеркала представлен ниже:

ALTER DATABASE [MIR] SET PARTNER OFF

Современные серверные операционные системы оснащаются встроенными Брандмауэрами. Такие сетевые экраны тоже способны помешать подключению к порту конечной точки. Проверить возможность подключения с основного сервера к серверу зеркальной копии можно с помощью консольной команды операционной системы:

telnet GLADCHENKO-VHD 5022

Шаг 5. Переключение Публикации на зеркальную копию

Ну а теперь мы перейдём к самому интересному, ради чего были все эти подготовительные действия. Итак, мы имеем репликацию транзакций между двумя серверами, и Распространителя на третьем сервере. Кроме того, у нас настроено Зеркальное отображение базы данных Издателя на сервер Распространителя. Перед нами стоит задача - возобновить репликацию, которая была прервана в результате отказа Издателя. Доступность Издателя определяется Зеркальным отображением базы данных, на основе объектов которой создана наша Публикация. Отказ этой базы данных расценивается нами, как отказ Зеркального отображения и поводом для ручной или автоматической смены роли, когда зеркальная копия становится основной базой данных.
Далее, имитируем отказ основного сервера, который у нас является Издателем:

ALTER DATABASE MIR SET PARTNER FAILOVER

В ответ получаем сообщение: "Nonqualified transactions are being rolled back. Estimated rollback completion: 100%".
После этого основная база данных Зеркального отображения меняется ролями со своим зеркалом. Причём, на Распространителе появляется публикация и видно, что у неё уже существует Подписка (которую мы создавали на сервере GLADCHENKO-TEST). Изменения, которые пользователи будут делать в базе данных MIR после её переезда на сервер GLADCHENKO-VHD, будут вначале закрепляться в новом зеркале, в базе данных на сервере GLADCHENKO-TEST. Потом они будут попадать в базу данных Распространителя, считанные Агентом чтения журнала, который умеет обращаться ко второму партнёру Зеркального отображения в случае отказа основного сервера или основной базы данных.
Если на сервере GLADCHENKO-TEST запустим следующую команду:

SELECT PUBLISHINGSERVERNAME()

Эта команда возвратит имя исходного издателя для опубликованной базы данных, участвующей в Зеркальном отображении. Эта функция показывает первоначального издателя опубликованной базы данных, и поэтому у нас она вернёт, и всегда будет возвращать: "GLADCHENKO-TEST".
Если после этого выполнить на сервере GLADCHENKO-VHD ту же саму команду: "ALTER DATABASE MIR SET PARTNER FAILOVER", то всё вернётся в изначальное состояние. Публикация снова будет располагаться на сервере GLADCHENKO-TEST.
В отличие от агентов репликации, Монитор репликации не очень приспособлен для отслеживания работы с перемещённой на зеркальный сервер базой данных. Хотя его интерфейс будет показывать перемещение Публикации, информация о сеансах репликации может вводить администратора репликации в заблуждение. Трассировочные маркеры тоже не всегда помогают контролировать процесс репликации. В таком режиме работы репликации лучше всего контролировать сами данные, сверяя последние их изменения на издателе и подписчиках.
Существует опасность, что отказ основной базы данных приведёт к возникновению проблем репликации транзакций. Это особенно актуально, когда не соблюдается порядок первоначальной фиксации транзакции в зеркале базе данных. Если случиться так, что несколько некорректных транзакций будут препятствовать продолжению репликации данных, можно принудительно проигнорировать эти транзакции, что позволяет сделать системная процедура sp_setsubscriptionxactseqno.

Выводы

Зеркальное отображение можно использовать для повышения доступности Издателя в топологиях Репликации транзакций и Репликации слияния. Если важно, чтобы клиенты продолжали получать возможность изменения данных в издаваемой базе данных и автоматически переключались на её зеркальную копию в случае отказа, применение зеркалирования является оправданным.
В упомянутом в самом начале настоящей статьи техническом документе Майкрософт: " SQL Server Replication: Providing High Availability using Database Mirroring" предлагается вариант решения для зеркалирования баз данных Подписчиков. Там же вы найдёте аргументы для выбора такого решения.
Для повышения доступности базы данных Распространителя существует пока только одно решение - отказоустойчивая кластеризация.

ЗАКАЗАТЬ

Обсудить книгу можно тут: http://www.sql.ru/forum/actualthread.aspx?tid=643700

Пример статьи из сборника:

Репликация программируемых объектов БД в SQL Server 2005

По материалам статьи Байя Павлиашвили (Baya Pavliashvili) «Replicating Code Modules with SQL Server 2005».

Перевод Ирины Николаевны Наумовой

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

Настройка репликации программируемых объектов.

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

Тип статьи	Причина
Представление	Таблицы, на которых базируется представление, должны существовать на подписчике. Однако эти таблицы могут не участвовать в репликации.
Индексированное представление	Таблицы, на которых базируется представление, должны существовать на подписчике. Однако эти таблицы не участвуют в репликации. На серверах – подписчиках должна быть установлена версия SQL Server 2000 и выше. Все подписчики должны использовать SQL Server в редакции Enterprise Edition.
Хранимые процедуры, определяемые пользователем функции	Все объекты, упомянутые в хранимой процедуре или пользовательской функции должны существовать на подписчике. Однако, эти объекты могут не участвовать в репликации.

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

Рис.1.

Чтобы выбрать опции для каждой добавляемой в публикацию статей, нужно нажать кнопку Article Properties. Можно выбрать несколько опций для каждого типа реплицируемых программируемых объектов. Также можно реплицировать схему представлений, индексируемых представлений и пользовательских функций. Для хранимых процедур предусмотрена дополнительная гибкость – помимо их определения вы можете реплицировать и их выполнение. В таблицу ниже сведены опции, доступные для настройки при репликации программируемых объектов.

Тип статьи	Опция/значение	Описание
Представления	Copy User Triggers: True or False	Для реплицируемого представления создается триггер в базе данных подписчика, если он есть на издателе.
Представления, индексированные представления, хранимые процедуры, определяемые пользователем функции.	Copy Extended Properties: True or False	На подписчике создаются расширенные свойства реплицируемого представления.
Представления, индексированные представления, хранимые процедуры, определяемые пользователем функции.	Destination Object Name/Destination Object Owner	Вы можете создать реплицируемый объект с тем же именем что и на издателе и владельцем объекта или с другим именем и/или владельцем.
Представления, индексированные представления, хранимые процедуры, определяемые пользователем функции.User Defined Function	Action if name in use: Keep existing object unchanged OR Drop existing object and create a new one	Запомните, если сохраняете существующий объект, определение этого программируемого модуля на издателе и подписчике может быть разным.
Представления, индексированные представления, хранимые процедуры, определяемые пользователем функции.	Create Schemas at Subscriber: True or False	Определяет, должен ли выполняться оператор CREATE SCHEMA на подписчике, если там нет схемы объекта.
Хранимые процедуры	Replicate: Stored procedure definition only; Execution of the stored procedure; Execution in a serialized transaction of the SP.	Определяет, должно ли реплицироваться выполнение хранимой процедуры. Запомните, оператор ALTER PROCEDURE будет реплицировать изменения схемы даже для публикаций, в которых реплицируется выполнение хранимой процедуры, таким образом, изменения в определении хранимой процедуры будут всегда доставлены подписчику (если только вы явно не задали репликацию изменений схемы).

Запомните: Некоторые опции для статьи нельзя изменить после создания публикации. Например, опция Procedure Replicate не может быть изменена на Stored Procedure Definition, изменить её можно только на Execution Of The Stored Procedure. Чтобы изменить эту опцию, можно удалить статью и добавить ее заново, а затем уже изменить опцию. Так что перед настройкой репликации, определите заранее какие опции вам нужно будет установить.

Как только Вы установили свойства для каждой статьи, можно создать снимок для публикации немедленно и/или создать расписание для запуска Snapshot Agent. Следующим шагом необходимо определить параметры настройки безопасности для Snapshot Agent и Log Reader agent, проанализируйте то, что должен сделать мастер, и нажмите кнопку Finish, чтобы создать публикацию.

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

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

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

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

exec sp_addpublication @publication = n’pub_name’,
@description=N’Transactional publication of database ’’AdventureWorksDW’’ .’,
@sync_method = N’concurrent’, @retention = 0, @allow_push = N’true’,
@allow_pull = N’true’, @allow_anonymous = N’true’,
@enabled_for_internet = N’false’, @snapshot_in_defaultfolder = N’true’,
@compress_snapshot = N’false’, @ftp_port = 21, @ftp_login = N’anonymous’,
@allow_subscription_copy = N’false’, @add_to_active_directory = N’false’,
@repl_freq = N’continuous’, @status = N’active’,
@independent_agent = N’true’, @immediate_sync = N’true’,
@allow_sync_tran = N’false’, @autogen_sync_procs = N’false’,
@allow_queued_tran = N’false’, @allow_dts = N’false’, @replicate_ddl = 1,
@allow_initialize_from_backup = N’false’, @enabled_for_p2p = N’false’,
@enabled_for_het_sub = N’false’
GO

--Добавление статьей в публикацию репликации транзакций
--Определяемая пользователем функция:
exec sp_addarticle @publication = n’pub_name’,
@article = N’udfMinimumDate’, @source_owner = N’dbo’,
@source_object = N’udfMinimumDate’, @type = N’func schema only’,
@description = N’’, @creation_script = N’’, @pre_creation_cmd = N’drop’,
@schema_option = 0x0000000008000001, @destination_table = N’udfMinimumDate’,
@destination_owner = N’dbo’, @status = 16
GO

--Хранимая процедура:
exec sp_addarticle @publication = n’pub_name’,
@article = N’update_factFinance’, @source_owner = N’dbo’,
@source_object = N’update_factFinance’, @type = N’proc exec’,
@description = N’’, @creation_script = N’’, @pre_creation_cmd = N’drop’,
@schema_option = 0x0000000008000001, @destination_table = N’update_factFinance’,
@destination_owner = N’dbo’, @status = 0
GO
--Индексированное представление:
exec sp_addarticle @publication = n’pub_name’,
@article = N’View_DimCustomer_Young’, @source_owner = N’dbo’,
@source_object = N’View_DimCustomer_Young’, @type = N’indexed view schema only’,
@description = N’’, @creation_script = N’’, @pre_creation_cmd = N’drop’,
@schema_option = 0x0000000008000001, @destination_table = N’View_DimCustomer_Young’,
@destination_owner = N’dbo’, @status = 16
GO
--Представление:
exec sp_addarticle @publication = n’pub_name’,
@article = N’vTimeSeries’, @source_owner = N’dbo’,
@source_object = N’vTimeSeries’, @type = N’view schema only’, @description = N’’,
@creation_script = N’’, @pre_creation_cmd = N’drop’,
@schema_option = 0x0000000008000001, @destination_table = N’vTimeSeries’,
@destination_owner = N’dbo’, @status = 16
GO

Изменение схемы репликации

Вспомните, в предыдущей версии SQL Server для того, чтобы определения программируемых объектов передались подписчику, нужно было запустить агента создания снимка. В SQL Server 2005 это уже не так: репликация передает операторы ALTER VIEW, ALTER FUNCTION, ALTER PROCEDURE и ALTER TRIGGER подписчику в реальном времени. Опция репликации триггеров уже не является единственной возможностью для статей соответствующих типов, но она по-прежнему позволяет копировать триггеры, определенные на таблице или представлении в публикуемой базе данных. Запомните, что нельзя реплицировать DDL триггеры (триггеры языка определения данных).

Давайте рассмотрим репликацию изменения индексируемого представления. Я создал очень простой пример индексируемого представления на издателе с помощью следующих команд:

CREATE VIEW [dbo].[View_DimCustomer_Young]
WITH SCHEMABINDING
AS
SELECT CustomerKey, GeographyKey, FirstName, LastName, BirthDate
FROM dbo.DimCustomer
WHERE (BirthDate > CONVERT(SMALLDATETIME, ’1/1/1980’, 101))
GO

CREATE UNIQUE CLUSTERED INDEX [ix_DCY_CustomerKey] ON [dbo].[View_DimCustomer_Young]
(
[CustomerKey] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
ON [PRIMARY]

Это представление возвращает несколько столбцов таблицы DimCustomer для записей клиентов, которые родились после 1 января 1980 года. Я добавил это представление в публикацию репликации транзакций и создал для нее подписку на другом сервере.

Теперь давайте изменим представление, чтобы оно возвращало клиентов, родившихся после 1 января 1978 года. Для этого я выполнил следующий код:

ALTER VIEW [dbo].[View_DimCustomer_Young]
WITH SCHEMABINDING
AS
SELECT CustomerKey, GeographyKey, FirstName, LastName, BirthDate
FROM dbo.DimCustomer
WHERE (BirthDate > CONVERT(SMALLDATETIME, ’1/1/1978’, 101))

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

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

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

Репликация индексируемых представлений как таблиц

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

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

Для того чтобы выполнять репликацию индексированного представления как таблицы, нужно изменить параметр @type системной процедуры sp_addarticle. По умолчанию этот параметр установлен в значение N’indexed view logbased’. Например, следующий код добавит индексированное представление View_DimCustomer_Young в существующую публикацию как таблицу.

exec sp_addarticle
@publication = N’publication_name’,
@article = N’View_DimCustomer_Young’,
@source_owner = N’dbo’,
@source_object = N’View_DimCustomer_Young’,
@type = N’indexed view logbased’,
@description = null,
@creation_script = null,
@pre_creation_cmd = N’none’,
@schema_option = 0x0000000008000001,
/* table name doesn’t have to be the same as view name */
@destination_table = N’View_DimCustomer_Young’,
@destination_owner = N’dbo’
GO

После настройки репликации индексированного представления как таблицы, операторы INSERT, UPDATE и DELETE, выполненные на представлении на издателе, будут реплицированы в таблицу на подписчике.

Репликация выполнения хранимых процедур

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

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

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

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

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

Например, предположим что у нас есть хранимая процедура, которая изменяет некоторое количество строк в таблице factFinance базы данных AdventureWorksDW:

CREATE PROC update_factFinance (
@PercentChange NUMERIC (3,2),
@OrganizationKey TINYINT,
@TimeKey INT)
AS

/*
Изменяем количество выданных ключей
*/
UPDATE factFinance
SET amount = amount * @PercentChange
WHERE OrganizationKey = @OrganizationKey
AND TimeKey = @TimeKey

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

{call "dbo"."update_factFinance " (1.10, 3, 32)}

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

Репликация выполнения хранимых процедур внутри сериализуемой транзакции.

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

• Уровень изоляции транзакций у подключения, в котором выполняется хранимая процедура, должен быть установлен в SERIALIZABLE.
• Необходимо выполнять процедуру внутри явной транзакции, используя операторы BEGIN TRANSACTION / COMMIT TRANSACTION.

Если хотя бы одно условие не будет выполнено, выполнение хранимой процедуры не реплицируется. Помимо этих обязательных условий, также необходимо использовать опцию SET XACT_ABORT ON. Использование этой опции гарантирует, что изменения, внесенные транзакцией, внутри которой выполняется хранимая процедура, будут автоматически отменены, если возникнут ошибки времени выполнения.

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

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

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET XACT_ABORT ON
BEGIN TRAN
EXEC update_factFinance 1.10, 3, 32

COMMIT

Однако, следующая команда не будет реплицирована, потому что она не включена в явную транзакцию:

EXEC update_factFinance 1.10, 3, 32

Теперь мы завершим выполнение процедуры, чтобы продемонстрировать значение установки XACT_ABORT. Я изменяю тип данных столбца amount таблицы factFinance на SMALLINT, вместо INT, выполняя следующую инструкцию:

ALTER TABLE factFinance ALTER COLUMN amount SMALLINT

Максимальное значение для типа SMALLINT – 32768; умножаем максимальное значение столбца на 1.15 чтобы результат превысил 32768, таким образом, следующее выполнение процедуры update_factFinance, приведет к ошибке:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
/* CORRECT setting*/
SET XACT_ABORT ON
BEGIN TRAN
EXEC update_factFinance 1.15, 3, 32

COMMIT

Результат таков:

Msg 8115, Level 16, State 2, Procedure update_factFinance, Line 10
Arithmetic overflow error converting expression to data type smallint.

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

Далее, выполним тот же набор команд, отменив установку XACT_ABORT:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
/* НКОРРЕКТНАЯ УСТАНОВКА! Это сделано только в демонстрационных целях! */
SET XACT_ABORT OFF
BEGIN TRAN
EXEC update_factFinance 1.15, 3, 32

COMMIT

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

{call "dbo"."update_factFinance" (1.15,3,32)}

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

Вывод

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

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

• Целочисленное моделирование (Integer Modelling)

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

• Углублённое использование гистограммы (Comprehensive Histogram Usage)

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

• Предположение основного ограничения (Base Containment Assumption)

  Обычно, при соединении двух отношений мы предполагаем, что для Х определенных кодовых точек (которые относятся к тому же самому ключевому диапазону, что и отношение R) при соединении с Y уникальных точек того же самого ключевого диапазона, что и в отношении S, всегда найдётся пара MIN(X,Y). Такое допущение называют "Простое ограничение". Мы предполагаем, что наименьшее число из уникальных точек кода соотносится с точками кода на другой стороне. Это моделирование игнорирует относительную совокупность уникальных точек кода в основных формах R и S, и также игнорирует любую фильтрацию, которая была применена к основными формами R и S перед соединением. Основное ограничение основывается на предположение, что ограничение применяется только к основным отношениям и использует вероятностные методы вычисления степени соединения. Кроме того, подразумевается, что модели фильтров применены правильно, так как их поведение очень отличается от ортогональных фильтров.

• Углублённое изменение отображения плотности (Comprehensive Density Remapping)

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

• Углублённое согласование плотности (Comprehensive Density Matching)

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

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

...(read more)

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

1. С помощью поставляемых с дисковым контроллером специализированных утилит, конфигурируем все диски полки как 14 массивов RAID0, каждый из которых должен состоять из одного диска, размер каждого массива выбирается равным всему доступному размеру диска, размер блока низкоуровневой разметки (размер сегмента) выбираем равным 64Кб, политики кэширования должны исключать кэширование чтения и записи. В некоторых контроллерах дисковых массивов выбор размера сегмента может быть ограничен несколькими предопределёнными значениями. Можно встретить рекомендованные для типовых конфигураций значения, например, для баз данных предлагают установить размер блока в 128Кб, а для хранения видеофильмов задать 256Кб. В этих случаях резонно выбрать рекомендованные вендором значения.
2. С помощью оснастки управления дисками, входящей в состав mmc-консоли управления компьютером, и системной утилиты DISKPART необходимо создать для каждого физического диска полки RAW-раздел (без форматирования NTFS) величиной на весь диск, и без присвоения буквы диска (буквы присваивать можно, но это не обязательно, к тому же, букв в алфавите может оказаться меньше числа дисков). Большие диски (более двух Терабайт) может потребоваться предварительно перевести в состояние Online, и конвертировать в GPT (GUID Partition Table). Для выравнивания начального смещения за счёт MBR используйте следующие команды DISKPART:

SELECT DISK=1
CREATE PARTITION PRIMARY ALIGN=128

В этом примере выбран диск 1 и смещение установлено в 128Кб. Выбор смещения зависит от размера сегмента.

1. Установить программу SQLIO. Везде по тексту настоящей статьи местоположение программы sqlio.exe выбрано следующее: C:\SQLIO\ sqlio.exe
2. Подготовьте командный файл, который будет запускать программу sqlio.exe в разных режимах для каждого диска и сохранять результаты в файлы. Пример командного файла можно найти в Приложении 1.
3. Запустите командный файл на исполнении, а потом сведите собранные в файлы результаты в общую таблицу, для дальнейшего сравнения и анализа.

Пример аппаратной конфигурации дисков представлен в Таблице 1.

Таблица 1. Конфигурация дисковых массивов для калибровки дисков.

На Рисунке 2 показано, как должны выглядеть 14 дисков, подготовленные к тестированию.

Рис.2.

Ниже представлен сокращённый пример командного файла для запуска калибровочного теста. В этом файле каждый вызов программы sqlio.exe имеет одинаковые аргументы, кроме аргумента R, который изменяется от 1 до 14. Каждый диск тестируется на чтение и, после 30 секундной паузы, на запись (аргумент k). Время тестирования каждого диска 300 секунд (аргумент s), размер блока 64Кб (аргумент b), Число блоков в строке равно единице (аргумент f), число строк равно 2000000 (аргумент i), число очередей тоже равно единице (аргумент o), число потоков равно единице (аргумент t). Суммарный размер файла рабочей нагрузки на каждом диске составляет 128Гб, что почти совпадает с размером диска.

sqlio -kR -s300 -b64 -f1 -i2000000 -o1 -t1 -R1 -LP -a0xf -BN > R01-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s300 -b64 -f1 -i2000000 -o1 -t1 -R1 -LP -a0xf -BN > W01-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kR -s300 -b64 -f1 -i2000000 -o1 -t1 -R2 -LP -a0xf -BN > R02-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s300 -b64 -f1 -i2000000 -o1 -t1 -R2 -LP -a0xf -BN > W02-b64-f1-i2000000-o1-t1.log

timeout /T 30

……

sqlio -kR -s300 -b64 -f1 -i2000000 -o1 -t1 -R13 -LP -a0xf -BN > R13-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s300 -b64 -f1 -i2000000 -o1 -t1 -R13 -LP -a0xf -BN > W13-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kR -s300 -b64 -f1 -i2000000 -o1 -t1 -R14 -LP -a0xf -BN > R14-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s300 -b64 -f1 -i2000000 -o1 -t1 -R14 -LP -a0xf -BN > W14-b64-f1-i2000000-o1-t1.log

Ниже представлен пример отчёта о тесте операций записи для первого диска:

sqlio v1.5.SG

calibrating timestamp counter for latency timings... done (MHz measured at 2359)

1 thread reading for 300 secs from file 1:

                        using 64KB IOs over 64KB stripes with 2000000 IOs per run

                        enabling multiple I/Os per thread with 1 outstanding

                        buffering set to not use file nor disk caches (as is SQL Server)

                        affinity mask is 0xf (15)

initialization done

CUMULATIVE DATA:

throughput metrics:

IOs/sec:   999.67

MBs/sec:    66.91

latency metrics:

Min_Latency(ms): 0

Avg_Latency(ms): 0

Max_Latency(ms): 802

histogram:

ms: 0  1  2  3  4  5  6  7  8  9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+

%: 87 13  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0

В этом отчёте нас интересует метрика скорости чтения, именно эту метрику мы будем сводить в общий график (MBs/sec: 66.91). Метрика IOs/sec практически линейно зависит от MBs/sec, поскольку в каждом из тестов у нас будет фиксированный размер запроса ввода-вывода. Метрики для задержки запроса ввода-вывода (latency) могут быть интересны вкупе с MBs/sec для сравнения разных дисковых подсистем. Нас такое сравнение не интересует, поэтому метрики MBs/sec вполне достаточно.

Автоматизировать извлечение из файлов с результатами тестов необходимых для дальнейших оценок значений показателей производительности можно, например, с помощью PowerShell. В примере ниже показана команда, которая сохраняет в CSV-файле имена файлов и соответствующее им значение MBs/sec:

(Select-String -Pattern MBs/sec: -Path *.log | format-table `

{$_.Filename -replace ".log", ""}, `

{($_.Line).replace(‘.’, ‘,’) -replace "MBs/sec: ", ";"} `

-HideTableHeaders) | Out-File SQLIO.csv -Encoding ASCII –Append

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

На Рисунке 3 показан результат калибровочного тестировании 13 дисков.

Рис. 3.

Наиболее важной задачей нагрузочного тестирования является выявление дисков, характеристики которых существенно отличаются от остальных. На Рисунке 2 диски практически идентичны, как по записи, так и по чтению. Однако, при тестировании большого числа дисков вероятность столкнуться с «подозрительными» дисками много выше. Например, такой пример показан на Рисунке 4. Обратите внимание на четырнадцатый диск.

Рис. 4.

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

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

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

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

sqlio -kRW -s300 -f1 -i2000000 -o1 -b64 -t1 -LP -R(2-14) -a0xff

sqlio -kRW -s300 -f1 -i2000000 -o1 -b64 -t1 -LP -R(0-13) -a0xff

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

Этап №2. Масштабирование дисков

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

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

Производительность отдельных компонент дисковой подсистемы в данной методике не исследуется, хотя понятно, что абстрагироваться от этого полностью невозможно. В предлагаемой методике мы смотрим на дисковую подсистему, условно, как на "чёрный ящик". Ограничения, а также разного рода ошибки, могут быть совершенно в разных местах. Чтобы начать разговор об этом с администраторами дисковой подсистемы, нужно иметь аргументы. Масштабирование позволяет получить на руки факты именно из этой области. Кроме того, поскольку мы хотим получить в итоге некий эталон производительности, подобные измерения бывают весьма полезны.

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

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

Рис.5.

Для документирования подобного приращения числа дисков я использую сочетание символов «à». Т.о. для обозначения приращения дисков, которое начинается с диска под номером 2 в нумерации операционной системы и заканчивается обоймой из тринадцати дисков по диск номер 14 включительно, можно использовать следующую запись: -R(2->14).

В командном файле это выглядит примерно так:

sqlio -kR -s180 -b64 -f1 -i2000000 -o1 -t1 -R2 -LP -a0xf -BN > R2-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s180 -b64 -f1 -i2000000 -o1 -t1 -R2 -LP -a0xf -BN > W2-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kR -s180 -b64 -f1 -i2000000 -o1 -t1 -R2,3 -LP -a0xf -BN > R23-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s180 -b64 -f1 -i2000000 -o1 -t1 -R2,3 -LP -a0xf -BN > W23-b64-f1-i2000000-o1-t1.log

timeout /T 30

… результаты сокращены…

sqlio -kR -s180 -b64 -f1 -i2000000 -o1 -t1 -R2,3,4,5,6,7,8,9,10,11,12,13 -LP -a0xf -BN > R2345678910111213-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s180 -b64 -f1 -i2000000 -o1 -t1 -R2,3,4,5,6,7,8,9,10,11,12,13 -LP -a0xf -BN > W2345678910111213-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kR -s180 -b64 -f1 -i2000000 -o1 -t1 -R2,3,4,5,6,7,8,9,10,11,12,13,14 -LP -a0xf -BN > R234567891011121314-b64-f1-i2000000-o1-t1.log

timeout /T 30

sqlio -kW -s180 -b64 -f1 -i2000000 -o1 -t1 -R2,3,4,5,6,7,8,9,10,11,12,13,14 -LP -a0xf -BN > W234567891011121314-b64-f1-i2000000-o1-t1.log

Получив информацию о том, какова реальная пропускная способность вашей дисковой подсистемы, вы можете спланировать дальнейшие тестовые конфигурации. Выбор тестируемых конфигураций может быть обусловлен вашими бизнес - требованиями к доступности и готовности серверов, а также отводимыми на тестирование сроками (полный цикл тестирования согласно данной методике может занимать порядка 7 суток круглосуточной работы тестового сервера). Зная, какое количество шпинделей потенциально может «запрудить» шину или контроллер, вы можете выбрать, сколько дисков оптимально собирать в один массив. Кроме того, в целях оптимизации времени тестов, данная методика предполагает допущение, что сервер баз данных, как и утилита SQLIO, балансирует нагрузку между дисками лучше, чем контроллер дискового массива. Как вы помните, база данных может состоять из нескольких файлов, и SQL Server будет резервировать в этих файлах пространство под новые страницы пропорционально размеру файлов. Учитывая эту особенность, мы ограничим число создаваемых массивов только некоторыми избранными конфигурациями, которые в лучшей мере используют преимущества балансировки ввода-вывода с помощью SQL Server. Т.е. это будут те конфигурации, которые не противоречат здравому смыслу и предлагают максимальное количество файлов. Ещё одним допущением для данной методики является то, что в каждой из тестовых конфигураций число шпинделей остаётся неизменным. Это нужно для того, чтобы сравнивать производительность разных массивов при равной стоимости оборудования.

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

Рис.6.

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

Следующие конфигурации, показанные на рисунках 7 и 8, представляют RAID1 и RAID0.

Рис.7.

Рис.8.

Конфигурации массивов RAID5 и RAID10 тоже внешне очень похожи, у них будет одинаковое количество логических дисков (LUN).

Рис.9.

Рис.10.

Каждая из показанных на рисунках конфигурация используется для прогона тестов, которые описаны на этапе 4.

Этап №3. Выбор размера сегмента

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

В этой статье мы будем полагаться на то, что бизнес - требования наших приложений указывают на необходимость обеспечения высокой производительности запросов упреждающего чтения. Как известно, упреждающее чтение характерно тем, что размер запроса ввода – вывода чаще всего равняется 64 Кб. SQL Server пытается считывать данные не страницами, а экстентами, разумеется, если размер запроса соответствующий и фрагментация данных позволяет это сделать. Мы допускаем, что фрагментацию удаётся поддерживать в приемлемых рамках, а оптимизация запросов ввода-вывода, размер которых близок к размеру страницы 8 Кб, не представляется необходимой, поскольку такие запросы будут обслуживаться практически одинаково в разных конфигурациях в силу своих малых размеров. Также, мы будем опираться на очень распространённое бизнес-правило, что время резервного копирования и восстановления базы данных из резервной копии должны быть минимальными. В своих измерениях Вы можете полагаться на иные требования и правила. Чтобы помочь Вам сориентироваться в выборе размеров нагрузки, тут будет уместно привести Таблицу 2, в которой показано соотношение разных операций и размеров запросов.

Таблица 2. Характеристики для основных типов нагрузки ввода – вывода SQL Server

Как правило, выбор размера сегмента не является сложной задачей и, зачастую, предсказуем. Например, ниже, на Рисунке 11 показана весьма распространённая зависимость производительности одного диска от размера сегмента.

Рис.11.

Многие специалисты рекомендуют устанавливать размер блока для форматирования NTFS раздела операционной системы Windows равным 64 Кб и делать такого же размера размер сегмента при монтировании дискового массива. Глядя на представленный Рисунком 11 график, становится понятно, откуда взялась эта рекомендация.

Этап №4. Сравнение производительности разных типов дисковых массивов

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

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

Каждый уровень RAID будет подвержен пяти тестовым сериям измерения скорости чтения и записи, для разных размеров блока. Размер блока (который задаётся параметром –b) будет принимать следующий набор значений: 1024, 512, 256, 64, 8. Как вы, наверное, заметили, размеры блока соответствуют размерам основных типов запросов ввода-вывода. Выбор размеров блока для тестирования важен, поскольку не стоит включать в тест те размеры запросов ввода – вывода, которые не важны, или не характерны для ваших приложений. Например, если в вашей промышленной среде существуют достаточно продолжительные окна для проведения технологических планов обслуживания баз данных, тогда можно исключить из теста размер блока в 128Кб. Если время создания базы данных тоже не критично (например, если вы используете RAW-партиции), можно убрать тест с размером блока 512Кб.

Кроме изменения размера блока (под блоком будем понимать размер запроса ввода-вывода), данная методика предполагает изменение ещё нескольких, критически важных характеристик рабочей нагрузки. Нам нужно оценить производительность не только для одного запроса, но и в условиях относительно глубоких очередей запросов ввода-вывода, а также распараллеливания этих запросов. Глубина очереди будет увеличиваться до 64. Шаг приращения величины очереди тоже стоит выбирать таким, какой характерен для ваших приложений или, как это будет показано в нашем примере, это могут быть отдельные, характерные для ваших задач значения. Мы будем использовать следующие параметры очереди: о1, о2, о4, о8, о16, о32, о64.

Число потоков у нас будет изменяться от одного до четырёх, ровно столько на тестовом сервере имеется ядер процессора. Синхронно с числом потоков будет изменяться страйп-фактор. Здесь, под числом страйпов (страйп-фактор) понимается число блоков в строке файла нагрузки. Значение переменной основного цикла программы (число страйпов в файле нагрузки) тоже будет меняться, оно вычисляется каждый раз таким образом, чтобы при текущем размере блока и числе блоков в страйпе, размер файла нагрузки был близок к ёмкости диска. Поскольку число вычислений значений параметра i (основной цикл) достаточно велико, лучше заранее подготовить небольшие таблички, охватывающую все выбранные для тестирования варианты параметров и конфигурации. Например, на рисунке 12 представлена подобная таблица.

Рис.12.

Здесь, в ячейке A1 указан размер раздела диска (в данном случае у всех дисков он одинаков). Количество строк файла нагрузки, помещаемого на каждый из задействованных дисков, вычисляется делением размера раздела в Кб на размер блока (b), потом на число потоков (t) блоков в страйпе (f).

Настоящая методика использует несколько эмпирических допущений, которые существенно уменьшают объём выполняемых тестов. Рисунок 12 как раз и демонстрирует одно из таких допущений. Т.е. мы тестируем не все возможные варианты сочетаний параметров запуска SQLIO, а только те, которые перечислены в таблице. Т.е. Значения для параметров t и f могут быть 1,2,4,8,16. Поскольку максимальное значение для параметра t рекомендуется избирать не выше числа ядер процессоров (а в пример на рисунке 12 их число было 16). Значение f не должно превышать значение t, диапазон изменения этих параметров будет именно такой, как вы видите на рисунке. В колонке A, начиная со строки 2, заданы размеры запросов ввода вывода, которые характерны для приложения. Эти значения используются для параметра b. В таблице не хватает только одного из определяющих конфигурацию каждого теста параметра, это глубина очереди, задающаяся параметром o. Как и в нашем случае, в примере на рисунке 12 использовались следующие параметры очереди: о1, о2, о4, о8, о16, о32, о64.

Документирование конфигурации проще показать и подробно разобрать на следующем примере:

f(1,2,4) -i(1950,975,487) -o(1-64) -b1024 -t(1,2,4) -LP -R2,3,4 -a0xf

Здесь, размер блока был установлен в 1024Кб. Число блоков в страйпе файла нагрузки изменялось синхронно с числом потоков, т.е. значения были равны, и составляли: 1, 2 и 4. В зависимости от последнего числа изменяется число страйпов файла рабочей нагрузки. В этом тесте для мегабайтного размера блока значения такие: i1950, i975, i487. Глубина очереди изменялась так, как я показывал ранее, от единицы до 64.

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

Таблица 3. RAID1:

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

Таблица 4. RAID1: f(1,2,4) -i(3900,1900,970) -o(1-64) -b512 -t(1,2,4) -LP -R2,3,4 -a0xf

 Таблица 5. RAID1: f(1,2,4) -i(500000,250000,125000) -o(1-64) -b256 -t(1,2,4) -LP -R2,3,4 -a0xf

 Таблица 6. RAID1: f(1,2,4) -i(2000000,1000000,500000) -o(1-64) -b64 -t(1,2,4) -LP -R2,3,4 -a0xf

 Таблица 7. RAID1: f(1,2,4) -i(16000000,8000000,4000000) -o(1-64) -b8 -t(1,2,4) -LP -R2,3,4 -a0xf

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

На Рисунке 13 показан график средних значений производительности троек массивов для разных уровней RAID. Разными цветами обозначены размеры блоков. Шкала показывает производительность в Мб/сек.

Рис.13.

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

 При выборе другого оборудования или в условиях другого характера нагрузки, например, OLTP, график вполне может стать другим. На Рисунке 14 показан график для другой аппаратной платформы, у которой можно было создавать массивы RAID10.

Рис.14.

На рисунках 14 и 15 показаны результаты для варианта прямого подключения дисковой подсистемы. На рисунке 16 показаны результаты тестирования дисковой подсистемы и сервера  другого вендора, и в варианте подключения SAN.

Рис.15.

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

Выводы

Сегодня типичные объёмы обслуживаемых СУБД данных настолько велики, что для дисковых подсистем уже недостаточно нескольких дисков, всё чаще, типовое приложение оперирует дисковой полкой в 14 или 15 дисков, а объёмы дисковых массивов достигают Терабайта. В таких условиях задачи сайзинга становятся очень важны, и грамотное распределение файлов вкупе с правильным выбором дисковых массивов позволяют существенно повысить производительность приложений, а также добиться требуемых значений производительности без лишних издержек энергопотребления.

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

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

Приложение 1. Пример командного файла.

SQLIO_noRAID.zip

Благодарности

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