Configurer la réplication avec T-SQL

S’applique à : SQL Server - Linux

Dans ce tutoriel, vous allez configurer la réplication de capture instantanée SQL Server sur Linux avec deux instances de SQL Server à l’aide de Transact-SQL (T-SQL). Le serveur de publication et le serveur de distribution sont identiques et l’abonné se trouve sur une instance distincte.

  • Activer les agents de réplication SQL Server sur Linux
  • Créer un exemple de base de données
  • Configurer le dossier de captures instantanées pour l’accès aux agents SQL Server
  • Configurer le serveur de distribution
  • Configurer le serveur de publication
  • Configurer une publication et des articles
  • Configurer l’abonné
  • Exécuter des tâches de réplication

Toutes les configurations de réplication peuvent être configurées avec des procédures stockées de réplication.

Prérequis

Pour terminer ce tutoriel, vous avez besoin de ce qui suit :

Procédure détaillée

  1. Activez les agents de réplication SQL Server sur Linux. Sur les deux machines hôtes, exécutez les commandes suivantes dans le terminal.

    sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
    sudo systemctl restart mssql-server
    
  2. Créez l’exemple de base de données et de table. Sur le serveur de publication, créez un exemple de base de données et de table qui servira d’articles pour une publication.

    CREATE DATABASE Sales;
    GO
    
    USE [Sales];
    GO
    
    CREATE TABLE Customer (
        [CustomerID] INT NOT NULL,
        [SalesAmount] DECIMAL NOT NULL
    );
    GO
    
    INSERT INTO Customer (CustomerID, SalesAmount)
    VALUES (1, 100), (2, 200), (3, 300);
    GO
    

    Sur l’autre instance de SQL Server, l’abonné, créez la base de données pour recevoir les articles.

    CREATE DATABASE Sales;
    GO
    
  3. Créez le dossier de captures instantanées pour SQL Server Agents à lire/écrire sur le serveur de distribution, créez le dossier de captures instantanées et octroyez l’accès à l’utilisateur « mssql ».

    sudo mkdir /var/opt/mssql/data/ReplData/
    sudo chown mssql /var/opt/mssql/data/ReplData/
    sudo chgrp mssql /var/opt/mssql/data/ReplData/
    
  4. Configurez le serveur de distribution. Dans cet exemple, le serveur de publication est également le serveur de distribution. Exécutez les commandes suivantes sur le serveur de publication pour configurer également l’instance pour la distribution.

    DECLARE @distributor AS SYSNAME;
    DECLARE @distributorlogin AS SYSNAME;
    DECLARE @distributorpassword AS SYSNAME;
    
    -- Specify the distributor name. Use 'hostname' command on in terminal to find the hostname
    SET @distributor = N'<distributor instance name>'; --in this example, it will be the name of the publisher
    SET @distributorlogin = N'<distributor login>';
    SET @distributorpassword = N'<distributor password>';
    
    -- Specify the distribution database.
    USE master
    
    EXEC sp_adddistributor @distributor = @distributor -- this should be the hostname
    
    -- Log into distributor and create Distribution Database. In this example, our publisher and distributor is on the same host
    EXEC sp_adddistributiondb @database = N'distribution',
        @log_file_size = 2,
        @deletebatchsize_xact = 5000,
        @deletebatchsize_cmd = 2000,
        @security_mode = 0,
        @login = @distributorlogin,
        @password = @distributorpassword;
    GO
    
    DECLARE @snapshotdirectory AS NVARCHAR(500);
    SET @snapshotdirectory = N'/var/opt/mssql/data/ReplData/';
    
    -- Log into distributor and create Distribution Database. In this example, our publisher and distributor is on the same host
    USE [distribution];
    GO
    
    IF (NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'UIProperties' AND type = 'U'))
        CREATE TABLE UIProperties (id INT);
    
    IF (EXISTS (SELECT * FROM::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', NULL, NULL)))
        EXEC sp_updateextendedproperty N'SnapshotFolder',
            @snapshotdirectory,
            'user',
            dbo,
            'table',
            'UIProperties';
    ELSE
        EXEC sp_addextendedproperty N'SnapshotFolder',
            @snapshotdirectory,
            'user',
            dbo,
            'table',
            'UIProperties';
    GO
    
  5. Configurez le serveur de publication. Exécutez les commandes T-SQL suivantes sur l’éditeur.

    DECLARE @publisher AS SYSNAME;
    DECLARE @distributorlogin AS SYSNAME;
    DECLARE @distributorpassword AS SYSNAME;
    
    -- Specify the distributor name. Use 'hostname' command on in terminal to find the hostname
    SET @publisher = N'<instance name>';
    SET @distributorlogin = N'<distributor login>';
    SET @distributorpassword = N'<distributor password>';
    
    -- Specify the distribution database.
    -- Adding the distribution publishers
    EXEC sp_adddistpublisher @publisher = @publisher,
        @distribution_db = N'distribution',
        @security_mode = 0,
        @login = @distributorlogin,
        @password = @distributorpassword,
        @working_directory = N'/var/opt/mssql/data/ReplData',
        @trusted = N'false',
        @thirdparty_flag = 0,
        @publisher_type = N'MSSQLSERVER';
    GO
    
  6. Configurez un travail de publication. Exécutez les commandes T-SQL suivantes sur l’éditeur.

    DECLARE @replicationdb AS SYSNAME;
    DECLARE @publisherlogin AS SYSNAME;
    DECLARE @publisherpassword AS SYSNAME;
    
    SET @replicationdb = N'Sales';
    SET @publisherlogin = N'<Publisher login>';
    SET @publisherpassword = N'<Publisher Password>';
    
    USE [Sales];
    GO
    
    EXEC sp_replicationdboption @dbname = N'Sales',
        @optname = N'publish',
        @value = N'true';
    
    -- Add the snapshot publication
    EXEC sp_addpublication @publication = N'SnapshotRepl',
        @description = N'Snapshot publication of database ''Sales'' from Publisher ''<PUBLISHER HOSTNAME>''.',
        @retention = 0,
        @allow_push = N'true',
        @repl_freq = N'snapshot',
        @status = N'active',
        @independent_agent = N'true';
    
    EXEC sp_addpublication_snapshot @publication = N'SnapshotRepl',
        @frequency_type = 1,
        @frequency_interval = 1,
        @frequency_relative_interval = 1,
        @frequency_recurrence_factor = 0,
        @frequency_subday = 8,
        @frequency_subday_interval = 1,
        @active_start_time_of_day = 0,
        @active_end_time_of_day = 235959,
        @active_start_date = 0,
        @active_end_date = 0,
        @publisher_security_mode = 0,
        @publisher_login = @publisherlogin,
        @publisher_password = @publisherpassword;
    
  7. Créer des articles à partir de la table des ventes.

    Exécutez les commandes T-SQL suivantes sur l’éditeur.

    USE [Sales];
    GO
    
    EXEC sp_addarticle @publication = N'SnapshotRepl',
        @article = N'customer',
        @source_owner = N'dbo',
        @source_object = N'customer',
        @type = N'logbased',
        @description = NULL,
        @creation_script = NULL,
        @pre_creation_cmd = N'drop',
        @schema_option = 0x000000000803509D,
        @identityrangemanagementoption = N'manual',
        @destination_table = N'customer',
        @destination_owner = N'dbo',
        @vertical_partition = N'false';
    
  8. Configurez l’abonnement. Exécutez les commandes T-SQL suivantes sur l’éditeur.

    DECLARE @subscriber AS SYSNAME;
    DECLARE @subscriber_db AS SYSNAME;
    DECLARE @subscriberLogin AS SYSNAME;
    DECLARE @subscriberPassword AS SYSNAME;
    
    SET @subscriber = N'<Instance Name>'; -- for example, MSSQLSERVER
    SET @subscriber_db = N'Sales';
    SET @subscriberLogin = N'<Subscriber Login>';
    SET @subscriberPassword = N'<Subscriber Password>';
    
    USE [Sales];
    GO
    
    EXEC sp_addsubscription @publication = N'SnapshotRepl',
        @subscriber = @subscriber,
        @destination_db = @subscriber_db,
        @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'SnapshotRepl',
        @subscriber = @subscriber,
        @subscriber_db = @subscriber_db,
        @subscriber_security_mode = 0,
        @subscriber_login = @subscriberLogin,
        @subscriber_password = @subscriberPassword,
        @frequency_type = 1,
        @frequency_interval = 0,
        @frequency_relative_interval = 0,
        @frequency_recurrence_factor = 0,
        @frequency_subday = 0,
        @frequency_subday_interval = 0,
        @active_start_time_of_day = 0,
        @active_end_time_of_day = 0,
        @active_start_date = 0,
        @active_end_date = 19950101;
    GO
    
  9. Exécutez les travaux de l’agent de réplication. Exécutez la requête suivante pour obtenir la liste des tâches :

    SELECT name, date_modified
    FROM msdb.dbo.sysjobs
    ORDER BY date_modified DESC;
    

    Exécutez la tâche de réplication de capture instantanée pour générer la capture instantanée :

    USE msdb;
    GO
    
    --generate snapshot of publications, for example
    EXEC dbo.sp_start_job N'PUBLISHER-PUBLICATION-SnapshotRepl-1';
    GO
    

    Exécutez le travail de réplication de capture instantanée pour générer la capture instantanée :

    USE msdb;
    GO
    --distribute the publication to subscriber, for example
    EXEC dbo.sp_start_job N'DISTRIBUTOR-PUBLICATION-SnapshotRepl-SUBSCRIBER';
    GO
    
  10. Connectez l’abonné et interrogez les données répliquées.

    Sur l’abonné, vérifiez que la réplication fonctionne en exécutant la requête suivante :

    SELECT * from [Sales].[dbo].[Customer];
    

Dans ce tutoriel, vous avez configuré la réplication de capture instantanée SQL Server sur Linux avec deux instances de SQL Server à l’aide de T-SQL.

  • Activer les agents de réplication SQL Server sur Linux
  • Créer un exemple de base de données
  • Configurer le dossier de captures instantanées pour l’accès aux agents SQL Server
  • Configurer le serveur de distribution
  • Configurer le serveur de publication
  • Configurer une publication et des articles
  • Configurer l’abonné
  • Exécuter des tâches de réplication