August 20, 2007 at 4:30 pm
Hello,
Our company develops a Centralized Management system for gaming facilities. Our situation is as follows:
Centralized Database where we have 1 SQL server and a single database. There are about 30 to 40 remote sites also with a server that are configured to replicate information back to the hall on a nightly basis. Throughout the year the databases at each remote site gather roughly 10GB of data.
We were currently thinking of changing the design to have mutliple databases at Central (I.e. One for each remote siste). In addition to this we would have multiple servers which contained 5 to 10 of each of these remote databases.
Does anybody see any flaws in this or perhaps can any suggest a better solution?
Any help or suggestions would be greatly appreciated!
August 20, 2007 at 11:13 pm
What is the existing single database used for? Depending on what you're using the existing database for, breaking it up could be painful (e.g. aggregated reporting?).
Joe
August 21, 2007 at 9:05 am
Currently the single database located at the Central location contains all the sales information for the remote sites.
Nightly on a scheduled basis the remote sites replicate information back to the central db.
The reason for breaking up the database is because of timing issues - I.e. we can't replicated 40 halls in one night back to a single database due to limited time.
As for breaking up the database into seperate DB's, I know that it will take some work to break it up into individual db's however we originally had it this way so we are familiar with it.
My question is, Is there any other options we can go with? A single DB also has some performance issues when we are doing consolidated reports. I'm curious to know how large companies such as banks deal with this. They must centralize all data at a main location..how is it managed?
August 21, 2007 at 9:17 am
"consolidated reporting"... that's what I kind of expected you to say which complicates matters - you're going to need to think through how you're going to reaggregate all that data once you've split it into 40 different databases across N servers and a what level you need to be able to report on that data.
It does make sense to create a layer of servers to accept transactions from your remote sites but you will then need to somehow aggregate/make available all that data for reporting - linked servers with views might work or you may need to then combine all the data into a single data store...
August 21, 2007 at 9:23 am
We the reporting is definately going to take some work - but we already have a web based application that currently has preset reports that do the querying. They'll have to be modified a little to adjust to the multple DB's.
I guess I was just concerned that we are creating more databases and adding more servers while most people want to consolidate for easier management.
So maybe we are on the right track...would you know of other technologies out there that would help me achieve what I am looking for?
August 21, 2007 at 9:56 am
Honestly, if you are thinking of this, I'd do it this way.
Replicate back to separate DBs on your main server. This gives you some DR stuff since you can quickly and easily move data for one store out to a new server. Think about what you replicate with this in mind.
Then create a data warehouse and use SSIS/DTS packages to nightly roll up the individual dbs into a central one. Spin off of here for reporting databases, marts, etc. You'll be in a good spot for building more BI type stuff and making this a strategic application rather than just a store.
August 21, 2007 at 11:02 pm
Steve makes a couple of good points - there is always more than one way to solve a problem.
One thing that I would probably recommend doing is taking a step back and determining why you want to break up your existing system in the first place and what you hope to accomplish by doing so. I'd also identify as best you can the costs associated, both short and long term, with the various options.
It sounds like you've already got a working environment but have concerns that it's undersized for what you're trying to do; however the costs to break up your existing environment into either 40 separate databases on a single server/cluster or multiple servers may actually outweigh the costs of buying one big honking server/cluster that can handle the load for the foreseeable future or upgrading your network to allow you move data more efficiently, etc.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply