September 13, 2011 at 8:30 am
Hello all!
I am trying to wrestle with filegroups and/or partitions to determine whether they would be beneficial in my case. ("It depends" will probably be the most common answer!)
I am running a single production database on a virtual server with no access to underlying hardware, so I can't make use of separate drives or arrays. The database is small at this time (<10GB) but has the potential to explode exponentially.
That being said, there is a large percentage of tables that are lookup tables only (with possible modifications on average once a month). There is a second set of tables that have low read/write (maybe 100 records per day), and a third that currently has about 400 rows added (and probably edited) throughout the day but which could grow to 50,000+ rows added/edited throughout the day.
Are there any theoretical advantages to separating these groups of tables to separate filegroups? I know you can back up filegroups separately, but since I just read the 6-page thread which contained info on whether or not different filegroups can be restored to different points-in-time and still have the entire database available, I'm thinking that it won't help to back up the lower-volume tables less often, because a restore would need all of the filegroups restored to the same point in time.... but I digress. There is plenty of information on the benefits with multiple drives/arrays, but not so much otherwise.
Also, one of the tables in the third group (higher read/write) needs to have the data moved to an archive table at the end of each day, and the data needs to be kept in that archive table for a year. From my reading (some from a textbook, a few forum posts and this article by Kimberly Tripp
http://msdn.microsoft.com/en-us/library/ms345146%28SQL.90%29.aspx#sql2k5parti_topic25), it seems that it would facilitate this move to have 3 partitions in the target archive table:
One for the single day a year ago - this would be data that can be 'dropped'
One for the bulk of the records, from a year ago until yesterday
one (empty) to add today's partition
and two in the 'current' table (one empty for data that will happen tomorrow, and one for data from today.)
It appears that this setup would allow me to easily move the day's partition to the archive table, and remove the "one-day-a-year-ago" data from the archive table.
Does it sound like I am on the correct track, or perhaps have woefully misunderstood the reading? Any comments or suggestions of additional reading would be much appreciated. Thank you!
September 13, 2011 at 10:22 am
I would wait until your data becomes substantially larger. 50,000+ rows a day is substantial, but why set up partitioning until you get closer to that. You could partition the archive table, but I don't see a need if you are only keeping 1 year's worth of data. We handle about 2 million rows of data a month, sometimes 3 million and partition it by date. If you notice that you are running out of space with your backup strategy, you could consider partitioning and then making the old partitions read-only and backing them up once and storing them somewhere other than you read_write backup. Overall, partitioning will not really be beneficial to you unless you have a good reason to partition it and a logical partition column. If your queries start lagging because of the amount of data, you may then consider partitioning. From what I can see of your case right now, I don't see a need for it and it can always be implemented later.
Jared
Jared
CE - Microsoft
September 16, 2011 at 1:38 pm
there are lots of reasons to setup partioning
And if there is a reasonable chance the database is going to grow as they tend to do. Better to do it first
helps with backups, archiving,
Performance if you put the filegroups on different drives
performance limiting the datasets
September 16, 2011 at 1:45 pm
danschl (9/16/2011)
there are lots of reasons to setup partioningAnd if there is a reasonable chance the database is going to grow as they tend to do. Better to do it first
helps with backups, archiving,
Performance if you put the filegroups on different drives
performance limiting the datasets
As noted, there are many reasons to do it, but also reasons not to. If you are partitioning on date(month), for example, you would probably want to set up a sliding window. Sometimes, the maintenance outweighs the benefits of the partitioning. As mentioned, look at your situation (hardware, growth, reporting needs) and make an educated decision.
Jared
Jared
CE - Microsoft
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply