March 3, 2010 at 11:53 am
We are contemplating adding a Merge publication between a SQL Server 2008 database and a SQL Server 2005 database where Transactional replication already exists in both directions. The Merge publication would be on the 2008 DB for a table that is currently replicated from the 2008 to the 2005 DB. Thus we will need to drop the existing 2005 subscription, modify the 2008 publication to remove that table, then recreate the 2005 subscription. Then we plan to create the Merge publication on the 2008 DB, and then the 2005 subscription for that one table.
Previous attempts to use Merge Replication in SQL 2000 didn't seem to go all that well - the current design of the table to be replicated uses an auto-increment field as the primary key. The primary use of the table will be appends rather than edits. Also note that the two databases are located about 100 miles apart and are synched across a VPN connection. Are we flirting with disaster? Any experience in this area would be appreciated. Thanks in advance.
Wendell Bell
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
March 3, 2010 at 12:10 pm
You could potentially have an issue with identity columns as keys, unless you offset carefully between the two systems. A better solution might be a guid ( NEWID() ) as a key.
We run a merge replication between three systems connected via VPN and separated by hundreds of miles without a problem.
March 5, 2010 at 12:00 pm
After a fair bit of testing, it appears the Merge replication is working just fine as a separate publication, and has no adverse effects on the existing Transaction replication, in case anyone else has the same question. And I did change the primary key from an integer to a guid. Replication added a second guid as the rowguid, and things seem happy. Thanks for the suggestion.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply