Linux上SQL Server合并复制

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.

复制是在不同节点上管理同一数据的多个副本的过程。 Microsoft SQL Server支持合并复制，事务复制，对等复制和快照复制。

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?

在本文中，我们将讨论在Linux环境上部署SQL Server合并复制。 Microsoft无法在Linux上使用许多SQL Server功能，但是Linux Env​​ironment Edition具有复制功能。 在开始任何事情之前，让我们解决这个问题– 什么是合并复制？

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.

您可以转到这篇有趣的文章SQL Server复制，以获取有关复制拓扑和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中配置分发数据库以实现合并复制。 如果我们在Windows操作系统中使用SSMS，则只需几个步骤即可轻松配置，但是在Linux操作系统中，用户需要使用SQL Server命令行工具。 在Windows中，我们有SQL Server复制监视器来生成快照并重新初始化订阅。 但是在Linux中，我们无法启动复制监视器。 因此，复制的每个任务都需要精心编写脚本，以由命令行执行。

SQL Server合并复制过程的步骤： (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?

如何使用SQL Server中的命令行在SQL Server中创建分发数据库？

Connect your SQL Server in Linux using,

使用以下命令在Linux中连接您SQL Server：

sqlcmd -S localhost -U SA

sqlcmd -S本地主机-U SA

In the Query Command line tool, execute below commands to create a distribution database and define the snapshot folder.

在查询命令行工具中，执行以下命令以创建分发数据库并定义快照文件夹。

USE master
  GO
  EXEC sp_adddistributor @distributor = N'sqlrepl2'
  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 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.

@distributor值将是SQL Server实例名称的名称，您将在其中配置分发服务器数据库。 默认情况下，SQL Server实例名称将与Linux实例名称相同。

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

@data_folder和@log_folder将数据文件和日志文件存储在上述目录中。 默认情况下，它使用/ var / opt / mssql /，但是如果要保留默认以外的目录，则在执行上述语句之前，请使用所需的权限创建该目录。

After completing the execution of the above statements, we can verify it in sys.databases Table.

完成以上语句的执行后，我们可以在sys.databases表中对其进行验证。

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.

如果您SQL Server版本不支持复制功能，则不允许配置分发。 因此，请确保安装适当的版本。

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.

创建一个Snapshot文件夹，并将其与分发数据库集成。 在此示例中，我使用了/ var / opt / mssql / data / repl_data 。 因此，在执行下一步之前，请创建repl_data文件夹的目录，并允许用户对其进行访问。

chmod -R u+rX /var/opt/mssql/data/repl_data

chmod -R u + rX / var / opt / mssql / data / repl_data

chmod a+rwx /var/opt/mssql/data/repl_data

chmod a + rwx / var / opt / mssql / data / repl_data

USE [distribution] 
GO
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'sqlrepl2', @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

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

使用下面的表添加两个新数据库pub_db （Publisher）和sub_db （Subscriber）

CREATE DATABASE pub_db
  CREATE DATABASE sub_db
  GO
  use pub_db
  GO
  CREATE TABLE [dbo].[data_in](
  	[id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION PRIMARY KEY,
  	[is_active] [bit] NULL,
  	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL DEFAULT (newsequentialid())
  )
  GO

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.

所有合并文章必须包含具有唯一索引和rowguid属性的唯一标识符列。 在Windows平台上，SQL Server在生成第一个快照时将唯一标识符列添加到不包含该列的已发布表中。 但是在Linux环境中，需要在创建表时完成此操作，或者稍后使用带有ADD语句的ALTER TABLE来完成。

Before adding the Publisher, Replication must be enabled on Publisher Database. You can use the below command to make it enable for Replication.

在添加发布服务器之前，必须在发布服务器数据库上启用复制。 您可以使用以下命令使其启用复制。

USE master
  EXEC sp_replicationdboption @dbname = N'pub_db', @optname = N'merge publish', @value = N'true'
  GO

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

现在，添加发布者名称为“ test_merge”的 Publisher ，我使用其余参数作为默认值。

添加合并出版物 (Adding Merge Publication)

use [pub_db]
  exec sp_addmergepublication @publication = N'test_merge', @description = N'Merge publication of database ''pub_db'' from Publisher ''sqlrepl2''.', @sync_mode = N'native', @retention = 14, @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_subdirectory = N'ftp', @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @dynamic_filters = N'false', @conflict_retention = 14, @keep_partition_changes = N'false', @allow_synctoalternate = N'false', @max_concurrent_merge = 0, @max_concurrent_dynamic_snapshots = 0, @use_partition_groups = N'false', @publication_compatibility_level = N'100RTM', @replicate_ddl = 1, @allow_subscriber_initiated_snapshot = N'false', @allow_web_synchronization = N'false', @allow_partition_realignment = N'true', @retention_period_unit = N'days', @conflict_logging = N'both', @automatic_reinitialization_policy = 0
  GO

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

您可以使用以下命令验证是否已添加发布者。

USE distribution
    GO
    SELECT publisher_db, publication 
    FROM MSpublications
    GO

添加合并文章 (Adding Merge Article)

use [pub_db]
  exec sp_addmergearticle @publication = N'test_merge', @article = N'data_in', @source_owner = N'dbo', @source_object = N'data_in', @type = N'table', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000010C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range = 100000, @identity_range = 10000, @threshold = 80, @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N'', @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'true', @partition_options = 0
  GO

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

@publication是Publication的名称，@ article是Table的名称。 其余参数使用默认值定义。 您可以使用以下命令验证是否已添加文章。

use pub_db
    GO
    SELECT p.Name AS publication_name, a.name as article_name
    FROM sysmergepublications p
    JOIN sysmergearticles a on p.pubid=a.pubid
    GO

添加订户 (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.

在此示例中，我们将sub_db作为订户数据库。 SQL Server允许按PUSH或PULL订阅类型配置订阅服务器。

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.

PUSH ，合并代理将在分发服务器端运行，通过在添加订阅时定义@sync_type参数，将发布服务器数据更改按需或连续推送到订阅服务器。

PULL, Merge Agent will run at the Subscriber end and its request to get changes that are made at the Publisher.

PULL ，合并代理将在订阅服务器端运行，并请求在发布服务器上进行更改。

-- Adding the merge subscriptions
    use [pub_db]
    exec sp_addmergesubscription @publication = N'test_merge', @subscriber = N'sqlrepl2', @subscriber_db = N'sub_db', @subscription_type = N'Push', @sync_type = N'Automatic', @subscriber_type = N'Global', @subscription_priority = 75, @description = N'', @use_interactive_resolver = N'False'

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.

在此示例中，订阅服务器，发布服务器和分发服务器数据库位于同一SQL Server实例sqlrepl2上，但也可以位于不同的实例上。

Get verified that Article is added or not using below command,

使用以下命令验证是否已添加文章，

select sp.name as Publication_name, sp.publisher AS publisher_server, sp.publisher_db, ss.subscriber_server, ss.db_name as subcriber_db
  FROM dbo.sysmergepublications sp 
  INNER join dbo.sysmergesubscriptions ss on ss.pubid = sp.pubid

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.

添加订阅后，需要基于@subscription_type在发布者或订阅者上配置合并代理。 在此示例中，我们使用了Push订阅。 因此，我们将使用脚本在发布者端添加合并代理。

use pub_db
  GO
  exec sp_addmergepushsubscription_agent @publication = N'test_merge', @subscriber = N'sqlrepl2', @subscriber_db = N'sub_db', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @publisher_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 = 0, @active_end_date = 0
  GO

产生快照 (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.

我们没有SQL Server Management Studio或SQL Server复制监视器来生成快照。 因此，首先，我们需要从msdb数据库中找到Snapshot Agent Job，然后使其开始使用命令。

The below query will help us to get a list of jobs in the SQL Server msdb database.

下面的查询将帮助我们获取SQL Server msdb数据库中的作业列表。

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, generate a snapshot by starting the job SQLREPL2-pub_db-test_merge-1 by T-sql command.

现在，通过使用T-sql命令启动作业SQLREPL2-pub_db-test_merge-1来生成快照。

EXEC dbo.sp_start_job N'SQLREPL2-pub_db-test_merge-1'

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

启动快照代理进程时可能会出现一些错误。 您可以使用以下T-SQL命令进行检查。

USE distribution
    GO
    SELECT time, error_text FROM MSrepl_errors ORDER BY time DESC
    GO

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.

在这里，我们有快照文件夹访问权限错误。 因此，我们将使用以下Linux命令在该文件夹目录上授予权限，并再次生成快照。

chmod -R u+rX /var/opt/mssql/data/repl_data
chmod a+rwx /var/opt/mssql/data/repl_data

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.

我再次在分发数据库中检查了SQL Server合并复制错误日志，现在看起来不错。

Snapshot successfully generated and inserted one rows at the publisher database. It quickly synchronized at the subscriber end.

快照成功生成，并在发布者数据库中插入了一行。 它在订户端快速同步。

结论 (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.

本文讨论了在Linux环境上部署SQL Server合并复制的过程。 希望对您有所帮助，请随时在下面的评论部分中提出任何问题。

翻译自: https://www.sqlshack.com/sql-server-merge-replication-on-linux/