SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replication options - merge or peer-to-peer


Replication options - merge or peer-to-peer

Author
Message
ktalley.dba
ktalley.dba
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 1060
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?
samalex
samalex
SSC-Addicted
SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)SSC-Addicted (423 reputation)

Group: General Forum Members
Points: 423 Visits: 1061
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.
ktalley.dba
ktalley.dba
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 1060
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.
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4986 Visits: 3694
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
ktalley.dba
ktalley.dba
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 1060
I think this option (Transactional Replication with Updateable Subscriptions) is deprecated. It won't be support after SQL 2012.
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4986 Visits: 3694
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
appu.cherukuri
appu.cherukuri
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 98
By reseed only we can manage the insert conflicts. But how come we manage conflicts of updates and deletes in peer to peer.
Jared Karney
Jared Karney
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4986 Visits: 3694
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search