Archiving via Table Partitioning
Article ID: 288 - Updated: Sep 28, 2018 - Version: V2017 - Category: Connectivity and Database
NiceLabel Control Center stores the history about printed events in a Microsoft SQL Server database. NiceLabel Control Center currently supports two types of data archiving.
- The first one, which is also a default setting, is archiving to Microsoft Access file via Windows scheduled task.
- The second one is archiving to SQL database via partitioning of the SQL tables, which is recommended for larger NiceLabel deployments where a lot of printing history is collected, 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
Prerequisites and Configuration Summary
Prerequisites for partition-based archiving
- SQL Server 2008R2 or later Enterprise edition or SQL Server 2016 SP1 or later any edition
- NiceLabel Control Center 2017.2
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 is used to trigger the archiving process
- [Using non-Enterprise edition] Setting up a scheduled batch job, which is used to trigger the archiving process
- Viewing archived data using partition switching scripts
NOTE: Using table partitioning is advanced SQL Server topic. This article will provide a process overview, but you have to make sure you have enough background knowledge and IT team capable of reconfiguring the SQL Server yourself.
Disabling Existing Archiving Process
To disable the existing Access archiving process, do the following:
- Open Task Scheduler on the server that is hosting Control Center.
- In the tree view, select Task Scheduler Library.
- Find and right-click the Control Center Archiving.
- Select Disable.
Setting up the Partitions
Download the sample SQL files (.SQL extension).
To set up the archiving via partition tables, run the script named Partition preparation.sql upon the Control Center database (default database name is NiceAN). The parameters declared in the script are defined as listed below:
- @ArchivationInterval. Declares the interval in which the archiving process runs.
- @ArchivationIntervalType. Defines the granularity of interval (0 for day, 1 for month).
- @ArchivationIntervalMax. Defines how long the data is kept 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, let us 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, SQL Agent service can be used to trigger archiving process.
To set up SQL Agent job, do the following:
- 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 will do that).
- SQL Agent job can then be set up with the script Partitioning Agent Job Creation.sql or added manually.
If the SQL Agent job is created via scripts, the three configuration variables should be set 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 the job is created manually, make sure the job periodically executes the stored procedure nan.ProcessArchivation. We recommend that the job is scheduled daily, as the stored procedure itself determines whether 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. That means the archiving job has to be performed 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). Scheduled batch job can then be setup 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, in Task Scheduler, create a scheduled job that triggers and runs the batch file each day. Be careful to set the user credentials to EPM_USER, as this user has sufficient permissions on database to run archiving. Set up daily trigger, as the stored procedure will actually decide whether to run archiving 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 done reviewing the archived data, run the script Restore Archived Data.sql restore the data to original state.