April 19, 2007 at 8:36 am
Good morning everyone,
I wasn't suere exactly where to post this so if this is the wrong group, I do apologize. I am trying to expand / confirm my understanding of filegroups and have a quick question.
lets say you have a 5 disk raid(5) set that you are placing multiple backup data files on. If you never take the time to create multiple filegroups and all DB objects reside in the primary filegroup, does the fact that you have multiple backup data files on different disks gain you anything? Or does all processing still have to go through the one primary filegroup so paralell processing doesnt occur???
I'm just trying to get a solid understanding of how the filegroup / multiple data files works. If there are additional resources regarding filegruops someone can point me to, that would be much appreciated as well.
Thanks,
Bob
April 19, 2007 at 8:52 am
Backups have no relationship to FILEGROUPS.
The bennefit of multiple backups files in a spanned format is that one thread per backup device is created and operating. However if they are on the same drive/drive array there will be minimal performance gain.
April 19, 2007 at 12:12 pm
Well, I'm very new to SQL so I'm going to tread lightly here.
However, I don't know that we are talking about the same thing. I am not referring to "backups" such as when you backup a DB. I am referring to multiple .mdf files where all of the non-primary .mdf files would be .ndf files. In SQL 2005 you can have up to 32k+ secondary DB files for any given DB, with each / multiple of those DB files belonging to a particular filegroup. Then, objects in the DB can be associated to a particular filegroup, in turn associated with a particular database file on one of the disks in the array.
So my question has nothing to do with DB backups but with secondary DB files.
Does that clear up what I initially asked?
April 19, 2007 at 2:50 pm
I believe your talking about Physical Files as related thru "file groups' to the SQL Server
The primary data file contains the startup information for the database and points to the other files in the database. User data and objects can be stored in this file or in secondary data files. Every database has one primary data file. The default file name extension for primary data files is .mdf.
Secondary data files are optional, are user-defined, and store user data. Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive. Additionally, if a database exceeds the maximum size for a single Windows file, you can use secondary data files so the database can continue to grow. The usual file name extension for secondary data files is .ndf. In prefer to let SQL Server manage my physical allocation, but the DBA next to me puts all of his indexes on a the secondary file group and physical disk. The problem is that his allocation the clusters end up in the secondary as well....For Huge databases, one way to span the small disk problem is to use multipole file groups that are on more disks.
The transaction log files hold the log information that is used to recover the database. There will be at least one file for each database. The default file name extension for transaction logs is .ldf. If your disk is filling up because of a huge transaction, you can add a secondary log file and the transaction log can continue to grow across multiple disks.
And while on sthe subject of disks, I prefer raid 1-0 or raid TEN over Raid 5.
Eric
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply