infromix-to-mssql-replication

Incremetal replication from Cisco CVP Reporting (IBM Infromix) to MSSQL
Solution made for acceleration of custom reports and reducing workload on CVP Reporting instance, benefit of this solution is speed up the reports, cause you gain the full control of tables/indexes/statistics/execution plan, and it's optimization.

One of the main advantages of OPENQUERY is remote execution, meaning that the query is sent from the local server to remote server while having knowledge of those remote tables that are now local to the query. By the way, remote execution also enables the use of native syntax of the remote server, so you can take advantage of other RDBMS system’s performance tricks. But, the main reason why OPENQUERY will use - it's perform better is the knowledge of the table in terms of indexes and stats, knowledge that a direct linked server doesn’t have.

Source DB schema and table description on Cisco.com

Prerequisite:

1. Depending on which Windows operating system version (32-bit or 64-bit) is used, install according IBM Informix Client SDK for Windows Operating Systems, which contains ODBC driver for IBM Informix
2. Configure ODBC driver on Windows used ODBC Data Sources Administrator (32-bit or 64-bit)
3. Configure Linked server on MSSQL to use previously configured ODBC connection - f.e check data/linked_server.sql

• On my deployment I used - clientsdk.4.10.FC14.windows64.zip
• For more information about connecting to CVP Reporting instance, check Cisco documentation

Description:

Sync tables:

1. call
2. vxmlsession
3. vxmlelement
4. vxmlelementdetail
5. vxmlcustomcontent

Destination DB tables:

1. call
2. vxmlsession
3. vxmlelement
4. vxmlelementdetail
5. vxmlcustomcontent
6. tIndexStateHistory - logs table for index reglament procedure
7. tSetting - global settings, RowCopyPerTime, Cleanup_Age, LinkedServer name, etc
8. tSettingsTables - settings for copy and update tables, sSyncReportTables used this table
9. tSyncLog - stored infromation about each run of copy/update/cleanup procedure

DB procedure:

1. sCleanupIndexStateHistoryTable - cleanup procedure for table tIndexStateHistory
2. sCleanupReportTables - cleanup procedure for tables in tSettingsTables
3. sCleanupSyncLogTable - cleanup procedure for table tSyncLog
4. sOnReglamentIndex - procedure for reglament work on indexes (reorganization/rebuild/update statistics)
5. sQueryGetColumns - helper procedure which preparing query columns
6. sQueryGetParameters - helper procedure which preparing query parameters
7. sSyncReportTables - main procedure which use tSettingsTables and run according job for each table
8. sSettingsTableUpdate - helper procedure for update tSettings tables setting
9. sSyncTablesIncremental - procedure for incremental copy with OPENQUERY
10. sSyncTablesUpdate - procedure for update tables with OPENQUERY

Jobs:

1. job_runSyncReportTable - run increment copy and update procedure
2. job_runOnReglamentIndex - run reglament procedure and disabled/enabled incremental copy job while the run
3. job_cleanupTasks - run cleanup procedure
4. job_maintenanceplan_db - run maintenanceplan plan for backup database
5. job_maintenanceplan_log - run maintenanceplan plan for backup transaction log (if you plan to use DB recovery mode: FULL)

• Configure database maintenance plan according your best practice

DB function:

UTF8_TO_NVARCHAR - helper scalar function which convert Unicode to NVARCHAR.

Note

SQL Server 2019 (15.x) introduces full support for the widely used UTF-8 character encoding as an import or export encoding, and as database-level or column-level collation for string data. UTF-8 is allowed in the char and varchar data types, and it's enabled when you create or change an object's collation to a collation that has a UTF8 suffix.
Detailed on Microsoft

Known issues:

1. In source DB, in the "call" and "vxmlsession" tables, enddatetime column is filled after the call is completed
2. In source DB, in the "call" and "vxmlsession" tables, enddatetime column may not be filled for some reason and stay NULL
3. In source DB, in the "vxmlelementdetail" table may contain repeated rows

Important

1. Convert "varvalue" column data which may contains non-Latin char from source "vxmlelementdetail" table to NVARCHAR using scalar function dbo.UTF8_TO_NVARCHAR(varvalue)
2. Convert date/datetime from source to according date/datetime in MSSQL
3. Periodically trying to update enddatetime value for "call" and "vxmlsession" tables because it’s presented in source tables with NULL value for uncompleted calls (we may have a call lasting up to an hour)

Tip

Tune the number of copied records and linked server name on tSetting and time and frequency running jobs according your workload and your tasks.