DBA 设置事务复制的方法 - 办公室里的 4 个不间断的工作日
5.00/5 (2投票s)
设置 SQL Server 2005 中的事务复制。
最近，我们需要对生产环境进行热修复（HotFix）推广。在热修复推广中，开发应用程序的供应商对应用程序和数据库进行了重大增强以修复 bug。作为前提条件，在热修复推广开始之前，我们需要中断日志传送（LogShipping）和事务复制。即将应用热修复的数据库大小约为 90 GB。重新设置日志传送并不难，但是重新设置事务复制却让我们陷入了深深的困境。在本文中，来自印度孟买 Larsen&Toubro Infotech 的高级 SQL Server DBA Satnam Singh 深入探讨了他经过连续 4 天不间断工作研究后最终成功的经验。
SQL Server 版本
发布服务器：OLTP 数据库服务器，SQL Server 2005，Service Pack 3，32 GB RAM，8 个 CPU
订阅服务器：报表数据库服务器，SQL Server 2005，Service Pack 3，16 GB RAM，12 个 CPU
上述两台服务器均位于美国同一数据中心。仅供参考，订阅服务器是报表服务器，通过事务复制每 15 分钟从 OLTP 服务器接收数据。另外，在设置事务复制时，决定将分发数据库托管在 OLTP 服务器本身。
这里有一点很重要，就是在订阅服务器（即报表服务器）上，我们创建了大量索引以提高 Select 语句的性能。在开始热修复推广之前，我确保备份了所有索引。为了备份索引，我在数据库 ABC（参与复制的数据库）上执行了以下查询，该数据库位于订阅服务器（报表数据库服务器）上。在开始热修复推广之前，我决定备份报表服务器上现有数据库中的所有 SQL Server 索引。为此，在订阅服务器（报表服务器）的数据库 ABC 上执行了以下 T-SQL。
//
SELECT
ixz.object_id,
tablename = QUOTENAME(scmz.name) + '.' + QUOTENAME((OBJECT_NAME(ixz.object_id))),
tableid = ixz.object_id,
indexid = ixz.index_id,
indexname = ixz.name,
isunique = INDEXPROPERTY (ixz.object_id,ixz.name,'isunique'),
isclustered = INDEXPROPERTY (ixz.object_id,ixz.name,'isclustered'),
indexfillfactor = INDEXPROPERTY (ixz.object_id,ixz.name,'indexfillfactor')
INTO #tmp_indexes
FROM sys.indexes ixz
INNER JOIN sys.objects obz
ON ixz.object_id = obz.object_id
INNER JOIN sys.schemas scmz
ON obz.schema_id = scmz.schema_id
WHERE ixz.index_id > 0
AND ixz.index_id < 255 ---- 0 = HEAP index, 255 = TEXT columns index
--AND INDEXPROPERTY (ixz.object_id,ixz.name,'ISUNIQUE') = 0
AND INDEXPROPERTY (ixz.object_id,ixz.name,'ISCLUSTERED') = 0
ALTER TABLE #tmp_indexes ADD keycolumns VARCHAR(4000), includes VARCHAR(4000)
GO
DECLARE @isql_key VARCHAR(4000),
@isql_incl VARCHAR(4000),
@tableid INT,
@indexid INT
DECLARE index_cursor CURSOR
FOR
SELECT
tableid,
indexid
FROM #tmp_indexes
OPEN index_cursor
FETCH NEXT FROM index_cursor INTO @tableid, @indexid
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @isql_key = '', @isql_incl = ''
SELECT --ixz.name, colz.colid, colz.name, ixcolz.index_id, ixcolz.object_id, *
--key column
@isql_key = CASE ixcolz.is_included_column
WHEN 0
THEN CASE ixcolz.is_descending_key
WHEN 1
THEN @isql_key + COALESCE(colz.name,'') + ' DESC, '
ELSE @isql_key + COALESCE(colz.name,'') + ' ASC, '
END
ELSE @isql_key
END,
@isql_incl = CASE ixcolz.is_included_column
WHEN 1
THEN CASE ixcolz.is_descending_key
WHEN 1
THEN @isql_incl + COALESCE(colz.name,'') + ', '
ELSE @isql_incl + COALESCE(colz.name,'') + ', '
END
ELSE @isql_incl
END
FROM sysindexes ixz
INNER JOIN sys.index_columns AS ixcolz
ON (ixcolz.column_id > 0
AND ( ixcolz.key_ordinal > 0
OR ixcolz.partition_ordinal = 0
OR ixcolz.is_included_column != 0)
)
AND ( ixcolz.index_id=CAST(ixz.indid AS INT)
AND ixcolz.object_id=ixz.id
)
INNER JOIN sys.columns AS colz
ON colz.object_id = ixcolz.object_id
AND colz.column_id = ixcolz.column_id
WHERE ixz.indid > 0 AND ixz.indid < 255
AND (ixz.status & 64) = 0
AND ixz.id = @tableid
AND ixz.indid = @indexid
ORDER BY
ixz.name,
CASE ixcolz.is_included_column
WHEN 1
THEN ixcolz.index_column_id
ELSE ixcolz.key_ordinal
END
--remove any trailing commas from the cursor results
IF LEN(@isql_key) > 1 SET @isql_key = LEFT(@isql_key, LEN(@isql_key) -1)
IF LEN(@isql_incl) > 1 SET @isql_incl = LEFT(@isql_incl, LEN(@isql_incl) -1)
--put the columns collection into our temp table
UPDATE #tmp_indexes
SET keycolumns = @isql_key,
includes = @isql_incl
WHERE tableid = @tableid
AND indexid = @indexid
FETCH NEXT FROM index_cursor INTO @tableid,@indexid
END --WHILE
CLOSE index_cursor
DEALLOCATE index_cursor
DELETE FROM #tmp_indexes WHERE keycolumns = ''
SET NOCOUNT ON
SELECT
'IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''' +
' ti.TABLENAME + '''' + ') AND name = N' + '''' + ti.INDEXNAME + '''' + ')' + ' ' +
'CREATE '
+ CASE WHEN ti.ISUNIQUE = 1 THEN 'UNIQUE ' ELSE '' END
+ CASE WHEN ti.ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END
+ 'INDEX ' + QUOTENAME(ti.INDEXNAME)
+ ' ON ' + (ti.TABLENAME) + ' '
+ '(' + ti.keycolumns + ')'
+ CASE
WHEN ti.INDEXFILLFACTOR = 0 AND ti.ISCLUSTERED = 1 AND INCLUDES = ''
THEN /*ti.Filter_Definition +*/ ' WITH (SORT_IN_TEMPDB = OFF) ON [SECONDARY]'
WHEN INDEXFILLFACTOR = 0 AND ti.ISCLUSTERED = 0 AND ti.INCLUDES = ''
THEN /*ti.Filter_Definition +*/ ' WITH (ONLINE = OFF, SORT_IN_TEMPDB = OFF) ON [SECONDARY]'
WHEN INDEXFILLFACTOR <> 0 AND ti.ISCLUSTERED = 0 AND ti.INCLUDES = ''
THEN /*ti.Filter_Definition +*/ ' WITH (ONLINE = OFF, SORT_IN_TEMPDB = OFF,
FILLFACTOR = ' + CONVERT(VARCHAR(10),ti.INDEXFILLFACTOR) + ') ON [SECONDARY]'
WHEN INDEXFILLFACTOR = 0 AND ti.ISCLUSTERED = 0 AND ti.INCLUDES <> ''
THEN ' INCLUDE ('+ti.INCLUDES+') WITH (ONLINE = OFF, SORT_IN_TEMPDB = OFF) ON [SECONDARY]'
ELSE ' INCLUDE('+ti.INCLUDES+') WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),
ti.INDEXFILLFACTOR) + ', ONLINE = OFF, SORT_IN_TEMPDB = OFF) ON [SECONDARY]'
END
FROM #tmp_indexes ti
JOIN sys.indexes i ON ti.Object_id = i.object_id and ti.indexname = i.name
JOIN sys.filegroups fg on i.data_space_id = fg.data_space_id
WHERE LEFT(ti.tablename,3) NOT IN ('sys', 'dt_') --exclude system tables
ORDER BY
ti.tablename,
ti.indexid,
ti.indexname
DROP TABLE #tmp_indexes
//
上述 T-SQL 执行后，我获得了所有索引的索引生成脚本，并将其保存在服务器上的特定位置。请注意，在报表服务器上，我们分配了一个独立的 RAID 10 驱动器，大小为 200 GB，仅用于存放 SQL Server 索引以提高性能。另外请注意，索引生成脚本不会脚本化表中存在的聚集索引，因为我们只移动数据库中的非聚集索引。如果我们尝试移动聚集索引，与之关联的表也会被移动，因为聚集索引的叶子就是数据页本身。
热修复推广完成后，我开始恢复更改。我决定先设置事务复制，因为这很重要，大多数业务用户都严重依赖报表。由于 OLTP 数据库大小约为 90 GB，我决定采用“无快照的复制”。
在 OLTP 服务器上，我首先使用 GUI 创建了发布服务器。创建发布服务器后，我指示系统从备份集中初始化备份。
然后，我使用以下 T-SQL 在 OLTP 服务器上对数据库进行了完整备份。
//
backup database ABC to disk='E:\Microsoft SQL Server\MSSQL\Backup\User\ABCt_Full_Backup_After_HotFix.bak'
//
The IP Address of the OLTP Server was 10.1.1.1
Once the Full Backup got completed, I started restoring the same on the Reporting Server using the below T-SQL.
//
RESTORE DATABASE ABC
FROM DISK='\\10.1.1.1\User\ABC_Full_Backup_After_HotFix.bak'
WITH MOVE 'ABC' TO 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABC.mdf',
MOVE 'ABC_Log' TO 'L:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABC_1.ldf',
stats=10,NORECOVERY
//
使用 NORECOVERY 模式还原了完整备份，以便可以在其上应用其他备份。
在完整备份还原到报表服务器的同时，我开始使用以下 T-SQL 在 OLTP 服务器上对该数据库进行差异备份。
//
backup database ABC to disk='E:\Microsoft SQL Server\MSSQL\
Backup\User\ABC_Differential_Backup_After_HotFix.bak' with differential
//
完整备份还原后，我开始使用以下 T-SQL 在报表服务器上的数据库 ABC 中还原差异备份。
//
RESTORE DATABASE ABC
FROM DISK='\\10.1.1.1\User\ABC_Differential_Backup_After_HotFix.bak'
WITH MOVE 'ABC' TO 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABC.mdf',
MOVE 'ABC_Log' TO 'L:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABC_1.ldf',
stats=10,NORECOVERY
GO
//
差异备份还原的同时，我开始使用以下 T-SQL 在 OLTP 服务器上的数据库 ABC 中进行事务日志备份。
//
backup log ABC to disk='E:\Microsoft SQL Server\MSSQL\Backup\User\ABC_Transactional_Backup_After_HotFix.bak'
//
然后，我使用以下 T-SQL 在报表服务器上的数据库 ABC 中还原了事务日志备份。
//
RESTORE DATABASE ABC FROM DISK='\\10.1.1.1\User\ABC_Transactional_Backup_After_HotFix.bak'
WITH MOVE 'ABC' TO 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABC.mdf',
MOVE 'ABC_Log' TO 'L:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABC_1.ldf',
stats=10
GO
//
在订阅服务器上的数据库 ABC 联机后，我决定使用以下 T-SQL 在 OLTP 服务器上创建订阅服务器。
//
exec sp_addsubscription
@publication = N'ABC',
@subscriber = N'KUS1111',
@destination_db = N'ABC',
@subscription_type = N'Push',
@sync_type = N'initialize with backup',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0,
@backupdevicetype ='disk',
@backupdevicename
='\\10.1.1.1\User\ABC_Transactional_Backup_After_HotFix.bak'
//
上述查询执行后，我收到了以下错误消息：
从指定备份创建的 nosync 订阅所需的事务在分发服务器上不可用。
此时，我意识到，如果我在报表服务器上创建发布服务器，然后只还原完整备份，而不是在完整备份之上还原差异备份和事务日志备份，那么日志读取器代理（Log Reader Agent）将自动处理 OLTP 服务器上发生的所有 DML 更改。然后我决定在 OLTP 服务器上创建发布服务器，指示其从备份集中初始化备份，在报表服务器上还原完整备份，然后创建订阅服务器。
实施上述逻辑后，我成功创建了订阅服务器，但在查看同步状态时，系统抛出了以下错误消息：
"表 abc 中违反主键约束"
此时，我对这项技术异常的行为感到非常困惑。几个小时过去了，即使经过了所有的故障排除，我也找不到原因。在花费了大约 12-16 小时调查后，我决定检查数据库中触发器使用的 T-SQL，我非常惊讶地看到了代码。触发器的 T-SQL 代码顶部没有 "Not For Replication" 子句。我认为这可能只是一些触发器的情况，但我非常惊讶的是，整个数据库中没有一个触发器在其代码顶部有 "Not For Replication" 子句。由于触发器的存在，某个表会生成重复条目，一个条目由日志读取器代理生成，而另一个条目由触发器生成，因此导致了主键约束的违反。
然后，我决定禁用报表服务器上的数据库 ABC 中的所有触发器，以确保复制过程正常工作。
然后，我改变了在 OLTP 和报表数据库服务器之间设置事务复制的策略，如下所示：
- 在 OLTP 服务器上创建发布服务器，并指示其从备份集中初始化备份。
- 在 OLTP 服务器上，对数据库 ABC 进行完整备份。
- 在报表服务器上，还原完整备份。
- 使用以下 T-SQL 脚本在报表服务器上的数据库 ABC 中禁用所有触发器。
//
Select Distinct 'DISABLE TRIGGER ALL ON [' +object_name(parent_obj) + ']' + char(10) + 'GO'
from sysobjects where parent_obj in (select objidfrom sysarticles) and xtype in ('TR', 'TA')
//
- 在报表服务器上的数据库 ABC 上执行上述查询的输出，所有触发器都已被禁用。
- 通过在 OLTP 服务器上的数据库 ABC 中执行以下 T-SQL 查询来创建订阅。
exec sp_addsubscription
@publication = N'ABC',
@subscriber = N'Name of the Reporting Server',
@destination_db = N'ABC',
@subscription_type = N'Push',
@sync_type = N'initialize with backup',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0,
@backupdevicetype ='disk',
@backupdevicename = Path and name of the Full Backup taken on the OLTP Server’
这是我用来在 OLTP 和报表环境之间设置事务复制的一种方法。衷心感谢所有观看者抽出宝贵时间阅读本文。如果您有任何建议，请告知我们。