Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

dynamic database generation Expand / Collapse
Author
Message
Posted Wednesday, July 2, 2008 11:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 8, 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
Posted Thursday, July 3, 2008 12:49 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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
Posted Thursday, July 3, 2008 2:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 8, 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
Posted Thursday, July 3, 2008 4:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 40,615, Visits: 37,080
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
Posted Thursday, July 3, 2008 7:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 14,205, Visits: 28,534
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 Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #528050
Posted Thursday, July 3, 2008 7:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:19 AM
Points: 1,307, Visits: 4,508
If you have one database per survey, you'll have a lot of fun trying to produce any consolidated reports...


Post #528054
Posted Friday, July 4, 2008 5:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:06 AM
Points: 56, Visits: 102
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
Posted Monday, July 7, 2008 8:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
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
Posted Friday, July 11, 2008 9:44 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr 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 »

Add to briefcase

Permissions Expand / Collapse