给你个例子: 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 test22. 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 GO3. 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 GOStep 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 GOIF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'two_way_test1') DROP TABLE two_way_test1 GOCREATE TABLE two_way_test1 (pkcol int primary key not null, intcol int, charcol char(100), datecol datetime ) GOINSERT 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()) GO2. Create a table in test2 and populate with 10 rows USE test2 GOIF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'two_way_test2') DROP TABLE two_way_test2 GOCREATE TABLE two_way_test2 (pkcol int primary key not null, intcol int, charcol char(100), datecol datetime ) GOINSERT 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
完全同步不好做,如果操作很频繁会很慢。 可以用触发器,如果完全同步可以先建立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表,增加一标志字段,和一传输字段)然后写任务,半小时或多久传一次
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 GOEXEC 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 GOEXEC 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 GO2. Add the transactional publication and article in test2 USE test2 GOEXEC 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 GOEXEC 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
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 GOEXEC 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' GO2. Add the transactional subscription in test2 USE test2 GOEXEC 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' GOStep 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 GOIF 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
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 GO2. Create custom stored procedures in test2 USE test2 GOIF 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
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
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 = 12. 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.
你所指的双向事务复制是在两台机器上同时进行发布和订阅?即互为发布和订阅。我试过,但是有冲突,还是有别的方法?
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 test22. 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
GO3. 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
GOStep 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
GOIF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'two_way_test1')
DROP TABLE two_way_test1
GOCREATE TABLE two_way_test1
(pkcol int primary key not null,
intcol int,
charcol char(100),
datecol datetime
)
GOINSERT 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())
GO2. Create a table in test2 and populate with 10 rows
USE test2
GOIF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'two_way_test2')
DROP TABLE two_way_test2
GOCREATE TABLE two_way_test2
(pkcol int primary key not null,
intcol int,
charcol char(100),
datecol datetime
)
GOINSERT 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
可以用触发器,如果完全同步可以先建立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表,增加一标志字段,和一传输字段)然后写任务,半小时或多久传一次
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
GOEXEC 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
GOEXEC 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
GO2. Add the transactional publication and article in test2
USE test2
GOEXEC 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
GOEXEC 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
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
GOEXEC 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'
GO2. Add the transactional subscription in test2
USE test2
GOEXEC 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'
GOStep 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
GOIF 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
GO2. Create custom stored procedures in test2
USE test2
GOIF 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
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 = 12. 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.
想问一下,你用merge和pll实现两台服务器上的数据库同步更新有没有成功,另外你所指的这两台服务器是透过internet还是内部网络做的复制
如果你发布的表很多,这种方法可能就比较繁了,因为每张表你都要建3个存储过程。
我用用merge和pll实现两台服务器上的数据库同步更新成功了,但是必须加一个rowguid字段,比较麻烦,两台服务器是局域网