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.