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’)] |
Parameters
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. |
Job Summary
- 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;
Key Operations
- 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.
Dependencies Overview
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. |
Migration Implementation Details
- 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.
Reporting and Monitoring
- 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.
Job Scheduling and Execution
- 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.