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:
- Configure Distribution (If not Exists)
- Add Publisher & Articles
- Add Subscriber
- Generate Snapshot
Configure Distribution
Interface SQL Server database engine in Linux utilizing,
|
1 |
sqlcmd -S localhost -U SA |
execute underneath commands in Query Command line tool to arrange Distributor inside distribution database and characterize the snapshot folder.
|
1 2 3 4 5 6 7 8 |
USE master GO EXEC sp_adddistributor @distributor = N'sqltranrepl' 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 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.
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.
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.
|
1 2 |
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] 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'sqltranrepl', @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 |
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,|
1 2 3 4 5 6 7 8 9 10 |
CREATE DATABASE pub_db CREATE DATABASE sub_db GO use pub_db GO CREATE TABLE [dbo].[data_in]( [id] [bigint] IDENTITY(1,1) PRIMARY KEY, [is_active] [bit] NULL ) GO |
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.
|
1 2 3 |
USE MASTER EXEC sp_replicationdboption @dbname = N'pub_db', @optname = N'publish', @value = N'true' GO |
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:
|
1 2 |
EXEC [pub_db].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1 GO |
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
|
1 2 3 4 |
-- Adding the Transactional Replication publication USE [pub_db] EXEC sp_addpublication @publication = N'linux_tran_repl', @description = N'Transactional publication of database ''pub_db'' from Publisher ''sqltranrepl''.', @sync_method = N'concurrent', @retention = 0, @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_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false' GO |
After including the Publication, the Snapshot Agent should be made to have purpose of having specified Publication at Publisher database.
|
1 |
EXEC sp_addpublication_snapshot @publication = N'linux_tran_repl', @frequency_type = 1, @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, @job_login = null, @job_password = null, @publisher_security_mode = 1 |
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.
|
1 2 |
EXEC sp_grant_publication_access @publication = N'linux_tran_repl', @login = N'sa' GO |
To get it verified, that publisher has been added or not, use the command below:
|
1 2 3 4 5 |
USE distribution GO SELECT publisher_db, publication FROM MSpublications GO |
Adding Article
|
1 2 3 |
USE [pub_db] EXEC sp_addarticle @publication = N'linux_tran_repl', @article = N'data_in', @source_owner = N'dbo', @source_object = N'data_in', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'data_in', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbodata_in]', @del_cmd = N'CALL [sp_MSdel_dbodata_in]', @upd_cmd = N'SCALL [sp_MSupd_dbodata_in]' GO |
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:
|
1 2 3 4 5 6 |
use pub_db GO SELECT p.Name AS publication_name, a.name as article_name FROM syspublications p INNER JOIN sysarticles a on p.pubid=a.pubid GO |
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:
|
1 2 3 |
-- Adding the Transactional Replication Subscriber USE [pub_db] EXEC sp_addsubscription @publication = N'linux_tran_repl', @subscriber = N'sqltranrepl', @destination_db = N'sub_db', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0 |
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.
|
1 2 3 |
USE pub_db GO EXEC sp_addpushsubscription_agent @publication = N'linux_tran_repl', @subscriber = N'sqltranrepl', @subscriber_db = N'sub_db', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor' |
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.
|
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, in order to generate a snapshot by starting the SQL job SQLTRANREPL-pub_db-linux_tran_repl-1 using below T-SQL command.
|
1 2 3 |
USE msdb GO EXEC dbo.sp_start_job N'SQLTRANREPL-pub_db-linux_tran_repl-1' |
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.
|
1 2 3 4 |
USE distribution GO SELECT time, error_text FROM MSrepl_errors ORDER BY time DESC GO |
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.
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.
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