Understanding data migration with JDBC Probe

[sachinbhasin11]

I recently had a chance to work on a large-scale data migration project for a customer who just got started on their ServiceNow journey while their legacy systems were being decommissioned.

The source from where the data had to be migrated into the instance was an MS SQL database comprising of attachments and other metadata related to their legacy case records. Since the customer was insistent to host the legacy data in ServiceNow and wasn't keen to explore any other options such as an external data lake, warehouse, etc. we had no other choice but to move ahead with the idea of migrating ~500 GB worth of data into the platform.

While there can be several approaches that can be taken to migrate the data into ServiceNow, this needs to be evaluated based on various factors such as:

1. Size of the data being imported – If it’s a small chunk a simple one-time excel load will do the trick but if it’s a large data set then we need to explore options such as DB-based, API based, etc.
2. Where is the data sitting currently i.e. db, any field, any remote location such as SharePoint etc. ....this will derive what method we will adopt for our migration
3. Type of data – E.g. for attachment we can look at attachment API, For file-based, we can look at Import set API, SFTP, etc.
4. How many resources are allocated for this work
5. How fast do we want to achieve this
6. What level of confidentiality do we want to maintain for the data being migrated?

I may have missed some other points but effectively we need to pivot our migration solution around these essential points above. 

The solution approach we undertook to migrate attachments into ServiceNow was using JDBC as our data source, while I agree that this may not be the best approach and that the ServiceNow’s best practice always highlights the use of Attachment API to port the attachments into ServiceNow. We had a restriction from the customer side which basically couldn’t honor our request to use ServiceNow Attachment API(s) and since the size of data was too large importing it via excel was a natural 'No'. Hence we choose to connect with the database directly 

Now since we had ~500 GB of data to import we used JDBC probes to query the SQL DB via Mid Server and the results of the query were brought back into the ecc_queue of the instance.

We, however, ran into an issue of maximum payload size exceeding, and upon analyzing further we found that there was a system property ‘mid.eccq.max_payload_size’  which is by default set to 20 MB. It’s also worth noting that upon being launched the JDBC probe fetches 200 result set rows by default from the source DB into ServiceNow. So you can imagine if each attachment is a row in SQL DB (attachments are stored using base64 encoding in SQL DB) and the size of each attachment row is 1MB this would mean the input payload in the ecc_queue for a single query was of 200 MB (1MB * 200 rows) whereas the property ‘mid.eccq.max_payload_size’ only let’s 20 MB worth of payload be imported into ServiceNow leading to oversized payload errors

To avoid this situation one must perform the below steps :

1. Re-adjust the value in the property ‘mid.eccq.max_payload_size’ to suit the data size being brought into the ecc_queue table
2. Set the value on the data source table record field ‘JDBC Probe Result Set rows’ (jdbcprobe_result_set_rows) to a lower number (it’s 200 by default) such that the total size of all the rows should always be less than what is defined in the system property above

Note: This dictionary entry may be missing from older instances of ServiceNow or recently upgraded ones as well. It appears to be a miss from the ServiceNow side. I recommend you import the attached XML into your instance to create this dictionary entry

    3. Launch the JDBC probe again

On a separate note, if you feel the data size is large you can perform multiple imports by using the scheduled import feature of the platform ( Your SQL query should be designed to support this approach). This enables to keep the import process running even when you are offline

I hope this approach may help some of the users out there who are still thinking about how to migrate their large data into ServiceNow