Configurer la réplication avec T-SQL

Article
11/16/2023

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 suivre ce didacticiel, vous aurez besoin des éléments suivants :

Deux instances de SQL Server avec la dernière version de SQL Server sur Linux
Un outil permettant d’émettre des requêtes T-SQL pour configurer la réplication, telle que SQLCMD ou SSMS

Consultez Utiliser SQL Server Management Studio sur Windows pour la gestion de SQL Server sur Linux.

Remarque

SQL Server 2017 (14.x) (CU18) et les versions ultérieures prennent en charge la réplication SQL Server pour les instances de SQL Server sur Linux.

Procédure détaillée

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
```
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
```
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/
```

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

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

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;

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

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

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

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

Configurer la réplication avec T-SQL

Prérequis

Procédure détaillée

Commentaires

Commentaires

Ressources supplémentaires

Configurer la réplication avec T-SQL

Prérequis

Procédure détaillée

Contenu connexe

Commentaires

Commentaires

Ressources supplémentaires