THE VIDEO

THE SYNOPSIS

In this video, we will briefly cover the various forms of replication available in SQL Server, as well as what is in SQL Server on Linux. Then, we will create a simple publication and subscription using T-SQL.

THE COMMANDS

Following are the command line operations I used throughout the video.

Bash script to run on Servers

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo systemctl restart mssql-server

sudo mkdir /var/opt/mssql/data/ReplData/
sudo chown mssql:mssql /var/opt/mssql/data/ReplData/

Create Distribution Database

USE master
GO
DECLARE @distributor SYSNAME = N'SQLAG2';
DECLARE @distributorlogin SYSNAME = N'ReplAdmin';
DECLARE @distributorpassword SYSNAME = N'ASutiablyGoodPassword?';

EXEC sp_adddistributor @distributor = @distributor;

EXEC sp_adddistributiondb @database = N'distribution',
    @log_file_size = 2,
    @deletebatchsize_xact = 5000,
    @deletebatchsize_cmd = 2000,
    @security_mode = 0,
    @login = @distributorlogin,
    @password = @distributorpassword;
GO

USE [distribution];
GO
DECLARE @snapshotdirectory NVARCHAR(500) = N'/var/opt/mssql/data/ReplData/';

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

Create Login

CREATE LOGIN ReplAdmin WITH PASSWORD = N'ASutiablyGoodPassword?'
ALTER SERVER ROLE [sysadmin] ADD MEMBER [ReplAdmin]

Tie Publisher to Distributor

USE master
GO
DECLARE @publisher SYSNAME = N'SQLAG2';
DECLARE @distributorlogin SYSNAME = N'ReplAdmin';
DECLARE @distributorpassword SYSNAME = N'ASutiablyGoodPassword?';
DECLARE @snapshotdirectory NVARCHAR(500) = N'/var/opt/mssql/data/ReplData/';

EXEC sp_adddistpublisher @publisher = @publisher,
    @distribution_db = N'distribution',
    @security_mode = 0,
    @login = @distributorlogin,
    @password = @distributorpassword,
    @working_directory = @snapshotdirectory,
    @trusted = N'false',
    @thirdparty_flag = 0,
    @publisher_type = N'MSSQLSERVER';
GO

Create Publication

USE [BLS];
GO
DECLARE @replicationdb SYSNAME = N'BLS';
DECLARE @publisherlogin SYSNAME = N'ReplAdmin';
DECLARE @publisherpassword SYSNAME = N'ASutiablyGoodPassword?';

EXEC sp_replicationdboption @dbname = @replicationdb,
    @optname = N'publish',
    @value = N'true';

EXEC sp_addpublication @publication = N'BLS_CPI',
    @description = N'Transactional replication publication of CLI data in database ''BLS'' from Publisher ''SQLAG2''.',
    @sync_method = N'concurrent',
    @retention = 72,
    @allow_push = N'true',
    @allow_pull = N'true',
    @allow_anonymous = N'false',
    @repl_freq = N'continuous',
    @status = N'active',
    @independent_agent = N'true',
    @replicate_ddl = 1;

EXEC sp_addpublication_snapshot @publication = N'BLS_CPI',
    @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;

EXEC sp_addarticle @publication = N'BLS_CPI',
    @article = N'CPIBase',
    @source_owner = N'dbo',
    @source_object = N'CPIBase',
    @type = N'logbased',
    @description = null,
    @creation_script = null,
    @pre_creation_cmd = N'drop',
    @schema_option = 0x000000000803509F,
    @identityrangemanagementoption = N'manual',
    @destination_table = N'CPIBase',
    @destination_owner = N'dbo',
    @vertical_partition = N'false',
    @ins_cmd = N'CALL sp_MSins_dboCPIBase',
    @del_cmd = N'CALL sp_MSdel_dboCPIBase',
    @upd_cmd = N'CALL sp_MSupd_dboCPIBase';
GO

EXEC sp_addarticle @publication = N'BLS_CPI',
    @article = N'CPIData',
    @source_owner = N'dbo',
    @source_object = N'CPIData',
    @type = N'logbased',
    @description = null,
    @creation_script = null,
    @pre_creation_cmd = N'drop',
    @schema_option = 0x000000000803509F,
    @identityrangemanagementoption = N'manual',
    @destination_table = N'CPIData',
    @destination_owner = N'dbo',
    @vertical_partition = N'false',
    @ins_cmd = N'CALL sp_MSins_dboCPIData',
    @del_cmd = N'CALL sp_MSdel_dboCPIData',
    @upd_cmd = N'CALL sp_MSupd_dboCPIData';
GO

Create Subscription

USE BLS
GO
DECLARE @subscribr SYSNAME = N'SQLAG1';
DECLARE @subscriber_db SYSNAME = N'BLS';
DECLARE @subscriberLogin SYSNAME = N'ReplAdmin';
DECLARE @subscriberPassword SYSNAME = N'ASutiablyGoodPassword?';

EXEC sp_addsubscription @publication = N'BLS_CPI',
    @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'BLS_CPI',
    @subscriber = @subscriber,
    @subscriber_db = @subscriber_db,
    @subscriber_security_mode = 0,
    @subscriber_login = @subscriberLogin,
    @subscriber_password = @subscriberPassword,
    @frequency_type = 64,
    @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 = 235959,
    @active_start_date = 20240101,
    @active_end_date = 99991231;
GO

LINKS AND ADDITIONAL INFORMATION

All of these videos are on behalf of Catallaxy Services, LLC, my consulting company where I help customers on problems all across the data platform space.

Leave a comment