Jignesh Raiyani
SQL Server Merge Replication - List Publisher databases with Publication.

SQL Server Merge Replication on Linux

August 15, 2019 by

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:

  1. Configure Distribution (If not Exists)
  2. Add Publisher & Articles
  3. Add Subscriber
  4. 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.

@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.

SQL Server Merge Replication - List databases to track Distribution database.

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.

Error on distrinution Configuration.

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

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

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.

Now, adding Publisher with publication name as “test_merge”, I used rest of parameters with default values.

Adding Merge Publication

You can verify if the publisher has been added or not, using the below command.

SQL Server Merge Replication - List Publisher databases with Publication.

Adding Merge Article

@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.

List Merge Articles with Publication.

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.

Adding Subscriber to Publisher.

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,

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.

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.

SQL Server Merge Replication - List Jobs in SQL Server to find Snapshot Agent

Now, generate a snapshot by starting the job SQLREPL2-pub_db-test_merge-1 by T-sql command.

There may be some error in starting the snapshot agent process. You can check it using below T-SQL command.

Error on Generating Snapshot

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.

Check data Synchronization with Publisher & Subscriber.

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.

Jignesh Raiyani

Jignesh Raiyani

Jignesh has good experience as Database Solutions and Architecture. Working with multiple Customers on Database Design & Architecture, SQL Development, Administration, Query Optimization, Performance Tuning, HA and Disaster Recovery.
Jignesh Raiyani
138 Views