Syncing data from BMC Helix SaaS to a destination database

As an administrator, use BMC Helix Data Connector to transfer data from BMC Helix SaaS (on PostgreSQL) to your preferred data lake or warehouse. You can transfer incident, change, and other ITSM and service management data. Supported destination databases can be a Microsoft SQL Server or Snowflake. BMC Helix Data Connector enables reliable data synchronization, giving you greater flexibility to access and analyze your data. It allows you to configure synchronization intervals, monitor ongoing data transfers, and review past synchronization results.

Licensing

The BMC Helix Data Connector service is available with the purchase of any of the following line items from your ordering document:

License	Unit of Measurement
BMC Helix Data Connector - 1TB	each

With "each" unit of measurement, a license is required for syncing selected data forms, adding up to 1TB within the BMC Helix SaaS database. This is typically sufficient for most implementations. However, if your data forms selected for syncing grow over time, have large attachments, and/or require additional data forms for syncing, you can purchase additional quantities. The quantity purchased is consumed across your production and non-production environments. (Example: Development, QA, Production)

For more information, see Additional license terms.

Additional license terms

The following additional terms apply to the BMC Helix Data Connector service:

Licensing terms and conditions are as defined in the governing master agreement and take precedence over any published description from this site.
BMC Helix Innovation Studio is a pre-requisite to accessing the Helix Data Connector and must be licensed separately.
BMC Helix Data Connector—The 1TB licensing purchased on your agreement is consumed across your production and non-production environments. Customers typically disable the non-production data sync after testing and keep the Helix Data Connector configured to production running for ongoing syncs.
Entitlements are subject to change.

For more information, see BMC Helix Subscriber Information.

Architecture of BMC Helix Data Connector

BMC Helix Data Connector provides a one-way data sync from BMC Helix SaaS to a preferred destination. It syncs both definition (DDL) and transactional data while tracking schema changes and deletions by using the DDL tracker and Delete tracker.

BMC Helix Data Connector consists of two key components: the Helix Data Connector application and Data Connect Engine, as shown in the following diagram:

Helix Data Connector architecture

The Helix Data Connector application enables administrators to configure the target destination, datasets, and sync intervals. The Data Connect Engine is a pod or singleton service that handles data movement from the source to the target. It uses REST APIs to retrieve the configuration details and JDBC connections for secure data transfer.

The data transfer mechanism is as follows:

For on-premises database (Microsoft SQL Server): Data transfer occurs over a JDBC connection via a Kaazing gateway, ensuring secure communication between the source and target environments.
For Snowflake: The system uses Snowflake JDBC connections for efficient data migration.

BMC Helix Data Connector syncs both definition (DDL) and transactional data while tracking schema changes and deletions by using DDL Tracker and Delete Tracker.

The DDL tracker supports the AR server to track the changes made to any of the forms in the system and generates SQLs that are compatible with the destination database. Any changes to the forms or record definitions are tracked.

The Delete tracker supports the syncing of deleted records. If a record is deleted from the source database, it is also deleted from the destination database. The Delete tracker monitors and maintains logs of each deleted record to ensure that the destination database remains accurate.

Scenarios

Apex Global wants to consolidate its BMC Helix ITSM data for executive reporting and compliance audits. The IT admin uses BMC Helix Data Connector to sync data from BMC Helix SaaS (on PostgreSQL database) to their on-premises (on Microsoft SQL Server database) environment for reporting and compliance audits.
Acme Corp, a Snowflake customer, wants to integrate their BMC Helix SaaS data with other enterprise datasets for advanced analytics. The IT admin uses Helix Data Connector to sync the Helix data directly into Snowflake for unified business intelligence and predictive modeling.

BMC Helix Data Connector ensures an interval-based ongoing sync of your data, making it available to your chosen data lake or warehouse for executive reporting, compliance audits, or advanced analytics.

Considerations for using BMC Helix Data Connector

Before using BMC Helix Data Connector, review the following requirements and limitations to ensure a smooth configuration and sync process:

Requirements for enabling Helix Data Connector capability

After subscribing to BMC Helix Data Connector—1TB, submit a Change Request (RFC) in BMC Helix Support Central to enable resourcing for your destination configuration and data syncing.
For more information, see the Request for Change process in the BMC Helix Subscriber Information online documentation.

Please ensure your RFC is completed before configuring your destination. Otherwise, the destination will not be created.
If you are syncing data to Snowflake, clearly mention in the RFC that the destination is Snowflake. This helps BMC Support allocate the necessary resources for data syncing. After the RFC is completed, you will receive your BMC Egress Network address, which is required for configuring network rules in Snowflake.

Security considerations

Encryption—By default, traffic between BMC Helix Data Connector and Snowflake is encrypted via SSL.
Supported authentication mechanisms—Only username-and-password-based authentication is supported for both Snowflake and Microsoft SQL Server. All other authentication types are not supported.
Permissions for destination as Snowflake—The Snowflake application requests permissions to create a database or warehouse. If you have concerns regarding this level of access to BMC Helix Data Connector, revoke the permissions after the installation and destination configuration are complete.

Role requirements

Destination: Microsoft SQL Server—The Microsoft SQL Server user must be the owner of the database.
Destination: Snowflake—This role should have the correct privileges and must have access to create and drop tables within the schema.

Configuration requirements

The BMC Helix Data Connector application is accessible to only administrators.
The source database must be PostgreSQL on SaaS environments only. On-premises environments are not supported.
You can configure only one destination per BMC Helix SaaS instance.
The destination database must either be Microsoft SQL Server or Snowflake.
To connect to Microsoft SQL Server as the destination, request the creation of a Kaazing gateway from BMC Helix SaaS to the SQL Server database by raising a support case with BMC Helix Support to initiate this request.

Data sync behavior and limitations

Only regular forms and view forms on select metadata tables can be replicated.
Data corresponding to field ID 102 and 123 and any fields marked as Encrypted At Rest are copied to the destination as is without any decryption.
Only user-friendly views/tables created by AR Server are replicated. Union views or custom tables are not supported.
Form-level data and row filtering are not supported.
Data is replicated in its original form without any transformation.
Any direct database changes (even when done using GLSQL API/direct SQL from workflow) won’t replicate unless C6 (last modified timestamp) is updated. For example, Direct update to DB by DB team or Updates using GLSQL API or Direct SQL action in Filters/Active Links (all BMC shipped workflows must be identified and fixed if required)
Importing old data by using the Import Tool will require a full sync to get the data synced to the destination if those records are needed on the destination.
When replicating data, the order of tables/data movement does not follow their relationships.
Data Connect Engine will move records from all dependent tables (B/BC/H/S/L) if the main table (T) record is modified, even if there are no changes to the dependent tables. For example, all attachments for an entry are moved (if the modified entry has attachments), even if there are no changes to the attachments.

Datasets and forms management

Deleting a form on the source removes the form from the dataset it was part of.
Recreating a form with the same name requires manually adding it to the dataset again.
Deleting a form on the source does not drop tables on the target.
Adding the same form to multiple datasets is not allowed. To move a form across datasets, delete it from the old dataset and add it to the new one.
Editing or deleting the destination is not supported.

Process overview

Perform the tasks in the following order to use BMC Helix Data Connector for syncing data from BMC Helix SaaS environment to your preferred data lake or warehouse :

Step	Action	Reference
1	Access BMC Helix Data Connector to configure the syncing details.	To access BMC Helix Data Connector
2	Set up the target destination where the data will be synced. Configure the destination as Microsoft SQL Server Configure the destination as Snowflake	To configure the destination as Microsoft SQL Server To configure the destination as Snowflake
3	(Optional) Configure advanced settings to customize the synchronization process.	To configure advanced settings
4	Configure the dataset and sync interval to manage how frequently the data is synchronized.	To configure the dataset and sync interval
5	(Optional) Perform an on-demand sync to immediately sync data as required.	To perform an on-demand sync
6	(Optional) Monitor and manage form jobs, check the status of active jobs, and view detailed job-level information.	To view the status of the jobs in the Job console To view the job-level information by using the Form job tracker

To access BMC Helix Data Connector

Select BMC Helix Innovation Studio > Workspace tab.
Click Application launcher and select Helix Data connector.

Troubleshooting

Review the troubleshooting information in this section to identify and resolve issues related to BMC Helix Data Connector and BMC Snowflake connector.

BMC Helix Data Connector logs

You can access the following logs for BMC Helix Data Connector and use these logs to get more information about errors:

Log file name	Purpose
ardataconnector.log (located in the Platform pod)	View DDL tracking-related information.
com.bmc.dsm.helix-data-connector.log (located in the Platform pod)	Check for job issues and API errors related to the connection between BMC Helix Data Connector and Data Connector Engine.
hdc.log (located in Data Connector Engine pod)	Investigate job failures, test connection failures, and monitor detailed job progress.

Snowflake connector troubleshooting

Review the troubleshooting information in this section for Snowflake connector:

Issues or errors	Resolution
Networking and connectivity	Check the following items for successful connectivity: Valid account identifier: Confirm that the account identifier is correct. Endpoint accessibility: The Snowflake endpoint must be reachable from BMC Helix Data Connector. Check for the following issues: Gateway or firewall issues Incorrect port Whitelisting of IP addresses, if required Valid SSL certificates: Expired or invalid certificates can block the connection. Proxy support: Database connections through proxies are not supported.
Authentication issues	Make sure authentication details are valid and supported: User credentials: Verify that the user name and password are correct. Check if the user account was deleted or permissions were modified. Unsupported authentication mechanisms: Multi-Factor Authentication (MFA) Key-pair authentication OAuth
Snowflake objects	Database: Check if the database exists. Role: Check if the role exists and is granted to the user. Schema: Check if the schema exists with the correct privileges. Warehouse: Must be in a running state, or if in a suspended state, auto-resume must be enabled. Must not exceed resource monitor limits (which could cancel queries). Monitor warehouse load for bottlenecks. Table/View privileges: Ensure privileges for any tables or views created by BMC Helix Data Connector are not revoked.
Common JDBC Errors	For common JDBC error messages and their causes, see Snowflake Connectivity Troubleshooting Guide.