Statistics migration

Job details

Name:	Statistics migration
Platform:	Sqlserver
Category:	Compliance
Premium package:	Migration EM12 to CC
Description:	Migrates statistics from a number of tasks from dbWatch version 12 to dbWatch CC.
Long description:	Migrates statistics from a number of tasks from dbWatch version 12 to dbWatch CC.
Version:	1.4
Default schedule:	0 16 1 *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Name	Default value	Description
copy statistics	NO	If set to YES the procedure will copy history data from dbWatch 12 engine to dbWatch CC engine.
dbWatch12 engine	AUTO	Name of dbWatch 12 engine to copy statistics from. If set to AUTO (default value) the procedure will find automatically the last in use dbWatch engine.

Purpose: The purpose of this job is to migrate statistics and other significant data from dbWatch version 12 to dbWatch Control Center (CC).
Why: This job is essential for ensuring that data from older dbWatch systems are updated and synced with the latest dbWatch Control Center. It helps maintain the continuity of monitoring and managing database performance and ensures that all data remains relevant and structured according to the latest standards. Migrating such data helps in leveraging updated features and optimizations provided in the new version.
Manual Checking: To check the migration manually in the database, the following SQL commands can be issued:

SELECT * FROM dbw_statistics_migration_info;
SELECT * FROM dbw_migration_db_info;
SELECT * FROM dbw_migration_checkhistrcc;

The job automatically identifies dbWatch 12 engines and executes data migrations for numerous monitoring statistics like memory usage, database growth, transaction logs, and session loads.
It manages dependencies and ensures data integrity by using a structure of cleanup on failures across multiple dependent objects like help tables and information tables.
Scheduled to run at a specific time (0 16 1 *), implying it initiates at 16:00 on the first of every month.

Dependency Object	Cleanup on Fail	Description
dbw_statistics_migration_proc	True	Main procedure for the migration process.
dbw_statistics_migration_info	True	Information table for migration data details.
dbw_migration_db_info	True	Stores last task execution details.
dbw_migration_dml	True	Contains DML operations for migration.
dbw_migration_param12	True	Parameters from dbWatch 12 for migration.
dbw_migration_paramcc	True	Parameters needed for migration in dbWatch CC.
dbw_migration_checkhistr12	True	History of alerts to migrate from version 12.
dbw_migration_checkhistrcc	True	History of alerts that need to be migrated to CC.

The process involves creating tables to store configuration and operational data, then using a series of INSERT statements to populate these tables based on conditions that match dbWatch version 12 structures.
Critical operations within the migration include deleting existing records (if necessary), fetching and transferring data from old to new database structures, and updating configuration parameters.
The procedure includes exception handling to manage any errors during the migration, ensuring any errors are captured and addressed accordingly.

A report template named “Migration status” is defined to monitor and present various aspects of the migration:
- Statistical migration details – Information on dbWatch engine detection and parameters – Differences in parameter values between versions – Alert history migration details – DML operations log
These reports are critical for analyzing the migration’s status and ensuring all data has been transferred correctly.

The default scheduling is set to monthly execution, which can be adjusted as per requirements. The execution includes various checks on data existence, version compatibility, and parameter consistency before proceeding with migration activities. This ensures the migration process only starts when all conditions are favorable, reducing the risk of data inconsistency or migration failures.