Creation of the ImageMaster database model with enabled partitioning

A special SQL script exists in the ImageMaster assembly release to create the ImageMaster database model with enabled partitioning: “db-model\sqlSQLServer\ima9_partition\ima9_core_partitioned_sqlserver.sql”.1 Run this script either through the provided batch file or directly in SQL Server Management Studio. The script expects parameters for the filegroups to use for unpartitioned tables and indices, as well as the name of the partition scheme to use for table and index partitioning (both can use the same scheme).

Configuration example: partitioning by tenant

The following partition function and partition scheme are used for partitioning by tenant (the partition key of each document is set to the tenant id (1-63) that created the document):

CREATE PARTITION FUNCTION ImaPartitionFunction (VARCHAR(50))

AS RANGE RIGHT

FOR VALUES ('2', '3', '4');

CREATE PARTITION SCHEME ImaPartitionScheme

AS PARTITION ImaPartitionFunction

TO (PartitionTenant1, PartitionTenant2, PartitionTenant3, PartitionOtherTenants);

This example assumes that filegroups named "PartitionTenant1", "PartitionTenant2", "PartitionTenant3" and "PartitionOtherTenants" exist in the database. This example splits the data between the following partitions: One for each tenant 1-3 and one for all tenants 4 and higher.

Configuration example: partitioning by document creation time 

The following partition function and partition scheme are used for partitioning by document creation time (the partition key of each document contains its creation time in the form "YYYYMMDDHHMISSFFF") :

CREATE PARTITION FUNCTION ImaPartitionFunction (VARCHAR(50))

AS RANGE RIGHT 

FOR VALUES ('20130101000000000', '20140101000000000', '20150101000000000');

CREATE PARTITION SCHEME ImaPartitionScheme

AS PARTITION ImaPartitionFunction

TO (Partition2013, Partition2014, Partition2015, PartitionFuture);

This example assumes that filegroups named "Partition2013", "Partition2014", "Partition2015" and "PartitionFuture" exist in the database. The data is divided between these partitions with Partition2013 receiving all documents created in 2013 (and prior), and PartitionFuture receiving all documents created starting in 2016.