SQL Server Replication-优快云博客

本文链接：https://blog.youkuaiyun.com/u013606536/article/details/145162818

文章目录

概要

SQL Server eplication 是 Microsoft SQL Server 数据库管理系统中的一项技术，它允许将数据和数据库对象从一个数据库复制到一个或多个其他数据库。这项技术的主要目的是为了提高数据的可用性、灵活性和可扩展性，同时还可以用于数据分发、负载均衡和灾难恢复等场景。

SQL Server 复制主要分为以下几种类型：

事务复制（Transaction Replication）
快照复制（Snapshot Replication）
合并复制（Merge Replication）

整体架构流程(事务复制)

sp_adddistributor
sp_MSupdate_agenttype_default
sp_adddistributiondb
sp_addqreader_agent
sp_adddistpublisher
sp_addsubscriber
sp_replicationdboption
sp_addlogreader_agent
sp_addqreader_agent
sp_addpublication
sp_addpublication_snapshot
sp_grant_publication_access
sp_addarticle
sp_addsubscription
sp_addpushsubscription_agent

技术名词解释

sp_adddistributor : 将服务器条目标记为分发服务器，并存储属性信息。原文链接

use master
exec sp_adddistributor @distributor = N'分发服务器名称', @password = N'登录名的密码'

sp_MSupdate_agenttype_default : 添加代理配置文件、更新代理配置文件默认值

exec sp_MSupdate_agenttype_default @profile_id = 1
GO
exec sp_MSupdate_agenttype_default @profile_id = 2
GO
exec sp_MSupdate_agenttype_default @profile_id = 4
GO
exec sp_MSupdate_agenttype_default @profile_id = 6
GO
exec sp_MSupdate_agenttype_default @profile_id = 11

sp_adddistributiondb : 创建新的分发数据库并安装分发服务器架构。分发数据库存储过程、架构以及用于复制的元数据。原文链接

use master
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data', @data_file = N'distribution.MDF', @data_file_size = 18509, @log_folder = N'D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data', @log_file = N'distribution.LDF', @log_file_size = 32393, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @deletebatchsize_xact = 5000, @deletebatchsize_cmd = 2000, @security_mode = 1
exec [distribution].sys.sp_addqreader_agent @job_login = N'Windows 帐户的登录名', @job_password = N'密码'

sp_adddistpublisher : 配置发布服务器以使用指定的分发数据库。原文链接

exec sp_adddistpublisher @publisher = N'分发服务器名称', @distribution_db = N'distribution', @security_mode = 0, @login = N'数据库登录名', @password = N'密码', @working_directory = N'\\分发服务器名称\Repldata', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'

sp_addsubscriber : 向发布服务器添加新的订阅服务器，使其能够接收发布。原文链接

exec sp_addsubscriber @subscriber = N'订阅服务器名称', @type = 0, @description = N'订阅服务器的文本说明'

sp_replicationdboption : 设置指定数据库的复制数据库选项。。原文链接

use master
exec sp_replicationdboption @dbname = N'复制数据库名称', @optname = N'publish', @value = N'true'

sp_addlogreader_agent : 为给定数据库添加日志读取器代理。原文链接

exec [复制数据库名称].sys.sp_addlogreader_agent @job_login = N'Windows 帐户的登录名', @job_password = N'密码', @publisher_security_mode = 0, @publisher_login = N'数据库登录名', @publisher_password = N'密码'

sp_addqreader_agent : 为给定分发服务器添加队列读取器代理。原文链接

exec [复制数据库名称].sys.sp_addqreader_agent @job_login = N'Windows 帐户的登录名', @job_password = N'密码', @frompublisher = 1

sp_addpublication : 创建快照或事务发布。原文链接

exec sp_addpublication @publication = N'事务发布名称', @description = N'文本说明', @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'

sp_addpublication_snapshot : 为指定的发布创建快照代理。原文链接

exec sp_addpublication_snapshot @publication = N'事务发布名称', @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 = N'Windows 帐户的登录名', @job_password = N'密码', @publisher_security_mode = 0, @publisher_login = N'数据库登录名', @publisher_password = N'密码'

sp_grant_publication_access : 将登录名添加到发布的访问列表中。原文链接

exec sp_grant_publication_access @publication = N'SystemServer_REPL_DWH', @login = N'sa'
GO
exec sp_grant_publication_access @publication = N'SystemServer_REPL_DWH', @login = N'Windows 帐户的登录名'
GO
exec sp_grant_publication_access @publication = N'SystemServer_REPL_DWH', @login = N'NT SERVICE\Winmgmt'
GO
exec sp_grant_publication_access @publication = N'SystemServer_REPL_DWH', @login = N'NT SERVICE\SQLWriter'
GO
exec sp_grant_publication_access @publication = N'SystemServer_REPL_DWH', @login = N'NT SERVICE\SQLSERVERAGENT'
GO
exec sp_grant_publication_access @publication = N'SystemServer_REPL_DWH', @login = N'NT Service\MSSQLSERVER'
GO
exec sp_grant_publication_access @publication = N'SystemServer_REPL_DWH', @login = N'distributor_admin'
GO
exec sp_grant_publication_access @publication = N'SystemServer_REPL_DWH', @login = N'数据库登录名'
GO

sp_addarticle : 创建项目并将其添加到发布中(复数次执行)。原文链接

use [复制数据库名称]
exec sp_addarticle @publication = N'事务发布名称', @article = N'项目的名称', @source_owner = N'dbo', @source_object = N'表名称', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'目标表名称', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbo表名称]', @del_cmd = N'CALL [sp_MSdel_dbo表名称]', @upd_cmd = N'SCALL [sp_MSupd_dbo表名称]'

sp_addsubscription : 将订阅添加到发布并设置订阅服务器的状态。原文链接

use [复制数据库名称]
exec sp_addsubscription @publication = N'事务发布名称', @subscriber = N'订阅服务器的名称', @destination_db = N'目标数据库的名称', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0

sp_addpushsubscription_agent : 添加新的预定代理作业，以使推送订阅与事务发布同步。原文链接

exec sp_addpushsubscription_agent @publication = N'事务发布名称', @subscriber = N'订阅服务器的名称', @subscriber_db = N'目标数据库的名称', @job_login = N'Windows 帐户的登录名', @job_password = N'密码', @subscriber_security_mode = 0, @subscriber_login = N'数据库登录名', @subscriber_password = N'密码', @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'