dynamic database generation

Question

dynamic database generation

sreerekhavs

SSC Journeyman

Points: 86
More actions
July 2, 2008 at 11:49 pm

#187790

I have to generate an online survey tool. Using this system we can generate surveys dynamically.
The system may have, say hundred of surveys and related data within it.
Our main concern is that if the entire data is saving into same database that will affect the performance of sql queries. So we have decided to generate separate database for each survey, dynamically from our website. While taking the database design into consideration, please advice me if any better methods are available.
Thanks in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

From what you've said I'd recommend that you use one database and ensure that your code is written properly and your indexes are useful

How much data are we talking here? Few GB per survey or a couple TB per survey?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sreerekhavs SSC Journeyman Points: 86 More actions · Answer 2

thats we are maintaining information related to each survey seperatly in a seperate database ,size may be in 1-5MB's.If we store all the details in a single database it will be GB in size.we need to consider security aspects also.If someone hack the db realated to a particular survey only that information will lost.It will not affect other surveys information.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Rather create one database. The data volumes don't seem to be sufficient for the increase administrative complexity of multiple DBs

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

I agree with Gail. Further, it seems like a SurveyId would be a great candidate for the leading edge of the clustered index on most tables. This would store the data by Survey internally. If any given survey is only 1gb or so of data, then much better to manage 1 database with 500gb than 500 databases.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 5

If you have one database per survey, you'll have a lot of fun trying to produce any consolidated reports...

Steve IoM SSC Enthusiast Points: 160 More actions · Answer 6

And from a security point of view - if you really need to - you can partition the table by dynamically creating a view for each survey and setting the right access permissions on the underlying table and view.

steveb. SSC-Forever Points: 46733 More actions · Answer 7

sreerekhavs (7/3/2008)
If someone hack the db realated to a particular survey only that information will lost.It will not affect other surveys information.

This is not neccesarily true, depending on how your security is set up, a hacker can access other databases on your server if they hack into one.

einman33 SSCrazy Points: 2813 More actions · Answer 8

If your really concenred about it, schemas could help you to create some groupings. Also check out SQL 2005 partioning, much improved over 2000 and can come in handy in many situations. I am also a fan of "One database to rule them all"