SQL Server 2019 on Linux Replication Step by Step Using Azure VM

Jiayi Yang Azure • Linux • Replication • VM 2019年3月7日

--STEP1
--Linux上のSQL Server レプリケーション エージェントを有効にする
--SQL01,SQL02両方実装
/*
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true 
sudo systemctl restart mssql-server
*/

--STEP2
--サンプル データベースの作成
--SQL01片方実装
USE [master]
GO

CREATE DATABASE [LinuxReplicationDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'LinuxReplicationDB', FILENAME = N'/var/opt/mssql/data/LinuxReplicationDB.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'LinuxReplicationDB_log', FILENAME = N'/var/opt/mssql/data/LinuxReplicationDB_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

USE [LinuxReplicationDB]
GO
CREATE TABLE [dbo].[Person](
	[PersonID] [int] NOT NULL,
	[PersonName] [varchar](50) NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
	[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[Person] ([PersonID], [PersonName]) VALUES (1,'JSSUG1'),(2,'JSSUG2'),(3,'JSSUG3')
GO

--STEP3
--SQL Serverエージェントのアクセスのスナップショットフォルダーの構成
--SQL01片方実装
--スナップショットフォルダの所有者の変更
/*
sudo mkdir /var/opt/mssql/data/ReplData/
sudo chown mssql /var/opt/mssql/data/ReplData/
sudo chgrp mssql /var/opt/mssql/data/ReplData/
*/

--STEP4
--ディストリビューターの構成
--UIでも可能
use master
exec sp_adddistributor @distributor = N'sql01', @password = N'nhy65432!QAZ'
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

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', N'/var/opt/mssql/data/ReplData', 'user', dbo, 'table', 'UIProperties' 
else 
	EXEC sp_addextendedproperty N'SnapshotFolder', N'/var/opt/mssql/data/ReplData', 'user', dbo, 'table', 'UIProperties'
GO

--STEP5
--パブリッシャーの構成
--UIでも可能
use [distribution]
GO

exec sp_adddistpublisher @publisher = N'sql01', @distribution_db = N'distribution', @security_mode = 0, @login = N'sa', @password = N'', @working_directory = N'/var/opt/mssql/data/ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO

--STEP6
--パブリケーションとアーティクルの構成
--UIでも可能
-- レプリケーション データベースを有効にしています
use master
exec sp_replicationdboption @dbname = N'LinuxReplicationDB', @optname = N'publish', @value = N'true'
GO

-- トランザクション パブリケーションを追加しています
use [LinuxReplicationDB]
exec sp_addpublication @publication = N'PersonRepl', @description = N'パブリッシャー ''sql01'' からのデータベース ''LinuxReplicationDB'' のトランザクション パブリケーション。', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @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'false', @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

exec sp_addpublication_snapshot @publication = N'PersonRepl', @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 = 0, @publisher_login = N'sa', @publisher_password = N''

use [LinuxReplicationDB]
exec sp_addarticle @publication = N'PersonRepl', @article = N'Person', @source_owner = N'dbo', @source_object = N'Person', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Person', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboPerson', @del_cmd = N'CALL sp_MSdel_dboPerson', @upd_cmd = N'SCALL sp_MSupd_dboPerson'
GO

--STEP7
--サブスクライバーの構成
--UIでも可能
use [LinuxReplicationDB]
exec sp_addsubscription @publication = N'PersonRepl', @subscriber = N'sql02', @destination_db = N'Person', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'PersonRepl', @subscriber = N'sql02', @subscriber_db = N'Person', @job_login = null, @job_password = null, @subscriber_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 = 20181210, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO

--STEP8
--レプリケーションジョブの実行
--UIでも可能
SELECT name, date_modified FROM msdb.dbo.sysjobs order by date_modified desc

USE [msdb]
GO
EXEC dbo.sp_start_job N'SQL01-LinuxReplicationDB-PersonRepl-1'
GO
EXEC dbo.sp_start_job N'sql01-LinuxReplicationDB-PersonRepl-SQL02-3'
GO

--INSERT INTOパブリッシャー
USE [LinuxReplicationDB]
GO

DECLARE @intLoop int = 4
DECLARE @intLimit int = 300
WHILE (@intLoop <= @intLimit)
BEGIN
	INSERT INTO [dbo].[Person] ([PersonID], [PersonName])
	VALUES(@intLoop, 'JSSUG' + CONVERT(VARCHAR(MAX),@intLoop))
	SET @intLoop += 1
END

--DELETE パブリッシャー
DELETE [dbo].[Person]
WHERE [PersonID] > 3

--ディストリビューター無効化
--セッションがあると、削除できないため、SPIDを確認する
use [master]
declare @spid varchar(10)
select @spid=spid from sys.sysprocesses where dbid = DB_ID('distribution')
while @@ROWCOUNT <> 0
    exec ('KILL ' + @spid)
exec sp_dropdistributor @no_checks = 1

--スナップショットフォルダを削除する
/*
sudo rmdir --ignore-fail-on-non-empty /var/opt/mssql/data/ReplData/
*/

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

--STEP1

--Linux上のSQL Server レプリケーションエージェントを有効にする

--SQL01,SQL02両方実装

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

sudo systemctl restart mssql-server

--STEP2

--サンプルデータベースの作成

--SQL01片方実装

USE [master]

CREATE DATABASE [LinuxReplicationDB]

CONTAINMENT = NONE

ON PRIMARY

( NAME = N'LinuxReplicationDB', FILENAME = N'/var/opt/mssql/data/LinuxReplicationDB.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )

LOG ON

( NAME = N'LinuxReplicationDB_log', FILENAME = N'/var/opt/mssql/data/LinuxReplicationDB_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )

USE [LinuxReplicationDB]

CREATE TABLE [dbo].[Person](

[PersonID] [int] NOT NULL,

[PersonName] [varchar](50) NULL,

CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED

(

[PersonID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

INSERT INTO [dbo].[Person] ([PersonID], [PersonName]) VALUES (1,'JSSUG1'),(2,'JSSUG2'),(3,'JSSUG3')

--STEP3

--SQL Serverエージェントのアクセスのスナップショットフォルダーの構成

--SQL01片方実装

--スナップショットフォルダの所有者の変更

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

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

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

--STEP4

--ディストリビューターの構成

--UIでも可能

use master

exec sp_adddistributor @distributor = N'sql01', @password = N'nhy65432!QAZ'

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

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

else

EXEC sp_addextendedproperty N'SnapshotFolder', N'/var/opt/mssql/data/ReplData', 'user', dbo, 'table', 'UIProperties'

--STEP5

--パブリッシャーの構成

--UIでも可能

use [distribution]

exec sp_adddistpublisher @publisher = N'sql01', @distribution_db = N'distribution', @security_mode = 0, @login = N'sa', @password = N'', @working_directory = N'/var/opt/mssql/data/ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'

--STEP6

--パブリケーションとアーティクルの構成

--UIでも可能

-- レプリケーションデータベースを有効にしています

use master

exec sp_replicationdboption @dbname = N'LinuxReplicationDB', @optname = N'publish', @value = N'true'

-- トランザクションパブリケーションを追加しています

use [LinuxReplicationDB]

exec sp_addpublication @publication = N'PersonRepl', @description = N'パブリッシャー ''sql01'' からのデータベース ''LinuxReplicationDB'' のトランザクションパブリケーション。', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @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'false', @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'

exec sp_addpublication_snapshot @publication = N'PersonRepl', @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 = 0, @publisher_login = N'sa', @publisher_password = N''

use [LinuxReplicationDB]

exec sp_addarticle @publication = N'PersonRepl', @article = N'Person', @source_owner = N'dbo', @source_object = N'Person', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Person', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboPerson', @del_cmd = N'CALL sp_MSdel_dboPerson', @upd_cmd = N'SCALL sp_MSupd_dboPerson'

--STEP7

--サブスクライバーの構成

--UIでも可能

use [LinuxReplicationDB]

exec sp_addsubscription @publication = N'PersonRepl', @subscriber = N'sql02', @destination_db = N'Person', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0

exec sp_addpushsubscription_agent @publication = N'PersonRepl', @subscriber = N'sql02', @subscriber_db = N'Person', @job_login = null, @job_password = null, @subscriber_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 = 20181210, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'

--STEP8

--レプリケーションジョブの実行

--UIでも可能

SELECT name, date_modified FROM msdb.dbo.sysjobs order by date_modified desc

USE [msdb]

EXEC dbo.sp_start_job N'SQL01-LinuxReplicationDB-PersonRepl-1'

EXEC dbo.sp_start_job N'sql01-LinuxReplicationDB-PersonRepl-SQL02-3'

--INSERT INTOパブリッシャー

USE [LinuxReplicationDB]

DECLARE @intLoop int = 4

DECLARE @intLimit int = 300

WHILE (@intLoop <= @intLimit)

BEGIN

INSERT INTO [dbo].[Person] ([PersonID], [PersonName])

VALUES(@intLoop, 'JSSUG' + CONVERT(VARCHAR(MAX),@intLoop))

SET @intLoop += 1

END

--DELETE パブリッシャー

DELETE [dbo].[Person]

WHERE [PersonID] > 3

--ディストリビューター無効化

--セッションがあると、削除できないため、SPIDを確認する

use [master]

declare @spid varchar(10)

select @spid=spid from sys.sysprocesses where dbid = DB_ID('distribution')

while @@ROWCOUNT <> 0

exec ('KILL ' + @spid)

exec sp_dropdistributor @no_checks = 1

--スナップショットフォルダを削除する

sudo rmdir --ignore-fail-on-non-empty /var/opt/mssql/data/ReplData/

Tagged with: Azure • Azure VM • Linux • Replication • SQL Server 2019