Jignesh Raiyani
List jobs to track Snapshot Agnet job.

SQL Server Transactional Replication on Linux

September 12, 2019 by

In this article, we will talk about how to send the SQL Server Transactional Replication on Linux Environment.

Replication is a procedure to deal with various copies of same data at not at all like each other hubs. Microsoft SQL Server underpins a few distinctive sort Data Synchronization procedures like (Merge Replication, Transactional Replication, Peer to Peer Replication and Snapshot Replication).

The Replication is one of the features given by Microsoft SQL Server and it is accessible with SQL Server on Linux as well. A few of the features are not gotten on Linux based SQL Server version, which is firmly coordinated with Windows Operating System. Presently let us perceive how the Transactional Replication functions in SQL Server on Windows and Linux Operation Systems:

How Transactional Replication works?

The Transactional Replication is a procedure to synchronize information from one database (Publisher) to different databases (Subscriber). The job of information synchronization arbiter is performed by Distributor (Distribution database) which can be arranged on the Publisher site, Subscriber Site or some other occasion too.

Exchange Replication is methodical overseen by Log Reader Agent and Distributor Agent. The usefulness of Log-Reader Agent is to catch the information changes from Publisher by Transaction log and move it to Distribution database.

To catch the new information changes the Distributor Agent is required, it catches the new information changes, for example, addition or schema changes in conveyance database and move them to Subscriber database utilizing Primary key reference of the table.

Presently before setting up the Transactional Replication, initially we need the Distributor to be arranged to utilize SSMS, which can be designed inside a couple of steps. Taking a gander at the means beneath we understand that the other Replication steps and errands are additionally simple to continue in SSMS, for example, Generate Snapshot, Re-introduction, Start/Stop Synchronization, Changes in Agent Profile and Replication Monitor for issue troubleshooting.

While working with SQL Server on Linux, the client needs to chip away at SQL Command-Line as it were. The client can associate that Linux SQL Server occurrence on SSMS by the remote association at any rate. This is as of now introduced in Windows Operating System, anyway the best practice with Linux Environment is to do troubleshooting and execution done by core application command as it were.

Transactional Replication Process steps:

  1. Configure Distribution (If not Exists)
  2. Add Publisher & Articles
  3. Add Subscriber
  4. Generate Snapshot

Configure Distribution

Interface SQL Server database engine in Linux utilizing,

execute underneath commands in Query Command line tool to arrange Distributor inside distribution database and characterize the snapshot folder.

@distributor is the value of the name of SQL Server instance, it is the place you will configure the Distribution database. As referenced over, the Distribution database can be designed on different SQL Server instance too. In the above model, we have utilized the same SQL Server Instance for Publisher, Subscriber, and Distributor.

@data_folder and @log_folder is utilized to define Data file and Log file in referenced index or Directory. As a matter of course, it utilizes /var/opt/mssql/. In case you need to keep the Data in other than default registry than make a point to give expected authorizations and permissions to directory in advance executing the above command. After finishing the execution of above statements the confirmation should be possible in the sys.databases Table.

List database to track distribution database.

We have to ensure getting the right Linux based SQL Server Edition to be introduced. generally, won’t bolster the Replication feature. In that case, SQL Server won’t permit to configure distribution too.

Error on Distribution Configuration

To make a Snapshot folder and incorporate it with Distribution database. We have utilized /var/opt/mssql/data/repl_data in this model. thusly, before executing the following stage, make directory of the repl_data folder and enable users to make it available by giving fitting authorizations or permissions on the folder.

Following the above steps will complete the configuration of Distribution with prerequisites.

Add Publisher & Articles (With Log Reader Agent)

Including two new databases pub_db(Publisher) and sub_db(Subscriber) with table as underneath,

See here, all Transaction articles must contain a Primary Key. Presently before including the Publisher, the Replication must be enabled on Publisher Database. The underneath command will enable the database to be utilized as a replication at Publisher or Subscriber. It could be utilized in Merge Replication, Transactional Replication, and Snapshot Replication too.

See here, the value of @optname parameter will be diverse as purpose behind database in Replication, for example, publish, merge publish, subscribe, etc.

In the event of including Log-reader agent on Publisher database, using command underneath:

Configure Log Reader Agent in transaction Replication.

Now, we will include Publisher with Publication name as linux_tran_repl. We utilized rest of parameters with default esteems (see the query underneath).

Adding Publication

After including the Publication, the Snapshot Agent should be made to have purpose of having specified Publication at Publisher database.

The below command will allow user to retrieve the details of the Publication as defined in @login parameter. We have just added the system administrator as a user however we can add any other users as well, if it is required.

To get it verified, that publisher has been added or not, use the command below:

List Publisher database with Publication in Transaction Replication

Adding Article

Here @publication parameter defines name of the Publication and @article is for the name of Table and the remainder of parameters are characterized with default values.

Now, to get it checked, that the Article has been included or not, utilize the command underneath:

List Articles with Publication in Transaction Replication.

Add Subscriber

In this model, we have utilized Subscriber database as sub_db. The subscriber can be included with Publication by PUSH or PULL subscription type.

The PUSH distributor Agent will run at the Distributor to the end and the publisher data changes will be pushed to subscribers on demand or Continuously using and defining @sync_type parameter while adding Subscription.

The PULL Distributor Agent will keep running at the Subscriber as far as possible and it solicitation to get the progressions which are made at the Publisher.

In case we have used PUSH subscription:

The Distributor Agent should be arranged relying upon @subscription type. In this model, we utilized the Push subscription type, henceforth we will include Distributor Agent at Publication site end utilizing underneath content.

Configured distributor agent while adding subscriber in Transaction Replication.

Generate Snapshot

Essentially, we generally Generate Snapshot utilizing SSMS or Replication Monitor. in Linux. To begin with, we have to find the Snapshot Agent Position from msdb database and afterward get started by SQL Command.

In order to get the list of the jobs in SQL Server by executing below T-SQL statement in msdb database.

List jobs to track Snapshot Agnet job.

Now, in order to generate a snapshot by starting the SQL job SQLTRANREPL-pub_db-linux_tran_repl-1 using below T-SQL command.

Generate Snapshot by executing the snapshot job in msdb in Transaction Replication.

There could be a few errors that could happen on beginning the Snapshot Agent, that can be checked utilizing beneath T-SQL query on Distribution database.

By checking Transactional Replication Error log in distribution database again, it looks good now.

The Snapshot successfully generated and inserted a row in the publisher database. Its quickly synchronized at subscriber end as well.

Data replicated at Subscriber end.

Conclusion

We attempted to cover the Deployment procedure of SQL Server Transactional Replication on Linux Operating System in this. I trust you think that it’s helpful, please feel free pose any question or put any recommendation to improve in the comment section below.

Jignesh Raiyani

Jignesh Raiyani

Jignesh has good experience in Database Solutions and Architecture, working with multiple customers on Database Design & Architecture, SQL Development, Administration, Query Optimization, Performance Tuning, HA and Disaster Recovery.

View all posts by Jignesh Raiyani
Jignesh Raiyani
225 Views