Modification of a partitioning function

Modifications to the partitioning setup can be performed by altering the partitioning function and partitioning scheme using the SQL statements ALTER PARTITION SCHEME and ALTER PARTITION FUNCTION. This directly affects all tables that use the partition scheme so a single statement can be used to reorganize the entire ImageMaster database. For this reason, there are no commands in the Upgrade Tool to generate an SQL script for these steps. Only the case where a partition should be emptied requires the generation of an SQL script, which is done using the Upgrade Tool command "truncateFileGroup".

On Microsoft SQL Server, partitions cannot be added or removed. Only splitting and merging is possible. However, it is possible to achieve the same by splitting or merging an empty partition which is a fast operation and effectively adds or removes a partition from the partitioning setup.

See the Microsoft online documentation for details:

• https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-partition-function-transact-sql?view=sql-server-ver15

• https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-partition-scheme-transact-sql?view=sql-server-ver15

Splitting a partition

To split a partition, a new filegroup must be added to the partition scheme and then the split must be performed on the partition function:

This operation is fast if the partition to split is empty.

Which range of the split belongs to the newly created partition depends on where the split boundary is assigned. This is determined by how the partition function was created (with RANGE LEFT or RANGE RIGHT).

Merging partitions

To merge two adjacent partitions, the partition function must be modified:

This operation is fast if the partitions to merge are empty. To see how a partition can be cleared, see the next section.

Which partition will remain and which partition will be merged depends on how the partition function was created (with RANGE LEFT or RANGE RIGHT).

Emptying a partition

Any manipulation of the partition function or partition scheme results in the restriction that a reorganization of partitions can only be done via split or merge. It is impossible via these commands to delete the contents of a partition as the "dropPartition" command would do on other databases. For this purpose, the Upgrade Tool provides the "truncateFileGroup" command, which deletes all data stored in a filegroup, so the corresponding partitions can then be merged into a neighboring partition once they are empty.

This command requires Microsoft SQL Server 2016 or newer.

Example:

Example: year-based rolling partitions

This example shows how to maintain a partition scheme with year based partitions. New partitions are added for future years and old partitions are removed to delete old data. The partition function and scheme should be created as follows:

This divides the tables among the following filegroups:

• PartitionSystem

  The group contains any data that should never be deleted (system documents etc.). The default partition key "0" falls into this range because it is lower than the "20170101000000000" boundary value.

• Partition2017 to Partition2019

  The group contains the documents for each of these years.

• Partition2020

  The group contains all data from 2020 onwards. It is recommended keeping this partition empty, i.e. to split it into future partitions before the year 2020 is reached. This way the splitting is always a fast operation.

To add a partition for the year 2021, create a new filegroup "Partition2021" and execute the following:

To remove the partition for the year 2017 and delete its contained documents, run the Upgrade Tool command "truncateFileGroup" and execute the generated script:

To remove the now empty Partition2017 from the partitioning setup, execute the following:

This merges the empty Partition2017 into PartitionSystem. The Partition2017 filegroup is now no longer used in the partition scheme and can be deleted.