Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Stef574
Starting Member
3 Posts |
 Posted - 2011-08-25 : 05:40:07
|
| 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!RegardsStefan |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-08-25 : 07:57:49
|
| If there are going to be many nodes, then I would rule out Peer to Peer right away and start looking at Merge. That would mean creating a "Master" system though.By the way, Row Filtering is available in all types of replication. You simply add a WHERE clause. There is a lot of overhead associated with this, and performance can become an issue.Adding nodes is no big deal in either scenario.With both, you'll need to make sure that your primary keys are structured in a way so as to avoid collisions.Have a look at this articleAnd thisHow will the nodes "talk" to each other? Over WAN? What speed and what is acceptable latency? |
 |
|
|
Stef574
Starting Member
3 Posts |
Posted - 2011-08-25 : 08:55:06
|
| Thanks for the reply. Nodes will be communicating over a VPN, unsure of the speed at this stage. Latency would need to be pretty low realistically (a few minutes at most). So can row filtering work both ways? In other words can it decide what gets sent from the subscriber to the publisher (and then on to other nodes), as well as from the publisher to the subscriber? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-08-25 : 09:19:51
|
| Yes, but you may find it easier and more efficient (remembering that filtering incurs significant overhead) to store the sensitive records in a seperate, non-published table. A view or stored proc that performs a UNION ALL on the two tables will make this transparent to any front end apps.Seems that the "master" could be allowed to read everything, as long as it doesn't publish everything. But I like the above solution better for security and performance. |
|
|
|
Stef574
Starting Member
3 Posts |
Posted - 2011-08-25 : 09:47:15
|
| It may be a legal requirement that the restricted data doesn't physically leave the country of origin, so we'd have to ensure that it didn't get replicated back to the master.The idea of separate tables, combined with views that union the data, has occurred to me. I guess it would work but would complicate the database redesign further, so I was hoping there might be a simpler solution. |
|
|
|
|
|
|
|
|
Best type of replication for us?