当前位置:Gxlcms > 数据库问题 > SqlServer 使用脚本创建分发服务及事务复制的可更新订阅

SqlServer 使用脚本创建分发服务及事务复制的可更新订阅

时间:2021-07-01 10:21:17 帮助过:16人阅读

  1. /************************【使用本地分发服务器配置发布】***********************/
  2. -- SqlServer 2008 R2
  3. -- https://technet.microsoft.com/zh-cn/library/ms151860(v=sql.105).aspx
  4. use master
  5. go
  6. -- 服务器上是否已安装分发服务器
  7. -- https://msdn.microsoft.com/zh-cn/library/ms190339(v=sql.105).aspx
  8. exec master.dbo.sp_get_distributor
  9. go
  10. -- 配置分发服务器
  11. -- https://msdn.microsoft.com/zh-cn/library/ms176028(v=sql.105).aspx
  12. exec master.dbo.sp_adddistributor
  13. @distributor = 'KK-PC' --分发服务器名称
  14. ,@heartbeat_interval = 10 --代理在不记录进度消息的情况下可以运行的最长分钟数
  15. ,@password = N'123456' --分发服务器密码
  16. go
  17. -- 配置分发数据库
  18. -- https://msdn.microsoft.com/zh-cn/library/ms189755(v=sql.105).aspx
  19. exec master.dbo.sp_adddistributiondb
  20. @database = N'distribution' --要创建的分发数据库的名称
  21. ,@data_folder = N'E:\TempFile\Distribution' --分发数据库数据文件的目录
  22. ,@data_file = N'distribution' --数据库文件的名称
  23. ,@data_file_size = 5 --初始数据文件大小,以兆字节 (MB) 为单位
  24. ,@log_folder = N'E:\TempFile\Distribution' ----分发数据库日志文件的目录
  25. ,@log_file = N'distribution_log'
  26. ,@log_file_size = 5 --初始日志文件大小,以兆字节 (MB) 为单位
  27. ,@min_distretention = 0 --从分发数据库中删除事务前的最小保持期,以小时为单位
  28. ,@max_distretention = 72 --删除事务前的最大保持期,以小时为单位
  29. ,@history_retention = 48 --历史记录的保留时间,以小时为单位
  30. ,@security_mode = 1 --同步时连接到分发服务器的安全模式。默认值为1:Windows验证,0: SQL验证
  31. ,@login = N'KK-PC\SqlReplicator'
  32. ,@password = N'123456'
  33. ,@createmode = 1 --1:创建或使用现有数据库(instdist.sql)
  34. go
  35. -- 配置发布服务器以使用指定的分发数据库
  36. -- https://msdn.microsoft.com/zh-cn/library/ms173807(v=sql.105).aspx
  37. exec master.dbo.sp_adddistpublisher
  38. @publisher = N'KK-PC' --发布服务器的名称
  39. ,@distribution_db = N'distribution' --分发数据库的名称
  40. ,@security_mode = 1 --安全模式,默认1:Windows验证,0: SQL验证
  41. ,@login = N'KK-PC\SqlReplicator'
  42. ,@password = N'123456'
  43. ,@working_directory = N'E:\TempFile\ReplData'--默认快照文件夹的UNC共享目录
  44. ,@thirdparty_flag = 0 --发布服务器是否是SQLServer,默认0:是,1:否
  45. ,@publisher_type = N'MSSQLSERVER' --发布服务器类型:MSSQLSERVER(默认)/ORACLE/ORACLE GATEWAY
  46. go
  47. /*****配置完成!!*****/


  1. /*****【查看分发属性】******/
  2. --
  3. 检查分发数据库目录
  4. exec master.dbo.xp_subdirs N'E:\TempFile\Distribution'
  5. --
  6. 发布服务器的属性(在分发服务器任何数据库执行)
  7. --
  8. https://technet.microsoft.com/zh-cn/library/ms190323(v=sql.105).aspx
  9. exec master.dbo.sp_helpdistpublisher N'KK-PC'
  10. --
  11. 分发数据库的属性(在分发服务器的分发数据库上执行)
  12. --
  13. https://msdn.microsoft.com/zh-cn/library/vstudio/aa238917.aspx
  14. exec master.dbo.sp_helpdistributiondb N'distribution'
  15. --
  16. 列出有关分发服务器相关信息(在分发服务器任何数据库执行)
  17. --
  18. https://msdn.microsoft.com/zh-cn/library/ms177504(v=sql.105).aspx
  19. exec master.dbo.sp_helpdistributor



  1. /*****【删除分发】******/
  2. --这里不必执行!
  3. --
  4. 删除分发发布服务器(在分发服务器任何数据库执行)
  5. --
  6. https://technet.microsoft.com/zh-cn/library/ms188411(v=sql.105).aspx
  7. exec master.dbo.sp_dropdistpublisher @publisher = N'KK-PC',@no_checks = 0,@ignore_distributor = 0 --检查对象;连接分发;
  8. --
  9. 删除分发数据库(在分发服务器任何数据库执行)
  10. --
  11. https://msdn.microsoft.com/zh-cn/library/ms188355(v=sql.105).aspx
  12. exec master.dbo.sp_dropdistributiondb N'distribution';
  13. --
  14. 卸载分发服务器(除分发数据库之外的任何数据库中执行)
  15. --
  16. https://technet.microsoft.com/zh-cn/library/ms173516(v=sql.105).aspx
  17. exec master.dbo.sp_dropdistributor @no_checks = 0,@ignore_distributor = 0 --检查对象;连接分发;


技术分享图片



【创建可更新订阅的事务发布】


  1. /*************************************【创建发布】**************************************/
  2. /*【实例:可更新订阅】
  3. * A 为发布数据库,id为每个表的主键
  4. * B,C 为订阅数据库
  5. * A中符合条件 [id % 2 = 0] 的同步到B中
  6. * A中符合条件 [id % 2 = 1] 的同步到C中
  7. * 以下以 B 创建发布订阅
  8. 当前发布数据库:[mytest]
  9. 当前订阅数据库:[mytestA]
  10. */
  11. --
  12. 作为发布的数据库
  13. use [mytest]
  14. --
  15. 设置指定数据库的复制数据库选项(发布服务器或订阅服务器执行)
  16. --
  17. http://msdn.microsoft.com/zh-cn/library/ms188769.aspx
  18. exec sys.sp_replicationdboption @dbname = N'mytest', @optname = N'publish', @value = N'true'
  19. go
  20. --
  21. 为给定分发服务器添加队列读取器代理(在分发库或发布库执行)(每个实例默认只1个,已存在可不须再执行)
  22. --
  23. http://msdn.microsoft.com/ZH-CN/LIBRARY/ms189517
  24. exec sys.sp_addqreader_agent @job_login = N'KK-PC\SqlReplicator', @job_password = N'123456', @job_name = null, @frompublisher = 1
  25. go
  26. --
  27. 为给定数据库添加日志读取器代理(在发布数据库执行)(每个数据库默认只1个,已存在可不须再执行)
  28. --
  29. http://technet.microsoft.com/zh-cn/library/ms189516.aspx
  30. exec sys.sp_helplogreader_agent @publisher = null --查看当前数据库存在的日志代理
  31. exec sys.sp_addlogreader_agent @job_login = N'KK-PC\SqlReplicator', @job_password = N'123456', @publisher_security_mode = 1, @job_name = null
  32. go
  33. /***************************【创建发布:以下用于1库多发布】******************************/
  34. --
  35. 添加(可更新订阅)事务发布(在发布数据库执行)
  36. --
  37. http://msdn.microsoft.com/zh-cn/library/ms188738(v=sql.100).aspx
  38. exec sys.sp_addpublication
  39. @publication = N'tran_repl',
  40. --【指定发布名称】
  41. @description = N'来自发布服务器“”的数据库“mytest”的具有可更新订阅的事务发布。',
  42. @sync_method = N'concurrent',
  43. --同步模式:本机模式大容量复制程序输出
  44. @retention = 0,
  45. --订阅活动的保持期(小时):默认值为336小时;0:订阅永不过期
  46. @allow_push = N'true',
  47. --推送订阅
  48. @allow_pull = N'true',
  49. --允许创建请求订阅
  50. @allow_anonymous = N'false',
  51. --不可创建匿名订阅
  52. @enabled_for_internet = N'false',
  53. --非Internet发布
  54. @snapshot_in_defaultfolder = N'false',
  55. --不指定快照默认文件夹,须设置@alt_snapshot_folder
  56. @alt_snapshot_folder = N'E:\TempFile\ReplData',
  57. --指定快照的备用文件夹的位置
  58. @compress_snapshot = N'false',
  59. --不压缩快照
  60. @ftp_port = 21,
  61. --默认分发服务器的FTP服务的端口号:21
  62. @ftp_login = N'anonymous',
  63. --默认用于连接到 FTP 服务的用户名:anonymous
  64. @allow_subscription_copy = N'false',
  65. --禁用复制订阅此发布的订阅数据库
  66. @add_to_active_directory = N'false',
  67. --(已不推荐使用)
  68. @repl_freq = N'continuous',
  69. --复制频率的类型:基于日志的事务的输出
  70. @status = N'active',
  71. --发布数据可立即用于订阅服务器
  72. @independent_agent = N'true',
  73. --【独立分发代理】
  74. @immediate_sync = N'false',
  75. --每次运行快照代理时不为发布创建同步文件
  76. @allow_sync_tran = N'true',
  77. --允许使用【立即更新订阅】
  78. @autogen_sync_procs = N'true',
  79. --在发布服务器上生成更新订阅的【同步存储过程】
  80. @allow_queued_tran = N'true',
  81. --在订阅服务器中启用更改的队列
  82. @allow_dts = N'false',
  83. --不允许数据转换
  84. @conflict_policy = N'sub wins',
  85. --排队更新订阅服务器选项时所遵从的冲突解决策略:【订阅入选】
  86. @centralized_conflicts = N'true',
  87. --在发布服务器上存储冲突记录
  88. @conflict_retention = 14,
  89. --冲突保持期(天)
  90. @queue_type = N'sql',
  91. --使用的队列类型:默认SQL Server存储事务
  92. @replicate_ddl = 1,
  93. --【支持架构复制】
  94. @allow_initialize_from_backup = N'false', --不允许用备份初始化订阅
  95. @enabled_for_p2p = N'false',
  96. --非对等复制
  97. @enabled_for_het_sub = N'false'
  98. --只支持SQL Server订阅服务器
  99. go
  100. --
  101. 为指定的发布创建快照代理(在发布数据库执行)
  102. --
  103. http://msdn.microsoft.com/zh-cn/library/ms174958(v=sql.100)
  104. exec sys.sp_addpublication_snapshot
  105. @publication = N'tran_repl',
  106. @frequency_type = 1,
  107. @frequency_interval = 0,
  108. @frequency_relative_interval = 0,
  109. @frequency_recurrence_factor = 0,
  110. @frequency_subday = 0,
  111. @frequency_subday_interval = 0,
  112. @active_start_time_of_day = 0,
  113. @active_end_time_of_day = 235959,
  114. @active_start_date = 0,
  115. @active_end_date = 0,
  116. @job_login = N'KK-PC\SqlReplicator',
  117. @job_password = N'123456',
  118. @publisher_security_mode = 1
  119. go
  120. --
  121. 更改发布属性
  122. --
  123. https://msdn.microsoft.com/zh-cn/library/ms188413(v=sql.105).aspx
  124. --
  125. exec sys.sp_changepublication ……


技术分享图片

  1. --
  2. 创建项目并将其添加到发布中(在发布数据库执行)
  3. --
  4. http://msdn.microsoft.com/zh-cn/library/ms173857
  5. /*1. 添加可筛选的表(默认架构dbo)*/
  6. declare @tableName
  7. nvarchar(100)
  8. declare @publName
  9. nvarchar(100)
  10. declare @mark
  11. bit
  12. -- 区分是否有sid的列,有则进行筛选
  13. declare @filterNum
  14. nvarchar(10)-- 一个数据库多个发布加编号区别
  15. declare @filterClause
  16. nvarchar(100)
  17. declare @SQLaddarticle
  18. nvarchar(max)
  19. declare @SQLarticlefilter
  20. nvarchar(max)
  21. declare @SQLarticleview
  22. nvarchar(max)
  23. set @publName = N'tran_repl' --
  24. 【指定发布名称】
  25. set @filterClause = N'dbo.f_SIDTOInt(SID) % 2 = 0' --
  26. 【指定行筛选】
  27. select @filterNum = CONVERT(NVARCHAR(10),count(*)) from distribution.dbo.MSpublications
  28. declare cur_addTable cursor local fast_forward
  29. for
  30. /*有主键 并且 有SID列(用于筛选)*/
  31. select name,1 mark from sys.tables t1(nolock) where is_ms_shipped = 0
  32. and exists(select 1 from sys.columns t2(nolock) where t1.object_id=t2.object_id and t2.name='SID')
  33. and name in(select table_name from information_schema.key_column_usage(nolock)
  34. where objectproperty(object_id(constraint_name),'isprimarykey')=1 )
  35. union all
  36. /*有主键 并且 无SID列(不可筛选)*/
  37. select name,0 mark from sys.tables t1(nolock) where is_ms_shipped = 0
  38. and not exists(select 1 from sys.columns t2(nolock) where t1.object_id=t2.object_id and t2.name='SID')
  39. and name in(select table_name from information_schema.key_column_usage (nolock)
  40. where objectproperty(object_id(constraint_name),'isprimarykey')=1 )
  41. open cur_addTable
  42. fetch next from cur_addTable into @tableName,@mark
  43. while @@fetch_status = 0
  44. begin
  45. if ( @mark = 1 ) /*可筛选的表对象*/
  46. begin
  47. set @SQLaddarticle = N'
  48. exec sp_addarticle
  49. @publication = N'''+@publName+''',
  50. @article = N'''+@tableName+''',
  51. @source_owner = N''dbo'',
  52. @source_object = N'''+@tableName+''',
  53. @type = N''logbased'',
  54. @description = null,
  55. @creation_script = null,
  56. @pre_creation_cmd = N''drop'',
  57. @schema_option = 0x0000000008035CDF,
  58. @identityrangemanagementoption = N''none'',
  59. @destination_table = N'''+@tableName+''',
  60. @destination_owner = N''dbo'',
  61. @status = 24,
  62. @vertical_partition = N''false'''
  63. exec(@SQLaddarticle)
  64. /*添加项目筛选器*/
  65. set @SQLarticlefilter = N'
  66. exec sp_articlefilter
  67. @publication = N'''+@publName+''',
  68. @article = N'''+@tableName+''',
  69. @filter_name = N''FLTR_'+@tableName+'_'+@filterNum+'__'+rtrim(ltrim(str(@@spid)))+''',
  70. @filter_clause = N'''+@filterClause+''',
  71. @force_invalidate_snapshot = 1,
  72. @force_reinit_subscription = 1'
  73. exec(@SQLarticlefilter)
  74. /*添加项目同步对象*/
  75. set @SQLarticleview = N'
  76. exec sp_articleview
  77. @publication = N'''+@publName+''',
  78. @article = N'''+@tableName+''',
  79. @view_name = N''SYNC_'+@tableName+'_'+@filterNum+'__'+rtrim(ltrim(str(@@spid)))+''',
  80. @filter_clause = N'''+@filterClause+''',
  81. @force_invalidate_snapshot = 1,
  82. @force_reinit_subscription = 1'
  83. exec(@SQLarticleview)
  84. print '已筛选:'+@tableName
  85. end
  86. else if ( @mark = 0 )
  87. begin
  88. set @SQLaddarticle = N'
  89. exec sp_addarticle
  90. @publication = N'''+@publName+''',
  91. @article = N'''+@tableName+''',
  92. @source_owner = N''dbo'',
  93. @source_object = N'''+@tableName+''',
  94. @type = N''logbased'',
  95. @description = null,
  96. @creation_script = null,
  97. @pre_creation_cmd = N''drop'',
  98. @schema_option = 0x0000000008035CDF,
  99. @identityrangemanagementoption = N''none'',
  100. @destination_table = N'''+@tableName+''',
  101. @destination_owner = N''dbo'',
  102. @status = 24,
  103. @vertical_partition = N''false'''
  104. exec(@SQLaddarticle)
  105. print '无筛选:'+@tableName
  106. end
  107. fetch next from cur_addTable into @tableName,@mark
  108. end
  109. close cur_addTable
  110. deallocate cur_addTable



  1. /*2. 添加视图/存储过程/函数对象(默认架构dbo)*/
  2. declare @publName
  3. nvarchar(100)
  4. declare @ObjectName
  5. nvarchar(100)
  6. declare @Type
  7. nvarchar(30)
  8. declare @ObjectType
  9. nvarchar(30)
  10. declare @SQLaddObject
  11. nvarchar(max)
  12. set @publName = N'tran_repl' --
  13. 【指定发布名称】
  14. declare cur_addObject cursor local fast_forward
  15. for select name,type from mytest.sys.objects where type in(N'P') and is_ms_shipped = 0
  16. union all
  17. select name,type from mytest.sys.objects a where type in(N'V') and is_ms_shipped = 0
  18. and exists(select 1 from mytest.sys.sql_modules b where a.object_id=b.object_id and is_schema_bound = 0)
  19. union all
  20. select name,N'B' as type from mytest.sys.objects a where type in(N'V') and is_ms_shipped = 0
  21. and exists(select 1 from sys.sql_modules b where a.object_id=b.object_id and is_schema_bound = 1)/*架构绑定的索引视图*/
  22. union all
  23. select name,type from mytest.sys.objects where type in(N'TF',N'FN') and is_ms_shipped = 0
  24. order by type,name
  25. open cur_addObject
  26. fetch next from cur_addObject into @ObjectName,@ObjectType
  27. while @@fetch_status = 0
  28. begin
  29. SET @Type =
  30. (
  31. CASE
  32. WHEN @ObjectType = N'V' THEN N'view schema only'
  33. WHEN @ObjectType = N'B' THEN N'indexed view schema only'
  34. WHEN @ObjectType = N'P' THEN N'proc schema only'
  35. WHEN @ObjectType in(N'TF',N'FN') THEN N'func schema only'
  36. END
  37. )
  38. set @SQLaddObject = N'
  39. exec sp_addarticle
  40. @publication = N'''+@publName+''',
  41. @article = N'''+@ObjectName+''',
  42. @source_owner = N''dbo'',
  43. @source_object = N'''+@ObjectName+''',
  44. @type = N'''+@Type+''',
  45. @description = null,
  46. @creation_script = null,
  47. @pre_creation_cmd = N''drop'',
  48. @schema_option = 0x0000000008000001,
  49. @status = 16,
  50. @destination_owner = N''dbo'',
  51. @destination_table = N'''+@ObjectName+''''
  52. exec(@SQLaddObject)
  53. print @ObjectType+ ':' + @ObjectName
  54. fetch next from cur_addObject into @ObjectName,@ObjectType
  55. end
  56. close cur_addObject
  57. deallocate cur_addObject
技术分享图片技术分享图片


【创建订阅】

  1. /***********************************【创建订阅】***************************************/
  2. /*【要在发布服务器上运行的脚本】*/
  3. use [mytest]
  4. --
  5. 将订阅添加到发布并设置订阅服务器的状态
  6. --
  7. (警告: distribution 代理作业隐式创建,并将在 SQL Server 代理服务帐户下运行)
  8. --
  9. http://technet.microsoft.com/zh-cn/library/ms181702(v=sql.100).aspx
  10. exec sys.sp_addsubscription
  11. @publication = N'tran_repl',
  12. --指定发布名称
  13. @subscriber = N'KK-PC',
  14. --订阅服务器
  15. @destination_db = N'mytestA',--订阅数据库
  16. @subscription_type = N'Push',
  17. --推送订阅
  18. @sync_type = N'automatic',
  19. --默认,已发布表的架构和初始数据将首先传输到订阅服务器
  20. @article = N'all',
  21. --发布所订阅的项目
  22. @update_mode = N'queued failover',
  23. --将订阅启用为排队更新订阅,并允许更改为立即更新模式
  24. @subscriber_type = 0
  25. --订阅服务器的类型:SQL Server订阅服务器
  26. go
  27. --
  28. 添加新的预定分发代理作业,以使推送订阅与事务发布同步
  29. --
  30. http://msdn.microsoft.com/zh-cn/library/ms175006(v=SQL.100)
  31. exec sys.sp_addpushsubscription_agent
  32. @publication = N'tran_repl',
  33. --指定发布名称
  34. @subscriber = N'KK-PC',
  35. --订阅服务器
  36. @subscriber_db = N'mytestA',
  37. --订阅数据库
  38. @job_login = N'KK-PC\SqlReplicator',
  39. @job_password = N'123456',
  40. @subscriber_security_mode = 1,
  41. --Windows 身份验证
  42. @frequency_type = 64,
  43. --分发代理计划的频率:自动启动(默认)
  44. @frequency_interval = 1,
  45. @frequency_relative_interval = 1,
  46. @frequency_recurrence_factor = 0,
  47. @frequency_subday = 4,
  48. @frequency_subday_interval = 5,
  49. @active_start_time_of_day = 0,
  50. @active_end_time_of_day = 235959,
  51. @active_start_date = 0,
  52. @active_end_date = 0,
  53. @dts_package_location = N'Distributor'
  54. go


  1. /*【要在订阅服务器上运行的脚本】*/
  2. use mytestA
  3. --
  4. 设置在连接到发布服务器时立即更新订阅的同步触发器所使用的配置和安全信息
  5. --
  6. http://msdn.microsoft.com/zh-cn/library/ms174991(v=sql.100).aspx
  7. exec sys.sp_link_publication
  8. @publisher = N'KK-PC',
  9. @publisher_db = N'mytest',
  10. @publication = N'tran_repl',
  11. @distributor = N'KK-PC',
  12. @security_mode = 1,
  13. --SQL Server 身份验证或 Windows 身份验证
  14. @login = N'KK-PC\SqlReplicator',
  15. @password = N'123456'
  16. go



【启动快照并初始化】

  1. /**********************************【启动快照并初始化】**************************************/
  2. --
  3. 发布服务器发布数据
  4. use [mytest]
  5. --
  6. 对其请求订阅中的新项目的订阅添加到发布中(在发布服务器的发布数据库中执行)
  7. --
  8. https://technet.microsoft.com/zh-cn/library/ms181680(v=sql.100).aspx
  9. --
  10. exec sys.sp_refreshsubscriptions @publication = N'tran_repl'
  11. --
  12. 将订阅标记为要重新初始化
  13. --
  14. https://msdn.microsoft.com/zh-cn/library/ms189469(v=sql.100).aspx
  15. exec sys.sp_reinitsubscription
  16. @publication = N'tran_repl',
  17. @subscriber = N'KK-PC',
  18. @destination_db = N'mytestA',
  19. @article = N'all'
  20. go
  21. --
  22. 启动可为发布生成初始快照的快照代理作业(在发布服务器的发布数据库中执行)
  23. --
  24. http://msdn.microsoft.com/zh-cn/library/ms176026(v=sql.105).aspx
  25. exec sys.sp_startpublication_snapshot @publication = N'tran_repl'
  26. go
  27. --
  28. 为所有表项目编写自定义 sp_MSins、sp_MSupd 和 sp_MSdel 过程的脚本
  29. --
  30. https://msdn.microsoft.com/zh-cn/library/ms187946(SQL.100).aspx
  31. --exec sys.sp_scriptpublicationcustomprocs 'tran_repl'
  32. --go
  33. --
  34. 至此,完成发布订阅!!!~ 初始化完成!!~



技术分享图片技术分享图片



之前测试了N遍!!!~~今晚有空终于成功处理了!~




SqlServer 使用脚本创建分发服务及事务复制的可更新订阅

标签:gre   info   tom   tar   snippet   detail   port   ide   null   

人气教程排行