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.
| Author |
Topic |
|
modemgeek
Starting Member
12 Posts |
 Posted - 2008-05-22 : 13:59:21
|
| My boss has asked me to look into this and I haven't been able to find any information on the web. I hope someone can answer this for me. We currently have a single database that is storing all the user information and transactions. Within the same database we are also logging different types of user activity. If both these tables are heavily used, would it make sense to separate it into different database, one for data and one for logging? Is there any pro or cons of having more than one database? Any opinion or suggestion would be greatly appreciated. I'm the closest thing they have to DBA and I'm really new to this. Thanks. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-22 : 14:28:01
|
| Actually there is no performance benefits as additional database will consume same memory of server. Seperating heavily used table in different filegroup in seperate drive will give you performance gain. you can consider using Table Partition if you have 2005 or Partition views if you have 2000. |
 |
|
|
modemgeek
Starting Member
12 Posts |
Posted - 2008-05-22 : 17:23:21
|
In regards to backups, if our database gets too large, should be separate the table into it's own dtabase so backups don't take as long?quote:
Originally posted by sodeep
Actually there is no performance benefits as additional database will consume same memory of server. Seperating heavily used table in different filegroup in seperate drive will give you performance gain. you can consider using Table Partition if you have 2005 or Partition views if you have 2000.
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-22 : 17:29:29
|
quote:
Originally posted by modemgeek
In regards to backups, if our database gets too large, should be separate the table into it's own dtabase so backups don't take as long?quote:
Originally posted by sodeep
Actually there is no performance benefits as additional database will consume same memory of server. Seperating heavily used table in different filegroup in seperate drive will give you performance gain. you can consider using Table Partition if you have 2005 or Partition views if you have 2000.
If you don't want to backup the log tables, then I'd say yes move it to another database. Otherwise, look into purchasing 3rd party backup tools such as SQL Litespeed from Quest. These backup tools compress the data, so you'll have smaller backup files and quicker backup times. Red Gate also has a similar product, but I understand that it compresses it at the end rather than during the backup.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-22 : 17:53:20
|
| Yes if you do full backup,it will take time for big database.If you put heavily used table in separate FG and perform FG backup,then it won't take long time or you can get compression tools like SQL lite for this. |
|
|
|
modemgeek
Starting Member
12 Posts |
Posted - 2008-05-22 : 17:57:49
|
| Thanks everyone. |
|
|
|
|
|
|
|
|
Should I seperate tables into new databases??