December 5, 2012 at 2:57 pm
I have read a lot of articles on this but still have trouble seeing the differences in these and when I should use one over the other.
I have 2 servers and need to sync about 30 tables. I need both nodes to be able to update the data. From what I have read, it seems like either merge or peer-to-peer would support this but peer-to-peer requires an enterprise license. Also, since I only want to sync 30 tables (there are other tables with data that is unique to its server and should not sync across), it seems that merge is the better option. With merge, I can create the snapshot containing only those tables. With peer-to-peer, initialization is done via a restore and would have to include all tables.
I think the terminology is confusing me - merge says it has one publish and multiple subscribers. Aren't the subscribers actually functioning as publishers as well?
December 5, 2012 at 3:16 pm
If only 30 tables could you write a manual process to sync them using Except? Here's an example:
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'DataA') DROP TABLE dbo.DataA
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'DataB') DROP TABLE dbo.DataB
CREATE TABLE DataA (ID INT PRIMARY key, NAME NVARCHAR(50))
CREATE TABLE DataB (ID INT PRIMARY key, NAME NVARCHAR(50))
INSERT INTO DataA (ID,Name) VALUES (1,'Mike Smith'),(2,'James King'),(3,'Scott Thompson'),(5,'Michael Moore'),(7,'Tony Bennet')
INSERT INTO DataB (ID,Name) VALUES (1,'Mike Smith'),(2,'James King'),(4,'Jeff Smith'),(6,'Susan Adams'),(8,'Parker Campbell')
INSERT INTO DataB
SELECT ID,Name FROM DataA EXCEPT SELECT * FROM DataB
INSERT INTO DataA
SELECT ID,Name FROM DataB EXCEPT SELECT * FROM DataA
-- Data is now synced
SELECT * FROM DataA
SELECT * FROM DataB
I do this with a few systems I need to keep in check, and though it depends on how your data is keyed this may be an option.
December 6, 2012 at 8:19 am
Thanks samalex. This doesn't really work for my situation though. These servers are in very different geographical locations and there is too much data to try to do this with a linked server.
December 6, 2012 at 8:44 am
If you are using SQL Server 2008 (which I have to assume you are since this is a 2008 forum), you should use Transactional Replication with Updateable Subscriptions. This is good to keep 2 servers in sync.
Merge replication is best for server to client replication. Example, I have a main server that houses all of the data for my company, but each branch has their own subscription and their own data. Also, it is not "transactional," so if changes are made to 1 row several times, only the most recent change is synced. Not true with transactional replication.
Peer-to-peer is really transactional from serverA to serverB and serverB to serverA. Both acting as a publisher and subscriber.
Jared
CE - Microsoft
December 6, 2012 at 8:55 am
I think this option (Transactional Replication with Updateable Subscriptions) is deprecated. It won't be support after SQL 2012.
December 6, 2012 at 9:21 am
ktalley.dba (12/6/2012)
I think this option (Transactional Replication with Updateable Subscriptions) is deprecated. It won't be support after SQL 2012.
Well... The option is not there in the GUI in 2012, but it can still be set through script. Peer-to-peer is probably your best option. However, you will have to build in conflict handling. Merge is really for separate clients working with their own data and then updating a main database. I wouldn't use it for stuff that is highly transactional on both sides. Still... I am continuing to use Transactional Replication with Updateable Subscriptions as long as it is still available.
Jared
CE - Microsoft
May 7, 2013 at 6:54 am
By reseed only we can manage the insert conflicts. But how come we manage conflicts of updates and deletes in peer to peer.
May 7, 2013 at 6:58 am
appu.cherukuri (5/7/2013)
By reseed only we can manage the insert conflicts. But how come we manage conflicts of updates and deletes in peer to peer.
http://technet.microsoft.com/en-us/library/bb934199%28v=sql.105%29.aspx
Jared
CE - Microsoft
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply