用SQL Server 2000实现数据复制的问题

[iamchristina]

实现两台服务器上的数据库同步更新，我现在用的是“合并复制”和“请求订阅”，但是有个缺点，合并复制必须在表中加一个字段：rowguid，这样一来，我原来的应用程序中调用的存储过程就要发生很大变动，尤其是insert操作

我想问一下：有没有办法不加这个字段，但仍能实现两台服务器上的数据库同步更新

[iamchristina]

请高手指教

[iamchristina]

怎么没人回答

[jinyefeng]

程序中用一个服务器上的数据都好呢

[bluelark]

在合并复制中，为了避免冲突，rowguid是必须的。
如果你只需要保证B机器和A机器上的数据同步，那么用事务复制吧（只要求表有主键）。
如果你需要双向的同步，那么可以考虑建立双向事务复制。

[iamchristina]

bluelark(残思):


你所指的双向事务复制是在两台机器上同时进行发布和订阅？即互为发布和订阅。

我试过，但是有冲突，还是有别的方法？

[bluelark]

双向复制的冲突可以通过自定义存储过程来解决。

能否进一步说明你的同步需求？是双向同步吗？复制的表多吗？

[iamchristina]

bwindmill(angelin) ：
我用用merge和pll实现两台服务器上的数据库同步更新成功了，但是必须加一个rowguid字段，比较麻烦，两台服务器是局域网

[iamchristina]

是要求双向同步，而且要及时更新，需要复制8个表

自定义存储过程怎么写？还请指教

[bluelark]

给你个例子：


Step 1: Create the Databases and Enable Replication
New Information - September 2001.

To set up bidirectional replication

Create the two databases for your application: test1 and test2.


Enable the server as a Publisher, Subscriber, and Distributor. If the server is already enabled as a Publisher, Subscriber, and Distributor, you can skip this step.


Enable the databases for replication.
Examples
1. Create the test databases
CREATE database test1
CREATE database test2

2. Enable the server as a Publisher, Subscriber, and Distributor
EXEC master..sp_adddistributor @distributor = @@SERVERNAME
GO
EXEC master..sp_adddistributiondb @database= 'distribution'
GO
EXEC master..sp_adddistpublisher @publisher = @@SERVERNAME, @distribution_db = 'distribution', @working_directory = 'C:\Program Files\Microsoft SQL Server\MSSQL\REPLDATA'
GO
EXEC master..sp_addsubscriber @subscriber = @@SERVERNAME, @type = 0, @security_mode = 1
GO
EXEC master..sp_changesubscriber_schedule @subscriber = @@SERVERNAME, @agent_type = 1, @active_end_date = 0
GO

3. Enable the databases for replication
USE master
GO
EXEC sp_replicationdboption N'test1', N'publish', true
GO
EXEC sp_replicationdboption N'test2', N'publish', true
GO

Step 2: Create a Bidirectional Schema
New Information - September 2001.

To create a bidirectional schema, create a table with the same schema in each test database and populate it with sample data.

Examples
1. Create a table in test1 and populate with 10 rows
USE test1
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'two_way_test1')
DROP TABLE two_way_test1
GO

CREATE TABLE two_way_test1
(pkcol int primary key not null,
intcol int,
charcol char(100),
datecol datetime
)
GO

INSERT INTO two_way_test1 VALUES (1, 10, 'row1', GETDATE())
INSERT INTO two_way_test1 VALUES (2, 20, 'row2', GETDATE())
INSERT INTO two_way_test1 VALUES (3, 30, 'row3', GETDATE())
INSERT INTO two_way_test1 VALUES (4, 40, 'row4', GETDATE())
INSERT INTO two_way_test1 VALUES (5, 50, 'row5', GETDATE())
INSERT INTO two_way_test1 VALUES (6, 60, 'row6', GETDATE())
INSERT INTO two_way_test1 VALUES (7, 70, 'row7', GETDATE())
INSERT INTO two_way_test1 VALUES (8, 80, 'row8', GETDATE())
INSERT INTO two_way_test1 VALUES (9, 90, 'row9', GETDATE())
INSERT INTO two_way_test1 VALUES (10, 100, 'row10', GETDATE())
GO

2. Create a table in test2 and populate with 10 rows
USE test2
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'two_way_test2')
DROP TABLE two_way_test2
GO

CREATE TABLE two_way_test2
(pkcol int primary key not null,
intcol int,
charcol char(100),
datecol datetime
)
GO

INSERT INTO two_way_test2 VALUES (1, 10, 'row1', GETDATE())
INSERT INTO two_way_test2 VALUES (2, 20, 'row2', GETDATE())
INSERT INTO two_way_test2 VALUES (3, 30, 'row3', GETDATE())
INSERT INTO two_way_test2 VALUES (4, 40, 'row4', GETDATE())
INSERT INTO two_way_test2 VALUES (5, 50, 'row5', GETDATE())
INSERT INTO two_way_test2 VALUES (6, 60, 'row6', GETDATE())
INSERT INTO two_way_test2 VALUES (7, 70, 'row7', GETDATE())
INSERT INTO two_way_test2 VALUES (8, 80, 'row8', GETDATE())
INSERT INTO two_way_test2 VALUES (9, 90, 'row9', GETDATE())
INSERT INTO two_way_test2 VALUES (10, 100, 'row10', GETDATE())
GO

[weixy]

完全同步不好做，如果操作很频繁会很慢。
可以用触发器，如果完全同步可以先建立linkserver然后update db1_server.db1.dbo.tab1 set ... from updated
但这样慢，可以在DB1上建立修改存储过程 update tab1 set col4 = b.col4 from tab1, db2_server.db2.dbo.tab1 b where tab1.key = b.key
和插入存储过程 insert .... 然后在再第一个机器的数据库的表的触发器里调用远程存储过程。
如果不要完全同步可以先用触发器写到本地的表中（建立一个结构一样的trans表，增加一标志字段，和一传输字段）然后写任务，半小时或多久传一次

[bluelark]

Step 3: Create Reciprocal Publications
New Information - September 2001.

To create reciprocal publications, add the transactional publication and article to each database. Note that:

Custom stored procedures are used for @ins_cmd, @del_cmd, and @upd_cmd.


The XCALL style parameters are used in UPDATE and DELETE stored procedures. For more information, see Using Custom Stored Procedures in Articles.


@schema_option disables autogeneration of the default custom stored procedures because they will be created in Step 4.
Examples
1. Add the transactional publication and article in test1
USE test1
GO

EXEC sp_addpublication @publication = N'two_way_pub_test1',
@restricted = N'false', @sync_method = N'native',
@repl_freq = N'continuous', @description = N'publ1',
@status = N'active', @allow_push = N'true',
@allow_pull = N'true', @allow_anonymous = N'false',
@enabled_for_internet = N'false',
@independent_agent = N'false', @immediate_sync = N'false',
@allow_sync_tran = N'false',
@autogen_sync_procs = N'false', @retention = 60
GO

EXEC sp_addarticle @publication = N'two_way_pub_test1',
@article = N'two_way_test1', @source_owner = N'dbo',
@source_object = N'two_way_test1',
@destination_table = N'two_way_test2',
@type = N'logbased', @creation_script = null,
@description = null, @pre_creation_cmd = N'drop',
@schema_option = 0x00000000000000F1, @status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_ins_two_way_test2',
@del_cmd = N'XCALL sp_del_two_way_test2',
@upd_cmd = N'XCALL sp_upd_two_way_test2',
@filter = null, @sync_object = null
GO

2. Add the transactional publication and article in test2
USE test2
GO

EXEC sp_addpublication @publication = N'two_way_pub_test2',
@restricted = N'false', @sync_method = N'native',
@repl_freq = N'continuous', @description = N'Pub2',
@status = N'active', @allow_push = N'true',
@allow_pull = N'true', @allow_anonymous = N'false',
@enabled_for_internet = N'false',
@independent_agent = N'false', @immediate_sync = N'false',
@allow_sync_tran = N'false',
@autogen_sync_procs = N'false', @retention = 60
GO

EXEC sp_addarticle @publication = N'two_way_pub_test2',
@article = N'two_way_test2', @source_owner = N'dbo',
@source_object = N'two_way_test2', @destination_table =
N'two_way_test1', @type = N'logbased',
@creation_script = null,
@description = null, @pre_creation_cmd = N'drop',
@schema_option = 0x00000000000000F1, @status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_ins_two_way_test1',
@del_cmd = N'XCALL sp_del_two_way_test1',
@upd_cmd = N'XCALL sp_upd_two_way_test1',
@filter = null, @sync_object = null
GO

[bluelark]

Step 4: Create Subscriptions with Cycle Detection Enabled
New Information - September 2001.

To create subscriptions with cycle detection enabled, add a transactional subscription to each database with @loopback_detection set to TRUE, so that the Distribution Agent does not send transactions that originated at the Subscriber back to the Subscriber.

Examples
1. Add the transactional subscription in test1
USE test1
GO

EXEC sp_addsubscription @publication = N'two_way_pub_test1',
@article = N'all', @subscriber = @@SERVERNAME,
@destination_db = N'test2', @sync_type = N'none',
@status = N'active', @update_mode = N'read only',
@loopback_detection = 'true'
GO

2. Add the transactional subscription in test2
USE test2
GO

EXEC sp_addsubscription @publication = N'two_way_pub_test2',
@article = N'all', @subscriber = @@SERVERNAME,
@destination_db = N'test1', @sync_type = N'none',
@status = N'active', @update_mode = N'read only',
@loopback_detection = 'true'
GO

Step 5: Create Custom Stored Procedures to Apply Changes and Handle Conflicts
New Information - September 2001.

The update procedures in the examples have been customized to detect and handle simple conflicts. If a conflict is detected in the intcol column, the current value and new increment are added together; if a conflict is detected in the charcol field, the values are concatenated together.

Examples
1. Create custom stored procedures in test1
USE test1
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_ins_two_way_test1' and type = 'P')
DROP proc sp_ins_two_way_test1
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_upd_two_way_test1' and type = 'P')
DROP proc sp_upd_two_way_test1
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_del_two_way_test1' and type = 'P')
DROP proc sp_del_two_way_test1
GO

-- Insert procedure
CREATE proc sp_ins_two_way_test1 @pkcol int,
@intcol int,
@charcol char(100),
@datecol datetime
AS
INSERT INTO two_way_test1 (pkcol, intcol, charcol,
datecol)
VALUES (@pkcol, @intcol, @charcol, GETDATE())
GO

-- Update procedure
CREATE proc sp_upd_two_way_test1 @old_pkcol int,
@old_intcol int,
@old_charcol char(100),
@old_datecol datetime,
@pkcol int, @intcol int,
@charcol char(100),
@datecol datetime
AS
-- IF intcol conflict is detected, add values
-- IF charcol conflict detected, concatenate values
DECLARE @curr_intcol int, @curr_charcol char(100)

SELECT @curr_intcol = intcol, @curr_charcol = charcol
FROM two_way_test1 WHERE pkcol = @pkcol

IF @curr_intcol != @old_intcol
SELECT @intcol = @curr_intcol +
(@intcol - @old_intcol)

IF @curr_charcol != @old_charcol
SELECT @charcol = rtrim(@curr_charcol) +
'_' + rtrim(@charcol)

UPDATE two_way_test1 SET intcol = @intcol,
charcol = @charcol, datecol = GETDATE()
WHERE pkcol = @old_pkcol

GO

-- Delete procedure
CREATE proc sp_del_two_way_test1 @old_pkcol int,
@old_intcol int,
@old_charcol char(100),
@old_datecol datetime
AS
DELETE two_way_test1 WHERE pkcol = @old_pkcol
GO

2. Create custom stored procedures in test2
USE test2
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_ins_two_way_test2' and type = 'P')
DROP proc sp_ins_two_way_test2
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_upd_two_way_test2' and type = 'P')
DROP proc sp_upd_two_way_test2
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'sp_del_two_way_test2' and type = 'P')
DROP proc sp_del_two_way_test2
GO

-- Insert procedure
CREATE proc sp_ins_two_way_test2 @pkcol int,
@intcol int,
@charcol char(100),
@datecol datetime
AS
INSERT INTO two_way_test2 (pkcol, intcol, charcol,datecol)
VALUES (@pkcol, @intcol, @charcol, GETDATE())
GO

-- Update procedure
CREATE proc sp_upd_two_way_test2 @old_pkcol int,
@old_intcol int,
@old_charcol char(100),
@old_datecol datetime,
@pkcol int,
@intcol int,
@charcol char(100),
@datecol datetime
AS
-- IF intcol conflict is detected, add values
-- IF charcol conflict detected, concatenate values
DECLARE @curr_intcol int, @curr_charcol char(100)

SELECT @curr_intcol = intcol, @curr_charcol = charcol
FROM two_way_test2 WHERE pkcol = @pkcol

IF @curr_intcol != @old_intcol
SELECT @intcol = @curr_intcol +
(@intcol - @old_intcol)

IF @curr_charcol != @old_charcol
SELECT @charcol = rtrim(@curr_charcol) +
'_' + rtrim(@charcol)

UPDATE two_way_test2 SET intcol = @intcol,
charcol = @charcol, datecol = GETDATE()
WHERE pkcol = @old_pkcol
GO

-- Delete procedure
CREATE proc sp_del_two_way_test2 @old_pkcol int,
@old_intcol int,
@old_charcol char(100),
@old_datecol datetime
AS
DELETE two_way_test2 WHERE pkcol = @old_pkcol

GO

[bluelark]

Step 6: Test the Application
New Information - September 2001.

To test the application you have built, execute updates and then select the data to see the results. Use the datecol column to see when rows were updated at each database, relative to each other.

Examples
1. Execute updates to the first row in test1 and test2
USE test1
GO
UPDATE two_way_test1 SET intcol = 20 , charcol = 'updated at test1' WHERE pkcol = 1

USE test2
GO
UPDATE two_way_test2 SET intcol = 60 , charcol = 'updated at test2' WHERE pkcol = 1

2. Select data from both tables to verify that the changes were propagated
SELECT * FROM test1..two_way_test1 WHERE pkcol = 1
SELECT * FROM test2..two_way_test2 WHERE pkcol = 1
If the starting value of intcol was 10, the value in test1 was incremented by 10 and the value in test2 was incremented by 50. The conflict resolution in the update custom procedure sums the values in this column, so this integer value converges to 70 in both databases. It may take a few seconds to see the results in both tables.

[iamchristina]

bluelark(残思) :

非常感谢你所给的存储过程，不过我想问一下，能不能在企业管理器里直接配置，如果可以的话，具体过程又是怎样的？

因为我发现step1-step4都是可以在企业管理器里直接配置的，所以step5解决冲突的部分是不是也可以通过选择某些选项来配置呢？

[bwindmill]

iamchristina:
想问一下，你用merge和pll实现两台服务器上的数据库同步更新有没有成功，另外你所指的这两台服务器是透过internet还是内部网络做的复制

[bluelark]

在企业管理器中，你打开发布属性，打开“项目”标签，在项目的属性中可以指定执行INSERT/UPDATE/DELETE时调用的存储过程。
如果你发布的表很多，这种方法可能就比较繁了，因为每张表你都要建3个存储过程。