Replication is a process to manage multiple copies of the same data at a different node. Microsoft SQL Server supports Merge Replication, Transaction Replication, Peer to Peer Replication and Snapshot Replication.
In this article, we will discuss to deploy SQL Server Merge Replication on Linux environment. Many SQL Server features are not available on Linux by Microsoft but the Replication feature exists for Linux Environment Edition. Before starting anything, let’s address this question – what is Merge Replication?
Merge Replication is a data synchronization process with one database (Publisher) to other databases (Subscriber) and vice versa. The data synchronization audit will be controlled by the distributor (Distribution database). The distributor database will manage the data synchronization between Publisher and Subscribers.
You can direct to this interesting article, SQL Server replication to gain more knowledge on replication topography and components in SQL Server.
As a first step, the distribution database must be configured in SQL Server to implement the Merge Replication. If we are using SSMS in the windows Operating system then it is easy to configure using a few steps but in the Linux Operating System, the user needs to play with SQL Server Command Line Tool. In windows, we have SQL Server Replication monitor to generate a snapshot and reinitialize the subscriptions. But in Linux, we cannot launch the Replication Monitor. So, each task of the replication needs to be well scripted to be executed by the command line.
SQL Server Merge Replication Process steps:
- Configure Distribution (If not Exists)
- Add Publisher & Articles
- Add Subscriber
- Generate Snapshot
Configure Distribution
How to Create Distribution Database in SQL Server using Command line in SQL Server?
Connect your SQL Server in Linux using,
sqlcmd -S localhost -U SA
In the Query Command line tool, execute below commands to create a distribution database and define the snapshot folder.
|
1 2 3 4 5 6 7 8 |
USE master GO EXEC sp_adddistributor @distributor = N'sqlrepl2' GO EXEC sp_adddistributiondb @database = N'distribution', @data_folder = N'/var/opt/mssql/data', @log_folder = N'/var/opt/mssql/data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000, @security_mode = 1 GO SELECT name, create_date FROM sys.databases GO |
@distributor value will be the name of the SQL Server instance name, where you are going to configure the distributor database. By default, the SQL Server instance name will be the same as the Linux Instance name.
@data_folder and @log_folder will store the Data file and Log file in the mentioned directory. By default, it uses /var/opt/mssql/ but if you want to keep in other than default then make that directory with required permissions before executing the above statement.
After completing the execution of the above statements, we can verify it in sys.databases Table.
If your SQL Server edition doesn’t support the Replication feature, then it does not allow to configure distribution. So, make sure to install the appropriate version.
Create a Snapshot folder and integrate it with distribution database. I used /var/opt/mssql/data/repl_data in this example. So before executing the next step, make directory of the repl_data folder and allow users to make it accessible.
chmod -R u+rX /var/opt/mssql/data/repl_data
chmod a+rwx /var/opt/mssql/data/repl_data
|
1 2 3 4 5 6 7 8 9 10 11 |
USE [distribution] GO IF (NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'UIProperties' AND type = 'U ')) CREATE TABLE UIProperties(id int) GO IF(EXISTS (SELECT * FROM ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null))) EXEC sp_updateextendedproperty N'SnapshotFolder', N'/var/opt/mssql/data/repl_data', 'user', dbo, 'table', 'UIProperties' ELSE EXEC sp_addextendedproperty N'SnapshotFolder', N'/var/opt/mssql/data/repl_data', 'user', dbo, 'table', 'UIProperties' GO EXEC sp_adddistpublisher @publisher = N'sqlrepl2', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'/var/opt/mssql/data/repl_data', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER' GO |
Now, Distribution is completely configured with prerequisites.
Add Publisher & Articles
Adding two new databases pub_db(Publisher) and sub_db(Subscriber) with a table as below
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE DATABASE pub_db CREATE DATABASE sub_db GO use pub_db GO CREATE TABLE [dbo].[data_in]( [id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION PRIMARY KEY, [is_active] [bit] NULL, [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newsequentialid()) ) GO |
All merge articles must contain a unique identifier column with a unique index and the rowguid property. On windows platform, SQL Server adds a unique identifier column to published tables that do not have one when the first snapshot is generated. But on Linux environment, it needs to be done while creating a table or later on with ALTER TABLE with ADD statement.
Before adding the Publisher, Replication must be enabled on Publisher Database. You can use the below command to make it enable for Replication.
|
1 2 3 |
USE master EXEC sp_replicationdboption @dbname = N'pub_db', @optname = N'merge publish', @value = N'true' GO |
Now, adding Publisher with publication name as “test_merge”, I used rest of parameters with default values.
Adding Merge Publication
|
1 2 3 |
use [pub_db] exec sp_addmergepublication @publication = N'test_merge', @description = N'Merge publication of database ''pub_db'' from Publisher ''sqlrepl2''.', @sync_mode = N'native', @retention = 14, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_subdirectory = N'ftp', @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @dynamic_filters = N'false', @conflict_retention = 14, @keep_partition_changes = N'false', @allow_synctoalternate = N'false', @max_concurrent_merge = 0, @max_concurrent_dynamic_snapshots = 0, @use_partition_groups = N'false', @publication_compatibility_level = N'100RTM', @replicate_ddl = 1, @allow_subscriber_initiated_snapshot = N'false', @allow_web_synchronization = N'false', @allow_partition_realignment = N'true', @retention_period_unit = N'days', @conflict_logging = N'both', @automatic_reinitialization_policy = 0 GO |
You can verify if the publisher has been added or not, using the below command.
|
1 2 3 4 5 |
USE distribution GO SELECT publisher_db, publication FROM MSpublications GO |
Adding Merge Article
|
1 2 3 |
use [pub_db] exec sp_addmergearticle @publication = N'test_merge', @article = N'data_in', @source_owner = N'dbo', @source_object = N'data_in', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000010C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range = 100000, @identity_range = 10000, @threshold = 80, @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'true', @partition_options = 0 GO |
@publication is the name of Publication and @article will be the name of Table. The rest of the parameters are defined with default values. You can verify that the Article has been added or not using the below command.
|
1 2 3 4 5 6 |
use pub_db GO SELECT p.Name AS publication_name, a.name as article_name FROM sysmergepublications p JOIN sysmergearticles a on p.pubid=a.pubid GO |
Add Subscriber
In this example, we have sub_db as a subscriber database. SQL Server allows to configure subscriber by PUSH or PULL subscription type.
PUSH, Merge Agent will run at the Distributor end and Publisher data changes will be pushed to subscribers on-demand or continuously by defining @sync_type parameter while adding Subscription.
PULL, Merge Agent will run at the Subscriber end and its request to get changes that are made at the Publisher.
|
1 2 3 |
-- Adding the merge subscriptions use [pub_db] exec sp_addmergesubscription @publication = N'test_merge', @subscriber = N'sqlrepl2', @subscriber_db = N'sub_db', @subscription_type = N'Push', @sync_type = N'Automatic', @subscriber_type = N'Global', @subscription_priority = 75, @description = N'', @use_interactive_resolver = N'False' |
In this example, Subscriber, Publisher and Distributor databases are on the same SQL Server instance sqlrepl2 but it can be on different instances as well.
Get verified that Article is added or not using below command,
|
1 2 3 |
select sp.name as Publication_name, sp.publisher AS publisher_server, sp.publisher_db, ss.subscriber_server, ss.db_name as subcriber_db FROM dbo.sysmergepublications sp INNER join dbo.sysmergesubscriptions ss on ss.pubid = sp.pubid |
After adding the subscription, Merge Agent needs to be configured at either publisher or subscriber based on @subscription_type. In this example, we used a Push subscription. So we will add the merge agent at publisher end using the script.
|
1 2 3 4 |
use pub_db GO exec sp_addmergepushsubscription_agent @publication = N'test_merge', @subscriber = N'sqlrepl2', @subscriber_db = N'sub_db', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @publisher_security_mode = 1, @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 = 0, @active_end_date = 0 GO |
Generate Snapshot
We do not have the SQL Server Management Studio or SQL Server Replication monitor to generate a snapshot. So first of all, we need to find Snapshot Agent Job from msdb database and then get it to start using command.
The below query will help us to get a list of jobs in the SQL Server msdb database.
|
1 2 3 4 5 |
use msdb go select sj.name, sc.name as category_name from sysjobs sj INNER JOIN syscategories sc on sj.category_id = sc.category_id |
Now, generate a snapshot by starting the job SQLREPL2-pub_db-test_merge-1 by T-sql command.
|
1 |
EXEC dbo.sp_start_job N'SQLREPL2-pub_db-test_merge-1' |
There may be some error in starting the snapshot agent process. You can check it using below T-SQL command.
|
1 2 3 4 |
USE distribution GO SELECT time, error_text FROM MSrepl_errors ORDER BY time DESC GO |
Here, we have an error of snapshot folder access permission. So, we will give permission on that folder directory using the below Linux Command and generate a snapshot again.
chmod -R u+rX /var/opt/mssql/data/repl_data
chmod a+rwx /var/opt/mssql/data/repl_data
I checked SQL Server Merge Replication Error log as well in the distribution database again, it looks good now.
Snapshot successfully generated and inserted one rows at the publisher database. It quickly synchronized at the subscriber end.
Conclusion
We covered the process of deploying SQL Server Merge Replication on Linux environment in this article. I hope you find it helpful, please feel free to ask any questions in the comments section below.
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server - July 17, 2020
- How to automate Table Partitioning in SQL Server - July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020