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

Статья
04/26/2023

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

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

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

Настройка Управляемого экземпляра в качестве издателя репликации.
Настройка Управляемого экземпляра в качестве распространителя репликации.
Настройка SQL Server в качестве подписчика.

Репликация между издателем управляемого экземпляра, распространителем управляемого экземпляра и подписчиком SQL Server

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

Примечание.

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

Необходимые компоненты

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

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

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

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

# set variables
$ResourceGroupName = "SQLMI-Repl"
$Location = "East US 2"

# Create a new resource group
New-AzResourceGroup -Name  $ResourceGroupName -Location $Location

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

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

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

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

Примечание.

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

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

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

Имя: sql-vm-sub
Образ: SQL Server 2016 или более поздней версии.
Группа ресурсов: та же, что выбрана для управляемого экземпляра.
Виртуальная сеть: sql-vm-sub-vnet.

См. сведения о развертывании виртуальной машины SQL Server в Azure в кратком руководстве по созданию виртуальной машины SQL Server 2017 на платформе Windows с помощью портала Azure.

Настройка пиринга виртуальных сетей

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

# Set variables
$SubscriptionId = '<SubscriptionID>'
$resourceGroup = 'SQLMI-Repl'
$pubvNet = 'sql-mi-publisher-vnet'
$subvNet = 'sql-vm-sub-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  

# Configure VNet peering from publisher to subscriber
Add-AzVirtualNetworkPeering `
  -Name $pubsubName `
  -VirtualNetwork $virtualNetwork1 `
  -RemoteVirtualNetworkId $virtualNetwork2.Id

# Configure VNet peering from subscriber to publisher
Add-AzVirtualNetworkPeering `
  -Name $subpubName `
  -VirtualNetwork $virtualNetwork2 `
  -RemoteVirtualNetworkId $virtualNetwork1.Id

# Check status of peering on the publisher VNet; should say connected
Get-AzVirtualNetworkPeering `
 -ResourceGroupName $resourceGroup `
 -VirtualNetworkName $pubvNet `
 | Select PeeringState

# Check status of peering on the subscriber VNet; should say connected
Get-AzVirtualNetworkPeering `
 -ResourceGroupName $resourceGroup `
 -VirtualNetworkName $subvNet `
 | Select PeeringState

Настроив пиринг виртуальных сетей, проверьте подключение, запустив SQL Server Management Studio (SSMS) на сервере SQL Server и подключившись к обоим управляемым экземплярам. См. сведения о том, как создать подключение к управляемому экземпляру SQL с использованием SSMS.

Проверка возможности подключения к управляемым экземплярам

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

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

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

Войдите на портал Azure.
Щелкните Создать ресурс, чтобы создать ресурс Azure.
Выполните в Azure Marketplace поиск по строке private dns zone.
Выберите ресурс Частная зона DNS, опубликованный корпорацией Майкрософт, и щелкните Создать для создания новой зоны DNS.
Выберите нужные подписку и группу ресурсов в раскрывающихся меню.
Укажите для зоны DNS произвольное имя, например repldns.com.
Выберите Review + create (Просмотреть и создать). Просмотрите параметры частной зоны DNS и щелкните Создать, чтобы создать этот ресурс.

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

Перейдите к новому ресурсу Частная зона DNS и щелкните Обзор.
Щелкните +Набор записей, чтобы создать новую запись A.
Укажите имя виртуальной машины SQL Server и частный внутренний IP-адрес.
Щелкните ОК, чтобы создать запись A.

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

Перейдите к новому ресурсу Частная зона DNS и щелкните Связи виртуальных сетей.
Выберите Добавить.
Укажите имя для связи, например Pub-link.
Выберите в раскрывающемся списке нужную подписку, а затем выберите виртуальную сеть для управляемого экземпляра издателя.
Установите флажок Включить авторегистрацию.
Щелкните ОК, чтобы установить связь с виртуальной сетью.
Повторите эти шаги, чтобы добавить ссылку на виртуальную сеть подписчика, указав для нее имя Sub-link.

Создание учетной записи службы хранилища 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=dYT5hHZVu9aTgIteGfpYE64cfis0mpKTmmc8+EP53GxuRg6TCwe5eTYWrQM4AmQSG5lb3OBskhg==;EndpointSuffix=core.windows.net

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

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

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

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

-- Create the databases
USE [master]
GO

-- Drop database if it exists
IF EXISTS (SELECT * FROM sys.sysdatabases WHERE name = 'ReplTutorial')
BEGIN
    DROP DATABASE ReplTutorial
END
GO

-- Create new database
CREATE DATABASE [ReplTutorial]
GO

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

-- Populate table with data
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

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

Итак, подключение установлено и у вас есть пример базы данных. Теперь вы можете настроить распространение в управляемом экземпляре sql-mi-distributor. Для этого выполните следующие шаги:

Запустите SQL Server Management Studio на SQL Server.
Подключитесь к управляемому экземпляру sql-mi-distributor.

Откройте окно Новый запрос и выполните следующий код Transact-SQL, чтобы настроить распространение в управляемом экземпляре распространителя.

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

EXEC sp_adddistributiondb @database = N'distribution'

EXEC sp_adddistpublisher @publisher = 'sql-mi-publisher.b6bf57.database.windows.net', -- primary publisher
     @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'<storage_connection_string>'
     -- example: @storage_connection_string = N'DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=dYT5hHZVu9aTgIteGfpYE64cfis0mpKTmmc8+EP53GxuRg6TCwe5eTYWrQM4AmQSG5lb3OBskhg==;EndpointSuffix=core.windows.net'

Примечание.

В параметре @working_directory используйте только символы обратной черты (\). Использование символа прямой косой черты (/) может привести к ошибке при подключении к общей папке.

Подключитесь к управляемому экземпляру sql-mi-publisher.
Откройте окно Новый запрос и выполните следующий код Transact-SQL, чтобы зарегистрировать распространитель в издателе.
```
Use MASTER
EXEC sys.sp_adddistributor @distributor = 'sql-mi-distributor.b6bf57.database.windows.net', @password = '<distributor_admin_password>'
```

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

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

Запустите SQL Server Management Studio на SQL Server.
Подключитесь к управляемому экземпляру sql-mi-publisher.
В обозревателе объектов разверните узел Репликация и щелкните папку Локальная публикация. Щелкните Создать публикацию.
Щелкните Далее, чтобы перейти со страницы приветствия.
На странице База данных публикации выберите созданную ранее базу данных ReplTutorial. Выберите Далее.
На странице Тип публикации выберите Публикация транзакций. Выберите Далее.
На странице Статьи установите флажок Таблицы. Выберите Далее.
На странице Фильтрация строк таблицы щелкните Далее, не добавляя новых фильтров.
На странице Агент моментальных снимков установите флажок Создать моментальный снимок немедленно и обеспечить доступ к нему для инициализации подписок. Выберите Далее.
На странице Безопасность агентов выберите Параметры безопасности.... Укажите учетные данные SQL Server для агента моментальных снимков и подключения к издателю. Щелкните OK, чтобы закрыть страницу Безопасность агента моментальных снимков. Выберите Далее.
На странице Действия мастера щелкните Создать публикацию и (необязательно) выберите Сформировать файл скрипта с этапами создания публикации, если вам нужен скрипт для использования в будущем.
На странице Завершение работы мастера присвойте публикации имя ReplTest и щелкните Далее, чтобы создать эту публикацию.
Создав публикацию, обновите узел Репликация в обозревателе объектов и разверните узел Локальные публикации, чтобы увидеть новую публикацию.

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

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

Запустите SQL Server Management Studio на SQL Server.
Подключитесь к управляемому экземпляру sql-mi-publisher.
Откройте окно Новый запрос и выполните следующий код Transact-SQL, чтобы добавить подписку и агент распространителя. Включите имя DNS в имя подписчика.

use [ReplTutorial]
exec sp_addsubscription
@publication = N'ReplTest',
@subscriber = N'sql-vm-sub.repldns.com', -- include the DNS configured in the private DNS zone
@destination_db = N'ReplSub',
@subscription_type = N'Push',
@sync_type = N'automatic',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0

exec sp_addpushsubscription_agent
@publication = N'ReplTest',
@subscriber = N'sql-vm-sub.repldns.com', -- include the DNS configured in the private DNS zone
@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

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

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

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

Use ReplSub
select * from dbo.ReplTest

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

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

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

На портале Azure перейдите к используемой группе ресурсов.
Выберите один или несколько управляемых экземпляров и щелкните Удалить. Введите yes в текстовое поле, чтобы подтвердить удаление ресурса, а затем щелкните Удалить. Этот процесс некоторое время выполняется в фоновом режиме, и до его завершения вы не сможете удалить виртуальный кластер и другие зависимые ресурсы. Вы можете проверить состояние процесса удаления на вкладке Действие, чтобы убедиться в удалении управляемого экземпляра.
После удаления управляемого экземпляра удалите виртуальный кластер, выбрав его в группе ресурсов и щелкнув Удалить. Введите yes в текстовое поле, чтобы подтвердить удаление ресурса, а затем щелкните Удалить.
Удалите все остальные ресурсы. Введите yes в текстовое поле, чтобы подтвердить удаление ресурса, а затем щелкните Удалить.
Удалите группу ресурсов, выбрав Удалить группу ресурсов, введя в текстовом поле имя группы ресурсов myResourceGroup и щелкнув Удалить.

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

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

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

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

Не удалось подключиться к службе хранилища Azure

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-SUB 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 или более поздней версии, чтобы исключить эту причину.

Следующие шаги

Включение функций безопасности

Полный список способов защиты базы данных приведен в статье Что такое Управляемый экземпляр SQL Azure?. Рассматриваются следующие средства безопасности:

Возможности Управляемого экземпляра SQL

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

Возможности Управляемого экземпляра SQL

Поделиться через

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

Необходимые компоненты

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

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

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

Настройка пиринга виртуальных сетей

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

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

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

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

Создание учетной записи службы хранилища Azure

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

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

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

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

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

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

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

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

Не удалось подключиться к службе хранилища Azure

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

Следующие шаги

Включение функций безопасности

Возможности Управляемого экземпляра SQL

Обратная связь

Дополнительные ресурсы

Поделиться через

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

Необходимые компоненты

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

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

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

Настройка пиринга виртуальных сетей

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

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

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

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

Создание учетной записи службы хранилища Azure

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

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

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

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

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

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

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

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

Не удалось подключиться к службе хранилища Azure

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

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

Следующие шаги

Включение функций безопасности

Возможности Управляемого экземпляра SQL

Обратная связь

Дополнительные ресурсы