Design Issues
-
Hi everyone,
We are in the designing (design upgrade) phase of a database project and my question to other DBA's or data modelers is;
Background Info:
OS - Windows 2000 Server, MS SQL 2000
Replication Topology:
dB has remote sites (db's) replicating to a central db server(which is push subscriber). This is 2000 to 2000
Question:
When dealing with replication is it a better design practice (for performance and administrative reasons) to have many dB's replicating to one authoritive dB? Or many dB's replicating to many dB's.
So let's say I have a 2 companies that each have 5 site locations. Each location (is its own dB, for data collection purposes) You can see right off the rip where this can be a maintenance nightmare. Each company's data should theoretically be seperate.
We'd like from an administrative point of view have the 10 dB's be consolidated into one dB per company. (so one dB would represent the data from 5 site's).
However, is this actually the best way to go? Am I considering all my options? Anyone with any thoughts? I would greatly appreciate some input as we are debating the issue currently in the office and would like to offer an educated (semi tested) input.
Let me know what you guys think.Thanks,
Christine
Aurora
-
Seems like either merge or transactional with updating subscribers would do the trick pretty well.
Andy
-
Thanks Andy, but I guess what I really want to know (and I realize this is a professional opinion, just hoping someone has been confronted with this design question)
When dealing with replication is it a better design practice (for performance and administrative reasons) to have many dB's replicating to one authoritive dB? Or many dB's replicating to many dB's? In other words, right now each remote site replicates to to its own dB as opposed to each remote site replicating to(a newly designed dB which incorporates all the sites)one dB. Is there any significant performance issues we need to consider, etc.
Let me know what you think,
Christine
Aurora
-
Not having had this situation, take this with a grain of salt.
I try to "loosely couple" things together. Why? So failures to not cascade. Also, updates, upgrades, etc. do not bring down entire systems. I like the idea of moving to a consolidated db, but if I could, I'd replicate to separate dbs and then consolidate this data. That way if one replication goes out of control or fails for some reason, it does not take down 4 others. Nice to have a little separation between items.
Steve Jones
-
Im for loose coupling, but I think from what you've described rolling them up makes sense, dont know that I'd add the extra layer Steve suggests - but it's worth considering. From a management perspective rolling each of the remote db's into one parent db makes sense...after all, if you look at it the other way they are really just slices. Using immediately updating subscribers with the queued update option or merge both offer the ability to keep working even if the rollup db is offline. Only performance issue you generally have to contend with is snapshots, over a slow link they can take a while.
Andy
-
Thanks guys,
The information you offered is greatly appreciated. As we move forward with our design discussion I will be sure to research more on the options we discussed and offer them to our group. I am a strong supporter of the consolidated dB method (obviously for administrative and management reasons)however, there is a strong case for the loose coupling design. I'll keep you guys posted on how we make out.
Thanks again, this site is awesome!!!!!
Christine
Aurora
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply