Partitioning#

Database partitioning is a feature that enables splitting a table among multiple files which can be beneficial for large tables, especially for those which hold historical data.

Partitioning also simplifies data management, making queries run faster by working with smaller ranges of data. This feature is also useful to age history data, efficient history data purging and the implementation of a sliding window scenario. This section describes the partitioning strategies used in Critical Manufacturing ODS database.

Partitioning in ODS#

Critical Manufacturing ODS database uses distinct partitioning functions and partitioning schemes depending of whether its a main or history table (e.g. T_Material is considered a Main Table whereas T_MaterialHistory is a history table) and for data and indexes. For more information on partitioned tables, indexes, schemas and functions please refer to the following link: Partitioned Tables and Indexes ⧉

By default, Critical Manufacturing partitions history data by month, i.e. each month it adds new data files that will contain the data and index information for a specific month. This configuration can be changed to a weekly or daily partitioning, depending on the history data volume. The partitioning approach can be changed at any time. If, for example, you change the partition strategy from a monthly to a weekly basis, Critical Manufacturing will apply these changes the next time it evaluates the need to create new data files. If the partition strategies overlap (changing from monthly to weekly they will), Critical Manufacturing will take this in account and only add the week data file once the current month data file is no longer in use.

A typical partitioned ODS database will contain files and filegroups like depicted on the next two pictures:

As you can see, there is one xxxODS_FG_HstTableDat_YYYYMM and one xxxODS_FG_HstTableIdx_YYYYMM datafile per month with the corresponding file groups (FG_HstTableDat_YYYYMM and FG_HstTableIdx_YYYYMM). Moreover, the picture does not represents it, but when partitioning is applied on main entity tables you shall also find datafiles with the naming format of xxxODS_FG_MainTableDat_YYYYMM and xxxODS_FG_MainTableIdx_YYYYMM per month with the corresponding file groups FG_MainTableDat_YYYYMM and FG_MainTableIdx_YYYYMM.

Partitioning - Dependencies and Components#

As described earlier in this manual, Critical Manufacturing MES supports partitioning on history and main data tables.

Distinct partition schemes are used for main and history tables, this is required because main entities remain on a default partition and only upon its termination are moved to the partition which corresponds to the termination date.

On the other hand, partitioning is done at the data and index level so distinct partition schemas are created to support this sharing same partition function on main and history entities accordingly. This implies that both data and indexes remain aligned during the history data life-cycle.

The main objectives for a partition function are:

• Define the boundary values of the initial set of partitions (for main entities this means a default behavior than for history tables that's why same partition function can't be shared among these)
• Has no reference to any tables or disk storage
• Basis for one or more partition schemes

A partition scheme is used to:

• Map particular partitions to filegroups
• Can be used for one or more partitioned tables, indexes, and indexed views
• Partitioned table or index is tied to a particular partition scheme
• Partition table has only an indirect relationship, through the partition scheme, to the partition function

Partition table dependencies:

Partition table components:

Some examples of a partition function and the basic operations that can be done:

Now some examples of a partition scheme and basic operations:

Note the position of Filegroup5 when issuing a split @15 in the partition function.

In the example above please note the difference between Range Right and Range Left.

For Range Right, SQL Server will move all data from the old partition 3 on Filegroup3 into the leftward Filegroup2 with the merged partition 2. This data movement will cause transaction log growth due to inserts and deletes. Filegroup3 will be the default NEXT USED filegroup.

For Range Left, SQL Server will move all data from the old partition 2 on Filegroup2 and move it into the rightward merged partition 2 and Filegroup3. This data movement will cause transaction log growth due to inserts and deletes. Filegroup3 will be the default NEXT USED filegroup.

Partitioning Tables and Indexes#

In a partitioned table, the partition column must be a part of the clustered index key, the primary key and the unique index and uniqueness constraint keys.

A secondary index does not have to use the same partition function as the underlying partitioned table to achieve index alignment, as long as the partition function used by each index has the same characteristics (equivalent data types of the partition column, number and values of the boundary values, and range direction.) However, it is much more convenient to use the same partition function and partition scheme for the indexes and the underlying partitioned table.

Index alignment helps in achieving partition elimination, where the query processor can eliminate unused partitions from a query plan and to access just the partitions required by the query. Index alignment is also required for using the SWITCH statement. If you have a nonaligned secondary index on a table and you have the need to use the SWITCH option, you can always disable the index during the switch process and re-enable it when done.

In Critical Manufacturing index alignment is guaranteed by using the same partition function.

Data Movements In A Partitioned Table#

The image below describes how data is moved between partitions.

Sliding Window Cheaper Storage Scenario#

The "Sliding Window Cheaper Storage Scenario" involves rolling new partitions in, and rolling aged partitions out of high performance I/O subsystems. All data is still accessible for querying or updating however the data which is expected to be most required can reside in faster I/O subsystems improving the overall performance. Such architecture supports, for instance, keeping the latest 6 months of data on solid state drives and older data on 15k Serial Attached SCSI drives with little maintenance efforts. The key is to maximize performance with an affordable solution.

In order to enable Critical Manufacturing sliding window cheaper storage scenario you need the following:

• Locate the script named P_RelocatePartitions.sql within the installation CD (path should be: Software\Partitioning)

• Setup a new Job with a proper schedule and a single step which code should be something similar to:

-- number of days to retain on source I/O subsystem, e.g. for the 6 months scenario, you should set this value to 180 days
DECLARE @RetentionPeriodInDays INT = 180
-- set the target folder where to move filegroup files
DECLARE @NewRootDir NVARCHAR(512) = 'E:\ODS\Archive'
-- 0 (default) - runs normally and logs into message window;
-- 1 - does nothing, only prints what it would do
DECLARE @PrintScriptOnly BIT = 1
--optional, use only if only a specific target storage shall be moved
DECLARE @OldRootDir NVARCHAR(512)
--optional, not required on DWH and ODS databases.
-- Used to filter the filegroups (e.g. 'FG_HstTable%\_______' or 'FG\_%\_________\_%')
DECLARE @FileGroupSearchCriteria NVARCHAR(512)
--optional, not required on DWH and ODS databases.
-- Used to retrieve the date from the filegroup name
--(e.g. 'convert(date, left(convert(nvarchar,
-- SUBSTRING(fg.name, CHARINDEX(''_'', fg.name, 4)+1
--, len(fg.name)))+''01'', 8))'
-- or 'convert(date, LEFT(RIGHT(fg.name, charindex(''_'',reverse(fg.name),10)-1), 8))')
DECLARE @DateConversionStatement NVARCHAR(512)

EXECUTE [Control].[P_RelocatePartitions] @RetentionPeriodInDays, @NewRootDir, @PrintScriptOnly,
@OldRootDir, @FileGroupSearchCriteria, @DateConversionStatement

• We suggest to manually run the above script to validate all the input parameters using @PrintScriptOnly=1 to check whether all database files are correctly caught
• Whenever you feel confident, enable the job and database files should be moved to the target directory without any downtime (make sure you set @PrintScriptOnly=0 otherwise it wont do nothing)

Sliding Window Archiving Scenario#

The "sliding window archiving scenario" involves rolling new partitions in and rolling aged partitions out of the partitioned table or view. The new data is available for the users to query while the aged data is archived. The key is to minimize downtime while moving the partitions.

The aged data can be archived and can be retrieved when necessary by restoring the appropriate backups, or it can be moved to a less permanent, more affordable I/O subsystem that is still available to users for queries.

A sliding window implementation can be extended by sliding the aged data to a cost effective I/O subsystem. You can slide out the aged data from a highly performing I/O subsystem to a less expensive I/O subsystem that does not have the same high performance.

This particular sliding window implementation cannot be accomplished using the backup and restore operation available in SQL Server.

The method implemented in Critical Manufacturing MES is the following:

• Load the data into another table that resides in the cost-effective I/O subsystem
• Rebuild the indexes
• Delete the old partition and add the newly loaded table to the partitioned table

The downtime will be the time that is required to switch partitions which is very negligible, regardless of the size of your data set.

For more information see Designing Partitions to Manage Subsets of Data in SQL Server<sup/TM</sup/ Books Online and Transferring Data Efficiently by Using Partition Switching ⧉

Purging Old History data#

Old History Tables#

Depending on the data retention requirements, old data can also be purged from Critical Manufacturing’s history tables.

For this, Critical Manufacturing implements two methods depending on the installed SQL Server version.

If you have Standard Edition where partitioning functionality is not available, old data is purged by issuing a direct delete statement.

This is implemented by Critical Manufacturing’s stored procedure [dbo].[P_PurgeEntityTablesForDay].

Usually this implemented in a SQL Server job and the retention days defined by the customer. The deleted data can also be exported to an xml file for archiving.

If you have Enterprise edition, thus partitioning enabled, the recommended method is the following:

• Create a staging table in the same filegroup that contains the data to be purged
• Switch the partition that contains the data to the new table
• Drop the newly created table

This method has numerous advantages over the previous one because it minimizes DB impact by not generating a log and requiring minimal system resources.

Main Entity Tables#

After following the steps described on Enabling ODS Partitioning for main entity tables, you should find a job named \<dbname\>ODS_MainTablePartitioning.

This job will perform the following 4 actions:

1. Applying the partitioning schemes to all not yet partitioned entity tables
2. Generating the "XML to table" reading T-SQL functions which allow to query XML files as if they were SQL tables (required for verification of the exported data into XML files)
3. Mark entities as terminated on ODS when they were purged on ONLINE (i.e. set the partition column accordingly)
4. Optionally, applying the actual export to XML files, verification of exported data and dropping of partitions (dropping of partitions internally creates a temporary table based on the source table, switches the partition which contains the data to drop and then drops the temporary table)

Please note that the action 4 is optional. The default data retention time is 1800 days and it is only exported to XML after that time period. The retention time can be defined in days in the following Critical Manufacturing configuration entry: /Cmf/System/Configuration/Reporting/HistoryArchiving/RetentionTimeInDays. If set empty, the archiving to XML and deletion of data from database will not take place. It is also important to have the export and import paths correctly defined, please refer to the ODS XML Export section.

Enabling ODS Partitioning#

Partitioning History Tables#

In order to enable Critical Manufacturing ODS partitioning on history tables you need the following:

1. Make sure you have SQL Server Enterprise Edition (2019) or any other Edition having the Partitioning feature
2. Confirm the partition function: PF_NavigoPartitionFunction and partition schemes PS_NavigoHstDataPartitionScheme and PS_NavigoHstIdxPartitionScheme are installed (under \<databaseODS\>/Storage/Partition*)
3. Define a location where the partitioning ldf files will be located. Please refer to Database Groups and Data Files section in the Installation Guide for the recommended location
4. Define the partitioning scenario you will need for your installation. Depending on the generated data volume, decide if you need daily, weekly or monthly partitioning. The most common approach will be to start with monthly partitioning and adjust later on if needed
5. Locate the partition job creation script (03-ManagePartitions.sql) located in the installation CD (path: Software\Partitioning)
6. Run the job installation script against the ODS database. Before running it, replace the $(ODSDatabaseName) string by the ODS database name; replace the $(ODSDataFilesPath) string by the location defined in 2.; and finally replace the $(ODSUser) string by the user name that will be used to access the ODS database

This script will create a scheduled job in ODS database. This job will run daily at 1AM. You can later manage the job just like any other SQL Server job.

Partitioning Main Entity Tables#

In order to enable Critical Manufacturing ODS partitioning on main entity tables you need the following:

1. Make sure you have SQL Server Enterprise Edition (2019) or any other Edition having the Partitioning feature
2. Confirm the partition function PF_NavigoMainDataPartitionFunction and partition schemes PS_NavigoMainDataPartitionScheme and PS_NavigoMainIdxPartitionScheme are installed (under \<databaseODS\>/Storage/Partition*)
3. Define a location where the partitioning ldf files will be located. Please refer to Database Groups and Data Files section in the Installation Guide for the recommended location
4. Define the partitioning scenario you will need for your installation. Depending on the generated data volume, decide if you need daily, weekly or monthly partitioning. The most common approach will be to start with monthly partitioning and adjust later on if needed.
5. Locate the partition job creation script (04-MainTablesPartitioning.sql) located in the installation CD (path: Software\Partitioning)
6. Run the job installation script against the ODS database. Before running it, replace the$(ODSDatabaseName) string by the ODS database name; replace the $(ODSDataFilesPath) string by the location defined in step 2.; replace $(ODSArchiveDataFilesPath) by the folder where you want the xml exported files to be generated; and finally replace the $(ODSUser) string by the user name that will be used to access the ODS database (usually Critical Manufacturing's power user account)
7. This script will create a scheduled job in ODS database with name format \<dbname\>ODS_MainTablePartitioning and composed by 4 steps which are responsible for applying the partitioning schemes to all not yet partitioned entities; generating the "XML to table" reading TSQL functions which allow to query XML files as if they were SQL tables; Mark entities as terminated on ODS when they were purged on ONLINE (i.e. set the partition column accordingly) and applying the actual export and purging of partitions
8. Before the first run, you should setup on Step #4 the retention period. By default it will retain terminated entities for 1800 days (i.e. ~5 years after being purged). Please notice that data is exported to XML files therefore you may want to backup these to other digital containers (e.g. DVD or TAPE) before actually purging these files from disk. Once XML files are purged from disk, unless you have a backup to other format, there is no way to recover data in case there is a need to
9. This job will run daily at 1AM. You can later manage the job just like any other SQL Server job