Table Compare (found under DataSync > Tools) allows you to compare tables between two ServiceNow instances, or one ServiceNow instance and another integrated database. This is useful because you can see each table's record count, as well as a list of record discrepancies by sys_id between the two tables you're comparing—in other words, you can see which records exist in one table but not the other.
Explore the various contexts and instructions for using this feature.
|
Below are instructions for setting up table compare in different situations:
There are two options when it comes to comparing tables between two instances of ServiceNow:
The Audit Shares option compares the records shared by bulk shares with the records in another ServiceNow instance. This is useful for ensuring that bulk shares are captured in the database properly.
The Compare Records by Table option compares the records that satisfy specified conditions with the same records in your database. This is useful if you have multiple dynamic/bulk shares on the same table and want to verify that the cumulative sharing of records is captured properly in the database.
Create a condition with the condition builder or leave it blank to select all records from the chosen table. If the bulk share selected contains conditions, the condition builder will be pre-filled with the same conditions.
This module allows you to compare records shared via bulk or dynamic shares with tables in another database. With bulk shares, a list of sys_ids will be sent from ServiceNow to the DataSync Agent for the Agent to check if the records with the corresponding sys_ids exist. If there are discrepancies, a message will be sent back to ServiceNow and the missing records will be re-shared to the Agent. This process will repeat until all records have made it to the target database.
Table Compare can also compare deleted records that dynamic shares share out to delete in the database. The Agent will check to make sure that the sys ids in the list do not exist in the database. The records that should have been removed but was not previously removed will be deleted immediately.
Here are the supported databases for table compare:
Database | Version* |
---|---|
SQL Server (MSSQL) | 2017, 2019 |
MySQL | 5.X.X, 8.X.X |
Oracle | 11g - 12c (12.2.0.1.0), 19c |
PostgreSQL | 13 |
SAP Hana | 2.0 |
*These versions of the associated databases have been tested at this time but in general these databases will work with Table Compare. Testing is ongoing to support more databases.
There are three options when it comes to comparing tables between a ServiceNow instance and another database:
|
The Audit Shares option compares the records bulk shared by a ServiceNow instance with the records in your database. This is useful for ensuring that bulk shares are captured in the database properly.
The Compare Records by Table option compares the records that satisfy specified conditions with the same records in your database. This is useful if you have multiple dynamic/bulk shares on the same table and want to verify that the cumulative sharing of records is captured properly in the database.
Create a condition with the condition builder or leave it blank to select all records from the chosen table. If the bulk share selected contains conditions, the condition builder will be pre-filled with the same conditions.
Enabling this feature will send a message to your agent database to start comparing which records were deleted. Once the deleted records are detected, a series of delete messages will be sent.
Here's how:
If the agent starts seeing these errors "java.lang.OutOfMemoryError: Java heap space" then the wrapper.conf file can be edited to increase the max heap size. See What if I'm seeing the following error: java.lang.OutOfMemoryError: Java heap space? |
After running a ServiceNow to Database table compare, you can view your results, including the total records compared, records not found in the target database that were shared by the ServiceNow instance or the records not deleted in the target database that were shared out as deleted. A quick summary of previous table compares that have run will also be available with the table provided in the Previous Table Compare Executions section.
Additionally, you can run a report with your table compare results.
Here's how to view your results:
To give you an idea of the table compare performance, tests were ran on a ServiceNow instance with two nodes, bulk sharing incidents with 4 MultiOutput processors jobs and records that averaged 5 KB in size. The total time for table compare to complete from ServiceNow to a database was as follows:
Number of incident records | Total Duration |
---|---|
500,000 | 1:13:16 |
750,000 | 1:57:12 |