July 9, 2010 at 2:20 pm
First of all, I'm down with how to partition tables (i understand the reasoning and steps) and how data that belongs in partitioned tables gets written to respective filegroups. But, I am confused about the concept of partitioning databases.
If I create a database whose data files are dividing among 13 files (like below), then along which boundaries (or range values, as one would put it if one were defining a table partition function) will data be written to the filegroups?
CREATE DATABASE db_name
ON PRIMARY
(NAME = db_system, FILENAME = c:\db_system.mdf, SIZE = 2MB),
FILEGROUP FG1
(NAME = db_data1, FILENAME = c:\db_data.ndf, SIZE = 2MB),
FILEGROUP FG2 -> FG3, FG4,...all the way up to....
FILEGROUP FG13
(NAME = db_data13, FILENAME = c:\db_data.ndf, SIZE = 2MB)
LOG ON
(NAME = db_log, FILENAME = c:\db_log.ldf, SIZE = 2MB)
GO
Is the above framework designed to support the partitioning of just one table within the database called db_name?
Thank you!
Helen
July 9, 2010 at 2:38 pm
Unless you specify a filegroup in the create statement all the data would get written to the data file in the primary group.
to get data to write to a different filegroup by default you would have to specify that filegroup as the default filegroup using alter database.
---------------------------------------------------------------------
July 9, 2010 at 2:46 pm
george sibbald (7/9/2010)
Unless you specify a filegroup in the create statement all the data would get written to the data file in the primary group.to get data to write to a different filegroup by default you would have to specify that filegroup as the default filegroup using alter database.
I am sorry but I was editing my original post while you were responding.
Along which boundaries will data get written to these filegroups? I am wondering if this kind of partitioning is put into place to support the partitioning of a specific table? If no then how does data in that database get divided up among the different filegroups ie. .ndf files?
Thanks.
July 9, 2010 at 3:38 pm
If I understand the question correctly my original answer holds true for unpartitioned tables.
You can partition more than one table across the same filegroups.
---------------------------------------------------------------------
July 9, 2010 at 3:52 pm
george sibbald (7/9/2010)
If I understand the question correctly my original answer holds true for unpartitioned tables.You can partition more than one table across the same filegroups.
To partition a table my understanding is that one takes the following steps:
1. creates a partition function in which one defined the datatype of the column to be partitioned and the range of values per partition
2. creates a partition scheme in which one defines the name of the filegroups that will take each partition
3. creates a table ON the partition scheme. This table will have a column with the datatype specified in the partition function.
Is there really any dependency on the partitions of the database? If yes, then do all databases have to be partitioned right from the start with the assumption that somewhere down the line a table will be partitioned. Is there a best practice ie. always 13 Filegroups for the database? Doesn't make sense to me.
Still stumped.
July 9, 2010 at 5:34 pm
no best practice as to no of filegroups. Maybe a primary plus one more to act as default group for user data.
If you decide at some point you need to partition a table, add the filegroups to cope with it. hopefully that is the sort of thing you know up front you are going to need to do.
---------------------------------------------------------------------
July 9, 2010 at 6:53 pm
george sibbald (7/9/2010)
no best practice as to no of filegroups. Maybe a primary plus one more to act as default group for user data.If you decide at some point you need to partition a table, add the filegroups to cope with it. hopefully that is the sort of thing you know up front you are going to need to do.
So, if I understand you correctly, then
1. when you first create a database you may or may not have lots of partitions (aka filegroups) on it. Typically, one marked as the default for data in addition to the Primary.
2. But, if later you decide you want to partition a table 5 ways you need to have the database that the table resides in partitioned 5 ways as well (ie. with 5 filegroups) and then you can proceed with creating the partition function, creating a partition scheme, and moving that table to the part scheme.
If I am correct about this then I feel it all makes sense again. Please confirm.
July 10, 2010 at 7:02 am
Correct.
calling filegroups database partitions is not normal terminology and may cause confusion. Just think in terms of filegroups, partition schemas and functions.
---------------------------------------------------------------------
July 10, 2010 at 9:36 am
Thank you, George Sibbald.
July 11, 2010 at 8:30 pm
I have another question related to this. .
If I have a table that I partition across a number of filegroups in a way that is favorable to me, what happens to the other tables that are not assigned a partition scheme? Does data pertaining to those just write across the filegroups in an arbitrary fashion?
July 12, 2010 at 3:18 am
This was removed by the editor as SPAM
July 12, 2010 at 4:57 am
No. Data for a non-partitioned table will be confined with a filegroup. You can split out nonclustered indexes to a different filegroup but you have to explicitly define that behavior when you create the indexes.
If you have multiple files within a filegroup data will be written to all files in the filegroup in a round robin fashion using a proportional fill algorithm (the emptier a file is the more data is written to it)
---------------------------------------------------------------------
July 12, 2010 at 11:27 am
george sibbald (7/12/2010)
No. Data for a non-partitioned table will be confined with a filegroup. You can split out nonclustered indexes to a different filegroup but you have to explicitly define that behavior when you create the indexes.If you have multiple files within a filegroup data will be written to all files in the filegroup in a round robin fashion using a proportional fill algorithm (the emptier a file is the more data is written to it)
Bear with me a little longer please. To which filegroup in below scenario would a newly created table with no 'ON partscheme' definition store data?
I am trying to cinch down on, what is the benefit of being able to back up and recover filegroups independently if only some them have tables that are deliberately managed across them. Since it is unlikely that every table will have a date_time column, I don't see how any filegroup would contain any complete cross section of data on it's own.
CREATE DATABASE db_name
ON PRIMARY
(NAME = db_system, FILENAME = c:\db_system.mdf, SIZE = 2MB),
FILEGROUP FG1
(NAME = db_data1, FILENAME = c:\db_data.ndf, SIZE = 2MB),
FILEGROUP FG2 -> FG3, FG4,...all the way up to....
FILEGROUP FG13
(NAME = db_data13, FILENAME = c:\db_data.ndf, SIZE = 2MB)
LOG ON
(NAME = db_log, FILENAME = c:\db_log.ldf, SIZE = 2MB)
GO
July 12, 2010 at 12:03 pm
Any tables not assigned a partition scheme or explicit filegroup will default to the primary filegroup. If multiple files are contained within a filegroup, data will be distributed between the files within that filegroup.
July 12, 2010 at 12:12 pm
janyalee (7/12/2010)
Any tables not assigned a partition scheme or explicit filegroup will default to the primary filegroup. If multiple files are contained within a filegroup, data will be distributed between the files within that filegroup.
OK, cool. Got the PRIMARY filegroup part.
A concept that's been brought up here is prompting this question: In what scenario would I have more than one file per filegroup? The syntax I am familiar with is:
(NAME = N'TK432_Data', FILENAME = N'c:\test\TK432.ndf', SIZE = 8MB, Etc.)
Shouldn't all the .ndf data for that filegroup just go to one file, the TK432.ndf file?
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply