Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replication options - merge or peer-to-peer Expand / Collapse
Author
Message
Posted Wednesday, December 05, 2012 2:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 2:56 PM
Points: 8, Visits: 497
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?
Post #1393231
Posted Wednesday, December 05, 2012 3:16 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 07, 2014 12:05 PM
Points: 183, Visits: 823
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.
Post #1393242
Posted Thursday, December 06, 2012 8:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 2:56 PM
Points: 8, Visits: 497
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.
Post #1393552
Posted Thursday, December 06, 2012 8:44 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:37 AM
Points: 2,673, Visits: 3,325
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.


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1393572
Posted Thursday, December 06, 2012 8:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 2:56 PM
Points: 8, Visits: 497
I think this option (Transactional Replication with Updateable Subscriptions) is deprecated. It won't be support after SQL 2012.
Post #1393578
Posted Thursday, December 06, 2012 9:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:37 AM
Points: 2,673, Visits: 3,325
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.


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1393604
Posted Tuesday, May 07, 2013 6:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 31, 2013 6:06 AM
Points: 1, Visits: 51
By reseed only we can manage the insert conflicts. But how come we manage conflicts of updates and deletes in peer to peer.
Post #1450111
Posted Tuesday, May 07, 2013 6:58 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:37 AM
Points: 2,673, Visits: 3,325
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


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1450113
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse