July 13, 2011 at 1:30 am
I am planning to design a Database in sql server 2008
The DB is divided into following tables
1> Read only data (static data updation will happen once a year)
2> Read and write data (Contain Booking,customer,Inventory table and all the procedures, making it as a default filegroup)
3> Log data (archive the data after 4 months)
4> system data
Is it better to add 1,2,3 tables in different Secondary filegroup or to add all the tables in same secondary filegroup but in different secondary files. Which is the better option?
I am also planning to add some application user configuration settings along with the system data in primary filegroup is it a better option or just store the system data only in primary filegroup.
Also is it better to store all the primary and secondary file in a same disk in a same drive
For example
In D:\ Drive i am going to store all the primary and secondary file and nothing else will be stored here
In E:\ Drive all the DB backups will store.
Is ther any performance isssue?
The database is B2C travel Domain.
Anticipating for a favourable reply:-)
Thank you
with regards
Dilip D
July 13, 2011 at 5:23 am
If you put Read Only data on a different file group, you can actually change your backup options to only backup the Read-Write filegroup. However, that changes your recovery strategy also, so you want to research recovery & backups before making that change.
Whatever you do, remember that if the secondary file group is set as Default, all data will be striped against all the files in that filegroup. And if you're not going to be taking advantage of using different disk drives for performance or specific partial / file / filegroup backups, why do you need to separate your data into different files or filegroups anyway? There's really no point that I can see.
July 13, 2011 at 5:50 am
I agree. There's no point in putting tables in different filegroups if you are going to keep files of each filegroup on the same drive.
July 13, 2011 at 6:15 am
thanks for the reply
that means Even if i use different filegroups there is no improvement in DB performance if I don't use different physical disk drive right.
Is there any improvement if i seperate the READ ONLY table in a seperate DB and READ/WRITE table in seperate DB.
July 13, 2011 at 6:39 am
dilipd006 (7/13/2011)
that means Even if i use different filegroups there is no improvement in DB performance if I don't use different physical disk drive right.
Pretty much.
Is there any improvement if i seperate the READ ONLY table in a seperate DB and READ/WRITE table in seperate DB.
I would not do that at all. It makes maintenance a nightmare. Not to mention all the TSQL joins between databases (3 part naming conventions) that you have to remember to add to your code. Also, there's a disk controller issue. If you're keeping the data on the same physical drive, and have only one disk controller, then the I/O will only be so fast regardless of what format the data is separated into.
Poor performance is usually an issue of poor database schema setup and poor coding, more than it is a function of virtual data layout. The only exceptions to this rule are 1) bad or under-performing hardware and 2) data fragmentation.
Are you actually having problems with performance or are you just trying to learn how this all works?
If the former, please let us know what issues you're having so we can advise you better. If the later, then don't stress over the multiple file / filegroups if you have only 1 disk. Just throw everything into one place unless you want to do the Backup / Recovery strategy of only backing up Read-Write data on a regular basis.
July 13, 2011 at 7:37 am
thanks Brandie
As i mention i am planning to design a DB for B2C Travel project.So before starting developing i wanted to know the various design issues.
As you mention there will be maintenance problem and coding problem (join) if i create two seperate DB.The READ only DB contain Static data which will be updated once in a year, so i have to take the backup of this DB once or twice in a year.
To the coding problem there would not be any join coding between READ and READ/WRITE DB , but yes there will be name specification(DB.schema.object) as you mentioned.
I think i go for the filegroup atleast we can manage everything in one DB.
I think it will be better to handle the index maintenance and backup, this will not affect the other filegroup.
July 13, 2011 at 8:59 am
Glad I could help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply