Post reply

Partitioning some large tables

  • November 19, 2014 at 3:16 pm

    #312719

    Hi,

    We decided to partition two large tables by month with 3 years retention period. I was thinking of creating each partition in a separate filegroup, so they can be backed up separately and some of them can be made read-only.

    I was wondering what would be a better approach: to create a new file group before the new month starts and truncate the last partition and delete the oldest filegroup or empty the old filegroup and set the new partition to the oldest filegroup. The latter will involve taking the oldest filegroup out of read-only and I am concerned about this approach messing up the backup strategy. It is a neater way though, as you just keep the same files/filegroups and rotate them.

    Also, is there any point in keeping each table in its own set of filegroups?

    Any ideas?

    Thanks.

  • November 20, 2014 at 10:39 am

    #1759917

    Hi Robust,

    You can create a file group for each monthly partition, so you would need 36 partitions for 3 years. You can setup an automated job that will switch the partition out and archive your data. I am not sure what you mean messing up the bacuk strategy, you can set a filegroup to be read only. You need to ask yourself why you would put certain tables in filegroups, it is for performance, VLDBs, indexing etc.

    Try this link from Brent Ozar[/url], to get you started.

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

  • November 20, 2014 at 11:36 pm

    #1760009

    quackhandle1975 (11/20/2014)

    Hi Robust,

    You can create a file group for each monthly partition, so you would need 36 partitions for 3 years. You can setup an automated job that will switch the partition out and archive your data. I am not sure what you mean messing up the bacuk strategy, you can set a filegroup to be read only. You need to ask yourself why you would put certain tables in filegroups, it is for performance, VLDBs, indexing etc.

    Try this link from Brent Ozar[/url], to get you started.

    qh

    Hi quackhandle1975,

    As I mentioned before, I have the option to create new filegroup for each new partition or rotate existing filegroups once they become available after removing the oldest partition. To be able to write again to the old filegroup (after it was made read-only) I need to perform a full backup of the database, as switching the filegroup from read-only to read-write will invalidate the previous filegroup backups.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply