March 17, 2008 at 4:41 am
Hello,
I am implementing a table partitioning on our database with TSQL.
At the moment (it is under developing) the data are correctly located in the relavant file group.
Our target is to meke that the oldest partions/File groups can be backup and removed from the database. This to reduce the size of DB (time period is used for partitioning).
Then, if the need arises, restoring the filegroup to make reporting or analysis.
Take care that data are conitnuosly added and thus new File groups are added to represent the new time period.
Based on your experience is it possible a solution like that?
March 18, 2008 at 4:27 pm
Any help will be very appreciated.
Thank
March 18, 2008 at 8:07 pm
Working on the same issue here, and have started playing with some queries using AdventureWorks. But yes.. you are correct. The idea is that new filegroups are created and the new partitioned data goes into the new filegroups... and eventually the old filegroups drop off. I'm still working on the concept of the sliding window, and moving data between partitions.
I am attaching some Lab play queries that may help you out... good luck
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
March 19, 2008 at 4:39 am
Thank for you contribution,
any help further help or suggestion will be very useful
Thank
March 19, 2008 at 4:49 am
There's a very good Whitepaper avilable which should answer all your questions.
[font="Verdana"]Markus Bohse[/font]
March 19, 2008 at 3:05 pm
Hello,
thank for the link above, but I have already read it and it is very useful.
But I need more help on how to manage filegroup that should be stored (backup), removed and if the need arises again to be added again to database for further analysis and/or reporting.
Thank
April 23, 2008 at 9:02 am
Hello,
I am working on partioned tables, but I have the following problem:
how to use partitioning if tables are reated to each other by foreign key, let me say:
T1 FK T2 FK T3.
T1, T2 and T3 have a timestamp.
If I set the partitiong on this common timestamp, I risk to have data (records) related in different partition : eg if a record in
T1 with timestamp 11:59:00 PM
T2 with timestamp 11:59:00 PM (record related with the record in T1)
T3 with timestamp 00:20:00 AM (record related with the record in T2)
If the logic partition is set at 00:00:00 AM, it means that the related records for T1 and T2 are on the same partition, but T3 is in another partition.
How can I build a partitioning to allow that all related records are in the same partition?
Thank
April 23, 2008 at 9:43 am
In short, you can't keep related tables in partitions as you will not be able to switch them in or out of the partition because of violation of FK constraints. I used to have working copy of data with constraints and everything, but when new batch was arriving, the older data had their constraints dropped and then they were put into partitioned structure which we used as short term archive. Since they were read-only, there was no risk of breaking integrity.
Piotr
...and your only reply is slàinte mhath
April 24, 2008 at 3:34 am
Hello,
could you explain better?
Thank
April 29, 2008 at 3:46 am
Anyone can explain me how to recover the partition left out and put it in tape, DVD or other media and then to be retrieve it and add to the database for analysis?
Thank
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply