深入MSSQL复制

MSSQL 数据库手册 Replication 1 / 10 深入 MSSQL 复制 1. 影响 Transaction 复制的因素 * 滞后时间： 在复制拓扑中的节点之间传播数据更改所用的时间。 * 吞吐量： 系统在某段时间内可以承受的复制活动量（通过某个时间段内传递的命令数量来度量）。 * 并发： 可以在系统上同时运行的复制进程数。 * 同步持续时间： 完成给定同步所用的时间。 * 资源消耗： 用作复制处理结果的硬件和网络资源。 可以通过 windows 性能计数器或者 复制监控器 来抓去相关数据. 滞后时间和吞吐量与事务复制关系最密切，因为建立在事务复制基础上的系统通常需要低滞后时间和大吞 吐量。 并发和同步持续时间与合并复制关系最密切，因为建立在合并复制基础上的系统通常具有大量的订 阅服务器，并且发布服务器可能具有大量与这些订阅服务器的并发同步。 提高常规复制的性能 * 设置分配给 Microsoft SQL Server 数据库引擎的最小和最大内存量。 数据库引擎根据可用的系统资源动态改变它的内存要求。为避免在复制活动期间出现低内存可用性 的问题，请使用 min server memory 选项设置最小可用内存。为避免将操作系统页写入磁盘以节省内存， 也可以使用 max server memory 选项设置最大内存量。 * 确保正确分配数据库数据文件和日志文件。使用单独的磁盘驱动器存放复制过程中所涉及的所有数 据库的事务日志。 通过将日志文件与数据库存储在不同的磁盘驱动器上，可以减少写入事务所需的时间。如果需要容 错，可以使用独立冗余磁盘阵列 (RAID)1+0 * 考虑增加复制所用的服务器的内存，尤其是分发服务器的内存。 * 使用多处理器计算机。 复制代理可以利用服务器中的附加处理器。如果运行时 CPU 使用很高，请考虑安装更快的 CPU 或 多个 CPU。 * 使用快速网络。 网络可能是一个重要的性能瓶颈，尤其是对于事务复制而言。需满足每秒 100 兆位 (Mbps) 或更 快的网络，可以显著提高将更改传播到订阅服务器的速度。 数据库设计 * 遵循数据库设计最佳实践。 复制数据库通常能够从性能优化中获得与非复制数据库一样的好处。但是，在订阅服务器中使用索 引时应谨慎：应为订阅服务器中的主键列建立索引，但附加的索引可能影响插入、更新和删除操作的性能。 * 考虑设置 READ_COMMITTED_SNAPSHOT 数据库选项。 为帮助减少用户活动与复制代理活动之间的争用，请为发布和订阅数据库设置下列选项： ALTER DATABASE AdventureWorks SET READ_COMMITTED_SNAPSHOT ON MSSQL 数据库培训手册 Replication 2 / 10 下面提供通过 SQL 脚本来抓去相关复制的性能数据: /*****************************************************************************************/ /*Create DataBase*/ Create Database DBCenter; /* DROP TABLE*/ USE dbcenter; IF OBJECT_ID(N'CounterData') IS NOT NULL DROP TABLE CounterData; IF OBJECT_ID(N'CounterDETAILS') IS NOT NULL DROP TABLE CounterDETAILS; IF OBJECT_ID(N'DisplayToID') IS NOT NULL DROP TABLE DisplayToID; /*CREATE PERFORMANCE*/ DECLARE @LogmanName SYSNAME ,@ServerNameAll SYSNAME ,@ServerName SYSNAME ,@DnsLoginName SYSNAME ,@Path SYSNAME ; SELECT @LogmanName='Performance'; SELECT @ServerNameAll = @@SERVERNAME; SELECT @ServerName = CASE WHEN CHARINDEX('\',@ServerNameAll) > 0 THEN LEFT(@ServerNameAll,CHARINDEX('\',@ServerNameAll)-1) ELSE @ServerNameAll END; SELECT @DnsLoginName = CASE WHEN CHARINDEX('\',@@SERVERNAME) > 0 THEN '.'+RIGHT(@@SERVERNAME,LEN(@@SERVERNAME) -CHARINDEX('\',@@SERVERNAME)+1) ELSE '.' END; SELECT @Path = 'C:\PerformanceConfigure_'+@ServerName+'_' +REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(19),GETDATE(),21),'-',''),':',''),' ','')+'.TXT' ----------------------------1.配置ODBC-------------------------- --配置ODBC EXECUTE('Master.dbo.xp_cmdshell ''odbcconf CONFIGSYSDSN "sql server" "DSN=' +@LogmanName+'|SERVER=' +@DnsLoginName+'|Database=dbcenter|Trusted_Connection=yes"''') --CREATE TempTable --本机上安装的所有性能计数器对象 MSSQL 数据库培训手册 Replication 3 / 10 IF OBJECT_ID(N'TEMPDB..Temp_PermonAll','U')IS NOT NULL DROP TABLE TEMPDB..Temp_PermonAll; CREATE TABLE TempDB.dbo.Temp_PermonAll(VarStr NVARCHAR(2000)) --用户自定义性能收集状态 IF OBJECT_ID(N'TEMPDB..Temp_PerformanceLogmanStatus','U')IS NOT NULL DROP TABLE TEMPDB..Temp_PerformanceLogmanStatus CREATE TABLE TempDB.dbo.Temp_PerformanceLogmanStatus([Content] NVARCHAR(2000)) --本机需要收集的性能计数器对象 IF OBJECT_ID(N'TEMPDB..Temp_PerformanceConfigure','U')IS NOT NULL DROP TABLE TEMPDB..Temp_PerformanceConfigure CREATE TABLE TempDB.dbo.Temp_PerformanceConfigure(VarStr NVARCHAR(2000)) --本机实际部署完成的性能计数器对象 IF OBJECT_ID(N'TEMPDB..Temp_PerformanceConfigureDone','U')IS NOT NULL DROP TABLE TEMPDB..Temp_PerformanceConfigureDone CREATE TABLE TempDB.dbo.Temp_PerformanceConfigureDone (VarStr NVARCHAR(2000)) --收集本机上所有的性能计数器 INSERT INTO TempDB.dbo.Temp_PermonAll EXEC('Master..XP_CMDSHELL ''TypePerf -qx''') --TempDB.dbo.Temp_PerformanceConfigure --Step1:通用性能计数器 INSERT INTO TempDB.dbo.Temp_PerformanceConfigure(VarStr) SELECT VarStr FROM TempDB.dbo.Temp_PermonAll(NOLOCK) where VarStr in ( '\PhysicalDisk(_Total)\Avg. Disk Queue Length', '\PhysicalDisk(_Total)\Current Disk Queue Length', '\System\Processor Queue Length' ) or VarStr like '%\SQLServer:General Statistics\User Connections' or VarStr like '%\SQLServer:Databases(_Total)\Transactions/sec' or VarStr like '%\SQLServer:Databases(_Total)\Active Transactions' or VarStr like '%Network Interface%\Bytes Received/Sec%' or VarStr like '%Network Interface%\Bytes Sent/sec%' --Step3:复制分发性能计数器 INSERT INTO TempDB.dbo.Temp_PerformanceConfigure(VarStr) SELECT DISTINCT VarStr FROM TempDB.dbo.Temp_PermonAll(NOLOCK) WHERE VarStr LIKE '%Replication%' AND (VarStr LIKE '%Logreader:Delivered Trans/sec%' --每秒传递到分发服务器的事务数。 OR VarStr LIKE '%Logreader:Delivery Latency%' MSSQL 数据库培训手册 Replication 4 / 10 --发布->分发延时(毫秒) OR VarStr LIKE '%Dist:Delivered Cmds/sec%' --每秒传递到订阅服务器的命令数。 OR VarStr LIKE '%Dist:Delivery Latency%' --分发->订阅延时(毫秒) ) --查询本机上需要部署哪些性能计数器 -- SELECT * FROM TempDB.dbo.Temp_PerformanceConfigure --将要添加的性能计数器对象导入本地服务器磁盘上Txt文件 EXECUTE('master..xp_cmdshell''BCP TempDB.dbo.Temp_PerformanceConfigure OUT ' +@Path+' -T -c -CRAW -S'+@ServerNameAll+'''') select '1' --添加性能计数器 EXEC('master..xp_cmdshell''Logman Create counter '+@LogmanName+' -si 00:00:15 -cf "' +@Path+'" -o '+@LogmanName+'!'+@LogmanName+' -f sql -v mmddhhmm''') --启动性能计数器 EXEC ('Master.dbo.XP_CMDSHELL ''Logman Start '+@LogmanName+'''') --将导入本地服务器磁盘上的添加的性能计数器对象Txt文件删除 EXEC('master..xp_cmdshell''DEL '+@Path+'''') DROP TABLE TEMPDB..Temp_PermonAll DROP TABLE TEMPDB..Temp_PerformanceLogmanStatus DROP TABLE TEMPDB..Temp_PerformanceConfigure DROP TABLE TEMPDB..Temp_PerformanceConfigureDone /* /* QUERY PERFORMANCE */ XP_CMDSHELL 'LOGMAN QUERY' /* START PERFORMANCE*/ XP_CMDSHELL ' LOGMAN start performance' /* STOP PERFORMANCE*/ XP_CMDSHELL ' LOGMAN STOP performance' /* DELETE PERFORMANCE*/ XP_CMDSHELL ' LOGMAN DELETE performance' */ 可以通过 windows 性能计数器或者 复制监控器 来抓去相关数据. 2. 快速事务复制初始化 *SnapShot 快照初始化：通过复制快照初始化 -- 添加事务项目 use [test_pub] exec sp_addarticle @publication = N'PUB_Test' ,@article = N'HjTest' ,@source_owner = N'dbo' MSSQL 数据库培训手册 Replication 5 / 10 ,@source_object = N'HjTest' ,@type = N'logbased' ,@description = N'' ,@creation_script = N'' ,@pre_creation_cmd = N'drop' ,@schema_option = 0x000000000803509F ,@identityrangemanagementoption = N'none' ,@destination_table = N'HjTest' ,@destination_owner = N'dbo' ,@status = 24 ,@vertical_partition = N'false' ,@ins_cmd = N'CALL [dbo].[sp_MSins_dboHjTest]' ,@del_cmd = N'CALL [dbo].[sp_MSdel_dboHjTest]' ,@upd_cmd = N'SCALL [dbo].[sp_MSupd_dboHjTest]' GO -- 添加事务订阅 use [test_pub] exec sp_addsubscription @publication = N'PUB_Test' , @subscriber = N'XWJ-PC' , @destination_db = N'test_sub' , @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'PUB_Test' , @subscriber = N'XWJ-PC' , @subscriber_db = N'test_sub' , @job_login = null , @job_password = null , @subscriber_security_mode = 0 , @subscriber_login = N'xwj' , @subscriber_password = N'xwj19850110' , @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 MSSQL 数据库培训手册 Replication 6 / 10 , @active_end_date = 0 , @dts_package_location = N'Distributor' GO *数据库同步方式： 备份,镜像,日志传送,SSIS等同步数据后,做复制 replication support only 模式. use [test_pub] create table HJTest_support_only (id int primary key, name varchar(20) ) insert into HJTest_support_only select 1,'a'; use [test_sub_support_only] create table HJTest_support_only (id int primary key, name varchar(20) ) insert into HJTest_support_only select 1,'a'; use [test_pub] exec sp_addarticle @publication = N'PUB_Test' , @article = N'HJTest_support_only' , @source_owner = N'dbo' , @source_object = N'HJTest_support_only' , @type = N'logbased' , @description = N'' , @creation_script = N'' , @pre_creation_cmd = N'none' , @schema_option = 0x000000000803509F , @identityrangemanagementoption = N'none' , @destination_table = N'HJTest_support_only' , @destination_owner = N'dbo' , @status = 8 , @vertical_partition = N'false' , @ins_cmd = N'CALL [sp_MSins_dboHJTest_support_only]' , @del_cmd = N'CALL [sp_MSdel_dboHJTest_support_only]' , @upd_cmd = N'SCALL [sp_MSupd_dboHJTest_support_only]' GO -- 添加事务订阅 use [test_pub] exec sp_addsubscription @publication = N'PUB_Test' , @subscriber = N'XWJ-PC' , @destination_db = N'test_sub_support_only' MSSQL 数据库培训手册 Replication 7 / 10 , @subscription_type = N'Push' , @sync_type = N'replication support only' , @article = N'all' , @update_mode = N'read only' , @subscriber_type = 0 exec sp_addpushsubscription_agent @publication = N'PUB_Test' , @subscriber = N'XWJ-PC' , @subscriber_db = N'test_sub_support_only' , @job_login = null , @job_password = null , @subscriber_security_mode = 0 , @subscriber_login = N'xwj' , @subscriber_password = 'xwj19850110' , @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' use [test_pub] insert into HJTest_support_only select 1,'b'; 3. 事务制常见错误处理 * 不能插入重复行 1 一般是有 dml 操作在订阅端。可以回收订阅端的权限。让程序账户只有读得权限。 2 可以通过 sp_browsereplcmds 和通过修改自定义 sp 来获取重复 * 自增列 订阅端去掉自增列 查找具有自增列的 SELECT 表名= D.NAME ,列名= A.NAME ,是否自增= CASE WHEN COLUMNPROPERTY( A.ID,A.NAME, 'ISIDENTITY ')=1 THEN '√'ELSE '' END ,主键= CASE WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE XTYPE= 'PK ' AND PARENT_OBJ=A.ID AND NAME IN ( SELECT NAME FROM SYSINDEXES WHERE INDID IN( SELECT MSSQL 数据库培训手册 Replication 8 / 10 INDID FROM SYSINDEXKEYS WHERE ID = A.ID AND COLID=A.COLID))) THEN '√' ELSE '' END FROM SYSCOLUMNS A LEFT JOIN SYSTYPES B ON A.XUSERTYPE=B.XUSERTYPE INNER JOIN SYSOBJECTS D ON A.ID=D.ID AND D.XTYPE= 'U' AND D.NAME <> 'DTPROPERTIES ' and d.category<>2 * 延迟 1mbps 2 iops 3 事务是否短小 * 没找到匹配的行 1 存在订阅端有 dml 操作 2 通过去除权限和察看信息来判断丢失的行 select top 100 e.xact_seqno ,e.command_id,e.* from dbo.MSdistribution_history h join dbo.MSrepl_errors e on h.error_id=e.id where comments not like '%transaction%' --失败的代理 order by id desc sp_browsereplcmds xact_seqno 3 修改 Call 系统过程 4. 事务复制 SP Call 的类型以及自定义 Call *Call 可用于insert、delete、update。默认情况下,复制将此语法用于insert和delete。 处理insert语句的sp传递所有列的值 Insert ：c1,c2,c3…cn 处理update语句的sp传递在项目中定义的所有列的已更新值，后跟主键列的原始值。 Update: c1,c2,c3…cn,pkc1,pkc2,pkc3…pkcn 处理delete语句的sp将传递主键列的值 Delete: pkc1,pkc2,pkc3…pkcn *SCall 只能用于update。默认情况下复制将此语法用于update 处理update语句的sp只传递已更改的列的更新值,后面依次是主键列的原始值和一个指明已更改列的 位掩码（binary（n））参数。下面C2列没更改。 Update:c1, ,c3,…cn,pkc1,pkc2,pkc3,….pkcn,bitmask *MCall 只能用于update 处理update语句的sp将传递在项目定义所有列的更新值，后面依次跟主键列的原始值和一个指示已更 改列的位掩码(binary(n))参数。 Update：c1,c2,c3…..cn,pkc1,pkc2,pkc3,…pkcn,bitmask *XCall 可用于update和delete 处理update语句的sp将传递在项目中定义的所有列的原始值（前像），后跟项目中定义的所有列的 更新值（后像）： Update:Old-c1,old-c2,old-c3,…old-cn,c1,c2,c3,…cn 处理delete语句的sp将传递项目中定义的所有列的原始值（前像） Delete: old-c1,oldc2,old-c3,…old-cn MSSQL 数据库培训手册 Replication 9 / 10 *VCall 用于可更新的订阅。仅供内部使用。 5. 事务复制监控和维护 监控：抓去系统信息通过邮件、短信来通知 DBA 维护：1 在业务低谷新增加发布表 2 新增加列 新增加列应为允许 null 3 大批量数据 delete 写成 sp 通过发布 sp 到订阅机 4 发布的表主键尽量不 update 6. 双向复制、对等复制、合并复制 Master-Master(双向复制) DB distribution DB M-M (图 1 Master-Master) Peer –to-Peer(对等拓扑) DB distribution DB DB distribution distribution MSSQL 数据库培训手册 Replication 10 / 10 (图 2 3 node Peer –to-Peer) Merge Replication (合并复制) DB distribution （图 3 Merage Replication） 7. 异构数据库之间的复制介绍 查看 MSSQL 支持的异构数据库的版本和字段类型 Select * from msdb.dbo. MSdbms_datatype select * from msdb.dbo.MSdbms select * from msdb.dbo.MSdbms_datatype_mapping 徐王锦 5173 携程