Activer les abonnements renouvelables de réplication transactionnelle dans SQL Server 2019

Date de création de la page : dimanche 29 mai 2022

Introduction et précautions

Cet article est destiné à la réplication dans SQL Server 2019. L’une des fonctions est que « l’abonnement renouvelable » est activé.

Cette fonctionnalité était activée par défaut dans les versions précédentes de SQL Server, mais est désactivée depuis (probablement) 2017.

Les futures versions de SQL Server peuvent supprimer cette fonctionnalité. À l’exception de la mise à niveau d’un environnement existant, nous vous recommandons de le remplacer par une nouvelle construction par une réplication de fusion, etc.

référence

environnement

Utilisez deux serveurs, un serveur et deux serveurs SQL Server. Windows Server est dans un état où aucun paramètre supplémentaire n’a été défini immédiatement après l’installation.

1ère unité (côté publication)

Windows Server 2019 Édition Standard
SQL Server 2019 Developer Edition (Édition Standard ou supérieure requise)
SQL Server Management Studio 18.9.1

Deuxième unité (côté abonné)

Windows Server 2019 Édition Standard
SQL Server 2019 Édition Express
SQL Server Management Studio 18.9.1

but

Même si les enregistrements de table dans les bases de données du premier ou du deuxième serveur sont mis à jour Assurez-vous que les enregistrements de table sur les serveurs des autres sont synchronisés.

Soit dit en passant, un du côté de la publication est bien, mais le côté abonné peut avoir n’importe quel nombre d’unités. Il fonctionne également avec plusieurs instances de SQL Server sur un seul serveur.

Installation de SQL Server

L’installation de SQL Server n’est pas la partie principale de ces conseils, donc je n’entrerai pas dans les détails. Seuls ceux qui concernent la réplication sont répertoriés.

L’installation du côté de la publication nécessite Standard Edition ou une version ultérieure. Ce conseil installe Developer Edition en tant qu’environnement de test.

Les abonnés sont d’accord avec Express Edition. Bien sûr, les autres éditions sont correctes, en fonction de la configuration de la base de données.

Pour Sélection des fonctionnalités, sélectionnez Service moteur de base de données et Réplication SQL Server. Il en va de même pour le côté publication et le côté abonné.

La configuration de l’instance est par défaut à la fois côté publication et côté abonné. Même si vous modifiez le nom de l’instance, la différence réside dans le fait que vous spécifiez ou non le nom de l’instance dans la spécification du serveur ultérieurement.

Côté publication, configurez l’Agent SQL Server pour qu’il démarre automatiquement. Vous pourrez modifier ce paramètre ultérieurement.

Cette astuce authentifie la réplication avec le compte SA SQL Server. Par conséquent, activez l’authentification SQL Server. Si vous ne souhaitez pas utiliser le compte sa pour des raisons de sécurité, remplacez la partie authentification SQL Server de l’explication ultérieurement par l’authentification Windows ou un autre compte.

Définissez ce paramètre de la même manière sur les deux serveurs.

Installation de SQL Server Management Studio

Installez SQL Server Management Studio que vous avez téléchargé. Étant donné que l’écran d’installation est uniquement à installer, l’explication est omise.

Redémarrez Windows si vous êtes invité à redémarrer après l’installation. Installez également tous les correctifs dans Windows Update.

Créer une base de données et des tables côté publication

Si vous créez une table côté publication, celle-ci réplique automatiquement la table ou l’enregistrement.

La méthode de création est la même que la procédure normale, donc je vais omettre les détails.

Vous créez une base de données nommée Base de données de publication dans SQL Server Management Studio.

Créez une table nommée « Utilisateurs » et enregistrez les colonnes comme suit : Tout ce que vous avez à faire est de vérifier la synchronisation des enregistrements, afin que le nom, le nombre et le contenu des colonnes de la table puissent être n’importe quoi. Toutefois, seule la clé primaire est requise.

Ajoutez quelques enregistrements pour vous assurer qu’ils sont synchronisés après la création de la réplication.

Configuration des connexions à distance

Configurez-les pour autoriser l’accès à distance aux bases de données des serveurs des autres. Les détails sont résumés dans les conseils suivants.

Configuration côté serveur pour la connexion à SQL Server avec l’authentification SQL Server

Définition des autorisations de dossier

Sur le serveur côté publication, ouvrez les propriétés du dossier suivant : Le chemin d’accès au dossier dépend de la version de SQL Server et du nom de l’instance.

C:\Program Files\Microsoft SQL Server\MSSQL15. MSSQLSERVER\MSSQL\ReplData

Dans l’onglet Sécurité, ajoutez le compte « SERVICE » et définissez-le sur contrôle total.

Configuration de la distribution

Cette fois, la distribution est créée sur le même serveur que la publication.

Démarrez SQL Server Management Studio, cliquez avec le bouton droit sur Réplication, puis sélectionnez Configurer la distribution.

Cliquez sur Suivant.

Cochez la case « Créez votre propre serveur > votre propre distributeur » <.

Après cela, cliquez sur « Suivant » tel quel pour le compléter.

Création d’une publication

À l’origine, il est créé à partir de l’Assistant, comme illustré dans la figure suivante, Dans SQL Server 2019, la création à partir d’un Assistant n’active pas les « abonnements renouvelables » et n’autorise pas la synchronisation des enregistrements entre les tables.

Par conséquent, nous utilisons T-SQL pour créer la publication ici.

SQL est utilisé ci-dessous pour créer une publication avec l’authentification SQL Server : Veuillez modifier le mot de passe en fonction de vos paramètres.

-- トランザクションパブリケーションを追加するための宣言
DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publicationDB = N'パブリケーションDB'; 
SET @publication = N'パブリケーション'; 
SET @login = 'sa'; 
SET @password = 'saパスワード'; 

USE [パブリケーションDB]

-- パブリケーションデータベースでトランザクションレプリケーションを有効にする。
EXEC sp_replicationdboption 
    @dbname=@publicationDB, 
    @optname=N'publish',
    @value = N'true';

-- sp_addlogreader_agent を実行して、エージェントジョブを作成します。
EXEC sp_addlogreader_agent 
    @publisher_login = @login, 
    @publisher_password = @password,
    @publisher_security_mode = 0;

-- 即時更新、キュー更新、およびプルサブスクリプションをサポートするトランザクションパブリケーションを作成します。
EXEC sp_addpublication 
    @publication = @publication, 
    @status = N'active',
    @allow_sync_tran = N'true', 
    @allow_queued_tran = N'true',
    @allow_pull = N'true',
    @independent_agent = N'true',
    -- 関連するデフォルトのプロパティを明示的に宣言する
    @conflict_policy = N'pub wins';

-- デフォルトのスケジュールを使用して、パブリケーションの新しいスナップショットジョブを作成します。
EXEC sp_addpublication_snapshot 
    @publication = @publication, 
    @publisher_login = @login, 
    @publisher_password = @password,
    @publisher_security_mode = 0;
GO

Lorsque vous utilisez l’authentification Windows, modifiez les parties « sp_addlogreader_agent » et « sp_addpublication_snapshot » comme suit.

Authentification SQL Server

    @publisher_login = @login, 
    @publisher_password = @password,
    @publisher_security_mode = 0;

Authentification Windows

    @job_login = @login, 
    @job_password = @password,
    @publisher_security_mode = 1;

S’il est créé avec succès, vous verrez ce qui suit :

Ouvrez également les propriétés de la publication créée et assurez-vous que « Abonnement renouvelable » est activé.

Spécification des articles

À l’origine, si vous le créez avec un assistant, vous spécifierez l’article en même temps, Étant donné que le script ci-dessus n’inclut pas le traitement des articles, il est spécifié à partir de la propriété.

Sélectionnez « Articles » dans le menu de gauche, cochez le tableau que vous souhaitez synchroniser et cliquez sur le bouton OK.

Un msrepl_tran_version est ajouté aux colonnes de la table à synchroniser et un déclencheur de table est ajouté en même temps.

Créer un abonnement

Cliquez avec le bouton droit sur la composition que vous venez de créer et sélectionnez Nouvel abonnement.

Cliquez sur Suivant.

Sélectionnez la composition que vous avez créée.

Cochez exécuter tous les agents sur le serveur de distribution <nom du serveur>.

Cliquez sur le bouton Ajouter un abonné SQL Server.

Spécifie le serveur (+ nom de l’instance) à inscrire en tant qu’abonné.

Si vous n’avez pas de base de données d’abonnés, créez-en une nouvelle. S’il existe déjà, sélectionnez-le car l’élément de sélection s’affiche.

Après avoir spécifié la base de données, cliquez sur Suivant.

Si vous avez plus d’un abonné, vous pouvez en inscrire autant que vous le souhaitez ici.

Cliquez sur le bouton « ... ».

Ici, l’agent s’exécute sous le compte sa, mais veuillez le définir en fonction de votre environnement.

Sélectionnez Exécuter en continu.

Le paramètre « Abonnement renouvelable » doit être opérationnel.

Dans le cas de « commit changes at the same time », il est synchronisé immédiatement, mais tous les serveurs doivent être en cours d’exécution.

« Modifications de la file d’attente et validation si possible » accumule les modifications dans la file d’attente, de sorte qu’elles sont validées lorsque le serveur est en cours d’exécution, même si le serveur n’est pas en cours d’exécution. Toutefois, la synchronisation est lente.