MSSQL数据分布式同步实现模式

1. 数据同步概述

数据同步指多台计算机之间的数据传输和同步的过程，在分布式环境下，为了保证数据的一致性和可用性，数据同步变得尤为重要。在MSSQL数据库中，数据同步主要分为单向同步和双向同步，而单向同步又分为增量同步和全量同步。

2. MSSQL数据同步实现模式

2.1 基于事务复制的同步模式

基于事务复制的同步模式是一种单向同步模式，即同步发生在一个主数据库和多个从数据库之间。主数据库负责更新，从数据库负责复制主数据库中的数据，从而保持数据的同步和一致性。

主数据库中的所有操作都被捕获并转换成事务日志，事务日志被发送到从数据库，然后在从数据库上执行相同的操作，从而保证数据的同步。这种同步模式需要保证主从数据库的网络连接正常，否则同步将会受到影响。

--启用主数据库的事务复制模式
ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
--在主数据库上创建发布者
EXECUTE sp_addpublication @publication = N'MyPublication', @description = N'Transactional publication of database ''MyDatabase'' from Publisher ''MyPublisher''', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_subscription_copy = N'true', @centralized_conflicts = N'false', @create_sync_job = N'true', @enabled_for_internet = N'false', @enabled_for_p2p = N'false', @dynamic_filters = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'', @allow_dts = N'true', @allow_subscription_synchronize = N'true', @alternatesnapshot_folder = N'', @conflict_retention = 14, @max_concurrent_merge = 0, @max_concurrent_dynamic_snapshots = 5, @use_partition_groups = N'false'
--在从数据库中，使用该发布者进行订阅
EXECUTE sp_addsubscription @publication = N'MyPublication', @subscriber = N'MySubscriber', @destination_db = N'MySubscriber', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0

2.2 基于对等复制的同步模式

基于对等复制的同步模式是一种双向同步模式，也就是说，数据可以在多个节点之间相互同步，实现真正的去中心化。

为了保证数据的一致性和可用性，节点之间需要使用一个共同的同步机制。在MSSQL数据库中，基于对等复制的同步模式使用Peer-to-Peer Replication技术来实现节点之间的数据同步。

--在每个节点上，都启用Peer-to-Peer Replication技术
EXEC sys.sp_configure 'clr enabled', 1;
RECONFIGURE;
EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure 'peer to peer topology', 1;
RECONFIGURE;
--配置不同节点之间的同步关系
EXEC sys.sp_adddistributor @distributor = N'NodeA';
EXEC sys.sp_adddistributiondb @database = N'TestDB', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA', @log_file_size = 2, @min_distretention = 0, @max_distretention = 100000, @history_retention = 0, @security_mode = 1;
EXEC sys.sp_adddistpublisher @publisher = N'NodeA', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'C:\Program Files\Microsoft SQL Server\130\COM', @heartbeat_interval = 10, @allow_push = 1, @allow_pull = 1, @allow_anonymous = 0, @immediate_sync = 0, @allow_sync_tran = 0, @autogen_sync_procs = 0, @retention = 0, @sync_rate = 0, @compress_snapshot = 0, @ftp_port = 21, @ftp_subdirectory = NULL, @ftp_login = NULL, @ftp_password = NULL, @allow_subscription_copy = 0, @add_to_active_directory = 0, @repl_freq = N'continuous', @enabled_for_internet = 0, @enabled_for_p2p = 1, @dynamic_snapshot_location = NULL, @use_partition_groups = NULL, @publication_access_list = NULL, @p2p_conflict_retention_period = NULL, @p2p_heartbeat_interval = NULL, @p2p_max_connections = NULL, @p2p_outbound_pull_cleanup = NULL, @p2p_publication = NULL, @p2p_send_timeout = NULL;
EXEC sys.sp_adddistpublisher @publisher = N'NodeB', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'C:\Program Files\Microsoft SQL Server\130\COM', @heartbeat_interval = 10, @allow_push = 1, @allow_pull = 1, @allow_anonymous = 0, @immediate_sync = 0, @allow_sync_tran = 0, @autogen_sync_procs = 0, @retention = 0, @sync_rate = 0, @compress_snapshot = 0, @ftp_port = 21, @ftp_subdirectory = NULL, @ftp_login = NULL, @ftp_password = NULL, @allow_subscription_copy = 0, @add_to_active_directory = 0, @repl_freq = N'continuous', @enabled_for_internet = 0, @enabled_for_p2p = 1, @dynamic_snapshot_location = NULL, @use_partition_groups = NULL, @publication_access_list = NULL, @p2p_conflict_retention_period = NULL, @p2p_heartbeat_interval = NULL, @p2p_max_connections = NULL, @p2p_outbound_pull_cleanup = NULL, @p2p_publication = NULL, @p2p_send_timeout = NULL;
--以NodeA为例，创建节点之间的同步对等关系
USE [distribution]
EXEC sys.sp_addpeeridentity @table_name = 'test'; 
--添加同步节点
EXEC sys.sp_addpeer @peer_id = 2, @peer_network_address = N'TCP://192.168.1.100:5022', @dbname = N'TestDB', @role = N'Peer';
EXEC sys.sp_addpeer @peer_id = 3, @peer_network_address = N'TCP://192.168.1.101:5022', @dbname = N'TestDB', @role = N'Peer';
--开启节点之间的数据同步
EXEC sys.sp_changepublication @publication = 'TestDB', @property = N'allow_peer_conflicts', @value = 1;
EXEC sys.sp_changepublication @publication = 'TestDB', @property = N'pub_frequency', @value = N'continuous';
EXEC sys.sp_changepublication @publication = 'TestDB', @property = N'allow_initiate_sync', @value = N'true';

2.3 基于集中式同步模式

基于集中式同步模式也是一种单向同步模式，主要是通过集中式调度来同步数据。一般来说，数据同步的流程应该是：先将数据上传到中心服务器，再通过中心服务器下发到其他从服务器，从而保证数据在整个分布式系统中的一致性。

MSSQL数据库中，可以使用SQL Server Integration Services (SSIS)来实现集中式同步。SSIS是一个数据仓库工具，用于数据的提取、转换和加载。通过使用SSIS，可以实现多个数据库之间的数据同步。

--创建数据仓库
USE master;
GO 
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'AdventureWorksDW2012')
BEGIN 
    CREATE DATABASE [AdventureWorksDW2012] 
END 
GO 
USE [AdventureWorksDW2012] 
GO 
--在单个数据库中创建多张表
CREATE TABLE [dbo].[FactInternetSales]
(
    [ProductKey] [int] NOT NULL,
    [OrderDateKey] [int] NOT NULL, 
    [DueDateKey] [int] NOT NULL, 
    [ShipDateKey] [int] NOT NULL, 
    [CustomerKey] [int] NOT NULL, 
    [PromotionKey] [int] NOT NULL, 
    [SalesOrderNumber] [nvarchar](20) NOT NULL, 
    [SalesOrderLineNumber] [tinyint] NOT NULL, 
    [SalesQuantity] [smallint] NOT NULL, 
    [SalesAmount] [money] NOT NULL, 
    [DiscountAmount] [money] NOT NULL, 
    [ProductStandardCost] [money] NOT NULL, 
    [TotalProductCost] [money] NOT NULL, 
    [TaxAmt] [money] NOT NULL, 
    [Freight] [money] NOT NULL 
); 
GO 
CREATE TABLE [dbo].[FactResellerSales]
(
    [ProductKey] [int] NOT NULL, 
    [OrderDateKey] [int] NOT NULL, 
    [DueDateKey] [int] NOT NULL, 
    [ShipDateKey] [int] NOT NULL, 
    [ResellerKey] [int] NOT NULL, 
    [EmployeeKey] [int] NOT NULL, 
    [PromotionKey] [int] NOT NULL, 
    [SalesOrderNumber] [nvarchar](20) NOT NULL, 
    [SalesOrderLineNumber] [tinyint] NOT NULL, 
    [SalesQuantity] [smallint] NOT NULL, 
    [SalesAmount] [money] NOT NULL, 
    [DiscountAmount] [money] NOT NULL, 
    [ProductStandardCost] [money] NOT NULL, 
    [TotalProductCost] [money] NOT NULL, 
    [TaxAmt] [money] NOT NULL, 
    [Freight] [money] NOT NULL 
); 
GO 

3. 总结

在MSSQL数据库中，数据同步主要由三种模式实现：基于事务复制的同步模式、基于对等复制的同步模式和基于集中式同步模式。不同的同步模式适用于不同的场景，可以根据实际需求进行选择。同时，在进行数据同步时，也需要注意保证数据的安全性、一致性和可恢复性。