April 12, 2004 at 9:45 am
We currently have a single Microsoft SQL Server db-driven .Net web app for a single city. It handles much data storage and statistical analysis. Soon we will be increasing this to several other cities in several states, each city needing its own separate database. However, we then need to provide access from both state and national levels for some users, allowing these users to see and query across certain databases.
So my question is basically what is the best overall design to implement for this scenerio. I have never done anything like this, but I would assume there is a "standard" general way to implement this. I am not asking for every detail to the answer, but need to get started in the right direction. Whatever information/advice someone could give me would be greatly appreciated. Even directions to the best book or training resources would be great.
Some details that might come in handy.
1. The individual cities MUST have their own db.
2. My thought is to replicate the single dbs into one huge db each night, allowing the individual cities' web apps to always refer to the individual db and then having one "overall" web app refer to the one "overall" db. Hopefully, this would allow us to use the same web code for ALL situations.
3. I suppose the other alternative is to keep all the dbs separate and query across multiple dbs. But this would seem to me to be too slow and require much change in web or db coding (don't want to support more than one app).
4. To combine multiple dbs, this would seem to necessitate many tables to use GUIDs (yuck!) as keys so that they will still be unique after the combining. Sounds ugly (and perhaps slow), but don't know what else can be done.
Sorry for the verbosity of this.
Thanks.
April 13, 2004 at 9:25 am
First, I really have trouble getting past the requirement for a separate DB for each city. I work in a Fortune 50 company with 100,000+ users in 30 states and 38 foreign countries, and we would never create separate DBs by location.
However, with that as background, I will try to respond.
1- You could do it with distributed partitioned dbs/tables running on federated servers
2- You could do it with multiple DBs on a single, large server, use application or SQL Server security to control access and allow all-DB queries using views or UNION statements
3- You could use DTS imports or something similar (e.g. bcp out, zip, copy, unzip and bulk insert) to create the central DB. Use security to control access.
Probably many more ways, but that's what I can think of right now.
April 13, 2004 at 11:32 am
The idea of separate dbs per city (altho it is not actually a national/state/city app--but this analogy works very well for what I need to know) is what has been requested, but, if there is a compelling reason to avoid this, it may be negotiated.
I appreciate your thoughts on how to do this. I, unfortunately, have never done this sort of thing and need help in designing and implementing. Can you direct me to some good resources?
One concern I have is handling keys across the dbs. A unique key in on db is no longer unique when mixed with another. Do I have to use GUIDS? Can I use composite keys (cityid + tableid)? I am concerned about speed and reuse.
Thanks.
April 14, 2004 at 1:42 am
You could certainly use composite keys for uniqueness, but you could also use an identity field in each table with a specific range per city. Set seed as 1000000 and increment as 1 for city 1, then seed = 2000000 with increment as 1 for city 2, etc.
BOL has good general info on partitioning and federated servers, but I don't know of a really detailed resource on this - it's pretty heavy duty design for most SQL Server implementations.
http://www.databasejournal.com/ has some excellent articles on DTS packages.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply