Знакомимся с репликацией транзакций

Репликация SQL Server включает набор возможностей, которые полностью пока еще не заменены другими функциями, реализованными в SQL Server. Некоторые из распространенных примеров использования предусматривают возможность распределения данных между различными экземплярами SQL Server и базами данных для формирования отчетов и разгрузки основной производственной базы данных. Репликация может приблизить данные к пользователям в распределенных средах и масштабировать производительность путем многоадресной пересылки данных. Кроме того, некоторые находят место для репликации даже в задачах восстановления после аварий и обеспечения высокой доступности (более подробно об этом рассказано в статье Пола Рэндала In defense of transactional replication as an HA technology по адресу http://www.sqlskills.com/blogs/paul/in-defense-of-transactional-replication-as-an-ha-technology).

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

Базовые понятия репликации

Существуют различные типы репликации, поэтому я начну описание набора возможностей с самого верхнего уровня. Репликация — это набор технологий, позволяющий копировать, распространять и синхронизировать определенные типы объектов базы данных и связанных с ними данных и зависимостей между ними из одной базы данных в одну или несколько других баз данных в одном и том же экземпляре или в разных экземплярах SQL Server. Экземпляр SQL Server, содержащий базу данных, из которой распространяются данные, называется издателем (Publisher). В одной базе данных можно определить одну или более публикаций (Publication), которые логически объединяются в одну или более статей (Article). Статья представляет собой особый объект в базе данных публикации, который требуется переслать в другую базу данных. Допустимые типы объектов в статьях включают в себя определяемые пользователем таблицы, хранимые процедуры и представления.

Для репликации необходима отдельная база данных для хранения метаданных и пересылаемых данных. Такая база данных называется базой данных распространителя (Distribution Database), а экземпляр SQL Server, на котором она хранится, — распространителем (Distributor). Распространитель может быть тем же экземпляром SQL Server, что и издатель, отдельными экземпляром или экземпляром, на который пересылаются данные. Решение о размещении базы данных распространителя обычно базируется на рассмотрении таких моментов как загруженность или доступность (например, если репликация транзакций сочетается с зеркалированием баз данных).

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

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

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

Выбор типа репликации

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

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

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

Последний тип репликации — тот, который мы рассмотрим, — репликация транзакций. При правильном проектировании репликация транзакций может обеспечивать потоковое изменение данных с низким уровнем задержек, выполняемое издателем для одного или более подписчиков. Реплицируемые данные, как правило, обрабатываются подписчиком в режиме «только чтение». Модификация данных у подписчика может привести к нарушению синхронизации всего набора данных с данными издателя. Репликация транзакций предоставляет не рекомендуемую сейчас функцию обновляемых подписок (updatable subscriptions), которая позволяет подписчику реплицировать данные в обратном направлении, к издателю. Одноранговая репликация (возможность SQL Server редакции Enterprise) также позволяет выполнять двунаправленную репликацию изменений в данных, используемых в базах данных с репликацией транзакций.

Развертывание репликации транзакций

Поскольку данная статья представляет собой введение в репликацию транзакций, я продемонстрирую использование различных мастеров консоли SQL Server Management Studio (SSMS) для реализации соответствующих функций. Кроме того, вы можете использовать сценарии. Более подробно об этом рассказано во врезке «Мастера или сценарии?». Чтобы воспроизвести описанные в статье действия, необходимо иметь аналог среды со следующими характеристиками.

• В демонстрациях используются два экземпляра SQL Server 2008 R2 SP1 на двух разных серверах в одном домене. Заметим, что редакция Enterprise не является обязательным требованием. Подойдут и виртуальные серверы, при условии корректных сетевых настроек. Некоторые экраны содержат информацию о моих тестовых серверах, названных SQLSKILLS-NODE1 (издатель и распространитель) и SQLSKILLS-NODE2 (подписчик).
• Оба экземпляра SQL Server требуют наличия установленных компонентов репликации. Если вы выбрали вариант SQL Server Replication при установке SQL Server, то компоненты репликации будут загружены. Если вы этого не сделали во время установки, компоненты репликации можно установить отдельно.
• Служба Agent должна быть запущена на экземпляре издателя.
• Тестовый домен в идеале должен иметь отдельные учетные записи для процессов Snapshot Agent, Log Reader Agent и Distribution Agent. Например, я создал три учетные записи: SQLskills\SQLskillsSnapshotAGT, SQLskills\SQLskillsLogReaderAGT, и SQLskills\SQLskillsDistAGT.
• Подписка будет создана для базы данных с именем AWReporting.

В качестве базы данных публикации используется демонстрационная база данных AdventureWorks версии SQL Server 2008 R2 (http://msftdbprodsamples.codeplex.com/). Мы будем реплицировать описания таблиц и соответствующие данные для следующих таблиц базы данных AdventureWorks:

[Person].[Address]

[Person].[Contact]

[Person].[CountryRegion]

[Person].[StateProvince]

[HumanResources].[Employee]

[HumanResources].[EmployeeAddress]

Кроме того, будет реплицироваться описание представления [HumanResources].[vEmployee]. И хотя представление не содержит данных, можно реплицировать описание этого объекта, при условии, что все зависимые объекты включены в публикацию.

Настройка экземпляра SQL Server для публикации

Далее описаны шаги по настройке экземпляра SQL Server в качестве издателя.

1. В левой панели окна консоли SSMS Object Explorer щелкните раскрывающийся список Connect и выберите экземпляр SQL Server, который будет настраиваться в качестве издателя или распространителя.
2. В окне Object Explorer раскройте узел нужного экземпляра SQL Server. Щелкните правой кнопкой мыши на папке Replication и выберите пункт Configure Distribution.
3. На экране мастера Configure Distribution нажмите кнопку Next. Этот экран больше не будет появляться, если вы отключите его, поставив галочку у поля Do not show this starting page again.
4. На экране Distributor, как показано на экране 1, вы указываете, будет ли данный SQL Server функционировать в качестве распространителя или распространителем для данного издателя будет другой сервер. Для нашего примера назначьте издателя собственным распространителем и нажмите Next.
5. Экран SQL Server Agent Start позволяет настроить автоматический запуск службы SQL Server Agent. Выберите автоматический запуск Yes, configure the SQL Server Agent service to start automatically и нажмите Next.
6. Экран Snapshot Folder задает размещение хранилища моментальных снимков. Мы в данном примере используем настройки по умолчанию. Выбор локального хранилища приводит к появлению предупреждения о том, что такая папка не поддерживает репликацию по запросу (для данного варианта необходим общий сетевой ресурс). Запишите путь к выбранной папке, так как он потребуется в дальнейшем. Нажмите Next.
7. На экране Distribution Database предлагаются варианты настройки имени базы данных распространителя (Distribution Database) и размещения файлов базы данных. В производственной базе данных файлы базы данных распространителя нужно размещать так, чтобы обеспечить максимальную пропускную способность и минимальные задержки, необходимые для вашей топологии репликации. Для тестового сценария выбираем настройки по умолчанию и нажимаем кнопку Next.
8. На экране Publishers определяем серверы, которые будут использовать данного распространителя для своих публикаций. В нашем примере необходимый SQL Server уже выбран. Нажатие кнопки со значком многоточия (...) справа от столбца Distribution Database отобразит дополнительные настройки, такие как режим подключения агента (Agent Connection Mode), задающий способ подключения к издателю, и папку по умолчанию для хранения моментальных снимков (Default Snapshot Folder). Нажимаем Next.
9. На экране Wizard Actions вы либо даете мастеру команду для автоматической настройки распространения, либо генерируете сценарий, с помощью которого можно настроить распространение, либо делаете и то и другое. В нашем примере оставляем все по умолчанию и нажимаем Next.
10. На завершающем экране Complete the Wizard нажимаем кнопку Finish. Далее вы будете наблюдать статус всех выполняемых шагов. Когда мастер успешно завершит свою работу, нажмите кнопку Close.

Экран 1. Назначение экземпляра SQL Server в качестве собственного распространителя

Разрешение публикации базы данных

Ниже описаны шаги для разрешения публикации базы данных.

1. В окне Object Explorer раскройте тот узел SQL Server, который только что сделали издателем. Щелкните правой кнопкой мыши на папке Replication и выберите пункт Publisher Properties (свойства издателя).
2. Заметим, что в левой части окна свойств издателя в панели Select a page имеются два параметра, требующие настройки. На странице General показаны настройки распространителя для выбранных издателей. В нашем демо-примере для указания издателя необходимо выбрать вариант This server acts as its own Distributor (данный сервер является собственным распространителем).
3. Выберите в панели Select a page пункт Select Publication Databases («Выбор баз данных для публикации»). Вам будет представлен список баз данных для включения репликации. Поставьте флажок на пересечении строки AdventureWorks и столбца Transactional, как показано на экране 2. Нажмите OK.

Экран 2. Выбор базы данных для ?репликации

Настройка параметров безопасности для агентов

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

1. На сервере-издателе в окне выполнения запросов выполните код, приведенный в листинге 1. Данный код создаст в базах данных публикации и распространения имя регистрации (login) для агента моментальных снимков (Snapshot Agent), сгенерирует учетную запись пользователя и добавит ей роль db_owner.
2. Выполните код из листинга 2. Данный код создаст в базах данных публикации и распространения имя входа для агента чтения журнала (Log Reader Agent), создаст учетную запись пользователя и добавит ей роль db_owner.
3. Выполните код из листинга 3. Данный код создаст в базе данных распространения имя регистрации для агента распространителя (Distribution Agent), создаст учетную запись пользователя и добавит ей роль db_owner. Заметим, что в электронной документации SQL Server Books Online (BOL) утверждается, что для агента распространителя требуется членство в списке доступа к публикации Publication Access List (PAL). PAL обеспечивает защиту доступа к публикациям и предотвращает создание неавторизованных подписок. Однако в функционировании PAL в некоторых сценариях имеются определенные недочеты, более подробно с этой информацией можно ознакомиться в моем блоге When is the Publication Access List required? (http://www.sqlskills.com/blogs/joe/when-is-the-publication-access-list-required/).
4. Подключитесь к серверу-подписчику и выполните код из листинга 4. Данный код создаст в базе данных подписки имя регистрации для агента распространителя, создаст учетную запись пользователя и добавит ей разрешения db_owner.
5. Назначьте учетной записи агента моментальных снимков разрешения на запись для папки снимков.
6. Назначьте учетной записи агента распространителя разрешения на чтение для папки моментальных снимков.

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

Создание публикации

Для создания новой публикации выполните следующие действия.

1. В окне Object Explorer раскройте объект SQL Server для сервера-издателя и откройте папку Replication. Щелкните правой кнопкой мыши на папке Local Publications и выберите в меню пункт New Publication («Новая публикация»).
2. На экране мастера новой публикации выберите Next (при условии, что вы ранее не отключили данный экран).
3. На экране Publication Database выберите базы данных AdventureWorks. Имейте в виду, что вы не можете выбрать несколько баз данных одновременно, так как отдельная публикация всегда привязана к отдельной базы данных. Нажмите Next.
4. На экране Publication Type выберите тип Transactional publication и нажмите Next.
5. На экране Articles, как показано на экране 3, раскройте список таблиц Tables и выберите таблицы, перечисленные ранее в разделе «Развертывание репликации транзакций». Затем прокрутите экран вниз, откройте список представлений Views и выберите представление vEmployee (HumanResources).
6. В списке публикуемых объектов Objects to publish щелкните мышью на самом верхнем узле списка Tables, нажмите кнопку Article Properties и выберите Set Properties of All Table Articles. Просмотрите различные параметры статьи в диалоговой панели Properties for All Table Articles, как показано на экране 4. Нажмите OK.
7. На экране Articles выберите Next.
8. Экран Article Issues содержит предупреждение о том, что, поскольку мы включили в статью представление, нам необходимо обеспечить, чтобы все используемые в представлении объекты были доступны для подписчика. Нажмите Next.
9. На экране Filter Table Rows можно задать предикаты для фильтрации строк таблицы, которые необходимо реплицировать. Помните, что фильтры могут вызвать дополнительные задержки при создании топологии с высокой пропускной способностью. Мы здесь не вносим никаких изменений и нажимаем Next.
10. Экран Snapshot Agent задает время запуска агента моментальных снимков и создания нового снимка. Выберите вариант Create a snapshot immediately and keep the snapshot available to initialize subscriptions («Создать снимок и сохранять его доступным для инициализации подписок»). При выборе этого варианта вы устанавливаете значение параметра immediate_sync равным True, что приводит к сохранению данных в базе данных распространения. Необходимо учитывать эффект, который может иметь этот параметр, в топологии с высокой пропускной способностью. Нажмите Next.
11. Экран Agent Security задает учетные данные агента моментальных снимков и агента чтения журнала. Снимите галочку у параметра Use the security setting from the Snapshot Agent («Использовать настройки безопасности от агента моментальных снимков») и нажмите кнопку Security Settings («Настройки безопасности») для агента моментальных снимков.
12. В диалоговой панели настроек безопасности агента моментальных снимков (экран 5) выберите Run under the following Windows account («Выполнять в контексте следующей учетной записи Windows»), введите имя учетной записи и пароль, нажмите OK.
13. На экране Agent Security нажмите кнопку Security Settings для агента чтения журнала. Введите учетную запись и пароль. Нажмите OK, затем Next.
14. На экране Wizard Actions выберите Create the publication («Создать публикацию») и нажмите Next.
15. На завершающем экране Complete the Wizard задайте имя публикации (я ее назвал Pub_AdventureWorks) и нажмите кнопку Finish. Вы увидите статус выполняемых действий. После успешного завершения всех шагов нажмите кнопку Close.

Экран 3. Выбор таблиц для репликации

Экран 4. Обзор настроек статьи

Экран 5. Ввод учетных данных агента моментальных снимков

Создание принудительной подписки

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

1. На сервере, выполняющем роль издателя/распространителя, в папке Replication раскройте список Local Publications, щелкните правой кнопкой мыши на [AdventureWorks]:Pub_AdventureWorks и выберите для создания новой подписки пункт меню New Subscriptions.
2. На экране мастера создания новой подписки нажмите кнопку Next (при условии, что вы ранее не отключили этот экран).
3. На экране Publication убедитесь, что выбрана новая публикация. Нажмите Next.
4. Экран Distribution Agent Location задает тип подписки (принудительная или по запросу). Оставьте все параметры без изменений и нажмите Next.
5. На экране Subscribers нажмите кнопку Add Subscriber, затем Add SQL Server Subscriber. В диалоговой панели Connect to Server подключитесь к экземпляру сервера-подписчика.
6. На экране Subscribers появится еще один сервер. Выберите для него из раскрывающегося списка базу данных подписки. Нажмите Next.
7. На экране Distribution Agent Security (экран 6) нажмите кнопку со значком многоточия (...) для запуска диалоговой панели настройки параметров безопасности агента распространителя. Введите на этой панели имя учетной записи и пароль агента распространителя. Нажмите OK.
8. На экране Distribution Agent Security нажмите Next.
9. На экране расписания синхронизации Synchronization Schedule оставьте значение по умолчанию (работать непрерывно) и нажмите Next.
10. На экране Initialize Subscriptions оставьте значение по умолчанию для немедленного запуска подписки. Нажмите Next.
11. На следующем экране мастера Actions оставьте отмеченный по умолчанию флажок Create the subscription(s) и нажмите Next.
12. На завершающей странице мастера нажмите кнопку Finish. После успешного выполнения всех этапов создания новой подписки нажмите Close.

Экран 6. Настройка параметров безопасности агента-распространителя

Тестирование репликации

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

1. В базе данных публикации выполните код:

INSERT [Person].[Address]
(AddressLine1, City, StateProvinceID, PostalCode)
VALUES
('2222 Test Drive', 'Minneapolis', 36, 55410);

2. Подождите секунд 10, затем выполните операцию SELECT на сервере-подписчике:

SELECT AddressID
FROM [Person].[Address]
WHERE AddressLine1 = '2222 Test Drive';

Если все было сделано правильно, то будут возвращены только что вставленные строки.

3. Протестируйте репликацию представления на сервер-подписчик с помощью кода:

SELECT COUNT(*)
FROM HumanResources.vEmployee;

У меня после исполнения данного кода было выдано 250 строк и не было никаких сообщений об ошибках, что указывает на то, что все зависимые объекты были реплицированы.

Изучение на практике

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

USE [master];
GO
CREATE LOGIN [SQLskills\SQLskillsSnapshotAGT] FROM WINDOWS;
USE [distribution];
GO
CREATE USER [SQLskills\SQLskillsSnapshotAGT]
FOR LOGIN [SQLskills\SQLskillsSnapshotAGT];
EXEC sp_addrolemember N'db_owner',
N'SQLskills\SQLskillsSnapshotAGT';
USE [AdventureWorks];
GO
CREATE USER [SQLskills\SQLskillsSnapshotAGT]
FOR LOGIN [SQLskills\SQLskillsSnapshotAGT];
EXEC sp_addrolemember N'db_owner',
N'SQLskills\SQLskillsSnapshotAGT';

USE [master];
GO
CREATE LOGIN [SQLskills\SQLskillsLogReaderAG] FROM WINDOWS;
USE [AdventureWorks];
GO
CREATE USER [SQLSKILLS\SQLskillsLogReaderAG]
FOR LOGIN [SQLSKILLS\SQLskillsLogReaderAG];
EXEC sp_addrolemember N'db_owner',
N'SQLskills\SQLskillsLogReaderAG';
USE [distribution];
GO
CREATE USER [SQLSKILLS\SQLskillsLogReaderAG]
FOR LOGIN [SQLSKILLS\SQLskillsLogReaderAG];
EXEC sp_addrolemember N'db_owner',
N'SQLskills\SQLskillsLogReaderAG';

USE [master];
GO
CREATE LOGIN [SQLskills\SQLskillsDistAGT] FROM WINDOWS;
USE [distribution];
GO
CREATE USER [SQLskills\SQLskillsDistAGT]
FOR LOGIN [SQLskills\SQLskillsDistAGT]
EXEC sp_addrolemember N'db_owner',
N'SQLskills\SQLskillsDistAGT';

USE [master];
GO
CREATE LOGIN [SQLskills\SQLskillsDistAGT] FROM WINDOWS;
USE [AWReporting];
GO
CREATE USER [SQLSKILLS\SQLskillsDistAGT]
FOR LOGIN [SQLSKILLS\SQLskillsDistAGT]
EXEC sp_addrolemember N'db_owner',
N'SQLskills\SQLskillsDistAGT'