Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
daninor
Starting Member
3 Posts |
 Posted - 2007-11-21 : 06:58:29
|
| Here is a peculiar one:One of our products allows users to create "projects" that are stored in SQL Server, and currently we set one database per project, so that we can backup, restore and manage those projects separately. We name the databases in a way that maps directly to the project ID, so we may have databases named p1, p2 and so on.Some of our customers may have several thousands of those projects, which means several thousand databases, some containing large amounts of data but some very little data ("ad-hoc projects"), and the management of so many databases may get complex to our customers.We are currently investigating the possibility of reducing the number of databases. One of the options considered was use of database schemas and filegroups, but that is really a security feature and complicates on the maintenance side. Another option is of course extensive use of prefixes and have everything in the same database, but that is just no elegant way of doing it.What I was wondering here is if yet another option (and the best one) could be to somehow come up with a some sort of binary database provider for .NET that basically stores a complete database as a set of binary (blob) fields. With a GUID, timestamp and a few other additional fields, it would also be easy to import/export/backup rows representing entire databases without much hassle.Any opinions, tips and resources on this are very much appreciated.Thanks |
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-21 : 07:04:19
|
| The correct solution is to change the design of your schema. Add Project_Id everywhere. But it will be a dramatic redesign, former unique columns would become unique only together with prohject_ID, almost all primary keys and foreign keys would change, etc etc.As a quick and dirty fix I suggest you to use namespaces in SQL 2005. |
 |
|
|
daninor
Starting Member
3 Posts |
Posted - 2007-11-23 : 05:27:02
|
| Thanks, but I suspect my point didn't come across, perhaps I didn't describe the problem good enough.What I was thinking is to create a provider that would be able to transform a set of blob fields in a table row into a programmable .NET database model. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-11-23 : 08:33:06
|
| HiI think you got the problem described about right. Your problem is, I think, with your proposed solution. I don't even know how that would work but it is almost certainly a terrible idea.evilDBA is right - the project should be considered as part of the primary key of your tables. What project the data relates too is a data matter, not a metadata matter. This way you have one schema. How are you going to make changes to your schema if you have multiple schemas that all need to be identical? Your problem is common enough. It is much, much easier to manage data than to manage multiple "identical" databases. |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-11-23 : 12:07:58
|
| First let me say that I agree 100% with pootle_flump and evilDBA!But, if you are determined to pursue your solution - SQL Server has to have files for a database, so your proposal would require grabbing the binary data out of the database and writing a file and then attaching that before SQL Server could use it, which is going to perform horribly.A solution that may be feasible would be to store the smaller databases in the database as XML datasets and then serialize the datasets into memory when you want to use them. Or you could use a different product, db4o comes to mind, for the in memory databases. I am not endorsing db4o, i don't use it, but it would help you in this scenario. |
|
|
|
daninor
Starting Member
3 Posts |
Posted - 2007-11-25 : 16:36:23
|
| Thanks for your feedback, guys. I'm in no way determined to pursue my proposal, but I wanted to sound it out and hearing that it is a terrible idea is just the kind of feedback I was looking for (or great idea, if someone should feel it was).As for evilDBAs suggestion, even though that would be the most conventional approach, the reason things aren't that way is the backup issue, we need the ability to back up and restore data on a project-level, which wouldn't be so easy if everything was in the same database. Furthermore, that database would soon become unmanageable due to the sheer size of it, as we are talking in thousands and thousands of projects each month.I also checked out db4o but I don't think that is a way to go. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-11-25 : 17:24:01
|
quote:
Originally posted by daninor
As for evilDBAs suggestion, even though that would be the most conventional approach, the reason things aren't that way is the backup issue, we need the ability to back up and restore data on a project-level, which wouldn't be so easy if everything was in the same database. Furthermore, that database would soon become unmanageable due to the sheer size of it, as we are talking in thousands and thousands of projects each month.
The backup and restore issue is a complexity. Third party backup software (e.g. litespeed, red gate backup etc) allow row level restores now I believe. I really couldn't say how well these would apply to your problem however. TBH I think this might just have to be a cost you have to endure unless someone can think of something better. I don't think horizontal partitioning would really be suitable in this case.As far as "Furthermore, that database would soon become unmanageable due to the sheer size of it, as we are talking in thousands and thousands of projects each month" is concerned, this is not an issue. Adding thousands of databases, or blob psuedo-databases, each month would become unmanageable very quickly. Databases are designed to store data so why should it be a problem? If you treat the project as the first column in your primary key, and cluster index on this, you shouldn't have any major performance problems. You will need to consider capacity planning though this will be no different to having thousands of databases per instance. |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-26 : 03:02:17
|
| you probably need to add "import/export" capabilities to you software. Instead of backing up a database, you need to back up an export file (XML?) containing your project.Or, if it is done to "roll back" the project to some point in time, then you need to add "versions" to your projects. The data you have now in a project becomes just a slice in a database containing different projects and slices for each project.SQL server can handle billions rows, it is designed for it, but it is not designed to handle millions databases ! |
|
|
|
|
|
|
|
|
Storing database in database (?)