Sql-server – Hot and Cold data Storage using partitioning

sql servert-sql

I am working with a data-ware house and i want to partition the last three years of data into Hot data storage and any further than three years should be placed to cold. I want to be able to run a script monthly that would move data from Hot to Cold if data as gotten older I have currently Started by looking on line and have been testing the following script 

Alter Database [Dev_PartionTest] Add Filegroup [HOT_DATA]       --------
Go                                                                  --------
Alter Database [Dev_PartionTest] Add Filegroup [COLD_DATA]       --------
Go                                                                  --------

Alter Database [Dev_PartionTest] Add FILE ( NAME = N'HOT_DATA', FILENAME = N'....\HOT_DATA.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB, MAXSIZE = 20000MB ) TO Filegroup [HOT_DATA]
Alter Database [Dev_PartionTest] Add FILE ( NAME = N'COLD_DATA', FILENAME = N'..\COLD_DATA.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB, MAXSIZE = 20000MB) TO Filegroup [COLD_DATA]


 Declare @Previousyear Date
 set @Previousyear = (SELECT DATEADD(year, -2, GETDATE()));

 Create Partition Function [MonthlyPartitionArchive] (datetime)
   as Range Right For Values (@Previousyear,  GETDATE())


Create Partition Scheme RangePartScheme as Partition 
 [MonthlyPartitionArchive]
 To ([COLD_DATA], [HOT_DATA], [PRIMARY])



 Create Clustered Index IDX_Part On Tester(created) 
 On RangePartScheme (created);

Could you guide me as to how i can develop such a a script. 

    Create Partition Function [MonthlyPartitionArchive] (datetime)
   as Range Right For Values (@Previousyear,  GETDATE())

The issue i face is if i use the getdate to insert a record

 INSERT INTO Tester( Name, gender, created, description )
 VALUES ('Nicks', 'M',GETDATE(), 'Lloyds Office'),

The getdate function would return date higher than the date time used in the function above to set the upper bounds on the right in the create partition function. how can i set the current year without having to type it in manually everytime.
Can this script be running on a monthly process to update partition is that possible.

Best Answer

You want RANGE LEFT instead of RANGE RIGHT, and you only need a single boundary value at the three-year-mark.

Make the partition function like this:

Create Partition Function [MonthlyPartitionArchive] (datetime)
 as Range LEFT For Values (@Previousyear);

The scheme should then be:

Create Partition Scheme RangePartScheme as Partition [MonthlyPartitionArchive]
 To ([COLD_DATA], [HOT_DATA]);

There is no way to create a dynamic partition function. You'll need to schedule a job to alter the partition function as needed. Look at this answer for details about how to automate it.

Related Question