Thread: Best type of replication for us?
08-25-11, 06:36 #1Registered User
- Join Date
- Sep 2002
Unanswered: Best type of replication for us?
We're in the early stages of planning a database upgrade/redesign/replication project. I have little experience of replication and the requirements will probably influence the new design, so I'm trying to get an idea of the best type of replication to use from the start. The databases in question are supporting a custom help desk type application which we plan to update and roll out globally.
The main aims of the project are:
* Migrate from two legacy databases (both run on SQL 2000, one of them is native 2000 and the other runs in 6.5 compatibility mode) to 2008 R2. Unsure whether we'll need to go with Enterprise or Standard edition at this point, replication considerations could affect this decision. There is currently one instance of each database, serving a single site.
* Once the above is complete we want to be able to set up remote instances of the new database as the system is rolled out to other sites.
The specific requirements for replication are:
* All sites need to be able to read/write data. All sites will be performing the same function so there will be no master database as such.
* Some data will probably need to be stored only at the site where it was added. So support tickets and associated records for designated sensitive customers would need to stay in the site where they were added, and not be replicated globally.
* All records not flagged as sensitive would need to be replicated globally, in other words replicate by default unless we specify otherwise.
* Tickets will need to be transferred between teams/sites (could be at the end of the working day in a particular location, or due to escalation etc.) so we'd need fairly low latency to make sure the teams were seeing consistent data without much delay, and to make sure that the application could lock tickets for editing by just one person at a time.
* Potential number of sites/users is unknown at this stage. We would want to be able to add/remove additional instances of the database with as little disruption as possible to the operation of the other instances.
* We'd may want to be able to switch our client web application to use a different instance of the database in a DR scenario (obviously any records just stored at a particular location would be inaccessible if that instance was unavailable but this might be acceptable on a temporary basis). We may also want to use mirroring or log shipping etc. in future to provide further resilience.
So I'm trying to determine the best type of replication for this scenario. I initially looked at peer to peer transactional replication. This seems like a good fit for many reasons, being decentralised and having low latency. However it looks like it doesn't support row filtering which would rule it out on the grounds that we would have to ensure that some data is not replicated from it's particular instance. Might it be possible to get round this by partitioning tables so that a portion of them get replicated and a portion doesn't?
Merge replication may be the next best option but it seems to be geared more towards having a master publication database and pushing out subsets of the data to subscribers. I'm not clear on whether it's possible to configure row filtering so that certain records will only be kept on the subscriber, and not replicated back to the publisher or other nodes? Also what happens if the publisher is offline or unable to connect to the subscribers, can they all operate independently while that is the case?
If neither of the above are appropriate then are there other options which would fit better?
Any help or guidance would be very much appreciated!
08-25-11, 12:36 #2Annie's Dog Walker
Provided Answers: 6
- Join Date
- Nov 2004
- on the wrong server
No replication. Service broker.If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry. Earnest Hemingway, A Farewell To Arms.