Руководство. Настройка репликации между двумя управляемыми экземплярами SQL

Область применения: Управляемый экземпляр SQL Azure

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

В этом руководстве описано следующее:

Это руководство предназначено для опытной аудитории и предполагает, что пользователь знаком с развертыванием и подключением как к управляемым экземплярам SQL, так и к виртуальным машинам SQL Server в Azure.

Требования

Для настройки Управляемого экземпляра в качестве издателя и (или) распространителя должны выполняться следующие требования:

• Управляемый экземпляр SQL издателя находится в той же виртуальной сети, что и распространитель и подписчик, или пиринг виртуальных сетей или VPN-шлюзы, настроены между виртуальными сетями всех трех сущностей.
• При подключении используется аутентификация SQL между участниками репликации.
• Общий ресурс учетной записи хранения Azure для рабочей папки репликации.
• Порт 445 (исходящий TCP) открыт в правилах безопасности NSG для управляемых экземпляров SQL для доступа к общей папке Azure. Если возникла ошибка failed to connect to azure storage <storage account name> with os error 53, необходимо добавить правило исходящего трафика в группу безопасности сети соответствующей подсети Управляемого экземпляра SQL.

1. Создание группы ресурсов

С помощью портала Azure создайте группу ресурсов с именем SQLMI-Repl.

2. Создание управляемых экземпляров SQL

Используйте портал Azure для создания двух управляемых экземпляров SQL в одной виртуальной сети и подсети. Например, назовите два управляемых экземпляра SQL:

• sql-mi-publisher (и еще несколько символов, чтобы имя было уникальным).
• sql-mi-subscriber (и еще несколько символов, чтобы имя было уникальным).

Вам также потребуется настроить виртуальную машину Azure для подключения к управляемым экземплярам SQL.

3. Создание учетной записи хранения Azure

Создайте учетную запись хранения Azure для рабочей папки, а затем создайте в этой учетной записи общую папку.

Скопируйте путь к общей папке в формате \\storage-account-name.file.core.windows.net\file-share-name.

Пример: \\replstorage.file.core.windows.net\replshare

Скопируйте ключи доступа к хранилищу в следующем формате: DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=****;EndpointSuffix=core.windows.net.

Пример: DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=123456789aBcDeFgHiJkLmNoPqRsTuVwXyZ==;EndpointSuffix=core.windows.net

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

4. Создание базы данных издателя

Подключитесь к управляемому экземпляру SQL издателя (sql-mi-publisher) с помощью SQL Server Management Studio и выполните следующий код Transact-SQL (T-SQL), чтобы создать базу данных издателя:

USE [master]
GO

CREATE DATABASE [ReplTran_PUB]
GO

USE [ReplTran_PUB]
GO
CREATE TABLE ReplTest (
  ID INT NOT NULL PRIMARY KEY,
  c1 VARCHAR(100) NOT NULL,
  dt1 DATETIME NOT NULL DEFAULT getdate()
)
GO

USE [ReplTran_PUB]
GO

INSERT INTO ReplTest (ID, c1) VALUES (6, 'pub')
INSERT INTO ReplTest (ID, c1) VALUES (2, 'pub')
INSERT INTO ReplTest (ID, c1) VALUES (3, 'pub')
INSERT INTO ReplTest (ID, c1) VALUES (4, 'pub')
INSERT INTO ReplTest (ID, c1) VALUES (5, 'pub')
GO
SELECT * FROM ReplTest
GO

5. Создание базы данных подписчика

Подключитесь к управляемому экземпляру SQL подписчика (sql-mi-subscriber) с помощью SQL Server Management Studio и выполните следующий код T-SQL, чтобы создать пустую базу данных подписчика:

USE [master]
GO

CREATE DATABASE [ReplTran_SUB]
GO

USE [ReplTran_SUB]
GO
CREATE TABLE ReplTest (
  ID INT NOT NULL PRIMARY KEY,
  c1 VARCHAR(100) NOT NULL,
  dt1 DATETIME NOT NULL DEFAULT getdate()
)
GO

6. Настройка распространения

Подключитесь к управляемому экземпляру SQL издателя (sql-mi-publisher) с помощью SQL Server Management Studio и выполните следующий код T-SQL, чтобы настроить базу данных распространителя.

USE [master]
GO

EXEC sp_adddistributor @distributor = @@ServerName;
EXEC sp_adddistributiondb @database = N'distribution';
GO

7. Настройка использования распространителя для издателя

В управляемом экземпляреsql-mi-publisher SQL издателя измените выполнение запроса на режим SQLCMD и выполните следующий код, чтобы зарегистрировать новый распространитель в издателе.

:setvar username loginUsedToAccessSourceManagedInstance
:setvar password passwordUsedToAccessSourceManagedInstance
:setvar file_storage "\\storage-account-name.file.core.windows.net\file-share-name"
-- example: file_storage "\\replstorage.file.core.windows.net\replshare"
:setvar file_storage_key "DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=****;EndpointSuffix=core.windows.net"
-- example: file_storage_key "DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=123456789aBcDeFgHiJkLmNoPqRsTuVwXyZ==;EndpointSuffix=core.windows.net"

USE [master]
EXEC sp_adddistpublisher
  @publisher = @@ServerName,
  @distribution_db = N'distribution',
  @security_mode = 0,
  @login = N'$(username)',
  @password = N'$(password)',
  @working_directory = N'$(file_storage)',
  @storage_connection_string = N'$(file_storage_key)'; -- Remove this parameter for on-premises publishers

Этот скрипт настраивает локального издателя в управляемом экземпляре SQL, добавляет связанный сервер и создает набор заданий для агента SQL Server.

8. Создание публикации и подписчика

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

-- Set variables
:setvar username sourceLogin
:setvar password sourcePassword
:setvar source_db ReplTran_PUB
:setvar publication_name PublishData
:setvar object ReplTest
:setvar schema dbo
:setvar target_server "sql-mi-subscriber.wdec33262scj9dr27.database.windows.net"
:setvar target_username targetLogin
:setvar target_password targetPassword
:setvar target_db ReplTran_SUB

-- Enable replication for your source database
USE [$(source_db)]
EXEC sp_replicationdboption
  @dbname = N'$(source_db)',
  @optname = N'publish',
  @value = N'true';

-- Create your publication
EXEC sp_addpublication
  @publication = N'$(publication_name)',
  @status = N'active';

-- Configure your log reader agent
EXEC sp_changelogreader_agent
  @publisher_security_mode = 0,
  @publisher_login = N'$(username)',
  @publisher_password = N'$(password)',
  @job_login = N'$(username)',
  @job_password = N'$(password)';

-- Add the publication snapshot
EXEC sp_addpublication_snapshot
  @publication = N'$(publication_name)',
  @frequency_type = 1,
  @publisher_security_mode = 0,
  @publisher_login = N'$(username)',
  @publisher_password = N'$(password)',
  @job_login = N'$(username)',
  @job_password = N'$(password)';

-- Add the ReplTest table to the publication
EXEC sp_addarticle
  @publication = N'$(publication_name)',
  @type = N'logbased',
  @article = N'$(object)',
  @source_object = N'$(object)',
  @source_owner = N'$(schema)';

-- Add the subscriber
EXEC sp_addsubscription
  @publication = N'$(publication_name)',
  @subscriber = N'$(target_server)',
  @destination_db = N'$(target_db)',
  @subscription_type = N'Push';

-- Create the push subscription agent
EXEC sp_addpushsubscription_agent
  @publication = N'$(publication_name)',
  @subscriber = N'$(target_server)',
  @subscriber_db = N'$(target_db)',
  @subscriber_security_mode = 0,
  @subscriber_login = N'$(target_username)',
  @subscriber_password = N'$(target_password)',
  @job_login = N'$(username)',
  @job_password = N'$(password)';

-- Initialize the snapshot
EXEC sp_startpublication_snapshot
@publication = N'$(publication_name)';

9. Изменение параметров агента

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

Выполните следующую команду T-SQL на издателе, чтобы увеличить время ожидания входа:

-- Increase login timeout to 150s
update msdb..sysjobsteps set command = command + N' -LoginTimeout 150'
where subsystem in ('Distribution','LogReader','Snapshot') and command not like '%-LoginTimeout %'

При необходимости выполните следующую команду T-SQL, чтобы задать время ожидания входа обратно в значение по умолчанию:

-- Increase login timeout to 30
update msdb..sysjobsteps set command = command + N' -LoginTimeout 30'
where subsystem in ('Distribution','LogReader','Snapshot') and command not like '%-LoginTimeout %'

Чтобы применить эти изменения, перезапустите все три агента.

10. Тестовая репликация

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

Выполните следующий фрагмент кода T-SQL, чтобы просмотреть строки в подписчике:

select * from dbo.ReplTest

Выполните следующий фрагмент кода T-SQL, чтобы вставить дополнительные строки на издателе, а затем снова проверить строки на подписчике.

INSERT INTO ReplTest (ID, c1) VALUES (15, 'pub')

Очистка ресурсов

Чтобы удалить публикацию, выполните следующую команду T-SQL:

-- Drops the publication
USE [ReplTran_PUB]
EXEC sp_droppublication @publication = N'PublishData'
GO

Чтобы удалить параметр репликации из базы данных, выполните следующую команду T-SQL:

-- Disables publishing of the database
USE [ReplTran_PUB]
EXEC sp_removedbreplication
GO

Чтобы отключить публикацию и распространение, выполните следующую команду T-SQL:

-- Drops the distributor
USE [master]
EXEC sp_dropdistributor @no_checks = 1
GO

Вы можете очистить ресурсы Azure, удалив ресурсы Управляемого экземпляра SQL из группы ресурсов, а затем удалив группу ресурсов SQLMI-Repl.

Связанный контент

• Репликация транзакций с управляемым экземпляром SQL Azure
• Руководство по настройке репликации транзакций между Управляемым экземпляром SQL Azure и SQL Server