Устранение неполадок репликации транзакций в SQL Server

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

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

Просмотр состояния репликации

Монитор репликации Replication Monitor является основным имеющимся в вашем распоряжении графическим инструментом для наблюдения за производительностью репликации и диагностики. Replication Monitor был включен в управляющую консоль Enterprise Manager в SQL Server 2000, однако в SQL Server 2005 он был выделен из SQL Server Management Studio в специальный исполняемый модуль (SSMS). Как и SSMS, Replication Monitor можно применять для мониторинга издателей (Publishers), подписчиков (Subscribers) и распространителей (Distributors), работающих на предыдущих версиях SQL Server, хотя те возможности, которые отсутствуют в SQL Server 2005, не будут отображаться на экране.

Для запуска Replication Monitor откройте SSMS подключитесь в окне Object Explorer к издателю, щелкните правой кнопкой мыши на папке Replication и выберите в контекстном меню пункт Launch Replication Monitor (запустить монитор репликации). На экране 1 показано окно Replication Monitor с несколькими зарегистрированными издателями. В левой панели окна мы видим дерево со списком зарегистрированных издателей, содержимое правой панели зависит от того элемента, который выбран в дереве.

Экран 1. Replication Monitor с зарегистрированными издателями

Выбор издателя в древовидном представлении отображает в правой панели представления в виде трех закладок: Publications ("Публикации"), где показаны название, текущий статус и количество подписчиков для каждой публикации данного издателя; Subscription Watch List ("Список наблюдения за подписками"), где показаны статус и приблизительная задержка (время выполнения находящихся в ожидании команд) всех подписок данного издателя; Agents ("Агенты"), где отображаются время последнего запуска и текущее состояние агента моментальных снимков, агента чтения журнала и агента чтения очереди, а также различные задания автоматического обслуживания, созданные SQL Server для поддержки работоспособности репликации.

Раскрытие в дереве узла какого-либо издателя показывает его публикации. В результате выбора публикации в правой панели отображаются представления из четырех закладок: All Subscriptions ("Все подписки"), где показаны текущий статус и приблизительная задержка агента распространителя для каждой подписки; Tracer Tokens ("Маркеры трассировки"), где показаны статусы последних маркеров трассировки для публикации (позднее мы рассмотрим маркеры трассировки более подробно); Agents ("Агенты"), где отражено время последнего запуска, время выполнения и текущее состояние агента моментальных снимков и агента чтения журнала, используемых данной публикацией; Warnings ("Предупреждения"), отображающие параметры всех предупреждений, настроенных для данной публикации.

Если щелкнуть правой кнопкой мыши на любой строке (на агенте) на закладках Subscription Watch List, All Subscriptions или Agents, появится контекстное меню с такими пунктами, как остановка и запуск агента, просмотр профиля агента и просмотр свойств заданий агента. В результате двойного щелчка мышью по агенту открывается окно, показывающее специфические детали статуса агента.

Окно агента распространителя содержит три закладки: Publisher to Distributor History ("Журнал операций от издателя к распространителю"), показывающая статус и список последних выполненных действий агента чтения журнала данной публикации; Distributor to Subscriber History ("Журнал операций от распространителя к подписчику"), показывающая статус и список последних выполненных действий агента распространителя; Undistributed Commands ("Нераспространенные команды"), на которой показано количество команд в базе данных распространителя, ожидающих применения к подписчику, и примерное время выполнения этих команд. Окна агентов чтения журнала и моментальных снимков содержат только закладку Agent History ("Журнал агента"), на которой показан статус и список последних выполненных действий агента.

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

Измерение потока данных

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

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

Для добавления нового маркера трассировки или просмотра статуса существующих маркеров перейдите в Replication Monitor на закладку Tracer Tokens. На экране 2 показан пример закладки Tracer Tokens с подробной информацией о задержках для ранее добавленных маркеров. Для создания нового маркера нажмите кнопку Insert Tracer (добавить трассировку). Информацию для существующих маркеров можно получить, выбрав соответствующий пункт из выпадающего списка в правой части окна.

Экран 2. Закладка Tracer Tokens с подробной информацией о задержках для ранее добавленных маркеров

Определение момента возникновения неполадки

Несмотря на то, что Replication Monitor очень полезен для просмотра состояния репликации, вряд ли кто-то станет держать его постоянно открытым в ожидании ошибки. У администратора базы данных много других дел, помимо разглядывания экрана в течение всего дня, и в какие-то моменты ему приходится покидать свое рабочее место.

Однако SQL Server можно настроить для отправки оповещений при возникновении определенных проблем репликации. При начальной настройке распространителя создается группа оповещений для событий, связанных с репликацией. Для просмотра списка оповещений откройте SSMS и подключитесь в окне Object Explorer к базе данных распространителя, а затем в дереве раскройте узлы SQL Server Agent и Alerts. Для просмотра или настройки оповещения откройте окно свойств оповещения двойным щелчком мыши на оповещении или щелчком правой кнопкой и выбором в контекстном меню пункта Properties ("Свойства"). Или же можно настроить оповещение по-другому: в окне Replication Monitor выбрать публикацию в левой панели, открыть закладку Warnings в правой панели и нажать кнопку Configure Alerts ("Настроить оповещения"). Варианты, доступные в окне свойств оповещения для определения ответных действий, уведомлений и т.д. - те же, что и в оповещениях для заданий агента SQL Server. На экране 3 показан пример закладки Warnings в Replication Monitor.

Экран 3. Закладка Warnings в Replication Monitor

Для репликации транзакций особый интерес представляют три оповещения: Replication: Agent failure ("Репликация: сбой агента"), Replication: Agent retry ("Репликация: повторная попытка агента") и Replication Warning: Transactional replication latency (Threshold: latency) ("Предупреждение: задержка репликации транзакций (порог задержки)"). По умолчанию включены (но не настроены для отправки уведомлений оператору) только оповещения для порогов задержки. Такие оповещения настраиваются на закладке Warnings для публикации в Replication Monitor. Оповещения запускаются при превышении этих порогов и используются монитором репликации для отображения на экране соответствующего значка оповещения. В большинстве случаев стандартные значения порогов не требуют изменения, однако их необходимо пересмотреть на предмет удовлетворения требованиям тех SLA и SLE, за которые вы несете ответственность.

Типичным ответным действием оповещения является отправка уведомления (например, по электронной почте) членам группы администраторов база данных. Поскольку отправка почтовых сообщений базируется на работе компонента Database Mail, вам необходимо ее настроить, если вы еще этого не сделали. Кроме того, чтобы избежать избытка оповещений, следует увеличить задержку между ответами до пяти минут или более. Наконец, не забудьте включить данное оповещение на странице General окна Alert properties ("Свойства оповещения").

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

Другие потенциальные проблемы, требующие внимания

Могут возникнуть и такие проблемы, к которым ни оповещения, ни Replication Monitor не привлекут вашего внимания: прекращение работы агентов и неконтролируемый рост базы данных распространителя.

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

Хранимая процедура dbo.Admin_Start_Idle_Repl_Agents, приведенная в листинге 1, может запускаться на сервере-распространителе (и на серверах-подписчиках с подписками по запросу) и служить для перезапуска агентов репликации, настроенных на постоянную работу, но в данный момент не функционирующих. Настройка данной процедуры для периодического запуска (например, каждые шесть часов) не допустит, чтобы простои агентов приводили к серьезным проблемам.

Неконтролируемый рост базы данных распространителя может происходить даже при работающих агентах. Как только команды будут доставлены ко всем подписчикам, их необходимо удалить, чтобы освободить пространство для новых команд. При начальной настройке распространителя создается задание службы SQL Server Agent под названием Distribution clean up: distribution для удаления команд, доставленных ко всем подписчикам. Если это задание отключено или работает некорректно (например, заблокировано), команды не удаляются и база данных распространителя растет. Проверка истории выполнения этого задания и размера базы данных распространителя на всех распространителях должна стать частью ежедневной работы администратора базы данных.

Распространенные неисправности и их устранение

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

Distribution Agents fail with the error message The row was not found at the Subscriber when applying the replicated command or Violation of PRIMARY KEY constraint [Primary Key Name]. Cannot insert duplicate key in object [Object Name].

(Агенты распространителя прекратили работу с ошибкой "Строка не обнаружена на подписчике при применении реплицированной команды или нарушение ограничения по первичному ключу [имя первичного ключа]. Невозможно вставить повторяющийся ключ в объект [имя объекта]).

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

Решение. Если вас не интересует, в какой команде произошел сбой, вы можете просто изменить профиль агента распространителя для игнорирования ошибок. Для изменения профиля перейдите в Replication Monitor к Publication, щелкните правой кнопкой мыши на проблемном подписчике в закладке All Subscriptions и выберите пункт Agent Profile ("Профиль агента"). Откроется новое окно, в котором вы можете изменить профиль выбранного агента; поставьте флажок для профиля Continue on data consistency errors и нажмите OK. На экране 4 показан пример окна профиля агента, в котором отмечен данный профиль. Агент распространителя необходимо перезапустить, чтобы вступил в действие новый профиль. Для этого щелкните правой кнопкой мыши на подписчике и выберите пункт Stop Synchronizing ("Остановить синхронизацию"). Когда статус подписчика сменится с Running ("Работает") на Not Running ("Не работает"), снова щелкните правой кнопкой мыши на подписчике и выберите пункт меню Start Synchronizing ("Запустить синхронизацию").

Экран 4. Профиль Continue on Data Consistency Errors, отмеченный в профиле агента распространителя

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

Если вы хотите узнать, когда именно возникла ошибка, можно выполнить на распространителе хранимую процедуру sp_browsereplcmds. Ей нужны три параметра: ID базы данных издателя, порядковый номер транзакции (transaction sequence number) и ID команды. Для получения ID базы данных издателя выполните на распространителе код листинга 2 (заполнив предварительно соответствующие значения для издателя, подписчика и публикации).

Для получения порядкового номера транзакции и ID команды перейдите в мониторе репликации к агенту, на котором произошел сбой, откройте окно его статуса, выберите закладку Distributor to Subscriber History и выберите самый последний сеанс с ошибочным статусом. Порядковый номер транзакции и ID команды содержатся в сообщении с подробным описанием ошибки. На экране 5 показан пример сообщения об ошибке, содержащего эти два значения.

Экран 5. Сообщение об ошибке с информацией о?порядковом номере транзакции и ID команды

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

(Агент распространителя прекратил работу с ошибкой "Не удалось найти хранимую процедуру 'sp_MSins_ <имя таблицы>'".)

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

Решение. С этой проблемой справиться довольно легко. В опубликованной базе данных на издателе выполните хранимую процедуру sp_scriptPublicationcustomprocs для создания на издателе хранимых процедур для команд INSERT, UPDATE и DELETE. Данная процедура использует всего один параметр (имя публикации) и возвращает в качестве результата столбец с данными типа nvarchar(4000). При запуске процедуры в SSMS обеспечьте вывод результата в виде текста (выберите меню Control-T или Query, затем Results To и далее Results To Text) и установите максимальное количество символов для вывода результата в текст не менее 8000. Данное значение можно установить, выбрав меню Tools, далее - Options, Query Results, Results to Text, Maximum number of characters displayed in each column. После выполнения процедуры скопируйте сгенерированные сценарии в окно с новым запросом и выполните их в соответствующей базе данных подписки на подписчике.

(Агенты распространителя не запускаются или не выполняют никаких действий)

Причина. Обычно такое происходит, когда на одном и том же сервере одновременно работает большое количество агентов распространителя, например на распространителе, который обслуживает более 50 публикаций или подписок. Агенты распространителя являются независимыми исполняемыми файлами, работающими вне процесса SQL Server в неинтерактивном режиме (то есть без графического интерфейса). Windows Server для запуска таких процессов использует специальную область памяти, называемую кучей неинтерактивного рабочего стола (non-interactive desktop heap). Если Windows расходует всю доступную в данной куче память, агенты распространителя не могут запуститься.

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

1. Запустите редактор реестра с помощью команды regedit32.exe в диалоговом окне "Выполнить" или в окне командной строки.
2. Перейдите в левой панели к ключу HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession ManagerSubSystems
3. В правой панели выполните двойной щелчок мышью на параметре Windows для запуска панели редактирования текстовой строки.
4. Найдите в панели ввода данных параметр SharedSection. В нем вы увидите три значения, разделенные запятыми и имеющие примерно такой вид:

За размер кучи рабочего стола отвечает третье значение (в данном примере 512). Увеличение этого значения на 256 или 512 (доведение его величины до 768 или 1024) должно быть достаточным для решения проблемы. После изменения значения нажмите OK. Чтобы изменения вступили в действие, необходима перезагрузка Windows. Более подробную информацию о куче неинтерактивного рабочего стола можно найти в статье "Unexpected behavior occurs when you run many processes on a computer that is running SQL Server" (http://support.microsoft.com/kb/824422).

Мониторинг среды репликации

Совместное применение таких инструментов как Replication Monitor, маркеры трассировки и оповещения является мощным средством мониторинга вашей топологии репликации и поиска источников возникающих проблем. Несмотря на то что описанные выше методы являются хорошим руководством для исправления наиболее распространенных неполадок в репликации транзакций, одной этой статьи недостаточно для описания всех известных проблем. Дополнительную информацию об устранении неисправностей в процессе репликации можно найти в блоге REPLTalk группы поддержки репликации Microsoft SQL Server (http://blogs.msdn.com/b/repltalk/).