October 2, 2008 at 6:15 am
Hi fellow forum members,
We have a requirement to perform "archiving" of data to improve performance of an existing system. Five of the tables within the system contain transactional information with a datestamp, and these tables contain millions of records. My idea is to create three partitions:
Current: Stores all non transactional data and transactional dat in the current month.
Previous: Stores all transactional data in the previous month.
Older: Stores all transactional data older than two months.
How would one go about doing something like this? Is it possible to create a dynamic partition function (e.g. GETDATE() - 30). Will the data be moved on a monthly bases etc...
October 21, 2008 at 6:57 am
Any one have any suggestions???
October 21, 2008 at 2:37 pm
Soft partioning
http://weblogs.sqlteam.com/peterl/archive/2007/09/24/Horizontal-partitioning.aspx
Hard partitioning
http://weblogs.sqlteam.com/peterl/archive/2008/06/12/Horizontal-partitioning-Enterprise-style.aspx
N 56°04'39.16"
E 12°55'05.25"
October 28, 2008 at 8:36 am
Table partitions (in SQL Server 2005 Enterprise edition) only support "static" partition boundaries -- that is, the value that the partitions are based on can't change once they're set up. A "typical" solution would be to add a new partition each month for that month's data, but that doesn't quite sound like what you're looking for.
Here's an outline of an idea that might fit what you described, based on partitioned views. (It's a bit complex, but it worked flawlessly until they changed the nature of the data being stored and had to rebuild from scratch.)
Create four identical tables, named (for example) Data_0, Data_1, Data_2, and Data_Data_Historical. The historical table will (surprise!) contain the historical data. This will be the big-but-not-used-all-that-much table.
Create views, say "vData_Current" and "vData_Previous". These are just "SELECT * from ..." views; start one on top of Data_0, the other on Data_1. Data for the current month goes into Current, and for the previous month goes into Previous
When a new month rolls around, the following actions must be taken:
- ALTER VIEW vData_Current to reference the empty table (unused this past month, Data_2 in our exapmle); new data will be loaded in there.
- ALTER VIEW vData_Previous to reference last month's current table (Data_0 in our example). Shazam, the current data is now the previous data!
- Copy all the data from last month's previous table (Data_1) into Data_Historical. This may take a while, unless you do fancy table partitioning tricks to swap data around (simple enough to do, assuming you have 2005 Enterpri$e edition).
- Once the data's copied over, truncate last months' "previous" table; it's now empty and waiting for the next month.
I used suffixes 0, 1, and 2, because I wanted to automate the "flip views" process; this called for setting up some management tables to track which table was serving which purpose, dynamically generated and executed code, and cycling through the tables using a "take #, add one, modulus 3" algorithm.
The thing I did took the N tables I was managing and glommed them together as a partitioned view -- so each table had a constraining column and other arbitrary stuff. This was all in SQL 2000; if I were writing it today, I might want to do it with table partitions... it'd probably depend on how much historical data you're storing, and for how long.
Philip
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply