Руководство. Настройка репликации транзакций между Azure SQL Managed Instance и SQL Server

Применимо к:Azure SQL Managed Instance

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

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

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

Предварительные требования

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

• Подписка Azure.
• Опыт развертывания двух управляемых экземпляров SQL в одной виртуальной сети.
• Подписчик SQL Server на локальной машине или на виртуальной машине Azure. В этом руководстве используется виртуальная машина Azure.
• SQL Server Management Studio (SSMS) 18.0 или более поздней версии.
• Последняя версия Azure PowerShell.
• Порты 445 и 1433 разрешают трафик SQL в брандмауэре Azure и брандмауэре Windows.

Обязательные имена и параметры сети

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

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

Используйте следующий фрагмент кода PowerShell для создания новой группы ресурсов.

Задайте переменные:

$ResourceGroupName = "SQLMI-Repl"
$Location = "East US 2"

Создайте группу ресурсов:

New-AzResourceGroup -Name $ResourceGroupName -Location $Location

Проверьте, создана ли группа ресурсов:

Get-AzResourceGroup -Name $ResourceGroupName | Select-Object ResourceGroupName, Location

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

Создайте два управляемых экземпляра SQL в этой новой группе ресурсов с помощью портала Azure.

• Имя управляемого экземпляра SQL издателя должно быть sql-mi-publisher (наряду с несколькими символами для случайной обработки), а имя виртуальной сети должно быть vnet-sql-mi-publisher.

• Имя управляемого экземпляра SQL распространителя должно быть sql-mi-distributor (наряду с несколькими символами для случайной обработки), и оно должно находиться в той же виртуальной сети, что и управляемый экземпляр SQL издателя.

  

Дополнительные сведения о создании управляемого экземпляра SQL см. в разделе Quickstart: Create Azure SQL Managed Instance.

Создание виртуальной машины SQL Server

Создайте виртуальную машину SQL Server с помощью портала Azure. Виртуальная машина SQL Server должна иметь следующие характеристики:

• Имя: sql-vm-subscriber
• Изображение: версия SQL Server, поддерживающая транзакционную репликацию с Azure SQL Managed Instance по матрице поддержки
• Группа ресурсов: то же, что и управляемый экземпляр SQL
• Виртуальная сеть: sql-vm-subscriber-vnet.

Дополнительные сведения о развертывании виртуальной машины SQL Server в Azure см. в статье Quickstart: создание SQL Server на виртуальной машине Windows на портале Azure.

Настройка пиринга VNet (Virtual Network)

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

Задайте переменные:

$SubscriptionId = '<SubscriptionID>'
$resourceGroup = 'SQLMI-Repl'
$pubvNet = 'sql-mi-publisher-vnet'
$subvNet = 'sql-vm-subscriber-vnet'
$pubsubName = 'Pub-to-Sub-Peer'
$subpubName = 'Sub-to-Pub-Peer'

Получите виртуальные сети:

$virtualNetwork1 = Get-AzVirtualNetwork -ResourceGroupName $resourceGroup -Name $pubvNet
$virtualNetwork2 = Get-AzVirtualNetwork -ResourceGroupName $resourceGroup -Name $subvNet

Настройте пиринг виртуальной сети от издателя к подписчику:

Add-AzVirtualNetworkPeering `
  -Name $pubsubName `
  -VirtualNetwork $virtualNetwork1 `
  -RemoteVirtualNetworkId $virtualNetwork2.Id

Настройте пиринг виртуальной сети от подписчика к издателю:

Add-AzVirtualNetworkPeering `
  -Name $subpubName `
  -VirtualNetwork $virtualNetwork2 `
  -RemoteVirtualNetworkId $virtualNetwork1.Id

Проверьте состояние пиринга в виртуальной сети издателя (должен быть возвращён Connected):

Get-AzVirtualNetworkPeering -ResourceGroupName $resourceGroup -VirtualNetworkName $pubvNet | Select-Object PeeringState

Проверьте состояние пиринга в виртуальной сети подписчика (должно возвращаться Connected):

Get-AzVirtualNetworkPeering -ResourceGroupName $resourceGroup -VirtualNetworkName $subvNet | Select-Object PeeringState

После установки пиринга виртуальной сети проверьте подключение, запуская SQL Server Management Studio (SSMS) на узле SQL Server и подключаясь к обоим управляемым экземплярам SQL. Дополнительные сведения о подключении к SQL managed instance с помощью SSMS см. в статье Use SSMS для подключения к SQL Managed Instance.

Создание частной зоны DNS

Частная зона DNS позволяет маршрутизации DNS между управляемыми экземплярами SQL и SQL Server.

Создание частной зоны

1. Войдите на портал Azure.

2. Выберите Создать ресурс, чтобы создать новый ресурс Azure.

3. Найдите private dns zone на Azure Marketplace.

4. Выберите ресурс Private DNS/c0 опубликованный Microsoft, а затем выберите Create для создания зоны DNS.

5. Выберите подписку и группу ресурсов из раскрывающегося списка.

6. Укажите для зоны DNS произвольное имя, например repldns.com.

   

7. Выберите Review + create (Просмотреть + создать). Просмотрите параметры частной зоны DNS и нажмите кнопку "Создать ", чтобы создать ресурс.

Создание записи A

1. Перейдите в новую зону Private DNS и выберите Overview.

2. Щелкните +Набор записей, чтобы создать новую запись A.

3. Укажите имя виртуальной машины SQL Server, а также частный внутренний IP-адрес.

   

4. Щелкните ОК, чтобы создать запись A.

Связывание виртуальной сети

1. Перейдите к новой зоне Private DNS и выберите Virtual network links.

2. Выберите Добавить.

3. Укажите имя для связи, например Pub-link.

4. Выберите подписку из раскрывающегося списка, а затем выберите виртуальную сеть для управляемого экземпляра SQL издателя.

5. Установите флажок Включить авторегистрацию.

   

6. Щелкните ОК, чтобы установить связь с виртуальной сетью.

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

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

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

Значения конфигурации хранилища

При настройке распределения требуются следующие значения:

• Формат пути к рабочему каталогу: \\<storage-account-name>.file.core.windows.net\<file-share-name>
• Формат строки соединения хранения: DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=<key>;EndpointSuffix=core.windows.net

Примеры значений, используемых в этом руководстве:

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

Создание базы данных

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

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

Удалите базу данных, если она существует и создайте новую:

USE [master];
GO

IF EXISTS (SELECT * FROM sys.sysdatabases WHERE name = 'ReplTutorial')
BEGIN
    DROP DATABASE ReplTutorial;
END
GO

CREATE DATABASE [ReplTutorial];
GO

Создайте тестовую таблицу репликации:

USE [ReplTutorial];
GO

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

Вставка примеров данных:

USE [ReplTutorial];
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 строк с идентификаторами 2, 3, 4, 5 и 6.

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

После установки подключения и создания образца базы данных можно настроить распределение на управляемом сервере SQL распространителя (sql-mi-distributor).

Параметры конфигурации распределения

Выполните сбор следующих значений перед настройкой распределения:

Настройте экземпляр распространителя

1. Запустите SQL Server Management Studio на SQL Server.
2. Подключитесь к управляемому экземпляру SQL распространителя (sql-mi-distributor).
3. Откройте окно "Создать запрос" и выполните следующие команды.

Добавьте распространителя:

EXECUTE sp_adddistributor
    @distributor = 'sql-mi-distributor.b6bf57.database.windows.net',
    @password = '<distributor_admin_password>';

Создайте базу данных распространителя:

EXECUTE sp_adddistributiondb @database = N'distribution';

Убедитесь, что база данных распространителя создана:

SELECT name FROM sys.databases WHERE name = 'distribution';

Добавьте издателя в распространитель:

EXECUTE sp_adddistpublisher
    @publisher = 'sql-mi-publisher.b6bf57.database.windows.net',
    @distribution_db = N'distribution',
    @security_mode = 0,
    @login = N'azureuser',
    @password = N'<publisher_password>',
    @working_directory = N'\\replstorage.file.core.windows.net\replshare',
    @storage_connection_string = N'DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=123456789aBcDeFgHiJkLmNoPqRsTuVwXyZ==;EndpointSuffix=core.windows.net';

Регистрация распространителя посредством издателя

1. Подключитесь к управляемому экземпляру SQL издателя (sql-mi-publisher).
2. Откройте окно создания запроса и выполните следующую команду, чтобы зарегистрировать распространителя:

USE master;
GO

EXECUTE sys.sp_adddistributor
    @distributor = 'sql-mi-distributor.b6bf57.database.windows.net',
    @password = '<distributor_admin_password>';

Убедитесь, что распространитель зарегистрирован:

SELECT * FROM sys.servers WHERE is_distributor = 1;

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

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

1. Запустите SQL Server Management Studio на SQL Server.

2. Подключитесь к управляемому экземпляру SQL издателя (sql-mi-publisher).

3. В Object Explorer разверните узел Replication и щелкните правой кнопкой мыши папку Local Publication. Щелкните Создать публикацию.

4. Щелкните Далее, чтобы перейти со страницы приветствия.

5. На странице База данных публикации выберите созданную ранее базу данных ReplTutorial. Выберите Далее.

6. На странице Тип публикации выберите Публикация транзакций. Выберите Далее.

7. На странице Статьи установите флажок Таблицы. Выберите Далее.

8. На странице Фильтрация строк таблицы щелкните Далее, не добавляя новых фильтров.

9. На странице Snapshot Agent установите флажок рядом с пунктом Создать моментальный снимок немедленно и сохранить его доступным для инициализации подписок. Выберите Далее.

10. На странице Agent Security выберите Security Settings... . Укажите SQL Server учетные данные для входа для Snapshot Agent и подключения к издателю. Выберите OK, чтобы закрыть страницу Snapshot Agent Security. Выберите Далее.

    

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

12. На странице "Завершение работы мастера " назовите публикацию ReplTestи нажмите кнопку "Далее ", чтобы создать публикацию.

13. После создания публикации обновите узел Replication в Object Explorer<>/c1> и разверните Local Publications, чтобы увидеть новую публикацию.

Создание подписки

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

1. Запустите SQL Server Management Studio на SQL Server.
2. Подключитесь к управляемому экземпляру SQL издателя (sql-mi-publisher).
3. Откройте окно New Query и выполните следующие команды Transact-SQL. Используйте DNS-имя, настроенное в частной зоне DNS в рамках имени подписчика.

Параметры подписки

Добавьте подписку:

USE [ReplTutorial];
GO

EXEC sp_addsubscription
    @publication = N'ReplTest',
    @subscriber = N'sql-vm-subscriber.repldns.com',
    @destination_db = N'ReplSub',
    @subscription_type = N'Push',
    @sync_type = N'automatic',
    @article = N'all',
    @update_mode = N'read only',
    @subscriber_type = 0;

Добавьте агента push-подписки.

EXEC sp_addpushsubscription_agent
    @publication = N'ReplTest',
    @subscriber = N'sql-vm-subscriber.repldns.com',
    @subscriber_db = N'ReplSub',
    @job_login = N'azureuser',
    @job_password = '<Complex Password>',
    @subscriber_security_mode = 0,
    @subscriber_login = N'azureuser',
    @subscriber_password = '<Complex Password>',
    @dts_package_location = N'Distributor';
GO

Убедитесь, что подписка создана:

SELECT * FROM distribution.dbo.MSsubscriptions;

Проверка репликации

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

Просмотр начальных данных по подписчику

Подключитесь к подписчику SQL Server и выполните следующий запрос:

USE ReplSub;
GO

SELECT * FROM dbo.ReplTest;

Ожидаемые выходные данные: 5 строк с идентификаторами 2, 3, 4, 5 и 6 (начальные данные издателя).

Вставка новых данных на издателе

Подключитесь к управляемому экземпляру SQL Server издателя (sql-mi-publisher) и вставьте новую строку:

USE ReplTutorial;
GO

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

Проверьте вставку на издателе:

SELECT * FROM ReplTest WHERE ID = 15;

Проверьте репликацию на подписчике.

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

USE ReplSub;
GO

SELECT * FROM dbo.ReplTest WHERE ID = 15;

Ожидаемые выходные данные: 1 строка с идентификатором 15 и значением c1 'pub'.

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

1. Перейдите к группе ресурсов на портале Azure.
2. Выберите управляемые экземпляры SQL и нажмите кнопку "Удалить". Введите yes текстовое поле, чтобы подтвердить удаление ресурса, а затем нажмите кнопку "Удалить". Этот процесс может занять некоторое время, чтобы завершить работу в фоновом режиме, и пока это не будет сделано, вы не сможете удалить виртуальный кластер или другие зависимые ресурсы. Отслеживайте удаление на вкладке "Действие ", чтобы подтвердить удаление управляемого экземпляра SQL.
3. После удаления управляемого экземпляра SQL удалите виртуальный кластер , выбрав его в группе ресурсов, а затем нажмите кнопку "Удалить". Введите yes текстовое поле, чтобы подтвердить удаление ресурса, а затем нажмите кнопку "Удалить".
4. Удалите все остальные ресурсы. Введите yes текстовое поле, чтобы подтвердить удаление ресурса, а затем нажмите кнопку "Удалить".
5. Удалите группу ресурсов, выбрав "Удалить группу ресурсов", введите имя группы myResourceGroupресурсов и нажмите кнопку "Удалить".

Известные ошибки

Вход в систему Windows не поддерживается

Exception Message: Windows logins are not supported in this version of SQL Server.

Агент был настроен с использованием имени входа Windows и теперь должен использовать вместо него имя входа SQL Server. Используйте страницу Agent Security в свойствах Публикации, чтобы изменить учетные данные входа на учетную запись SQL Server.

Не удалось подключиться к Azure Storage

Connecting to Azure Files Storage '\\replstorage.file.core.windows.net\replshare' Failed to connect to Azure Storage '' with OS error: 53.
2019-11-19 02:21:05.07 Obtained Azure Storage Connection String for replstorage
2019-11-19 02:21:05.07 Connecting to Azure Files Storage '\\replstorage.file.core.windows.net\replshare'
2019-11-19 02:21:31.21 Failed to connect to Azure Storage '' with OS error: 53.

Скорее всего, порт 445 закрыт в брандмауэре Azure, брандмауэре Windows или обоих.

Connecting to Azure Files Storage '\\replstorage.file.core.windows.net\replshare' Failed to connect to Azure Storage '' with OS error: 55.

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

• Это допустимо: \\replstorage.file.core.windows.net\replshare
• Это может привести к ошибке OS 55: \\replstorage.file.core.windows.net/replshare

Не удалось подключиться к подписчику

The process could not connect to Subscriber 'SQL-VM-SUBSCRIBER
Could not open a connection to SQL Server [53].
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.

Возможные решения:

• Убедитесь, что порт 1433 открыт.
• Убедитесь, что в подписчике разрешен протокол TCP/IP.
• Убедитесь, что при создании подписчика использовалось имя DNS.
• Убедитесь, что виртуальные сети правильно связаны в частной зоне DNS.
• Убедитесь, что запись A правильно настроена.
• Убедитесь, что пиринг виртуальных сетей настроен правильно.

Отсутствие публикаций, на которые можно подписаться

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

There are no publications to which you can subscribe, either because this server has no publications or because you do not have sufficient privileges to access the publications.

Хотя это сообщение об ошибке может быть точным, и действительно, на издателе, к которому вы подключились, может не быть доступных публикаций или у вас может не быть достаточных разрешений, причиной этой ошибки также может быть более старая версия SQL Server Management Studio. Попробуйте обновить до SQL Server Management Studio 18.0 или более поздней версии, чтобы исключить это в качестве основной причины.

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

• Что такое Azure SQL Managed Instance?
• Аудит SQL Server в Azure SQL Managed Instance
• Always Encrypted
• Настройка защиты от расширенных угроз в Azure SQL Managed Instance
• Динамическое маскирование данных
• Безопасность на уровне строк
• Прозрачное шифрование данных (TDE)