Tutoriel : Configurer la réplication entre deux instances managées SQL

S’applique à :Azure SQL Managed Instance

La réplication transactionnelle vous permet de répliquer des données d’une base de données vers une autre hébergée sur SQL Server ou Azure SQL Managed Instance. SQL Managed Instance peut être un éditeur, un serveur de distribution ou un abonné dans la topologie de réplication. Consultez Configurations de réplication transactionnelle pour connaître les configurations disponibles.

Dans ce tutoriel, vous allez apprendre à :

Ce didacticiel est destiné à un public expérimenté et suppose que l’utilisateur est familiarisé avec le déploiement et la connexion à des instances managées SQL et à des machines virtuelles SQL Server dans Azure.

Spécifications

La configuration de SQL Managed Instance pour fonctionner en tant que base de données du serveur de publication et/ou base de données du serveur de distribution implique que les conditions suivantes soient respectées :

• L’instance managée SQL de l’éditeur se trouve sur le même réseau virtuel que le serveur de distribution et l’abonné, ou les passerelles VNet peering ou VPN ont été configurées entre les réseaux virtuels des trois entités.
• La connectivité doit utiliser l’authentification SQL entre les participants de la réplication.
• Il existe un partage de compte de stockage Azure pour le répertoire de travail de réplication.
• Le port 445 (tcp sortant) est ouvert dans les règles de sécurité du groupe de sécurité réseau pour les instances managées SQL afin d’accéder au partage de fichiers Azure. Si vous rencontrez l’erreur failed to connect to azure storage <storage account name> with os error 53, vous devez ajouter une règle de trafic sortant au groupe de sécurité réseau du sous-réseau SQL Managed Instance approprié.

1 - Créer un groupe de ressources

Utilisez le Portail Azure pour créer un groupe de ressources avec le nom SQLMI-Repl.

2 - Créer des instances managées SQL

Utilisez le portail Azure pour créer deux instances managées SQL sur le même réseau virtuel et le même sous-réseau. Par exemple, nommez les deux instances managées SQL :

• sql-mi-publisher (avec certains caractères pour la randomisation)
• sql-mi-subscriber (avec certains caractères pour la randomisation)

Vous devez également configurer une machine virtuelle Azure pour vous connecter à vos instances managées SQL.

3 - Créer un compte de stockage Azure

Créez un compte de stockage Azure pour le répertoire de travail, puis créez un partage de fichiers au sein du compte de stockage.

Copiez le chemin d’accès au partage de fichier au format : \\storage-account-name.file.core.windows.net\file-share-name

Exemple : \\replstorage.file.core.windows.net\replshare

Copiez les clés d’accès de stockage au format : DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=****;EndpointSuffix=core.windows.net

Exemple : DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=123456789aBcDeFgHiJkLmNoPqRsTuVwXyZ==;EndpointSuffix=core.windows.net

Pour plus d’informations, consultez Gérer les clés d’accès au compte de stockage.

4 - Créer une base de données du serveur de publication

Connectez-vous à votre instance managée SQL de l’éditeur (sql-mi-publisher) à l’aide de SQL Server Management Studio, puis exécutez le code Transact-SQL (T-SQL) suivant pour créer votre base de données d’éditeur :

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 - Créer une base de données de l’abonné

Connectez-vous à votre instance managée SQL d’abonné (sql-mi-subscriber) à l’aide de SQL Server Management Studio et exécutez le code T-SQL suivant pour créer votre base de données d’abonné vide :

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 - Configurer la distribution

Connectez-vous à votre instance managée SQL publisher (sql-mi-publisher) à l’aide de SQL Server Management Studio et exécutez le code T-SQL suivant pour configurer votre base de données de distribution.

USE [master]
GO

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

7 - Configurer la base de données du serveur de publication pour utiliser la base de données du serveur de distribution

Sur votre instance managée SQL du serveur de publication (sql-mi-publisher), remplacez l’exécution de la requête en mode SQLCMD et exécutez le code suivant pour inscrire le nouveau serveur de distribution auprès de votre serveur de publication.

: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

Ce script configure un éditeur local sur l’instance managée SQL, ajoute un serveur lié et crée un ensemble de travaux pour l’agent SQL Server.

8 - Créer la publication et la base de données de l’abonné

En utilisant le mode SQLCMD, exécutez le script T-SQL suivant pour activer la réplication pour votre base de données et configurer la réplication entre la base de données du serveur de publication, la base de données du serveur de distribution et la base de données de l’abonné.

-- 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 - Modifier les paramètres de l’agent

Azure SQL Managed Instance rencontre actuellement des problèmes de back-end concernant la connectivité avec les agents de réplication. Ce problème est en cours de résolution. La solution de contournement consiste à augmenter la valeur du délai d’expiration de connexion pour les agents de réplication.

Exécutez la commande T-SQL suivante sur le serveur de publication pour allonger le délai d’expiration de connexion :

-- 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 %'

Exécutez à nouveau la commande T-SQL suivante si nécessaire pour définir le délai d’expiration de connexion sur la valeur par défaut :

-- 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 %'

Pour appliquer ces modifications, redémarrez les trois agents.

10 - Tester la réplication

Une fois la réplication configurée, vous pouvez la tester en insérant de nouveaux éléments sur l’éditeur et en regardant les modifications propagées à l’abonné.

Exécutez l’extrait de code T-SQL suivant pour afficher les lignes sur la base de données de l’abonné :

select * from dbo.ReplTest

Exécutez l’extrait de code T-SQL suivant pour insérer d’autres lignes sur l’éditeur, puis vérifiez à nouveau les lignes sur l’abonné.

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

Nettoyer les ressources

Pour supprimer la publication, exécutez la commande T-SQL suivante :

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

Pour supprimer l’option de réplication de la base de données, exécutez la commande T-SQL suivante :

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

Pour désactiver la publication et la distribution, exécutez la commande T-SQL suivante :

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

Vous pouvez nettoyer vos ressources Azure en supprimant les ressources SQL Managed Instance du groupe de ressources, puis en supprimant le groupe de ressources SQLMI-Repl.

Contenu connexe

• Réplication transactionnelle avec Azure SQL Managed Instance
• Tutoriel : Configurer la réplication transactionnelle entre Azure SQL Managed Instance et SQL Server