Folks,
Help me figure this out once and for all!
We have three geographically dispersed offices, each with their own local SQL Server. We are creating a DB application (SQL Server 2005) for our primary business (insurance) and expect to have one database with replicas on the other servers. (There is no "primary" or home server -- each of the local replicas/servers are peers.) Users in each office will update records in the local replica of the database for clients principally served out of that office. All data, however, must appear on all replicas for business intelligence purposes. In addition, there will be some (though limited) amount of record upating in one office for clients principally served out of another office. The nature of the business is such that it is unlikely users in two different offices would be working on the same record at any one time. It is not unreasonable, however, that during the course of the day, two users in different offices might edit the same record. Therefore, data inserted/updated in one server should be replicated to the other two servers within no more than an hour or so.
Given the above, how should I approach replication The way I read the MS documentation, I should be looking at peer-to-peer merge replication. As an alternative, I might also use a peer-to-peer topology with a transactional replication. In the latter case, however, I should be looking at updating subscribers. Some here have suggested that neither approach is necessary and that I can use plain vanilla peer-to-peer replication with straight transactional updating (or I have completely misunderstood their posts). Because there is no central server, creating "non-peer" publishers on each of the local servers doesn't seem to be an option. As a final twist, so far as I have been able to figure out, MS SQL Server Studio 2005 will not allow me to create peer-to-peer replication with anything other than straight, non-updatable, updating -- With transactional plus updating subscribers the option to enable peer-to-peer is greyed out and the menu entry to configure peer-to-peer topology does not exist. With merge replication, the option does not even exist (on the GUI) and, again, the menu entry is missing.
Any thoughts, help, comments or criticisms on this subject would be most appreciated.
Thanks.
Randy
Which Replication Topology?
KPeters
Hi,
I ve a question. Why dont anyone suggest to use central database with partition tables to allow multiple clients to execute
Thanks
Il-Sung Lee - MSFT
Hi Randy, this is the 2nd time i've seen someone reference "peer-to-peer merge replication", unless I missed it somewhere, it doesn't exist.
Can you please take a look at Books Online topic "Selecting the appropriate type of replication", plug in this URL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/c76b01f6-0b1e-4b60-8d76-07faf0259129.htm, and review the scenarios again. Let me know if you have any questions.
What edition of SQL 2005 do you have P2P feature is only available on Enterprise Edition.
David Reeves
Interesting. I think what we have here is a failure to communicate on Microsoft's part. Read this link and you'll see why I believed there is a merge peer-to-peer topology.
Based on your response, I re-read very carefully the link you sent me. (Please believe me, I try to figure these things out myself before hollering for help!) I noticed for the first time that the table header half way down the page talks about peer-to-peer transactional replication enabling updates at the subscriber. Everywhere else the documentation speaks about straight transactional replications, it's pretty clear that it does not support subscriber updates in and of itself. (There is, of course, discussion of transactional replication with updating subscribers.)
So, with your input, I now understand that transactional peer-to-peer is the way to go. I strongly urge the folks at MS to think about some substantial revisions to the discussion of replication so that all this is clearer to us mere mortals. :-)
Thanks for your help!
Jeremy Vianna
Hi,
You might want to speak to us about a solution that we sell to customers that allows real-time replication of SQL databases, it also provides high availability for failover and failback scenarios. If you want more information and a free trial, please email me at paul.maziere@dataplex-systems.com
Regards, Paul.
Frent
I read the link, however I'm still not sure where it mentions peer-to-peer merge replication, however maybe there's some confusion as to the different kinds of replication.
Transactional replication, by default, is one-way replication. Meaning changes are made at the publisher and sent to the subscribers. Changes made to the subscriber will not be sent back up to the publisher. This is how it's been since day 1, and is suited well for high volume of changes requiring low latency over a well-connected link.
Merge replication, by default, is bi-directional replication. Meaning changes are uploaded/downloaded to publisher and subscriber, as well as data can be propogated to other subscribers as well. This is designed well for disconnected environments who want partitioned data, or all of the data. Depending on how the data is partitioned, a subscriber can get all or part of the data, changes from one subscriber can also be propagated to other subscribers. Merge replication also has conflict handling.
Transactional replication also allows updatable subscriptions where changes at the subscriber are replicated up to the publisher, this can happen via Immediate Updating subscriptions, Queue subscriptions and P2P (new in SQL 2005), all forms of Transactional replication. There's a time and place for these features, you have to check with your business requirements.
The link you pointed to talks about a given scenario and what would work. Since changes need to flow both ways and you need to achieve high availability and scalability in a server-server environment, it recommends P2P Transactional replication, but also mentions merge replication which, if designed correctly, can work.
The Books Online section for Replication has been rewritten extensively in SQL 2005, but the documentation is there. If you're new to replication, you could probably start with BOL topic "Configuring and Maintaining Replication" as it gives a good intro to different types of replication.