搭配 T-SQL 設定複寫

發行項
03/11/2024

適用於：SQL Server - Linux

在本教學課程中，於 Linux 上使用 Transact-SQL (T-SQL) 搭配兩個 SQL Server 執行個體來設定 SQL Server 快照式複寫。發行者和散發者將會是相同的執行個體，且訂閱者將會位於個別的執行個體上。

在 Linux 上啟用 SQL Server 複寫代理程式
建立範例資料庫
針對 SQL Server 代理程式存取設定快照集資料夾
設定散發者
設定發行者
設定發行集和發行項
設定訂閱者
執行複寫作業

所有複寫設定都可以搭配複寫預存程序來加以設定。

必要條件

若要完成此課程，您會需要：

具有 Linux 上的 SQL Server 最新版本的兩個 SQL Server 執行個體
用來發出 T-SQL 查詢以設定複寫的工具，例如 SQLCMD 或 SSMS

請參閲使用 Windows 上的 SQL Server Management Studio 來管理 Linux 上的 SQL Server。

注意

SQL Server 2017 (14.x) (CU18) 和更新版本，支援 SQL Server on Linux 執行個體的 SQL Server 複寫。

詳細步驟

在 Linux 上啟用 SQL Server 複寫代理程式。在兩部主機電腦上，於終端機中執行下列命令。
```
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo systemctl restart mssql-server
```

建立範例資料庫與資料表。在發行者上，建立範例資料庫和資料表以作為發行的發行項。

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

在另一個 SQL Server 執行個體 (訂閱者) 上建立資料庫以接收發行項。

CREATE DATABASE Sales;
GO

建立快照集資料夾以供 SQL Server Agent 對其進行讀取/寫入，在散發者上，建立快照集資料夾並將存取權授與 'mssql' 使用者
```
sudo mkdir /var/opt/mssql/data/ReplData/
sudo chown mssql /var/opt/mssql/data/ReplData/
sudo chgrp mssql /var/opt/mssql/data/ReplData/
```

設定散發者。在此範例中，發行者也會是散發者。在發行者上執行下列命令以同時設定散發執行個體。

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

設定發行者。在發行者上執行下列 T-SQL 命令。

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

設定發行集作業。在發行者上執行下列 T-SQL 命令。

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;

從 Sales 資料表建立文章。

在發行者上執行下列 T-SQL 命令。

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

設定訂閱。在發行者上執行下列 T-SQL 命令。

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

執行複寫代理程式作業。執行下列查詢以取得作業清單：

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

執行快照式複寫作業以產生快照集：

USE msdb;
GO

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

執行快照式複寫作業以產生快照集：

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

連線訂閱者並查詢複寫資料。

在訂閱者上，執行下列查詢來檢查複寫是否正常運作：
```
SELECT * from [Sales].[dbo].[Customer];
```

在本教學課程中，您已在 Linux 上使用 T-SQL 搭配兩個 SQL Server 執行個體來設定 SQL Server 快照式複寫。

在 Linux 上啟用 SQL Server 複寫代理程式
建立範例資料庫
針對 SQL Server 代理程式存取設定快照集資料夾
設定散發者
設定發行者
設定發行集和發行項
設定訂閱者
執行複寫作業

搭配 T-SQL 設定複寫

必要條件

詳細步驟

意見反應

意見反應

其他資源

搭配 T-SQL 設定複寫

必要條件

詳細步驟

相關內容

意見反應

意見反應

其他資源