Configurare la replica con T-SQL

Articolo
11/16/2023

Si applica a:SQL Server - Linux

In questa esercitazione, viene configurata la replica snapshot di SQL Server in Linux con due istanze di SQL Server tramite Transact-SQL (T-SQL). Il server di pubblicazione e il server di distribuzione si troveranno nella stessa istanza e il sottoscrittore si troverà in un'istanza separata.

Abilitare agenti di replica di SQL Server in Linux
Creare un database di esempio
Configurare la cartella snapshot per l'accesso degli agenti di SQL Server
Configurare il server di distribuzione
Configurare il server di pubblicazione
Configurare la pubblicazione e gli articoli
Configurare il sottoscrittore
Eseguire i processi di replica

Tutte le configurazioni di replica possono essere configurate con stored procedure di replica.

Prerequisiti

Per completare questa esercitazione, sono necessari gli elementi seguenti:

Due istanze di SQL Server con la versione più recente di SQL Server in Linux
Uno strumento per eseguire query T-SQL per la configurazione della replica, ad esempio SQLCMD o SSMS

Vedere Usare SQL Server Management Studio in Windows per gestire SQL Server in Linux.

Nota

SQL Server 2017 (14.x) (CU18) e versioni successive supportano la replica di SQL Server per le istanze di SQL Server in Linux.

Procedura dettagliata

Abilitare gli agenti di replica di SQL Server in Linux. Nel terminale di entrambi i computer host eseguire i comandi seguenti.
```
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo systemctl restart mssql-server
```
Creare il database e la tabella di esempio. Nel server di pubblicazione creare una tabella e un database di esempio che fungeranno da articoli per una pubblicazione.
```
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
```
Nell'altra istanza di SQL Server, il sottoscrittore, creare il database per la ricezione degli articoli.
```
CREATE DATABASE Sales;
GO
```
Creare la cartella snapshot per la lettura/scrittura da parte degli agenti di SQL Server nel server di distribuzione, creare la cartella snapshot e concedere l'accesso all'utente "mssql"
```
sudo mkdir /var/opt/mssql/data/ReplData/
sudo chown mssql /var/opt/mssql/data/ReplData/
sudo chgrp mssql /var/opt/mssql/data/ReplData/
```

Configurare il server di distribuzione. In questo esempio il server di pubblicazione fungerà anche da server di distribuzione. Eseguire i comandi seguenti nel server di pubblicazione per configurare l'istanza anche per la distribuzione.

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

Configurare il server di pubblicazione. Eseguire i comandi T-SQL seguenti nel server di pubblicazione.

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

Configurare il processo di pubblicazione. Eseguire i comandi T-SQL seguenti nel server di pubblicazione.

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;

Creare articoli dalla tabella Sales.

Eseguire i comandi T-SQL seguenti nel server di pubblicazione.

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

Configurare la sottoscrizione. Eseguire i comandi T-SQL seguenti nel server di pubblicazione.

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

Eseguire i processi dell'agente di replica. Eseguire la query seguente per ottenere un elenco di processi:

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

Eseguire il processo di replica snapshot per generare lo snapshot:

USE msdb;
GO

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

Eseguire il processo di replica snapshot per generare lo snapshot:

USE msdb;
GO
--distribute the publication to subscriber, for example
EXEC dbo.sp_start_job N'DISTRIBUTOR-PUBLICATION-SnapshotRepl-SUBSCRIBER';
GO

Connettere il sottoscrittore ed eseguire query sui dati replicati.

Nel sottoscrittore verificare che la replica funzioni eseguendo la query seguente:
```
SELECT * from [Sales].[dbo].[Customer];
```

In questa esercitazione, è stata configurata la replica snapshot di SQL Server in Linux con due istanze di SQL Server tramite Transact-SQL.

Abilitare agenti di replica di SQL Server in Linux
Creare un database di esempio
Configurare la cartella snapshot per l'accesso degli agenti di SQL Server
Configurare il server di distribuzione
Configurare il server di pubblicazione
Configurare la pubblicazione e gli articoli
Configurare il sottoscrittore
Eseguire i processi di replica

Configurare la replica con T-SQL

Prerequisiti

Procedura dettagliata

Commenti e suggerimenti

Commenti e suggerimenti

Risorse aggiuntive

Configurare la replica con T-SQL

Prerequisiti

Procedura dettagliata

Contenuto correlato

Commenti e suggerimenti

Commenti e suggerimenti

Risorse aggiuntive