1 db or 3 db - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

1 db or 3 db

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2010-04-28 : 11:14:51

I have a huge database with student information, courses, etc.
I want to add quizzes and discussion forums
is it better to keep the discussion forums and quizzes in separate dbs
i.e quizzes, courses, forums will be 3 dbs
Use foreign keys to link for example
quizzes.quiz.studentid to courses.student.studentid
and
forums.threads.senderid to courses.student.studentid

performance wise which is better

sarah

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-28 : 13:10:59

You should use one database. I don't see any reason with what you have described to use separate databases.

When you say huge database, how big do you consider huge? I have a couple of 1 terabyte OLTP databases and some tables with billions of rows. We do not separate them out into smaller databases.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sarahmfr
Posting Yak Master

214 Posts

Posted - 2010-04-28 : 13:48:46

Thanks How you are distributing the filegroups indexes and logs
I am thinks of putting the database on three filegroups
one for data
one for indexes
one for logs
is that good
I am using a vm
Please advise

sarah

Kristen
Test

22859 Posts

Posted - 2010-04-28 : 13:52:05

I try to avoid multiple databases for the single reason that doing a restore, such that all 3 databases are exactly in sync, is way more aggro than just restoring a single database.

forums.threads.senderid where the courses.student.studentid no longer exists

(and I don;t think you can enforce a Foreign Key across databases, so you'd wind up having to use triggers ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-28 : 14:05:31

sarah,

That is fine, but we also separate out tempdb, backups, and the other system databases. On databases that are over 60GB, we use multiple data files. On one of our 1TB databases, we have 21 data files. Here is an example of that environment (each is a mount point):

F:\ (system databases, except for tempdb)
F:\Backup
F:\Data1
F:\Data2
F:\Data3
F:\Data4
F:\Data5
F:\Data6
F:\Data7
F:\Log
F:\Tempdb

Each Data mount point has 4 data files. Tempdb has 16 data files since we have 16 CPUs (4 quad-core sockets).

We don't currently separate out the indexes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-28 : 14:07:07

We have so many data files as the recommendation is to not have any data files greater than 64GB. We set ours to a maximum of 60GB. We keep adding files any time we get close to the 60GB threshold. Because we use 7 mount points pointing to 7 different RAID groups, we add 7 data files at a time but their initial size is small.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sarahmfr
Posting Yak Master

214 Posts

Posted - 2010-04-28 : 19:06:55

Thanks I am using vm not physical server would that make a difference.
Thanks a lot for your help

sarah

sarahmfr
Posting Yak Master

214 Posts

Posted - 2010-04-28 : 19:09:14

is Keeping the indexes on a separate file group beneficial performance wise.
Any recommendation about separating clustered and non clustered

sarah

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-28 : 19:15:36

Separating the indexes probably only makes sense if you are putting them on a different set of disks.

Using a VM or a physical server doesn't really change the recommendations. VM's can be configured to be very fast too.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources