August 31, 2010 at 2:54 am
Hi,
I' m relativly new as SQL Server DBA. I've question about standard installation of a SQL Server Database.
Microsoft recommends using "User Filgroups" instead of the "Primary Filegroup", what exactly is the benefit of using User Filegroups instead of Primary with multiple Datafiles?
And why does Microsoft put the tables of their default databases, like ReportServer or Data Collection (Management Data Warehous), in the Primary Filegroup.
Thanks for your answers
Sam
August 31, 2010 at 5:05 am
This list is not exhuastive, but some reasons you may want to place user tables / data on seperate user created filegroups are:
Admin - you can backup and restore filegroups seperately.
Performance - You can place heavily used tables on different fielgroups, and place those files on seperate disks to avoid IO contention.
August 31, 2010 at 5:34 am
The benefit of not having the data of your user objects in the primary filegroup, is that in such case, your primary filegroup would only contain catalog info.
This may be interesting if you want to perform partial restores. (Ent. feature)
With a partial restore, you need to restore the primary filegroup (because it holds the catalog info) and the filegroup(s) that actually hold the data of the objects you are interested in.
Having your own filegroups, may be your first step on managing IO in favor of your sql server database(s) because you can put the different data files on disks with different characteristics, ...
When creating a table or index, you can only point to a filegroup for the object to reside to. If a filegroup has more than one file, you have no control over which file the actual data will be written to. SQLServer will balance the data over the files that belong to the same filegroup.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 31, 2010 at 6:03 am
Thanks guys, that are some good points.
But the problem we have is, lots of "out of the box" applications are developed like put all the tables and indexes (on primary).
After the applications are installed we have to move all the objects to the right filegroup.
If there is an application with hundreds of tables and n-indexes, is it a lot of work.
On the other hand, we have SAN storage, so that means i can't tell which files are on which disk.
thanks for the answers.
Sam
August 31, 2010 at 6:23 am
You can set a filegroup to be the "default".
Alter database [yourdb] MODIFY FILEGROUP FGUserData DEFAULT;'
This means, if they don't specify [primary] as the objects filegroup, the object will be created in the FGUserData filegroup.
So you should still double check if the installers specify the filegroup or not.
If they do, maybe you can have them use the one you want.
It's a petty MS didn't implement a default filegroup for non clustered indexes. :ermm: (like they did for image/text columns at table level)
BTW If you SAN is actually being managed for performance, your SANAdmin(s) will manage LUNs for high speed disks or low speed disks, not mixing the up, as they will do with SAN-mirroring and if they can , they may even be able to determine not all LUNs to reside on raid5 volumes.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 31, 2010 at 6:29 am
thats the problem, the application creates the objects with "create table xyz .... on primary" hard coded
August 31, 2010 at 6:48 am
DBASam (8/31/2010)
thats the problem, the application creates the objects with "create table xyz .... on primary" hard coded
Yep .... stupid, but hard reality.
Still, just pop the question to the vendor, if this can be altered. (best they just don't specify the FG, or they provide install parameters to configure their application to use the desired filegroups for data and non-clustering indexes.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply