SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


dynamic database generation


dynamic database generation

Author
Message
sreerekhavs
sreerekhavs
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86270 Visits: 45232
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
sreerekhavs
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86270 Visits: 45232
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
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39131 Visits: 32616
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
Ian Scarlett
Ian Scarlett
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2193 Visits: 6849
If you have one database per survey, you'll have a lot of fun trying to produce any consolidated reports...



Steve IoM
Steve IoM
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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.
steveb.
steveb.
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4354 Visits: 7195
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
einman33
Say Hey Kid
Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)Say Hey Kid (707 reputation)

Group: General Forum Members
Points: 707 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"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search