August 2, 2011 at 7:38 am
I got this question from my company today, I'm not sure how to answer. We have 3 sites in North America with good bandwidth on our WAN. Anybody have a good answer to this?:
We want to put in a new SQL based Quality system for all three NA sites . I would like to have a local server at each site so that we could have fast instant access to the data but I only want 1 database. With that said, I'm imagining that on a periodic basis (to be determined if that is once an hour, once a day or realtime), each of the databases would be updated to reflect changes made in the other two.
Is this possible?
How difficult/costly it would be?
How much bandwidth would it take?
How much trouble would it be to maintain?
Would a single shared database server at one of the sites be better/fast enough?
August 2, 2011 at 7:49 am
I would only go that way if I was absolutly sure that a single shared database at one site would not be practical.
August 2, 2011 at 7:59 am
denver.drake (8/2/2011)
I got this question from my company today, I'm not sure how to answer. We have 3 sites in North America with good bandwidth on our WAN. Anybody have a good answer to this?:We want to put in a new SQL based Quality system for all three NA sites . I would like to have a local server at each site so that we could have fast instant access to the data but I only want 1 database. With that said, I'm imagining that on a periodic basis (to be determined if that is once an hour, once a day or realtime), each of the databases would be updated to reflect changes made in the other two.
Is this possible?
How difficult/costly it would be?
How much bandwidth would it take?
How much trouble would it be to maintain?
Would a single shared database server at one of the sites be better/fast enough?
More detailed information is needed to have the opportunity of giving you an educated guess.
How big is the database?
How much tlog is generated?
How many concurrent users are sending requests to the database?
Last but not least, having a handsome bandwith in between locations... is it not enough to serve your remote users?
Finally... if synch is actually needed how about transactional replication?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 2, 2011 at 8:19 am
Good questions. Since this is a "we're thinking about it" situation, I have no idea what the actual size of the database will be, or anything about the Tlog. However, I would think that it will be relatively small. Quality results are input by 1-3 lab technitions at each site periodically throughout each day. Shouldn't be more than 10 people connected at any one time, more likely less than 5.
I have no idea what "transactional replication" is. I think the only real issue with having a single centralized database is that this data is used by the process computers at each site, to automatically adjust the product, and each order that goes out must pull data for the shipping paperwork. Therefore, any loss of connectivity to the central site could potentially shutdown production at all sites.
August 2, 2011 at 8:31 am
denver.drake (8/2/2011)
Good questions. Since this is a "we're thinking about it" situation, I have no idea what the actual size of the database will be, or anything about the Tlog. However, I would think that it will be relatively small. Quality results are input by 1-3 lab technitions at each site periodically throughout each day. Shouldn't be more than 10 people connected at any one time, more likely less than 5.I have no idea what "transactional replication" is. I think the only real issue with having a single centralized database is that this data is used by the process computers at each site, to automatically adjust the product, and each order that goes out must pull data for the shipping paperwork. Therefore, any loss of connectivity to the central site could potentially shutdown production at all sites.
I see. Well... any loss of connectivity will also affect synch processes 🙂
For transactional replication please check here: http://msdn.microsoft.com/en-us/library/aa179423(v=SQL.80).aspx
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 2, 2011 at 9:06 am
Are you looking to sync all three databases back to each other or simply back to a centralized DB (sorry if I'm not reading your original post correctly)?
If you're only needing to sync back to a centralized DB then you can use transactional or snapshot replication (works rather well). If you're going to be making changes to the data at each of these 3 satellite locations and that changed data needs to go back to the centralized DB, then you'll need something a little more involved.
Are you familiar with SSIS?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 2, 2011 at 9:11 am
There will only be 3 sites involved, techs at each site will be entering lab results for their own sites. However, all 3 sites want to use the same database, that each has a replica of on a local server. In this way, a Quality Manager, or Engineer at any site could get reports on all 3 sites from their local server. So technically, there is no "Master", they are all equal peers.
August 2, 2011 at 10:41 am
In that case,IMO you're going to be limited to using a custom solution than anything you'll find out of the box...
I'd try pitching the centralized database idea...If that's not possible you're going to need to do some sort of manual ETL process between the servers...and that could get hairy to say the least: periodically load data from the peers into a "holding table", detect changes amongst the peers, update/delete if necessary, re-sync, and so forth.
If it were me I'd have a centralized server that the peers get it's main data from (say a few times daily), then based upon your biz requirements, load data that's changed in the peers since the last sync into a set of holding tables, then assuming that a peer cannot change the other peer's data, compare each record from each peer against the original data on the centralized server...delete the original record, inserting the updated record from the peer. During the next snapshot from the centralized server to the peers, they would all reflect the new changes.
Of course this depends greatly on the business requirements and the volume of data...I've done something quite similar in the past using SSIS and TSQL and could assist.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 2, 2011 at 10:52 am
>>Would a single shared database server at one of the sites be better/fast enough?
Definitely would be simpler and for me that often means better.
Is it really acceptable for one of your sites to be working on an out of date database?
How will you resolve conflicts if they exist?
If you use transactional replication (real-time) is the performance really going to be significantly faster than the one-database strategy?
If performance is unacceptable would additional network capacity be cheaper than maintaining database replication?
What's your DR strategy for each scenario?
What problem are you trying to solve, performance or availability?
I would evaluate the simpler solution first (a single optimized central database) and then move on if it didn't met my needs. Managerial types typically don't understand the complexities of what they call 'synching'.
If you go with the one database solution also consider mirroring to a second site for high availability.
Good luck.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply