Réplication transactionnelle avec Azure SQL Managed Instance

S’applique à : Azure SQL Managed Instance

La réplication transactionnelle est une fonctionnalité d’Azure SQL Managed Instance et de SQL Server qui vous permet de répliquer les données d’une table dans Azure SQL Managed Instance ou d’une instance SQL Server dans des tables placées dans des bases de données distantes. Cette fonctionnalité vous permet de synchroniser plusieurs tables dans différentes bases de données.

Vue d’ensemble

Vous pouvez utiliser la réplication transactionnelle pour transmettre les modifications apportées à une instance gérée Azure SQL à :

• Une base de données SQL Server (locale ou sur une machine virtuelle Azure)
• Une base de données dans Azure SQL Database
• Une base de données d’instance dans Azure SQL Managed Instance

Components

Les principaux composants de la réplication transactionnelle sont la base de données du serveur de publication, la base de données du serveur de distribution et l’abonné, comme indiqué dans l’image suivante :

La base de données du serveur de publication publie les changements apportés à certaines tables (articles) en envoyant les mises à jour à la base de données du serveur de distribution. La base de données du serveur de publication peut être une instance gérée Azure SQL ou une instance SQL Server.

La base de données du serveur de distribution collecte les changements apportés aux articles à partir d’une base de données du serveur de publication et les distribue aux Abonnés. Le Distributeur peut être une instance gérée par Azure SQL ou une instance de SQL Server (n'importe quelle version pourvu qu'elle soit égale ou supérieure à la version de l'Éditeur).

L’abonné reçoit les modifications apportées sur la base de données du serveur de publication. Une instance SQL Server et une instance gérée Azure SQL peuvent être des abonnés d’envoi (push) et d’extraction (pull), bien qu’un abonnement par extraction ne soit pas pris en charge quand la base de données du serveur de distribution est une instance gérée Azure SQL et que l’abonné n’en est pas une. Une base de données dans Azure SQL Database peut uniquement être un abonné par envoi.

Azure SQL Managed Instance peut prendre en charge le fait d’être un abonné des versions suivantes de SQL Server :

• SQL Server 2016 et versions ultérieures
• SQL Server 2014 RTM CU10 (12.0.4427.24) ou SP1 CU3 (12.0.2556.4)
• SQL Server 2012 SP2 CU8 (11.0.5634.1), SP3 (11.0.6020.0) ou SP4 (11.0.7001.0)

Types de réplication

Il existe différents types de réplications :

Matrice de prise en charge

La matrice de prise en charge de la réplication transactionnelle pour Azure SQL Managed Instance est identique à celle de SQL Server.

Quand l’utiliser

La réplication transactionnelle est utile dans les scénarios suivants :

• Publier les changements apportés dans une ou plusieurs tables d’une base de données et les distribuer à une ou plusieurs bases de données dans une instance de SQL Server ou Azure SQL Database abonnées aux changements.
• Maintenir plusieurs bases de données distribuées dans un état synchronisé.
• Migrer des bases de données d’une instance de SQL Server ou d’une instance managée Azure SQL vers une autre base de données en publiant les modifications en continu.

Comparaison entre Data Sync et la réplication transactionnelle

Configurations courantes

En règle générale, le serveur de publication et le serveur de distribution doivent se trouver dans le cloud ou en local. Les configurations suivantes sont prises en charge :

Base de données du serveur de publication avec une base de données du serveur de distribution locale sur SQL Managed Instance

La base de données du serveur de publication et la base de données du serveur de distribution sont configurées dans une même instance gérée SQL, et les changements sont distribués à une autre instance gérée SQL, base de données SQL ou instance SQL Server.

Base de données du serveur de publication avec base de données du serveur de distribution distante sur SQL Managed Instance

Dans cette configuration, une instance gérée SQL publie les changements dans une base de données du serveur de distribution placée sur une autre instance gérée SQL qui peut servir plusieurs instances gérées SQL sources et distribuer les changements à une ou plusieurs cibles sur Azure SQL Database, Azure SQL Managed Instance ou SQL Server.

Le serveur de publication et le serveur de distribution sont configurés sur deux instances managées. Il existe des contraintes avec cette configuration :

• Les deux instances managées sont sur le même réseau virtuel.
• Les deux instances managées se trouvent au même emplacement.

Base de données du serveur de publication/distribution locale avec abonné distant

Dans cette configuration, une base de données dans Azure SQL Database ou Azure SQL Managed Instance est un abonné. Cette configuration prend en charge la migration de données locales vers Azure. Si un abonné est une base de données dans Azure SQL Database, il doit être en mode push.

Spécifications

• Utiliser l’authentification SQL pour la connectivité entre les participants de la réplication
• Utiliser un partage de compte Stockage Azure pour le répertoire de travail utilisé par la réplication
• Ouvrir le port TCP 445 sortant dans les règles de sécurité de sous-réseau pour accéder au partage de fichiers Azure
• Ouvrez le port TCP 1433 sortant quand la base de données du serveur de publication/distribution est une instance gérée SQL et que l’abonné n’en est pas une. Il vous faudra peut-être aussi modifier la règle de sécurité de trafic sortant du groupe de sécurité réseau de l’instance gérée SQL pour allow_linkedserver_outbound pour l’Étiquette Service de destination du port 1433 en remplaçant virtualnetwork par internet.
• Placer les bases de données du serveur de publication et de distribution dans le cloud, ou toutes les deux localement
• Configurer le peering VPN entre les réseaux virtuels des participants de réplication si les réseaux virtuels sont différents

Sécurité

Connectez-vous à l’adresse replAgentUser

À des fins de réplication transactionnelle, une SQL Managed Instance a une ou plusieurs connexions précréées avec le nom replAgentUser. Cette connexion est membre du rôle serveur sysadmin et est utilisée par les agents de réplication qui doivent se connecter à une SQL Managed Instance participant à l’installation de la réplication transactionnelle.

Si la réplication transactionnelle n’est pas utilisée, la connexion replAgentUser peut être désactivée. Elle peut être réactivée ultérieurement si vous décidez de commencer à utiliser la réplication transactionnelle.

Limites

La réplication transactionnelle présente certaines limitations spécifiques à Azure SQL Managed Instance. Pour en savoir plus sur ces limitations, consultez cette section.

Les fichiers d’instantanés ne sont pas supprimés du compte de stockage Azure

Azure SQL Managed Instance utilise un compte de stockage Azure configuré par l’utilisateur pour les fichiers d’instantanés utilisés pour la réplication transactionnelle. Contrairement à SQL Server dans l’environnement local, Azure SQL Managed Instance ne supprime pas les fichiers d’instantanés du compte de stockage Azure. Une fois que les fichiers ne sont plus nécessaires, vous devez les supprimer. Pour ce faire, utilisez l’interface du stockage Azure sur le portail Azure, Explorateur Stockage Microsoft Azure, ou des clients de ligne de commande (Azure PowerShell ou CLI) ou l’API REST de gestion du stockage Azure.

Voici un exemple de la façon dont vous pouvez supprimer un fichier et comment supprimer un dossier vide.

az storage file delete-batch --source <file_path> --account-key <account_key> --account-name <account_name>
az storage directory delete --name <directory_name> --share-name <share_name> --account-key <account_key> --account-name <account_name>

Nombre d’agents de distribution en cours d’exécution continue

Le nombre d’agents de distribution configurés pour s’exécuter en continu est limité à 30 sur Azure SQL Managed Instance. Pour avoir plus d’agents de distribution, ils doivent être exécutés à la demande ou avec une planification définie. La planification peut être définie avec une fréquence et une occurrence quotidiennes toutes les 10 secondes (ou plus), par conséquent, même si elle n’est pas continue, vous pouvez toujours avoir un serveur de distribution qui introduit une latence de quelques secondes seulement. Lorsqu’un grand nombre de bases de données du serveur de distribution est nécessaire, il est recommandé d’utiliser une configuration planifiée et non continue.

Avec les groupes de basculement

L’utilisation de la réplication transactionnelle avec des instances qui se trouvent dans un groupe de basculement est prise en charge. Toutefois, si vous configurez la réplication avant d’ajouter votre instance gérée SQL dans un groupe de basculement, la réplication s’interrompt lorsque vous commencez à créer votre groupe de basculement, et le moniteur de réplication affiche l’état Replicated transactions are waiting for the next log backup or for mirroring partner to catch up. La réplication reprend une fois le groupe de basculement créé.

Si une instance gérée SQL de publication ou de distribution se trouve dans un groupe de basculement, l’administrateur de l’instance gérée SQL doit nettoyer toutes les publications de l’ancienne instance principale et les reconfigurer sur la nouvelle instance principale après un basculement. Les activités suivantes sont nécessaires dans ce scénario :

1. Arrêtez tous les travaux de réplication en cours d’exécution sur la base de données, le cas échéant.

2. Supprimez les métadonnées d’abonnement du serveur de publication en exécutant le script suivant sur la base de données du serveur de publication. Remplacez les valeurs de <name of publication> et <name of subscriber> :

   EXEC sp_dropsubscription @publication = '<name of publication>',
       @article = 'all',
       @subscriber = '<name of subscriber>'
   

3. Supprimez les métadonnées d’abonnement de l’abonné. Exécutez le script suivant sur la base de données d’abonnement sur l’instance gérée SQL de l’abonné. Remplacez la valeur de <full DNS of publisher>. Par exemple, example.ac2d23028af5.database.windows.net:

   EXEC sp_subscription_cleanup
      @publisher = N'<full DNS of publisher>',
      @publisher_db = N'<publisher database>',
      @publication = N'<name of publication>';
   

4. Supprimez définitivement tous les objets de réplication du serveur de publication en exécutant le script suivant dans la base de données publiée :

   EXEC sp_removedbreplication;
   

5. Supprimez définitivement l’ancienne base de données du serveur de distribution de l’instance gérée SQL principale d’origine (en cas de basculement vers une ancienne instance principale qui utilisait une base de données du serveur de distribution). Exécutez le script suivant sur la base de données master sur l’ancienne instance gérée SQL de la base de données du serveur de distribution :

   EXEC sp_dropdistributor 1, 1;
   

Si une instance gérée SQL abonnée fait partie d’un groupe de basculement, la publication doit être configurée pour se connecter au point de terminaison de l’écouteur du groupe de basculement pour l’instance gérée SQL abonnée. En cas de basculement, l’action suivante de l’administrateur de l’instance gérée SQL dépend du type de basculement qui s’est produit :

• Pour un basculement sans perte de données, la réplication continue de fonctionner après le basculement.
• Pour un basculement avec perte de données, la réplication fonctionne également. Elle réplique à nouveau les modifications perdues.
• Pour un basculement avec perte de données, mais où la perte de données est en dehors de la période de conservation de la base de données de distribution, l’administrateur de l’instance gérée SQL doit réinitialiser la base de données d’abonnement.

Résoudre des problèmes courants

Journal des transactions et réplication transactionnelle

Dans les circonstances habituelles, le journal de transaction est utilisé pour enregistrer les modifications des données au sein d’une base de données. Les modifications sont enregistrées dans le journal des transactions, ce qui fait augmenter la consommation de stockage des journaux. Il existe également un processus automatique qui permet un tronquage sécurisée du journal des transactions, et ce processus réduit l’espace de stockage utilisé pour le journal. Lorsque la publication pour la réplication transactionnelle est configurée, le tronquage du journal des transactions est empêchée jusqu'à ce que les modifications du journal soient traitées par le travail de lecture du journal. Dans certaines circonstances, le traitement du journal des transactions est effectivement bloqué et cet état peut entraîner le remplissage de l’intégralité du stockage réservé pour le journal des transactions. Lorsqu'il n'y a plus d'espace libre pour le journal des transactions et qu'il n'y a plus d'espace pour que le journal des transactions se développe, le journal des transactions devient complet. Dans cet état, la base de données ne peut plus traiter de charge de travail d’écriture et devient une base de données en lecture seule.

Agent de lecture du journal désactivé

Parfois, la publication de réplication transactionnelle est configurée pour une base de données, mais l’agent de lecture du journal n’est pas configuré pour s’exécuter. Dans ce cas, les modifications s’accumulent dans le journal des transactions et ne sont pas traitées. Cela conduit à une croissance constante du journal transactionnel, et finalement à un journal de transcation complet. L’utilisateur doit s’assurer que le travail de lecteur de journal existe et qu’il est actif. L’alternative consiste à désactiver la réplication transactionnelle, si elle n’est pas nécessaire.

Délais d’expiration des requêtes de l’agent de lecture du journal

Parfois, le travail du lecteur de journal ne peut pas progresser efficacement en raison de délais d’attente de requête répétés. Un moyen de corriger les délais d’expiration des requêtes consiste à augmenter le paramètre de délai d’expiration de requête pour le travail de l’agent de lecture du journal.

L’augmentation du délai d’expiration des requêtes pour le projet de lecteur de journaux d’activité peut être effectuée avec SSMS. Dans l’Explorateur d’objets, sous SQL Server Agent, recherchez le projet que vous souhaitez modifier. Commencez par l’arrêter, puis ouvrez ses propriétés. Recherchez step 2 et modifiez-le. Ajoutez la valeur de commande avec -QueryTimeout <timeout_in_seconds>. Pour la valeur de délai d’expiration de la requête, essayez 21600 ou version ultérieure. Enfin, recommencez le projet.

La taille de stockage des journaux d’activité a atteint la limite maximale de 2 To

Lorsque la taille de stockage du journal des transactions atteint la limite maximale, qui est de 2 To, le journal ne peut pas augmenter physiquement plus que cela. Dans ce cas, la seule prévention disponible consiste à marquer toutes les transactions qui doivent être répliquées comme traitées, afin d’autoriser la troncation du journal des transactions. Cela signifie efficacement que les transactions restantes dans le journal d’activité ne seront pas répliquées et que vous devez réinitialiser la réplication.

Pour effectuer la prévention, vous devez d’abord arrêter l’agent de lecture de journaux d’activité sur le distributeur. Ensuite, vous devez exécuter la procédure stockée sp_repldone avec l’indicateur reset défini sur 1 sur la base de données du serveur de publication pour autoriser la troncation du journal des transactions. Cette commande doit ressembler à EXEC sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1. Après cela, vous devez réinitialiser la réplication.

Étapes suivantes

Pour plus d’informations sur la configuration de la réplication transactionnelle, consultez les tutoriels suivants :

• Configurer la réplication entre un éditeur et un abonné SQL Managed Instance
• Configurer la réplication entre un éditeur SQL Managed Instance, un distributeur SQL Managed Instance et un abonné SQL Server.
• Créer une publication.
• Créer un abonnement par émission de données en utilisant le nom du serveur en tant qu’abonné (par exemple N'azuresqldbdns.database.windows.net) et le nom de la base de données Azure SQL Database comme base de données de destination (par exemple Adventureworks).

Contenu connexe

• Réplication avec une instance managée SQL et un groupe de basculement
• Réplication sur SQL Database
• Réplication sur une instance managée
• Créer une publication
• Créer un abonnement par émission de données
• Types de réplication
• Surveillance (réplication)
• Initialiser un abonnement