มาทำ SQL Server Replication บน Linux กันเถอะ

Snapshot replication ทำงานอย่างไร ?

เมื่อเพื่อนๆพอจะเข้าใจการทำงานของ Snapshot Replication แล้วขั้นตอนต่อไปนี้เรามาดูวิธีการเตรียมความพร้อมและวิธีติดตั้ง Snapshot Replication กันค่ะ

วิธีเตรียมความพร้อมก่อนทำการติดตั้ง Replication 🔩

ใน Demo นี้จะทำการติดตั้ง Linux และ install SQL Server 2019 ลงบน Linux Platform ค่ะ หลังจากนั้นจะสร้าง Publication Database และ Subscription Database อยู่บน instance เดียวกัน และ user ที่ใช้ใน Demo นี้คือ SA

1. ทำการเปิด agent job ของ SQL Server instance ด้วย command ต่อไปนี้

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
sudo systemctl restart mssql-server

2. ทำการสร้าง snapshot folder สำหรับ SQL Server Agent ให้สามารถเข้าถึง directory ด้วย mssql user

sudo mkdir /var/opt/mssql/data/ReplData/
sudo chown mssql /var/opt/mssql/data/ReplData/
sudo chgrp mssql /var/opt/mssql/data/ReplData/

ทำการตรวจสอบว่า directory มี user mssql เป็นเจ้าของ ผลลัพธ์ตามตัวอย่างรูปด้านล่างนี้

mssql user เป็นเจ้าของ ReplData Directory

3. ทำการสร้าง database ขึ้นมาสองตัวเพื่อทำเป็น publication database และ subscription database โดย publication database ตั้งชื่อว่า sales_pub_db และ subscription database ตั้งชื่อว่า sales_sub_db

Command สร้าง Database

Note: สามารถเข้าใช้งาน SQL Server ได้ด้วย command ต่อไปนี้
sqlcmd -S localhost -U SA -P ‘P@ssw0rd’
password ที่เข้าใช้งานนั้นจะถูกสร้างตอนติดต้ัง SQL Server สามารถดูขั้นตอนการติดตั้ง SQL Server 2019 ได้ตามนี้เลยค่ะ Install SQL Sever 2019 on Linux

ทำการตรวจสอบรายชื่อ database บน SQL Server instance พบ sales_pub_db และ sales_sub_db database ที่สร้างขึ้น

Command ตรวจสอบรายชื่อ Database บน instance

ทำการสร้าง table ลงใน publication database หรือ sales_pub_db database Table ที่สร้าง ใน sales_pub_db database นี้ชื่อว่า CUSTOMER table และ insert ข้อมูลลงใน table นี้

command สร้าง CUSTOMER Table

ทำการตรวจสอบ table และข้อมูลที่ได้ทำการ insert เข้าไปใน CUSTOMER table

ตรวจสอบข้อมูลจาก dbo.cusomer table

ในฝั่งของ Subscription Database นั้นไม่ต้องทำการสร้าง table ใดๆไว้เลยเพราะเมื่อทำการ replication แล้ว ข้อมูลและโครงสร้าง table จะถูก replicate ไปยัง Subscription Database

เมื่อเสร็จสิ้นขั้นตอนการเตรียม environment ก่อนการติดตั้ง snapshot replication เรียบร้อยแล้ว เรามาเริ่มขั้นตอนการติดตั้ง snapshot replication บน linux ได้เลย

วิธีการติดตั้ง Snapshot Replication 🔑

1. ทำการ configure distributor และสร้าง distribution databaseโดยรัน command ด้านล่างนี้ที่ master database โดย Replicate_Linux คือชื่อ hostname และ P@ssw0rd คือ password ของ sa user ที่ใช้ในการ configure ครั้งนี้

use master
exec sp_adddistributor @distributor = N’Replicate_Linux’, @password = N’P@ssw0rd’

sp_addistributor store procedure บน master database

ขั้นตอนต่อมาคือ ทำการสร้าง distribution database ด้วย command ต่อไปนี้

exec sp_adddistributiondb @database = N’distribution’, @log_file_size = 2, @deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000, @security_mode = 0, @login = N’sa’, @password = N’P@ssw0rd’

เมื่อทำการรัน command สร้าง distribution database เสร็จแล้ว ตัว Process เองจะทำการสร้าง object ต่างๆที่เกี่ยวกับการ Replication สังเกตได้จากรูปด้านซ้ายที่มีการสร้าง store procedure และ guest user เพิ่มขึ้นมา

หลังจากนั้นทำการตรวจสอบ distribution database บน SQL Server instance พบว่า distribution database ถูกสร้างเรียบร้อยแล้ว เหมือนรูปด้านล่างนี้

Distribution Database ปรากฎเมื่อสร้างเสร็จ

ทำการ update extend property ลงใน distribution database เพราะ publisher และ distributor นั้นอยู่บนเครื่องเดียวกัน

use [distribution]

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’, ‘/var/opt/mssql/data/ReplData/’, ‘user’, dbo, ‘table’, ‘UIProperties’

else EXEC sp_addextendedproperty N’SnapshotFolder’, ‘/var/opt/mssql/data/ReplData/’, ‘user’, dbo, ‘table’, ‘UIProperties’

2. ทำการ configure publisher database ด้วย command ต่อไปนี้

exec sp_adddistpublisher @publisher = ‘Replicate_Linux’, @distribution_db = N’distribution’, @security_mode = 0, @login = ‘sa’, @password = ‘P@ssw0rd’,
@working_directory = N’/var/opt/mssql/data/ReplData’, @trusted = N’false’,
@thirdparty_flag = 0, @publisher_type = N’MSSQLSERVER’

sp_adddistpublisher store procedure

3. ทำการ configure publisher job ด้วย command ต่อไปนี้

use [sales_pub_db]
exec sp_replicationdboption @dbname = N’sales_pub_db’,
@optname = N’publish’, @value = N’true’

sp_replicationdboption store procedure ใช้ในการ configure Publisher

exec sp_addpublication @publication = N’SnapshotRepl’,
@description = N’Snapshot publication of database ‘’sales_pub_db’’ from Publisher ‘’mssqlpublisher’’.’,@retention = 0, @allow_push = N’true’, @repl_freq = N’snapshot’, @status = N’active’,@independent_agent = N’true’

sp_addpublication store procedure ใช้สร้าง snapshot publication

exec sp_addpublication_snapshot @publication = N’SnapshotRepl’,
@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 = N’sa’, @publisher_password = N’P@ssw0rd’

sp_addpublication_snapshot store procedure ใช้เอาไว้สร้าง Snapshot Agent

4. สร้าง article และเพิ่มเข้าไปใน publication ด้วย Command ต่อไปนี้

use [Sales]
exec sp_addarticle @publication = N’SnapshotRepl’, @article = N’customer’,
@source_owner = N’dbo’, @source_object = N’customer’, @type = N’logbased’, @description = null, @creation_script = null, @pre_creation_cmd = N’drop’, @schema_option = 0x000000000803509D,@identityrangemanagementoption = N’manual’, @destination_table = N’customer’, @destination_owner = N’dbo’, @vertical_partition = N’false’

sp_addarticle store procedure สร้าง article และเพิ่มเข้าไปใน publication

5. ทำการเพิ่ม subscription เข้าไปใน publication และ set ของ subscriber status

use [sales_pub_db]
exec sp_addsubscription @publication = N’SnapshotRepl’, @subscriber = ‘Replicate_Linux’, — 192.168.0.109@destination_db = N’sales_sub_db’, @subscription_type = N’Push’, @sync_type = N’automatic’, @article = N’all’, @update_mode = N’read only’, @subscriber_type = 0

sp_addsubsciption store procedure เพิ่ม subscription ลงไปที่ pubblication

exec sp_addpushsubscription_agent @publication = N’Snaps hotRepl’, @subscriber = ‘Replicate_Linux’,@subscriber_db = N’subscriber_db’,
@subscriber_security_mode = 0, @subscriber_login = ‘sa’,@subscriber_password = ‘P@ssw0rd’,@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 = 0, @active_start_date = 0, @active_end_date = 19950101

sp_addpushsubscription_agent store procedure ทำการเพิ่ม job ใหม่เข้าไปเพื่อ syn push subscription ไปยัง transactional publication

ทำการตรวจสอบ replication job ที่ msdb database และ รัน job เพื่อทำการ replicate ข้อมูล สามารถตรวจสอบได้โดยการรัน command ต่อไปนี้

SELECT sj.name, sc.name AS category_name FROM sysjobs sj
INNER JOIN syscategory sc ON sj.category_id = sc.category_id

รัน job ตามรูปภาพด้านล่างนี้ตามลำดับ เพื่อทำการ replicate ข้อมูลจาก publication database ไปยัง subscription database

รายชื่อ Job ที่ใช้ Replicate ข้อมูล

สุดท้าย ทำการตรวจสอบข้อมูลบน subscription database พบว่ามี dbo.customer table พร้อมข้อมูลมาปรากฎอยู่ที่ sales_sub_db database เรียบร้อยแล้ว

ข้อมูลในฝั่ง subscription database ก่อนจากการรัน job

ทำการทดสอบเพิ่มเติมโดยการเพิ่มข้อมูลลงใน customer table ฝั่ง sales_pub_db ตามรูปภาพด้านล่างนี้และทำการรัน job อีกครั้ง

ข้อมูลในฝั่ง Publication Database

หลังจากทำการรัน job บน Agent เรียบร้อยแล้ว ก็มาตรวจสอบข้อมูลที่ฝั่ง sales_sub_db database อีกครั้งก็พบว่าข้อมูลได้รับการ update ให้เหมือน sales_pub_db เรียบร้อยแล้ว

ข้อมูลในฝั่ง subscription database หลังจากการรัน job

Reference:
Snapshot Replication Microsoft Reference
Snapshot Replication on Linux
Trasactional replication on Linux