Archiving via Table Partitioning

Official translation unavailable. Translate this page with Google:

Problem

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

  • The default setting is archiving to Microsoft Access file via Windows scheduled task.

  • The alternative way is archiving to SQL database via partitioning of the SQL tables, which we recommend for larger NiceLabel deployments with a larger amount of printing history, or when Access file archiving is not desired.

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

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

Solution

Prerequisites and a configuration summary

Prerequisites for partition-based archiving

  • SQL Server 2012R2 or later Enterprise edition or SQL Server 2016 SP1 or later any edition.

  • NiceLabel Control Center 2017.2 or later.

Partition-based archiving process setup includes the following steps:

  • Disabling of existing Access archiving process.

  • Setting up the partitions, during which the SQL tables are partitioned and indexed for archiving.

  • [Using Enterprise edition] Setting up the SQL Agent job, which triggers the archiving process.

  • [Using non-Enterprise edition] Setting up a scheduled batch job, which triggers the archiving process

  • Viewing archived data using partition switching scripts.

For NiceLabel 10: Download the sample SQL scripts.

For NiceLabel 2017 and NiceLabel 2019: Download the sample SQL scripts.

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.

Disabling the existing archiving process

  1. Open Task Scheduler on the server that is hosting Control Center.

  2. In the tree view, select Task Scheduler Library.

  3. Right-click the Control Center Archiving.

  4. Select Disable.

Setting up the partitions

Download the sample SQL files (.SQL extension).

Run the script Partition preparation.sql on the Control Center database (default database name is NiceAN). The parameters declared in the script are:

  • @ArchivationInterval. Declares the archiving interval.

  • @ArchivationIntervalType. Defines the granularity of interval (0 for day, 1 for month).

  • @ArchivationIntervalMax. Defines how long the data is stored in the original tables based on the selected interval type.

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.

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

@ArchivationInterval = 1

@ArchivationIntervalType = 1

@ArchivationIntervalMax = 12

Setting up the SQL Agent job

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

  • Verify that the stored procedure nan.ProcessArchivation already exists in the database, the partitions are set, and new archiving process settings are in nan.Settings table (Partition preparation.sql script will do that).

  • You can set SQL Agent job can with the script Partitioning Agent Job Creation.sql or add it manually.

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

  • @OwnerLogin. Defines database login name of the job owner.

  • @ServerName. Defines SQL Agent server name.

  • @DatabaseName. Defines the NiceLabel database name (the default database name is NiceAN).

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.

Setting up the scheduled batch job

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

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.Settings table (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"

Where elements in [ ] should be replaced with:

  • [ServerName]. Defines the database server name (example: localhost\SQLEXPRESS)

  • [DatabaseName]. Defines the Control Center database name (example: NiceAN)

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.

Retrieving the archived data

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 Switch Archived Data.sql. When reviewing the archived data is completed, run the script Restore Archived Data.sql to restore the data to the original state.