6

I'm an experienced SQL Server DBA but new to partitioning, and I have a couple of questions. Using SQL Server 2008 R2 Enterprise Edition.

I've inherited a large customer metrics database that grows by about 10 GB per day. This database currently consists of one large data file in one filegroup (PRIMARY). All tables have a datetime column called InsertedDate. I want to horizontally partition the data by InsertedDate, using a separate data file for each calendar week.

In a test environment I added the required additional filegroups and data files to this database, put a clustered index on InsertedDate in each table, and set up the partition function and partition scheme. By querying sys.partitions and other system tables, I've confirmed that the data is now physically residing in the correct partitions and data files.

Among others, the goals are:

  • Decrease backup time by only backing up the PRIMARY file group and the file group for the current date range (I'm currently running nightly full backups). Once a date range is in the past, that partition will never be written to again, so I'd like to set the filegroup to read-only and back it up one final time.

  • Be able to eventually take a partition "out of service". After 3 months there's no longer a need to keep older data online, so I'd like to take take that data offline (but be able to bring that data back online again if necessary).

Questions:

1) How to I perform backups on this partitioned database without having to back up the entire database? I can back up an individual filegroup, but to restore it requires the rest of the database, which defeats the purpose of using multiple smaller data files.

2) How to I take a partition out of service? I've read about switching, but it seems that only works if you want to move data between partitions within the same database. I want to be able to simply and safely take a range of data offline (and bring it back online of necessary).

Improve this question
6
  • 1
    What's the thinking/motivation behind taking older partitions offline? There's a number of things not possible or not advisable with your approach but understanding why you want this would lead to better answers.
    – Mark Storey-Smith
    Jun 18, 2013 at 14:09
  • 1
    Agreed with @Mark. Once older partitions are in a read-only filegroup and are no longer being backed up, how do they harm you except take up disk space (which they will take up when they're "offline" too)?
    – Aaron Bertrand
    Jun 18, 2013 at 14:10
  • As Aaron mentioned, one of the main reasons is really disk space. I won't be able to keep an unlimited amount of data online on the production systems, so I'll need to dismantle older data eventually. I've done some testing with file group backups and piecemeal restores, but they seem fraught with multiple dangers and caveats and don't really seem like a good option.
    – Scott
    Jun 19, 2013 at 21:03
  • Check out this presentation by Jeff Moden at our last User Group meeting, I am almost positive your answer is in there. semssug.sqlpass.org/MeetingArchive.aspx
    – Mark Wilkinson
    Feb 27, 2014 at 13:07
  • Is it possible to set whole filegroups offline? You could then physically move away the offline FG files and move them back in case you need them... I have no idea how supported or reliable this technique would be.
    – usr
    Jul 5, 2016 at 14:59

2 Answers 2

Reset to default
2

I can't give you an answer to question 1).
But to answer question 2):
What you are requesting is known as the "sliding window scenario".
If you want to eliminate a partition containing data that is no longer needed you have to perform following steps:

  • Create a table on the same filegroup as the partition you want to delete (typically, this table is referred to as a staging table).
    This table has to have exact the same structure as the partitioned table, including indexes.
  • Switch the partition to the staging table
  • You have to remove the reference to the filegroup from the partition function vie MERGE RANGE ...
  • Now you can truncate the staging table and drop it. What is left is an empty filegroup you can remove, too.

Some more details on this at http://technet.microsoft.com/en-us/library/aa964122.aspx and the white paper on partitioning, available under http://msdn.microsoft.com/en-us/library/dd578580%28v=sql.100%29.aspx.

Improve this answer
0

For "2) How to I take a partition out of service?"
This is probably a terrible idea but... Could you:

  1. Bring the database offline
  2. Delete the physical files in the FileGroup
  3. Bring database online
  4. Alter partition to exclude the FileGroup
  5. Rebuild Indexes
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.