July 10, 2009 at 2:47 pm
I'm currently working for a software house (ASP model) who has a number of production pools with hundreds of client databases per production pool. The lead developer would like to change the data model to put all of the individual client databases into one huge multi-schema database for each production pool. The thinking is to create a separate schema for each of the original databases in the large aggregate database. They are thinking that this will allow for the minimum amount of code changes in our db connection libraries etc.
Has anyone taken a similar step? If so, were there any drawbacks (I have already considered the issues related to managing backups). Any comments or suggestions would be MOST appreciated.
Thanks in Advance!
Steve
July 11, 2009 at 9:48 am
I considered this scenario at one point. The biggest factors in deciding to keep each client in it's own database were:
1) database size and ability to manipulate / backup / move / locate files
2) security
3) ability to switch out a client if they ever decided to go from the ASP model to in-house
4) flexibility on how to treat the data, for example have the option of additional environments for testing / training etc., separate databases give us more choice in how to handle that
Note that these are all administrative reasons rather than development reasons; there will be some work required in the code regardless of which way you go.
July 11, 2009 at 10:27 am
If the databases have the need to share data, then it makes sense to consider one large database. I've seen separate applications that need to share data, but had separate security requirements do well.
However, if you are trying to easy administration of clients by putting the data together, it's a bad idea. As Matt mentioned, the ability to separate out clients if there is a need is much easier if there are separate databases.
It also means you have a consistent security setup for each client, not dealing with potential issues from multiple schemas (deploying scripts, etc.).
July 13, 2009 at 8:16 am
Thanks for your answers. There are some cases where the data needs to be shared (aggregations of clients) but in some cases not. Depends on the level and version of the web app their using. Personally I think it is a stretch to put all of it into one large database. It will make the client backups, which some clients request, not doable and would completely change how we manage out backups.
Thanks again and if any one else has some input, please post it!
July 13, 2009 at 10:26 am
hadn't thought about client backups, but that's been a big request from me before and sucking out data for one client is a PIA!
One other thing to think about. Andy Warren used to work for a company that separated each client into a database precisely to ensure that security was followed. Also allowed client apps to be sure that they were connected to the right db. They had 250+ DBs on an instance, all the same structure. they even had a bunch of tables (lookup, meta data, etc.) that was the same in all database. They set up replication from a "master" database to move this common data from a mastermodel to each individual one.
This even allowed them to separate larger clients to their own server with essentially the same architecture, just move the subscriber.
July 14, 2009 at 3:06 am
Steve Slaughter (7/13/2009)
Personally I think it is a stretch to put all of it into one large database. It will make the client backups, which some clients request, not doable and would completely change how we manage out backups.
Per-client backups are 'do-able' using standard methods so long as data for more than one client doesn't exist in the same table, client data structures are placed on separate file-groups and the PRIMARY file-group contains no user data.
You're right that it would change your backup strategy though: It would be more complex, but arguably more flexible. I suppose I should also mention that if you need online piecemeal restore, the SQL Server needs to be Enterprise Edition, or equivalent.
Paul
July 14, 2009 at 7:31 am
Thanks Paul, I had not considered placing them is separate file groups. I'll need to explore this more.
Steve
July 14, 2009 at 11:18 am
Steve Jones - Editor (7/11/2009)
If the databases have the need to share data, then it makes sense to consider one large database. I've seen separate applications that need to share data, but had separate security requirements do well.However, if you are trying to easy administration of clients by putting the data together, it's a bad idea. As Matt mentioned, the ability to separate out clients if there is a need is much easier if there are separate databases.
It also means you have a consistent security setup for each client, not dealing with potential issues from multiple schemas (deploying scripts, etc.).
True - you may end up with one single security scheme. That said if you have that many clients you will end up with one devilishly ocmplicated security scheme, since you now have to ensure that clients cannot see each other's data (easy in a multi-database scenario, not at all easy in a single DB scenario).
This also has the other side effect of forcing all clients to "update" at the same time. Meaning - one update to the shared pieces would presumably change ALL clients, so everyone ends up in a "lock-step" scenario for upgrades. Cuts odwn on code versions to maintain, but can be brutal on customer relations.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 15, 2009 at 9:41 am
If you have separate databases then you can move them around to suit your needs.
In a virtual environment you can tailor the virtual server to the needs of the database.
If they are all in one huge database then I would seriously consider having separate filegroups and researching filegroup backups.
Looking at the way DB design is going designing for scale-out is getting increasingly attractive. SQL Server Enterprise Edition is extremely expensive when you have to use one big box. Being able to split down onto standard or web edition offers massive cost savings.
Hardware is relatively cheap, M$ licensing isn't.
July 15, 2009 at 3:01 pm
David.Poole (7/15/2009)
Being able to split down onto standard or web edition offers massive cost savings.
But fewer features and potentially poorer performance.
David.Poole (7/15/2009)
Hardware is relatively cheap, M$ licensing isn't.
It is compared to certain other database vendors.
July 16, 2009 at 3:24 pm
True Paul but the trick is to work out if you actually need those features and if not, why pay for them.
You wouldn't try and run your entire operation on SQL Server web edition but if you are building a sharding approach to data load then web edition may be viable.
Lots of places get away with standard edition and I can assure you that W2K8 + SQL2008 64bit can take one hell of a load even on quite a measily box.
July 17, 2009 at 2:10 am
David.Poole (7/16/2009)
True Paul but the trick is to work out if you actually need those features and if not, why pay for them.You wouldn't try and run your entire operation on SQL Server web edition but if you are building a sharding approach to data load then web edition may be viable.
Lots of places get away with standard edition and I can assure you that W2K8 + SQL2008 64bit can take one hell of a load even on quite a measily box.
Yes we run mostly Standard on x64 2K8 as you say, but the big boxes are Enterprise. I would be very much against moving to Standard on those boxes - there are many more differences between the editions than many people appreciate (read-ahead being one of them).
Each edition has its place of course - my previous post was to add balance.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply