[en] Archiving via Table Partitioning

[en] Problem

[en] NiceLabel Control Center stores the history of printed events in an MS SQL database. NiceLabel Control Center currently supports two types of data archiving:

[en] The default setting is archiving to Microsoft Access file via Windows scheduled task.
[en] The alternative way is archiving to SQL database via partitioning of the SQL tables, which we recommend for larger deployments with a larger amount of printing history or when Access file archiving is not desired.

[en] This article describes how to set up the archiving via table partitioning.

[en] Information on table partitioning is available here: https://docs.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes

[en] Solution

[en] Prerequisites and a configuration summary

[en] Prerequisites for partition-based archiving

[en] SQL Server 2016 or later Enterprise edition or SQL Server 2016 SP1 or later any edition.
[en] Control Center 2019.3 or later.

[en] Partition-based archiving process setup includes the following steps:

[en] Disabling of existing Access archiving process.
[en] Setting up the partitions, during which the SQL tables are partitioned and indexed for archiving.
[en] [Using Enterprise edition] Setting up the SQL Agent job, which triggers the archiving process.
[en] [Using non-Enterprise edition] Setting up a scheduled batch job, which triggers the archiving process
[en] Viewing archived data using partition switching scripts.

[en] For NiceLabel 10: Download the sample SQL scripts.

[en] For NiceLabel 2019: Download the sample SQL scripts.

[en] NOTE: Using table partitioning is an advanced SQL Server topic. This article provides a process overview, but you must have enough background knowledge to reconfigure the SQL Server.

[en] Disabling the existing archiving process

[en] Open Task Scheduler on the server that is hosting Control Center.
[en] In the tree view, select Task Scheduler Library.
[en] Right-click the Control Center Archiving.
[en] Select Disable.

[en] Setting up the partitions

[en] Download the sample SQL files (.SQL extension).

[en] Run the script PartitionPreparation.sql (for V 2019: Partition preparation.sql) on the Control Center database (default database name is NiceAN). The parameters declared in the script are:

[en] @ArchivationInterval. Declares the archiving interval.
[en] @ArchivationIntervalType. Defines the granularity of interval (0 for day, 1 for month).
[en] @ArchivationIntervalMax. Defines how long the data is stored in the original tables based on the selected interval type.

[en] For example, if @ArchivationInterval is set to 2, @ArchivationIntervalType is set to 0, and @ArchivationIntervalMax is set to 30, the script generates partitions every 2 days for 30 days.

[en] As another example, archive all entries that are older than 12 months every 1 month:

[en] @ArchivationInterval = 1

[en] @ArchivationIntervalType = 1

[en] @ArchivationIntervalMax = 12

[en] Setting up the SQL Agent job

[en] When using SQL Server Enterprise editions, you can use the SQL Agent service to trigger the archiving process.

[en] Verify that the stored procedure nan.ProcessArchivation already exists in the database, the partitions are set, and new archiving process settings are in nan.Setting table (PartitionPreparation.sql (for V 2019: Partition preparation.sql) script will do that).
[en] You can set SQL Agent job with the script PartitioningAgentJobCreation.sql (for V 2019: Partitioning AgentJob Creation.sql) or add it manually.

[en] If you create the SQL Agent job with scripts, define the three configuration variables first:

[en] @OwnerLogin. Defines database login name of the job owner.
[en] @ServerName. Defines SQL Agent server name.
[en] @DatabaseName. Defines the NiceLabel database name (the default database name is NiceAN).

[en] If you create the job manually, make sure the job periodically executes the stored procedure nan.ProcessArchivation. We recommend that you schedule the job daily, as the stored procedure itself determines if it should execute archiving logic or not.

[en] Setting up the scheduled batch job

[en] When SQL edition lower than Enterprise is used, the SQL Agent service is not present. In this case, the archiving job is performed using scheduled tasks.

[en] To set up the scheduled batch job, first make sure that the stored procedure nan.ProcessArchivation already exists in the database, the partitions are set, and new archiving process settings are in nan.Setting table (PartitionPreparation.sql (for V 2019: Partition preparation.sql) script should do that). You can set the scheduled batch job by creating a new batch file that contains the following command:

Sqlcmd –S [ServerName] –d [DatabaseName] –q "EXEC nan.ProcessArchivation"

[en] Where elements in [ ] should be replaced with:

[en] [ServerName]. Defines the database server name (example: localhost\SQLEXPRESS)
[en] [DatabaseName]. Defines the Control Center database name (example: NiceAN)

[en] After creating the batch file, create a scheduled job in Task Scheduler that triggers and runs the batch file each day. Set the user credentials to EPM_USER, This user has sufficient permissions on the database to run archiving. Set up the daily trigger because the stored procedure decides if archiving should run or not.

[en] Retrieving the archived data

[en] Archived data is stored in tables ArchiveEvent, ArchiveLabelJob, ArchivePrintJob, and ArchiveVariable. To move all of the archived data back to Control Center, run the script ReArchiveData.sql (for V2019: Re-archive Data.sql). When reviewing the archived data is completed, run the script RestoreArchivedData.sql (for V2019: Restore Archived Data.sql) to restore the data to the original state.