Migrating from 4.1 to 5.0: estimating data migration time

The xMatters 5.0 patch 005 release included a data migration tool that will allow customers to migrate their 4.1 patch 015 deployments to 5.0 patch 005. While the process itself is relatively quick, the transfer of information from the 4.1 database to the 5.0 version can take a significant amount of time. This article provides a simple way to determine a rough estimate of how long it will take the data migration tool to migrate the data on your system.

For more information about the data migration tool, and the options available when running it, refer to the xMatters installation and administration guide for 5.x.

Estimating migration time

Migration times can vary depending on the migration method you use. The defauilt method of streaming data over a JDBC connection will copy rows at approximately 3000 to 4000 rows per second. Using the database link featue (available in xMatters 5.0 patch 007) can increase this rate tenfold.

To estimate how long it will take to migrate your database, divide the number of rows returned by the provided SQL queries by 3500 for migrations streaming data over a JDBC connection, and by 35000 for migrations using a database link. This should provide a rough estimate of how long the migration will take (in seconds).

Note that larger databases with large data tables may impact this estimate calculation. There is not a direct linear relationship between the number of records, the size of the data in KB, and the time it takes to migrate.  Data content will also impact performance; CLOBs, BLOBs, and large character data columns will take longer to migrate.

SQL Queries

Each of the following SQL queries will return the number of rows in your database that will need to be migrated. If you are intending to run the migration tool in SYSTEM mode, you need to run only the first query. If you intend to include REV data, either in a second pass of the migration tool or as part of the ALL mode, then run both queries and add the numbers together. Note that all of the SQL queries depend on the database having up-to-date statistics.

NOTE: You need to prefix these queries with "USE"; e.g., "USE XMATTERS". Otherwise, the queries  will either not return a value, or may target the wrong database.

SQL Server deployments

// SYSTEM tables for SQLServer  
  SELECT SUM(pa.rows) RowCnt  
FROM sys.tables ta  
INNER JOIN sys.partitions pa  
ON pa.OBJECT_ID = ta.OBJECT_ID  
INNER JOIN sys.schemas sc  
ON ta.schema_id = sc.schema_id  
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0) and ta.name IN ('ADDRESSES', 'AGNT_INTR_SVCS', 'APIA_OUTBOUND_QUEUE', 'AP_AGNTS', 'AP_CLNTS', 'ATTRS', 'ATTR_CAT', 'ATTR_CAT_JOIN', 'BASIC_SRCH_CRTN', 'CLUSTERS', 'COMP_CONST', 'CONTINUATIONS', 'CREDS_HISTORY', 'CRTA_SRCH_CRTN', 'CUST_ATTR_CAT', 'CUST_ATTR_SRCH_CRTN', 'CUST_FLDS', 'CUST_FLD_SRCH_CRTN', 'CUST_MSG_PNLS', 'CUST_MSG_PNL_ROLES', 'CUST_PAGES', 'CUST_PAGE_ROLES', 'DATA_LOCK', 'DIALING_AREA_CODES', 'DVC_NAMES', 'EV_DMN', 'EV_DMN_CONST', 'EV_DMN_PRED', 'EV_DMN_PRED_VALUES', 'EV_SUBS', 'EXT_USER_SESSION', 'GLB_CONST', 'HIBERNATE_SEQUENCES', 'INTR_AGNTS', 'INTR_AGNTS_VALS', 'INTR_AGNT_ACSS_CTRL', 'INTR_AGNT_REGS', 'INTR_SVCS', 'INTR_SVC_ROLES', 'MSG_DMN', 'MSG_DMN_PREDS', 'MSG_DMN_ROLES', 'NODE_COMPONENTS', 'NODE_MONS', 'NODE_PP_MONS', 'ORG_ATTR_CAT', 'ORG_CLUSTERS', 'ORG_COUNTRIES', 'ORG_DVC_NAMES', 'ORG_DVC_TYPES', 'ORG_HOLIDAYS', 'ORG_LANGUAGES', 'ORG_LDAPS', 'ORG_LDAP_SERVER', 'ORG_LDAP_TMPLS', 'ORG_PWD_POLICIES', 'ORG_QUOTAS', 'ORG_SVC_PROVS', 'ORG_SVC_PROV_PROTS', 'ORG_TZ', 'PROT_BESS', 'PROT_DTMFS', 'PROT_GENS', 'PROT_GEN_ARGS', 'PROT_GSMS', 'PROT_HTTPC', 'PROT_HTTPCONF', 'PROT_HTTPC_PARAMS', 'PROT_HTTPXMCONF', 'PROT_HTTPXMSMS', 'PROT_JABBERS', 'PROT_MAPIS', 'PROT_NOTES', 'PROT_PAGERS', 'PROT_PHONES', 'PROT_PUCPS', 'PROT_SIPS', 'PROT_SMPPS', 'PROT_SMPP_ROUTE_OV', 'PROT_SMPP_TAGS', 'PROT_SMTPS', 'PROT_SNPPS', 'PROT_SUCPS', 'PROT_TAPLS', 'PROT_TAPS', 'PROT_UCPS', 'PROT_VDVC', 'PROT_WCTPS', 'PROV_PROTS', 'QCP_VALUES', 'QCPS', 'QRTZ_BLOB_TRIGGERS', 'QRTZ_CALENDARS', 'QRTZ_CRON_TRIGGERS', 'QRTZ_FIRED_TRIGGERS', 'QRTZ_JOB_DETAILS', 'QRTZ_JOB_LISTENERS', 'QRTZ_LOCKS', 'QRTZ_PAUSED_TRIGGER_GRPS', 'QRTZ_SCHEDULER_STATE', 'QRTZ_SIMPLE_TRIGGERS', 'QRTZ_TRIGGERS', 'QRTZ_TRIGGER_LISTENERS', 'REGIONS', 'RESPONSE_CONFIG', 'SCR', 'SCR_IDS', 'SCR_OBJS', 'SCR_PKGS', 'SCR_PKG_VERS', 'SCR_VARS', 'SCR_VAR_OBS', 'SEC_AUDIT', 'SIMP_SMS_RESPS', 'SIMP_SMS_RESPS_LOCK', 'SITES', 'SITE_HOLIDAYS', 'TOLL_FREE_CODES', 'VOICE_MSGS', 'WS_AUDIT', 'BES_DVC_DTL', 'DATE_HOLIDAY_CNTNER', 'DATE_RNG', 'DATE_RPT_PATTERNS', 'DATE_RULES', 'DRNGSS', 'DRNGS_RNG', 'DRSS', 'DRS_EXCLUDES', 'DRS_INCLUDES', 'DVC', 'DVC_TF', 'DYN_TMS', 'DYN_TM_OBSERVERS', 'DYN_TM_SUPS', 'EMAIL_DVC_DTL', 'GEN_DVC_DTL', 'GRP', 'GRP_FAVORITES', 'GRP_OBSERVERS', 'GRP_SUPS', 'GRP_TF', 'HOLIDAYS', 'IM_DVC_DTL', 'LOW_USE_USER_DATA', 'NUM_PAGE_DVC_DTL', 'PERSONS', 'PERSON_ATTRS', 'PERSON_CUST_VALUES', 'PERSON_FAVORITES', 'PERSON_MSPS', 'PERSON_SUPS', 'PHONE_CREDS', 'RECIPIENTS', 'SCEN_DVC_FILTERS', 'SCEN_PREDS', 'SCEN_PRED_LIST_VALUES', 'SCEN_PRED_TEXT_VALUES', 'SCEN_RECIPIENTS', 'SCEN_RESPONSES', 'SCEN_SUPS', 'SCHED_SCENS', 'SCS', 'SC_CALL_STKS', 'SC_EXCE_ELEMENT', 'SC_FOR_STKS', 'SC_JUMP_STKS', 'SC_KEYS', 'SC_SUB_ELEMENT', 'SC_WHILE_STKS', 'SRCH_CRTA', 'SRCH_CRTN', 'STD_ROLES', 'STD_ROLE_FUNC', 'STD_ROLE_PERM_GRANTS', 'SUBS', 'SUBSCR_DMN_ROLES', 'SUBS_ORG_DVC_TYPES', 'SUB_DMN', 'SUB_DMN_ACTIONS', 'SUB_DMN_DVC_SUPRSD', 'SUB_DMN_PRED', 'SUB_MANAGERS', 'SUB_PREDS', 'SUB_PRED_VALUES', 'SUB_RECIPIENTS', 'SUB_TF', 'TM', 'TMP_RPMT', 'TM_ESCALATION', 'TM_MBRSHIPS', 'TM_ROTATION', 'TM_TMPLS', 'TM_TMPL_ESCALATION', 'TM_TMPL_MBRSHIPS', 'TN_ESC_DELAYS', 'TXT_PAGE_DVC_DTL', 'TXT_PHONE_DVC_DTL', 'URL_ALIAS', 'URL_ALIAS_PARAM', 'VIRTUAL_DVC_DTL', 'VOC_DVC_DTL', 'WEB_CREDS', 'WEB_HDS', 'WEB_HD_MONS', 'WS_USERS', 'WS_USERS_FUNC', 'WS_USER_ACL')  
 
// REV tables for SQLServer  
SELECT SUM(pa.rows) RowCnt  
FROM sys.tables ta  
INNER JOIN sys.partitions pa  
ON pa.OBJECT_ID = ta.OBJECT_ID  
INNER JOIN sys.schemas sc  
ON ta.schema_id = sc.schema_id  
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0) and ta.name LIKE '%_REV'  

Oracle deployments

// SYSTEM tables for Oracle  
select  
  sum(num_rows)  
from  
  user_tables  
where  
  table_name IN ('ADDRESSES', 'AGNT_INTR_SVCS', 'APIA_OUTBOUND_QUEUE', 'AP_AGNTS', 'AP_CLNTS', 'ATTRS', 'ATTR_CAT', 'ATTR_CAT_JOIN', 'BASIC_SRCH_CRTN', 'CLUSTERS', 'COMP_CONST', 'CONTINUATIONS', 'CREDS_HISTORY', 'CRTA_SRCH_CRTN', 'CUST_ATTR_CAT', 'CUST_ATTR_SRCH_CRTN', 'CUST_FLDS', 'CUST_FLD_SRCH_CRTN', 'CUST_MSG_PNLS', 'CUST_MSG_PNL_ROLES', 'CUST_PAGES', 'CUST_PAGE_ROLES', 'DATA_LOCK', 'DIALING_AREA_CODES', 'DVC_NAMES', 'EV_DMN', 'EV_DMN_CONST', 'EV_DMN_PRED', 'EV_DMN_PRED_VALUES', 'EV_SUBS', 'EXT_USER_SESSION', 'GLB_CONST', 'HIBERNATE_SEQUENCES', 'INTR_AGNTS', 'INTR_AGNTS_VALS', 'INTR_AGNT_ACSS_CTRL', 'INTR_AGNT_REGS', 'INTR_SVCS', 'INTR_SVC_ROLES', 'MSG_DMN', 'MSG_DMN_PREDS', 'MSG_DMN_ROLES', 'NODE_COMPONENTS', 'NODE_MONS', 'NODE_PP_MONS', 'ORG_ATTR_CAT', 'ORG_CLUSTERS', 'ORG_COUNTRIES', 'ORG_DVC_NAMES', 'ORG_DVC_TYPES', 'ORG_HOLIDAYS', 'ORG_LANGUAGES', 'ORG_LDAPS', 'ORG_LDAP_SERVER', 'ORG_LDAP_TMPLS', 'ORG_PWD_POLICIES', 'ORG_QUOTAS', 'ORG_SVC_PROVS', 'ORG_SVC_PROV_PROTS', 'ORG_TZ', 'PROT_BESS', 'PROT_DTMFS', 'PROT_GENS', 'PROT_GEN_ARGS', 'PROT_GSMS', 'PROT_HTTPC', 'PROT_HTTPCONF', 'PROT_HTTPC_PARAMS', 'PROT_HTTPXMCONF', 'PROT_HTTPXMSMS', 'PROT_JABBERS', 'PROT_MAPIS', 'PROT_NOTES', 'PROT_PAGERS', 'PROT_PHONES', 'PROT_PUCPS', 'PROT_SIPS', 'PROT_SMPPS', 'PROT_SMPP_ROUTE_OV', 'PROT_SMPP_TAGS', 'PROT_SMTPS', 'PROT_SNPPS', 'PROT_SUCPS', 'PROT_TAPLS', 'PROT_TAPS', 'PROT_UCPS', 'PROT_VDVC', 'PROT_WCTPS', 'PROV_PROTS', 'QCP_VALUES', 'QCPS', 'QRTZ_BLOB_TRIGGERS', 'QRTZ_CALENDARS', 'QRTZ_CRON_TRIGGERS', 'QRTZ_FIRED_TRIGGERS', 'QRTZ_JOB_DETAILS', 'QRTZ_JOB_LISTENERS', 'QRTZ_LOCKS', 'QRTZ_PAUSED_TRIGGER_GRPS', 'QRTZ_SCHEDULER_STATE', 'QRTZ_SIMPLE_TRIGGERS', 'QRTZ_TRIGGERS', 'QRTZ_TRIGGER_LISTENERS', 'REGIONS', 'RESPONSE_CONFIG', 'SCR', 'SCR_IDS', 'SCR_OBJS', 'SCR_PKGS', 'SCR_PKG_VERS', 'SCR_VARS', 'SCR_VAR_OBS', 'SEC_AUDIT', 'SIMP_SMS_RESPS', 'SIMP_SMS_RESPS_LOCK', 'SITES', 'SITE_HOLIDAYS', 'TOLL_FREE_CODES', 'VOICE_MSGS', 'WS_AUDIT', 'BES_DVC_DTL', 'DATE_HOLIDAY_CNTNER', 'DATE_RNG', 'DATE_RPT_PATTERNS', 'DATE_RULES', 'DRNGSS', 'DRNGS_RNG', 'DRSS', 'DRS_EXCLUDES', 'DRS_INCLUDES', 'DVC', 'DVC_TF', 'DYN_TMS', 'DYN_TM_OBSERVERS', 'DYN_TM_SUPS', 'EMAIL_DVC_DTL', 'GEN_DVC_DTL', 'GRP', 'GRP_FAVORITES', 'GRP_OBSERVERS', 'GRP_SUPS', 'GRP_TF', 'HOLIDAYS', 'IM_DVC_DTL', 'LOW_USE_USER_DATA', 'NUM_PAGE_DVC_DTL', 'PERSONS', 'PERSON_ATTRS', 'PERSON_CUST_VALUES', 'PERSON_FAVORITES', 'PERSON_MSPS', 'PERSON_SUPS', 'PHONE_CREDS', 'RECIPIENTS', 'SCEN_DVC_FILTERS', 'SCEN_PREDS', 'SCEN_PRED_LIST_VALUES', 'SCEN_PRED_TEXT_VALUES', 'SCEN_RECIPIENTS', 'SCEN_RESPONSES', 'SCEN_SUPS', 'SCHED_SCENS', 'SCS', 'SC_CALL_STKS', 'SC_EXCE_ELEMENT', 'SC_FOR_STKS', 'SC_JUMP_STKS', 'SC_KEYS', 'SC_SUB_ELEMENT', 'SC_WHILE_STKS', 'SRCH_CRTA', 'SRCH_CRTN', 'STD_ROLES', 'STD_ROLE_FUNC', 'STD_ROLE_PERM_GRANTS', 'SUBS', 'SUBSCR_DMN_ROLES', 'SUBS_ORG_DVC_TYPES', 'SUB_DMN', 'SUB_DMN_ACTIONS', 'SUB_DMN_DVC_SUPRSD', 'SUB_DMN_PRED', 'SUB_MANAGERS', 'SUB_PREDS', 'SUB_PRED_VALUES', 'SUB_RECIPIENTS', 'SUB_TF', 'TM', 'TMP_RPMT', 'TM_ESCALATION', 'TM_MBRSHIPS', 'TM_ROTATION', 'TM_TMPLS', 'TM_TMPL_ESCALATION', 'TM_TMPL_MBRSHIPS', 'TN_ESC_DELAYS', 'TXT_PAGE_DVC_DTL', 'TXT_PHONE_DVC_DTL', 'URL_ALIAS', 'URL_ALIAS_PARAM', 'VIRTUAL_DVC_DTL', 'VOC_DVC_DTL', 'WEB_CREDS', 'WEB_HDS', 'WEB_HD_MONS', 'WS_USERS', 'WS_USERS_FUNC', 'WS_USER_ACL')  
 
// REV tables for Oracle  
select  
  sum(num_rows)  
from  
  user_tables  
where  
  table_name like '%_REV'

Further information

This functionality may be included as part of the data migration tool in a future release.

xMatters Reference

XFO-3292, DTN-3081, JDN-3335

Originally created by Don Clark

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk