Home > Automate Table Partitioning > Automating Sliding Window Table Partitioning

Automating Sliding Window Table Partitioning

Here we will discuss how can we automate Table partitoning being created by using Partiton Scheme and Partition Fucntion.

I have seen DBA running around in search how we can automate table partitioning.

Common question I have faced are

How to add new filegroups with and associated files on the fly (when production is up and running)?

How to Alter Partition Scheme and Partition Function?

How to split partition so new partition can be used by partition function and partition scheme?

Here we will discuss these issues and automate the same using SSIS.

 Scenario:

Let’s say we have a database and being partitioned using partition scheme and partition function with INT DataType.

Let say we have primary filegroup and partition filegroup.

Let’s check whether table is using partition or not.

Now Check in which partition data is getting stored.

Now let’s add a new partition every month and alter the partition to use new partition for data storage. We will automate the above said process using SSIS.

Open SQL Server Business Intelligence Visual Studio

Create a new Project —>Select Integration Services Projects

On the Integration Service Project canvas (Control Flow) drag and drop the sequence container control flow item. Rename it with meaningful name.

Drag & Drop the Execute SQL Task name it as (Create FileGroup and Files)  inside the sequence container and create a connection to the server on which database partitioning is to be automated.

Edit the Execute SQL Task and paste the below code (change the databasename & database_id)

DECLARE @SQL nvarchar(max);
DECLARE @data_path nvarchar(max);
DECLARE @FileGroupName varchar(max);

SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX (N’ServerList_LogShipping.mdf’, LOWER(physical_name)) – 1)   FROM master.sys.master_files     WHERE database_id = 25 AND file_id = 1);
print @data_path
SET @FileGroupName=’DataFileGroup_’+convert(varchar(10),MONTH(getdate()))+’_’+convert(varchar(10),YEAR(getdate()))
SELECT @FileGroupName as FileGroupName
–AddFile Group for the Month–

BEGIN
EXECUTE(‘ALTER DATABASE ServerList_LogShipping
ADD FILEGROUP ‘+ @FileGroupName);
Print ‘File Group Created……………’
–Add Secondry Datafile with above created filegroup
END

BEGIN
SET @SQL=’ALTER DATABASE ServerList_LogShipping ADD FILE (NAME =’+ @FileGroupName+’,FILENAME =’+ ””+ @data_path + @FileGroupName +’.ndf”,
    SIZE = 5MB,    MAXSIZE = unlimited,    FILEGROWTH = 5MB
) TO FILEGROUP ‘ +@FileGroupName

exec sp_executesql @SQL
Print  @SQL
Print ‘File  Created……………’
END
GO

Drag & Drop the second Execute SQL Task name it as

(ALTER PARTITION SCHEME (NEXT USED FILEGROUP) — ALTER PARTITION FUCNTION (SPLIT RANGE)) inside the sequence container and create a connection to the server on which database partitioning is to be automated.

Edit the Execute SQL Task and paste the below code (Change Partition Functition and Partition Scheme name)

DECLARE @PartitionSchemeId varchar(max)
DECLARE @SplitPartition nvarchar(max)
DECLARE @rowcounts nvarchar(max)
DECLARE @FileGroupName nvarchar(max)
DECLARE @AlterPartitionScheme nvarchar(max)
SELECT @PartitionSchemeId=data_space_id from sys.data_spaces where type=’PS’
Print @PartitionSchemeId
SELECT @rowcounts=sum(rows) FROM SYS.PARTITIONS WHERE object_id in (select object_id from sys.tables where lob_data_space_id=@PartitionSchemeId)

SELECT @FileGroupName=name from sys.filegroups
WHERE data_space_id =(SELECT MAX(data_space_id) from sys.filegroups)
Print @FileGroupName

SET @AlterPartitionScheme=’ALTER PARTITION SCHEME [myRangePS1] NEXT USED [‘+@FileGroupName+’];’

Print @AlterPartitionScheme

exec sp_executesql @AlterPartitionScheme

SET @SplitPartition=’ALTER PARTITION FUNCTION myRangePF1 () SPLIT RANGE (‘+@rowcounts+’)’;

Print @SplitPartition

exec sp_executesql @SplitPartition

Once all is set and done execute the package.

Now check if new partition is in use or not.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: