November 1, 2010 at 6:54 pm
After several tests, and scenarios taken from several articles, I am almost ready to implement table partitioning. I have already made my case that partitioning the table is the right path to take, considering that the table that will be partitioned is (just the table) sized to 300G. It has historical data, that is wished to be online but a large portion can be left as read-only. Maintenance of this table is a pain, and thus, partitioning is really heaven-sent solution.
Now, my concerns:
1. Is it a better to create another table that will hold the Archived records? Implement SWITCHING and MERGING.
(As demo-ed in the Partioning and Switchin samples for AdventureWorks)
Problems foreseen:
Quotes are from http://msdn.microsoft.com/en-us/library/ms191160(v=SQL.90).aspx
The source table cannot be referenced by a foreign key in another table.
That is a key problem because the table is actually reference several times by other tables. 🙁 Unless create the Foreign key reference WITH NOCHECK? But is that an acceptable idea?
Both the source and the target of the ALTER TABLE...SWITCH statement must reside in the same filegroup, and their large-value columns must be stored in the same filegroup. Any corresponding indexes, or index partitions, must also reside in the same filegroup.
UGH, isn't one of the beauty of partitioning, is being able to have several filegroups which we can independently back-up, or restore? To be able to apply index maintenance on just a selected filegroup so that the log file that can be generated won't be as large compared to applying the index maintenance to the full table?
2. Or what if it's just left as one partitioned table, with several filegroups. Some of which are just read-only.
In Production, all files are always online, but in QA and DEV environments (obfuscated data), it will be partially restored.
Problems foreseen: the risk of accessing records that may be on the OFFLINE filegroup.
The second item seems to be what makes sense, but I'd like to get some thoughts and comment from anyone in the community. What are the risks involved, what beauty and comfort (yeah, what headaches too) awaits if this is implemented. 🙂
November 2, 2010 at 3:41 pm
Hmmm... okay, now I'm starting to understand it.
If you have two tables and wish to slide data from one table to the archive table, it doesn't really need that all partitions be onthe same filegroup in order to implement sliding window partitioning. The main thing is the junction, the junction (that's how I kind of associate it), has to be in the same filegroup.
It's like on the two tables, one partition actually overlaps each other. But on the history or archive table, it's always empty because the data is still in the active one. The empty partition from the history, and the populated partition from the active one has to be in the same filegroup.
It's like I'm back to the original #1 solution, the issue left is the foreign key references to that table.
Does FK with NOCHECK an acceptable solution. Does anyone cringe at that thought?
November 4, 2010 at 6:37 am
You can't switch a partition if the source table is the reference of a Foreign key.
There are tons of restriction to switch a partition, you can see it all here: http://msdn.microsoft.com/en-us/library/ms191160.aspx
In your case it can still help though, but you'll have to either get rid of the foreign key or use an extra table (like in a n-n relationship), you can still switch the partitions you want to archive to another table with less indexes.
That way you can have all the indexes you need on the current data while not having to waste space on the archived data (which would have only minimum indexes or even only the clustered index).
If you need to move the archived data to a different filegroup then you'll have to copy the data instead of switching the partition.
In this case you can still use partitioning to be able to bulk insert data instead of doing regular insert but that's still a million time slower than a simple switch.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply