Сбор базовых данных для репликации слиянием с помощью SSIS

SQL Server предоставляет многочисленные функции, с помощью которых администраторы и разработчики баз данных могут успешно внедрять, проектировать и обслуживать различные задачи. SQL Server Integration Services (SSIS) и репликация слиянием — два компонента, которые прекрасно дополняют друг друга, хотя специалисты не всегда задумываются о возможности их совместного использования.

Службы SSIS появились в версии SQL Server 2005 в качестве замены для DTS. С помощью SSIS можно строить корпоративные решения для извлечения, преобразования и загрузки (ETL). Кроме того, это чрезвычайно мощная платформа автоматизации и обслуживания заданий. Администратор баз данных может внедрять различные пакеты SSIS, автоматизируя высокоуровневые превентивные задачи с целью оптимального выполнения экземпляров SQL Server в любой среде.

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

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

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

1. Распространение
2. Публикация
3. Подписка

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

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

• sp_replmonitorhelpmergesession;
• sysmergearticles;
• sysmergesubscriptions;
• MSreplication_monitordata;
• MSmerge_sessions;
• MSmerge_genhistory;
• MSmerge_agents.

Обратите внимание, что тип подписок, используемый при репликации, может повлиять на местоположение таблиц репликации слиянием и место выполнения системных хранимых процедур репликации слиянием. Выбор подписки по запросу или принудительной подписки приводит к различиям в местоположении метаданных и месте выполнения работы при синхронизации исторических и активных сеансов. Дополнительные сведения о различиях между подписками по запросу и принудительными подписками можно найти в электронной документации по SQL Server «Implementing Replication Overview» (http://msdn.microsoft.com/en-us/library/ms151215%28v=sql.105%29.aspx).

Сбор сведений из заголовка подписки

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

Рисунок 1. Структура таблицы SubscriptionHeader

Чтобы заполнить таблицу SubscriptionHeader, воспользуйтесь MSmerge_agents (таблица базы данных распространителя) и sysmergesubscriptions (таблица базы данных публикации). Запрос в листинге 2 показывает, как получить требуемые данные. Чтобы воспользоваться этим исходным текстом, необходимо заменить . именем базы данных публикации во фрагменте с меткой A.

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

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

Автоматизация добавочных обновлений

Обычно использование служб SSIS — лучший способ выполнять добавочные обновления, так как их можно задействовать быстро и с минимальными затратами на разработку. Затраты на разработку — важный фактор. Для расчета затрат необходимо сравнить время (в часах), потраченное на разработку пакета SSIS, с затратами на выполнение добавочных обновлений вручную. Например, компенсируют ли разовые затраты 20 часов на разработку ежедневные потери 20 минут на ручную операцию? В этом случае потратить 20 часов на автоматизацию задачи лучше, чем терять примерно 87 часов в год (5 дней x 20 минут x 52 недели/60) на применение добавочных обновлений вручную. Выгода очевидна, но только благодаря временным показателям в пользу SSIS. Прежде чем браться за автоматизацию процесса, убедитесь, что таким образом вы выиграете время.

На рисунке 2 показана задача добавочного обновления в службах SSIS. Прежде чем рассмотреть, что происходит в этом потоке данных, необходимо усвоить некоторые сведения. Если подписка удалена, а затем восстановлена, имя подписки будет таким же, но имя ReplNick может измениться. Версия схемы часто меняется. Поэтому таблица SubscriptionHeader содержит столбцы replnickname и schemaversion, чтобы при необходимости менять имя ReplNick и версию схемы.

Рисунок 2. Обновление при добавлении таблицы SubscriptionHeader

В потоке данных на рисунке 2 первая задача Lookup определяет, существует ли имя, возвращенное из источника (то есть запрос в листинге 2), в таблице SubscriptionHeader. Если имени не существует, задача Lookup переходит к условию No Match и вставляет строку в таблицу SubscriptionHeader как нового подписчика. Если имя существует, то следующий шаг — определить, изменились ли версия схемы или имя ReplNick. Для этого в путь вставлено преобразование Conditional Split. Следующие выражения проверяют совпадение исходных данных с данными назначения и гарантируют, что сравнение типов данных является допустимым:

! ISNULL(Lookup.schemaversion)
&&! ISNULL([Source — Query].schemaversion)
&& (DT_STR,300,1252)Lookup.schemaversion! =
[Source — Query].schemaversion
! ISNULL(Lookup.replnickname)
&&! ISNULL([Source — Query].replnickname)
&& (DT_STR,300,1252)Lookup.replnickname! =
(DT_STR,300,1252)[Source — Query].replnickname

Если любое из выражений возвращает значение True, то соответствующая команда OLE DB будет выполнена, чтобы обновить schemaversion или столбец replnickname в таблице SubscriptionHeader. Дополнительные сведения о добавочных обновлениях можно получить, прочитав блоги Backup File Contents with SSIS — INSERT/UPDATE Decisions (http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/backup-file-contents-with-ssis) и «SSIS Design Pattern — Incremental Loads» (http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx).

После того, как подготовлена задача добавочного обновления для информации о подписке, необходимо добавить задачу, которая проверяет существование таблицы SubscriptionHeader. Исходный текст для этой задачи можно получить из пакета SSIS, спроектированного для данной статьи; Merge Replication Baseline Collector можно загрузить из CodePlex (http://mergebaselinecollect.codeplex.com/). Там же вы найдете инструкции по настройке переменных и конфигурации. Обе задачи можно поместить в контейнер Sequence. Контейнеры Sequence используются для логического группирования похожих задач, что может быть полезно при обработке ошибок. На рисунке 3 показан контейнер Sequence для загрузки данных заголовка подписки.

Рисунок 3. Контейнер Sequence для загрузки данных заголовка подписчика

Сбор статистики о публикации

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

Для выполнения sp_replmonitorhelpmergesession требуются следующие параметры:

* @agent_name. Этот параметр указывает имя агента, которое будет получено из agent_id, соответствующего agent_name.

* @hours. По умолчанию данному параметру присваивается значение -1, и возвращаются все предшествующие сеансы (до 100).

* @publisher. Данный параметр указывает имя экземпляра SQL Server, в котором размещена публикация.

* @publisher_db. Этот параметр предоставляет имя публикуемой базы данных.

* @publication. Данный параметр указывает имя публикации, участвующей в событиях слияния.

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

Информация, полученная из MSreplication_monitordata, может оказаться полезной для анализа истории. В процессе сбора agent_name, publisher, publisher_db и publication будут использоваться для заполнения параметров хранимой процедуры sp_replmonitorhelpmergesession. Чтобы применить инструкцию SELECT к MSreplication_monitordata, необходимо иметь уникальный ключ, обеспечивающий итерацию каждой строки в результате, то есть основной ключ. Сделать это удобнее всего с помощью функции ROW_NUMBER(). Она позволяет быстро последовательно нумеровать строки, добавляя номера в новый столбец. Затем этот столбец можно использовать для прохождения по результатам и выполнения специфических операций в зависимости от возвращаемых значений.

Все описанные действия соединены в сценарии Create_And_Populate_MERGE_STATS.sql (листинг 6). После создания таблицы MERGE_STATS этот сценарий получает подписки из MSreplication_monitordata, а затем проходит по всем строкам и выполняет хранимую процедуру sp_replmonitormergesession. Результаты каждого исполнения вставляются во вспомогательную глобальную временную таблицу с именем ##GLOBALSTATS.

Позднее данные в ##GLOBALSTATS вставляются в таблицу MERGE_STATS, структура которой показана на рисунке 4. Обратите внимание, что при использовании задачи для создания временной таблицы в службе SSIS необходимо назначить значение True параметру DelayValidation. Это не дает задаче вызвать ошибку в среде разработки и позволяет построить пакет, когда объектов еще не существует.

Рисунок 4. Структура таблицы MERGE_STATS

В службах SSIS запрос в Create_And_Populate_MERGE_STATS.sql используется в качестве источника для задачи Data Flow, которая получает окончательные результаты и импортирует их в таблицу MERGE_STATS. На рисунке 5 показан контейнер Sequence, содержащий этот процесс. Как мы видим, сначала выполняется проверка существования глобальных временных таблиц. Если они существуют, то они отбрасываются, чтобы предотвратить ошибки в дальнейшем. Последний шаг процесса — вставить содержимое результирующей глобальной временной таблицы в таблицу MERGE_STATS.

Рисунок 5. Контейнер Sequence для загрузки статистики подписчика

Сбор статистики для статей

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

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

Сбор статистики для статьи позволяет реагировать и предупреждать всплески, либо заранее сообщать подписчикам о надвигающихся событиях. К счастью, при репликации слиянием довольно просто получить количество изменений для статьи из таблиц sysmergearticles, sysmergesubscriptions и MSmerge_genhistory. Обращаться ко всем трем таблицам можно с помощью запроса в листинге 3.

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

Результаты запроса в листинге 3 используются для заполнения таблицы MERGE_COUNTS, которая создается с помощью программного кода в листинге 4. На рисунке 6 показана структура этой таблицы.

Рисунок 6. Структура таблицы MERGE_COUNTS

В службах SSIS процесс сбора статистики для статей находится в контейнере Sequence, показанном на рисунке 7. Запрос в листинге 4 используется в качестве источника задачи Data Flow. Последний шаг — импорт результатов в таблицу MERGE_COUNTS.

Рисунок 7. Контейнер Sequence

Отчеты с базовой информацией

Разместив все собранные данные в трех таблицах, можно воспользоваться простым запросом для отслеживания базовой статистики. Запрос в листинге 5 получает информацию о числе произошедших событий передачи (вставок, обновлений и удалений). Результаты запросов можно импортировать в Microsoft Excel, SQL Server Reporting Services (SSRS) или другие программы подготовки отчетов для построения диаграмм, отображающих события и заметные всплески. Например, в диаграмме на рисунке 8 высокие полосы 5/22/2012 означают всплеск, заслуживающий более пристального внимания. Другие всплески могут означать известные явления, свойственные обычной работе. Если всплески нормальные, то обладателю системы нужно учитывать, что синхронизация в эти дни недели или месяца может занимать больше времени.

Рисунок 8. Диаграмма по результатам запроса

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

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

CREATE TABLE [dbo].[SubscriptionHeader] (
[name] [nvarchar](100) NOT NULL
, [publication] [nvarchar](128) NOT NULL
, [subscriber_db] [nvarchar](128) NULL
, [subscriber_name] [nvarchar](128) NULL
, [agent_id] [int] NOT NULL
, [application_name] [nvarchar](128) NULL
, [replnickname] [binary](6) NULL
, [schemaversion] [varchar](50) NULL
) ON [PRIMARY]
GO

SELECT agents.[name]
, agents.publication
, agents.subscriber_db
, agents.subscriber_name
, agents.id AS [agent_id]
, subnames.application_name
, subnames.replnickname
, subnames.schemaversion
FROM distribution..msmerge_agents AS agents
— BEGIN CALLOUT A
LEFT JOIN. .sysmergesubscriptions
— END CALLOUT A
AS subnames
ON agents.subscriber_name = subnames.subscriber_server
GROUP BY agents.[name]
, agents.publication
, agents.subscriber_db
, agents.subscriber_name
, agents.id
, subnames.application_name
, subnames.replnickname
, subnames.schemaversion;

SELECT NAME AS [Object Name]
, MSMerge_genhistory.subscriber_number
, IsNull(subs.subscriber_server, 'Unknown')
[Subscriber Name]
, MAX(subs.last_sync_date) [Last Sync Date]
, MAX(coldate) [Generation Date]
, SUM(changecount) AS [Change Count]
FROM dbo.MSmerge_genhistory WITH (NOLOCK)
INNER JOIN dbo.sysmergearticles WITH (NOLOCK)
ON art_nick = nickname
LEFT JOIN dbo.sysmergesubscriptions subs WITH (NOLOCK)
ON MSmerge_genhistory.subscriber_number =
subs.subscriber_number
WHERE genstatus = 1
OR genstatus = 2
GROUP BY NAME
, MSMerge_genhistory.subscriber_number
, subs.subscriber_server

CREATE TABLE [dbo].[MERGE_COUNTS] (
[Object Name] [nvarchar](128) NOT NULL
, [subscriber_number] [int] NOT NULL
, [Subscriber Name] [nvarchar](128) NOT NULL
, [Last Sync Date] [datetime2](3) NULL
, [Generation Date] [datetime2](3) NULL
, [Change Count] [int] NULL
, [CollectDate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MERGE_COUNTS]
ADD DEFAULT (getdate()) FOR [CollectDate]
GO

SELECT hdr.publication
, hdr.subscriber_name
, stats.delivery_rate
, stats.upload_inserts
, stats.upload_updates
, stats.upload_deletes
, counts.TotalChanges
, counts.[Object Name]
, CONVERT(VARCHAR(10), InsertDateTime, 121) CollectionDate
FROM MERGE_STATS stats
INNER JOIN SubscriptionHeader hdr
ON stats.agent_id = hdr.agent_id
INNER JOIN (
SELECT CollectDate
, [Subscriber Name]
, [Object Name]
, [Last Sync Date]
, SUM([Change Count]) TotalChanges
FROM dbo.MERGE_COUNTS
WHERE [Last Sync Date] > '1899-01-01'
GROUP BY CollectDate
, [Subscriber Name]
, [Object Name]
, [Last Sync Date]
) counts ON hdr.subscriber_name = counts.[Subscriber Name]
GROUP BY InsertDateTime
, hdr.publication
, hdr.subscriber_name
, stats.delivery_rate
, stats.upload_inserts
, stats.upload_updates
, stats.upload_deletes
, counts.TotalChanges
, counts.[Object Name]

Листинг 6. Сценарий Create_And_Populate_MERGE_STATS.sql

CREATE TABLE [dbo].[MERGE_STATS] (
[agent_id] [int] NULL
, [InsertDateTime] [datetime] NULL
, [PubRegion] [varchar](6) NULL
, [sessionid] [int] NULL
, [starttime] [datetime] NULL
, [endtime] [datetime] NULL
, [DurHelpMon] [int] NULL
, [uploads] [int] NULL
, [downloads] [int] NULL
, [lastmessage] [nvarchar](500) NULL
, [duration] [int] NULL
, [delivery_rate] [numeric](12, 2) NULL
, [upload_conflicts] [int] NULL
, [upload_deletes] [int] NULL
, [upload_inserts] [int] NULL
, [upload_rows_retried] [int] NULL
, [upload_time] [int] NULL
, [upload_updates] [int] NULL
, [download_conflicts] [int] NULL
, [download_deletes] [int] NULL
, [download_inserts] [int] NULL
, [download_rows_retried] [int] NULL
, [download_time] [int] NULL
, [download_updates] [int] NULL
, [bulk_inserts] [int] NULL
, [metadata_rows_cleanedup] [int] NULL
, [schema_changes] [int] NULL
, [CollectDate] [datetime] NULL
) ON [PRIMARY]
GO
DECLARE @loop INT = 1
DECLARE @cmd NVARCHAR(2500)
DECLARE @publisher SYSNAME
DECLARE @publisher_db SYSNAME
DECLARE @publication SYSNAME
DECLARE @tbl_session TABLE (
ident INT identity(1, 1)
, sessionid INT
, STATUS INT
, starttime DATETIME
, endtime DATETIME
, duration INT
, uploads INT
, downloads INT
, errormessage INT
, errorid INT
, percentage DECIMAL
, timeremaining INT
, currentphase INT
, lastmessage NVARCHAR(500)
, isactive INT
, mergeagent INT
)
SELECT *
, ROW_NUMBER() OVER (
ORDER BY agent_name
) rowid
INTO ##temp
FROM distribution.dbo.MSreplication_monitordata(NOLOCK)
WHILE (
@loop <= (
SELECT MAX(rowid)
FROM ##temp
)
)
BEGIN
SELECT @publisher = publisher
, @publisher_db = publisher_db
, @publication = publication
FROM ##temp
WHERE rowid = @loop
SET @cmd = N'exec sys.sp_replmonitorhelpmergesession @agent_name = ''' + (
SELECT agent_name
FROM ##temp
WHERE rowid = @loop
) + ''',@hours = -1' + ',@publisher=''' + @publisher + ''',@publisher_db=''' + @publisher_db + ''',@publication=''' + @publication + ''''
INSERT INTO @tbl_session (
sessionid
, STATUS
, starttime
, endtime
, duration
, uploads
, downloads
, errormessage
, errorid
, percentage
, timeremaining
, currentphase
, lastmessage
, isactive
)
EXEC sp_executesql @cmd
SET @loop += 1
END
SELECT GETDATE() InsertDateTime
, 'Region' PubRegion
, a.sessionid
, a.starttime
, a.endtime
, a.duration DurHelpMon
, a.uploads
, a.downloads
, a.lastmessage
, b.agent_id
, b.duration
, b.delivery_rate
, b.upload_conflicts
, b.upload_deletes
, b.upload_inserts
, b.upload_rows_retried
, b.upload_time
, b.upload_updates
, b.download_conflicts
, b.download_deletes
, b.download_inserts
, b.download_rows_retried
, b.download_time
, b.download_updates
, b.bulk_inserts
, b.metadata_rows_cleanedup
, b.schema_changes
INTO ##globalstats
FROM @tbl_session a
INNER JOIN distribution.dbo.MSmerge_sessions b ON a.sessionid = b.session_id