dynamic database generation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » dynamic database generation

dynamic database generation

Rate Topic

Display Mode

Topic Options

Author

Message

sreerekhavs

Posted Wednesday, July 02, 2008 11:49 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 08, 2012 2:19 AM
Points: 6, Visits: 164

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.

Post #527770

GilaMonster

Posted Thursday, July 03, 2008 12:49 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:25 AM
Points: 37,679, Visits: 29,934

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 2008, MVP
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

Post #527803

sreerekhavs

Posted Thursday, July 03, 2008 2:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 08, 2012 2:19 AM
Points: 6, Visits: 164

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.

Post #527848

GilaMonster

Posted Thursday, July 03, 2008 4:43 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:25 AM
Points: 37,679, Visits: 29,934

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 2008, MVP
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

Post #527901

Grant Fritchey

Posted Thursday, July 03, 2008 7:40 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 13,372, Visits: 25,154

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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #528050

Ian Scarlett

Posted Thursday, July 03, 2008 7:44 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 2:12 AM
Points: 1,322, Visits: 4,400

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

Post #528054

Steve IoM

Posted Friday, July 04, 2008 5:45 AM

Valued Member

Group: General Forum Members
Last Login: Monday, December 17, 2012 7:44 AM
Points: 56, Visits: 74

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.

Post #528628

steveb.

Posted Monday, July 07, 2008 8:15 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 7:42 AM
Points: 2,802, Visits: 7,103

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.

Post #529343

einman33

Posted Friday, July 11, 2008 9:44 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 8:14 AM
Points: 567, Visits: 512

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"

Post #532939

« Prev Topic | Next Topic »

Permissions