1. Getting Started

SnowMirror

1.1. Introduction

SnowMirror is a data replication tool for ServiceNow. It replicates data from ServiceNow into your local (on-premise) database.

SnowMirror is an on-premise software. You install it into your own ICT infrastructure. All data stays within your organization.

It provides a simple-to-use user interface, where you can choose which tables and columns you want to synchronize and how often you want to do that. The user interface also allows you to see a history of your synchronizations - what tables it synchronized, when it synchronized the data and how many records it downloaded.

SnowMirror does not modify or transform the data. It is up to you, how you use the data. Usually, customers use it for reporting. They connect their reporting tools to the mirror database and run reports against the replica. That is useful because they don’t have to run reports against a live ServiceNow environment. Customers also use SnowMirror for backups or as an integration point for other applications.

1.2. Architecture

SnowMirror reads data from ServiceNow using SOAP or REST API and stores it into your database. We call this process a synchronization. When SnowMirror receives data, it keeps it in memory (RAM). Once the data is permanently stored in mirror database, it may be freed from memory.

Configuration settings are stored in a configuration database. Data is stored in mirror database. SnowMirror does not modify the data - what it receives from API, is what it stores to the database.

SnowMirror’s user interface is accessible via an internet browser. Optionally, you can the check status of your synchronizations directly from within your ServiceNow instance using SnowMirror Monitor.

snowMirrorBasicScheme

Multiple SnowMirror installations with separate databases can be connected to single ServiceNow instance. It can also be installed into a high-availability cluster.

1.2.1. Databases

SnowMirror supports multiple databases: SQL Server, Oracle, MySQL, Postgres, Snowflake and Azure Synapse. See detailed information in Administrator Guide.

  • Configuration database - stores metadata, i.e. what to synchronize, when to synchronize, etc.

  • Mirror database - stores un-modified data mirrored from ServiceNow. The structure of tables and columns is a one-to-one of tables and columns from ServiceNow. You should never manipulate database schema from any other place than SnowMirror’s user interface.

    All tables contain additional columns mirror_created_on and mirror_updated_on. They contain a date and time when SnowMirror inserted/updated a record in the database.

1.2.2. Data to Synchronize

SnowMirror is able to synchronize and backup tables, views and attachments. Synchronization is a process that creates a one-to-one copy of your data without remembering history of the data. Backup is a process where we make a snapshot at the given moment and therefore it keeps history of the data.

SnowMirror is able to synchronize system tables, your own custom tables (u_) and tables from scoped applications. SnowMirror is not able to synchronize tables that do not have SysId column.

Follows a list of supported synchronizations and backups.

Type Target Description

Table

Database

Stores ServiceNow tables into your database. Keeps a one-to-one copy of your data.

View

Database

Stores ServiceNow views into your database. Keeps a one-to-one copy of your data.

Attachment

File system

Synchronizes attachments of ServiceNow tables into your file system. Keeps a one-to-one copy of your attachments.

Backup

File system

Stores ServiceNow tables as XML or CSV file to your file system. Keeps history.

Backup Attachment

File system

Stores attachments of ServiceNow tables to your file system. Keeps history.

1.3. Migration Process

SnowMirror loads data from ServiceNow table using SOAP Web Services or REST API.

SOAP API replication algorithm: SnowMirror requests a batch of 10,000 SysIds which were created or updated in ServiceNow. After receiving SysIds, it loads data using these SysIds in batches of 250 records. It repeats these steps until it downloads all records.

REST API replication algorithm: SnowMirror requests a batch of 1,000 records which were created or updated in ServiceNow. It repeats these steps until it downloads all records.

SnowMirror processes multiple batches at the same time to improve the replication times. This is thoroughly explained in Performance Tuning chapter. Batch sizes are configurable on Performance Tuning page.

SnowMirror does not lock the table in ServiceNow because it only reads the data. If some other process updates and locks your table in ServiceNow, SnowMirror may have to wait until it is free to read the data.

SnowMirror creates and updates records in mirror database. Therefore, it has to lock affected records util it updates them. If some other process is accessing the mirror database table, SnowMirror may have to wait until it is free to commit the update.

To ensure correct synchronization process, please make sure you have correct date and time set on your computer.

1.3.1. Transactions

Data batches are transactional but whole synchronization runs are not. This prevents SnowMirror from reverting data in case of an error.

1.3.2. Migration Steps

synchronization process
  1. Initialization: Loads data from the configuration database and decides which data it will be synchronizing. It downloads meta-data from ServiceNow.

  2. Logs: Logs information about your environment to the activity log. This helps us to diagnose errors.

  3. Auto Schema Update: If enabled, SnowMirror finds out if the schema has changed and makes appropriate changes in mirror database. Optionally, it sends notifications about schema changes to an email of your choice.

  4. Count: Counts records in ServiceNow so that it know how many records it will be synchronizing.

  5. Create or Alter Table: If it does not already exist, it creates a new table in mirror database. Otherwise, it just updates the table (if necessary).

  6. Replication: This is the most important step. SnowMirror synchronizes data from ServiceNow. The replication process is a bit different based on which API you use.

    SOAP API

    SnowMirror requests a list of SysIds which were created or updated in ServiceNow. By default, it requests in a batch of 10,000 SysIds. It uses getKeys method from SOAP Web Services.

    After receiving SysIds, it loads data by these SysIds. By default, data are loaded in batches of 250 records. It uses getRecords method from SOAP Web Services.

    REST API

    SnowMirror requests records which were created or updated in ServiceNow. By default, it requests in a batch of 1,000 records. It uses REST Table Api

    Common to SOAP API and REST API

    If an encoded query is defined, SnowMirror checks for records which matched the query before, but now they don’t match it anymore. Such records are deleted from mirror database. To do that, SnowMirror load SysIds of all updated records (regardless the query) and compares them with SysIds of replicated records.

    As of version 4.0.0, SnowMirror processes multiple batches at the same time to improve the replication times. This is thoroughly explained in Performance Tuning chapter. Batch sizes are configurable on Performance Tuning page.

  7. Delete: Most ServiceNow tables store deleted data in sys_audit_delete table - i.e. deletes audited. This is the preferred way of determining deleted records. However, not every table is audited.

    For such cases, SnowMirror offers multiple deleting strategies. SnowMirror cannot determine if a table is audited or not. Note, that most tables in ServiceNow can be set as audited. If you want more information about auditing tables see ServiceNow Documentation on Auditing.

    Delete Strategies

    Audit

    Audit deleting strategy looks into sys_audit_delete table to find deleted records.

    To find deleted records of a synchronized table since the last synchronization, Audit Delete strategy filters data using tablename and sys_updated_on fields. You should have database index on these fields in ServiceNow.

    This is the most efficient strategy for finding deleted records. To be able to use this strategy, your table needs to audit deleted records in ServiceNow. To find out, whether deletes are audited on your table or not, please go to Edit Settings and use Validate button.

    Advanced topics:

    • Optionally, you SnowMirror can query data using sys_created_on field instead of sys_updated_on field. This is configurable via Advanced Settings using snowMirror.auditDelete.column property.

    • In ServiceNow, some tables make up a hierarchy (for example task → incident). Audit delete strategy has to look for deleted records not only from the table being synchronized but also from child tables. For example task → incident has to filter sys_audit_delete like this: tablename=task^ORtablename=incident.

    Diff

    Diff compares sys_ids in ServiceNow table with the sys_ids in mirror DB. Sys_ids that are present in mirror table, but not ServiceNow table are deleted. sys_updated_on and sys_created_on are intact.

    Fast Difference

    It counts records and downloads max. value of sys_updated_on column. If the values don’t match with the mirror database it splits the SysIds into smaller chunks and repeats until it gets until to 10,000 keys. Only then it downloads the keys and compares them with the mirror database. This strategy is useful when you have a large table and small amount of deletes because it reduces the number of requests to ServiceNow.

    None

    Does not delete any data from the mirror table. Insert and update tasks act as normal. Deleting data can be done manually by Clean and Synchronize button on the detail of the synchronization.

    Truncate (Deprecated)

    Truncate deleting strategy deletes all data from mirror table and downloads all records from scratch. It is deprecated - use Full Load Scheduler instead.

Default deleting strategy is Audit. If the synchronized table is not audited, consider auditing it. Use some other strategy only if the synchronized table is not audited.
When synchronizing a full load view, it is only possible to you use Truncate strategy
  1. Consistency check: Checks that number of records in ServiceNow matches the number of records in mirror database. It is explained in more detail in chapter Consistency Check.

  2. Finalize Migration: Updates the activity logs and finishes the synchronization.

1.3.3. Cache Management

For a better performance, SnowMirror caches ServiceNow metadata (data related to tables and its columns, parent-child structure, etc.). Because of this cache, some changes in ServiceNow can be taken into account with a delay.

If you want to clear the cache manually go to Settings menu, choose General Settings and click on Clear All Caches button.

Cache timeout is configurable through Advanced Settings.

1.3.4. ServiceNow Performance Considerations

  • Download times and number of requests

    The first initial load may take a while to finish. The download time depends on how many rows the table has, how many columns it has and how big the data inside the cells is. SnowMirror downloads data in batches, i.e. it makes a lot of small requests to download huge tables.

    It may take several minutes to download smaller tables or even several hours to download huge tables (millions of records).

    The initial load can be time-consuming. However,subsequent synchronizations will download just the changes (new, update and deleted records). Downloading changes is fast and does not impact the performance of your ServiceNow instance.

  • Database indexes in ServiceNow

    When SnowMirror queries data from ServiceNow, it extensively uses sys_updated_on column. If a table does not have sys_update_on column, it uses sys_created_on instead. To ensure fast processing times:

    • You should have an index on sys_updated_on column in ServiceNow.

    • If the table does not have sys_updated_on column, you should have an index on sys_created_on column.

    • If you have a table hierarchy, only the base table in the hierarchy needs to have the column indexed.

    You can use validate button to verify if you need to create the index. To create an index open ServiceNow and go to System Definition → Tables page. Select your table and open Database Indexes tab. You can create the index there.

1.3.5. Azure Synapse

since 4.10.0 The synchronization process is a bit different for Azure Synapse.

  1. SnowMirror downloads data from ServiceNow.

  2. SnowMirror stores the data into temporary files into local snow-mirror/data/synapse directory.

  3. Once the file reaches 100,000 records, SnowMirror uploads it to Azure Storage.

  4. SnowMirror copies data from Azure Storage into a stage table.

  5. At the end of a synchronization, SnowMirror merges stage table into the target table.

  6. At the end of a synchronization, SnowMirror deletes temporary files from local file system.

azure synapse

1.4. Display Values

In addition to raw values, ServiceNow allows its users to define so-called display values. Display
values are usually human-friendly texts which users understand more easily than raw values. For example:

  • Reference fields: When your records reference a user, ServiceNow has to store SysId of that user (raw value). The related display value can contain name of the user.

  • Choice fields: ServiceNow may store choices as numbers. The display values contains human-readable description of the choice.

SnowMirror allows you download display values of:

  • Fields of "reference", "domain_id", "document_id" or "glide_list".

  • Choices where choice value is greater than 0

1.4.1. Synchronization Strategies

SnowMirror provides you with three options how to handle display values.

  • Only sys_ids - SnowMirror does not download display values. It just downloads the raw value. For example reference fields will contain SysId of the referenced record.

  • Only Display Values - SnowMirror downloads and stores display values. It does not store raw values.

  • Both - SnowMirror downloads stores both raw values and display values. Display values are stored to a column with prefix dv_.

Note that there is a limitation imposed on display value synchronization. If display value changes and the record which references the display value does not change SnowMirror cannot discover the change. The display value is only updated when the referencing record changes too.

1.4.2. Display Value Replication Algorithm

Let’s focus on the reference fields. How does ServiceNow evaluate which field to use as a display value? It is not so straightforward as it might seem.
Imagine a table hierarchy Security Incident → Incident → Task. It is possible to specify display values in each table or just in one of them.
It is possible to omit the display values at all and rely on the defaults and finally, have you heard of Dictionary Overrides?

Here is the actual evaluation algorithm ServiceNow uses:

  • Dictionary Override – Current Table – The highest priority in the evaluation algorithm has a flag called Override Display Value in the Dictionary Override (i.e. table called sys_dictionary_override). It is possible to set a different display value then the parent table is specifying. E.g. the Task table has the field number as a display value. In the child table Security Incident it is possible to set up an override configuring short_description as a display value.

  • Dictionary Override – Parent Table – same as previous. If there is no override in the referenced table then the algorithm searches for overrides in the table hierarchy from the child to the parent. E.g. override in the Incident table takes precedence over a display value field specified in the Security Incident table but not over an override in the Security Incident table.

  • Display value field – Current Table – if there are no overrides then the standard display value fields come into play. The display value on the child table overrides the display value on the parent. E.g. Security Incident can specify its own field u_security_code as a display value for security incidents which would override the number field in Task.

  • Display value field – Parent Table – same as previous. If there is no display value field in the referenced table then the algorithm searches for display fields in the table hierarchy from the child to the parent.

  • Default fields – even if there are no display values explicitly set up, ServiceNow searches for default fields to make them as display value fields. These fields are “name“, “u_name“, * “sys_name“, “number“, “u_number“. The algorithm first searches the whole table hierarchy for a field called name. Then, if not found, it searches for u_name, etc. Please note that the child-parent mechanism does not work here. Name has always precedence over Number.

  • SysId – if everything fails then ServiceNow uses sys_id as a display value.

Note that the display value field can be another reference field or can be a choice list field. Transitive relation applies here so ServiceNow is traversing through the display values to find the last in the row.

1.5. Views Synchronization

Migration process described in previous chapters works perfectly when synchronizing tables. Beside tables ServiceNow allows you to define database views. SnowMirror supports views synchronization with some limitations. There are several issues SnowMirror has to tackle:

  • A record in a view is a Cartesian product of records from several tables. That means there is no unique and permanent SysId identifying the record.

  • A record in a view does not have to contain "sys_created_on" and "sys_updated_on" columns.

  • There is no delete log for views in ServiceNow.

SnowMirror provides two options to synchronize a view:

  • Full Load - full load synchronization deletes data from mirror database and then downloads all data from ServiceNow. This way you are guaranteed to have the most recent and 100 percent accurate copy of your data in mirror database. Depending on the amount of your data the synchronization may take a long time to perform.

  • Incremental Load - incremental load synchronization downloads only increments, i.e. new created, updated and deleted data. To use this synchronization type you have to choose a master table. SnowMirror uses this table to find out the increments. This means if records in other tables are changed SnowMirror would not find out. Also, the master table should not contain any duplicate SysId values.
    Incremental Load View synchronization requires less amount of time to perform at the cost of not having 100 percent accurate copy of your data. To tackle this issue we recommend to set up Full Load Scheduler to run once in a while because it clears data from mirror database and downloads all data from ServiceNow.

1.6. Attachments synchronization

Attachments synchronization process is very similar to table synchronization process. This section lists some differences between table synchronization and attachment synchronization.

1.6.1. Migration steps

Create directory if not exists

Besides configuration and mirror database, attachment synchronization needs access to the file system so that it can store attachments there. During the synchronization, SnowMirror creates a directory for attachments (if it does not already exist).

Path to the directory is defined by snowMirror.attachment.rootDir property which can be configured in Advanced properties. Be sure to have sufficient rights for creating directories and files in this directory.

Replication Using SOAP API
  1. SnowMirror loads serviceNow.number.of.keys keys (by default, this is 10,000) which were created or updated since the last synchronization run. The keys are loaded from sys_attachment table.

  2. Attachment meta-data are loaded for these keys. Meta-data are loaded in batches by serviceNow.number.of.records records (by default this is 250).

  3. Based on the meta-data, SnowMirror start to download the attachments from sys_attachment_doc table. SnowMirror repeatedly requests data from this table because it returns the attachments divided into small chunks. SnowMirror downloads serviceNow.number.of.attachment.chunks chunks (by default this is 500) per one request. Then it writes the chunks into temporary files and joins them to make up the whole attachment file.

  4. After downloading attachments their meta-data are stored to Mirror table and temporary files are moved to snowMirror.attachment.rootDir. If there already exists file with same name, new file is renamed (suffix is added to filename).

Replication Using REST API
  1. SnowMirror loads attachment meta-data using REST Attachment API. It loads the meta-data in batches of serviceNow.number.of.records records (by default, this is 1,000). It always loads records which were created or updated since the last synchronization run.

  2. Based on the meta-data, SnowMirror downloads the attachments one by one using REST Attachment API.

  3. After downloading attachments their meta-data are stored to Mirror table and temporary files are moved to snowMirror.attachment.rootDir. If there already exists file with same name, new file is renamed (suffix is added to filename).

Delete strategies
Delete Strategies for Attachments

Audit

Audit deleting strategy is not available when synchronizing attachments

Diff

Diff compares sys_ids in ServiceNow table with the sys_ids in mirror DB. Sys_ids that are present in mirror table, but not ServiceNow table are deleted (with their corresponding attachment file). sys_updated_on and sys_created_on are intact.

Fast Difference

It counts records and downloads max. value of sys_updated_on column. If the values don’t match with the mirror database it splits the SysIds into smaller chunks and repeats until it gets until to 10,000 keys. Only then it downloads the keys and compares them with the mirror database. This strategy is useful when you have a large table and small amount of deletes because it reduces the number of requests to ServiceNow.

None

Does not delete any data from the mirror table or attachment directory. Insert and update tasks act as normal. Deleting data can be done manually by Clean and Synchronize button on the detail of the synchronization.

Truncate (Deprecated)

Truncate deleting strategy deletes all data from mirror table and all attachments from local directory before the insert task. Update task is skipped. Note, that sys_updated_on and sys_created_on columns are set to time of last synchronization start. It can not be used to determine the time of actual row insert or update in ServiceNow. It is deprecated - use Full Load Scheduler instead.

1.6.2. Attachments Directory

Content of snowMirror.attachment.rootDir has following structure:
<snowMirror.attachment.rootDir>/<synchronization name>/<display value> where:

  • snowMirror.attachment.rootDir - configurable directory where attachments are stored

  • synchronization name is a name of a synchronization that you filled in Synchronization settings form

  • display value is a value of a field which is marked as display value for the given table

1.6.3. Encoded Query

If you have entered Encoded query it is applied on related table (e.g. incident) and NOT on sys_attachment table.
While getting keys of created (or updated) attachments since the last synchronization another additional request is made on related table (i.e. incident, task, …​) so that SnowMirror can learn which records fit encoded query. This assures that only attachments for records which fit encoded query are downloaded.

1.7. Index Replication

SnowMirror allows you to replicate not only tables and data but also database indexes. If you define an index in your ServiceNow instance SnowMirror will pick up the change and create the index in your mirror database. That is particularly useful if you make complicated SQL queries to your mirror database and need to improve the performance of these queries.

You can enable index replication on General Settings page or per-synchronization in Advanced Settings.

1.7.1. Custom Indexes

What if you need an index in the mirror database, but you don’t want to create it in your ServerNow instance? Don’t worry, we have it covered. You can create the index manually in your database, and we will preserve it intact.

1.7.2. SysId Columns

ServiceNow defines primary keys on sys_id columns. SnowMirror ignores these keys because it always creates its own primary key on "sys_id" column.

1.7.3. Performance Implications

If have you enabled Auto Schema Update or set up Notifications on schema change SnowMirror will send one additional request per synchronization. In the case of views, it will send several requests depending on how many tables the view consists of.

The request itself is not resource expensive and time-consuming. Yet, it should be taken into consideration especially when SnowMirror is configured to run many synchronizations very often.

1.7.4. Limitations

In some cases, ServiceNow Web services return us encoded column names. That happens when a column name is too long. It also happens on few columns on "task" table and its descendants. We cannot then couple the column in mirror database with a column of the ServiceNow index. Therefore, we ignore those indexes.

1.8. Synchronization Types

This chapter describes all types of synchronizations you can encounter in SnowMirror.

1.8.1. Incremental Synchronization

SnowMirror requests and downloads only records inserted, updated or deleted since the last synchronization. This is the most commonly used type of synchronization.

  • New and updated records - incremental synchronization uses sys_updated_on column to query the data. Thanks to that column, SnowMirror is able to download just the new and updated records.

  • Deleted records - there are multiple strategies how to find deleted records.

1.8.2. Clean and Synchronize

SnowMirror cleans all data in a mirrored table and downloads all records from scratch.

After the clean, it may take a long time to synchronize the data again. The old data won’t be available during the synchronization. To tackle the issue you may want to enable "snowMirror.replication.cleanAndSyncToTmpTable" which synchronizes the data to temporary table. Only after the synchronization finishes successfully the original mirror table is removed and replaced by the temporary table.

Optionally, you can enable Clean Synchronization to temporary table. If enabled, SnowMirror downloads data during a clean synchronization to a temporary table. After the synchronization finishes, it drops the old table and replaces it with the temporary table. This feature allows you to have all your data available during a long clean synchronization. On the other hand, you will lose all your custom permissions and indexes defined on the target table.

1.8.3. Differential Synchronization

Inserts all missing and deletes all excessive records. By default, it does not update any records. Optionally, it can be configured to check for updates too.

Differential synchronizations works as follows:

  1. It requests the first 10,000 SysIds from ServiceNow ordered alphabetically.

  2. It loads the first 10,000 SysIds from the mirror database ordered alphabetically.

  3. It computes a difference to find out new and deleted SysIds. If it cannot decide if the record is new or deleted it will process it in the next batch.

  4. It repeats the first three steps until there is no records left to process.

If checking for updates is enabled, it also has to load and compare values of sys_updated_on column.

Differential synchronization is particularly useful if you need to repair your mirror table. See Consistency Check chapter for examples.

1.8.4. Synchronize From

SnowMirror downloads only records whose sys_updated_on column contains a value greater than or equal to the date you chose.

This is useful if your synchronization missed or ignored some records, you have fixed the root cause of the issue, and now you want to download the missing records.

1.8.5. Resume

Let’s say you have a long-running synchronization and halfway through the synchronization process it fails (for whatever reason). It’s inconvenient to run it again because it takes some time to do so. "Resume" functionality is here to rescue.

It allows you to resume a failed synchronization. If you resume a synchronization, SnowMirror starts to download data from the point of the failure.

SnowMirror can resume table and view synchronizations. Backups and attachments are currently not supported.

To resume a synchronization, open Synchronization Detail page and click Resume button.

1.9. Archiving Support

ServiceNow provides optional Data Archiving Plugin. It allows users to store older data into archive tables.

If you use Archiving in ServiceNow, you may want to enable support in SnowMirror too. You can do that globally in General Settings or per synchronization in Advanced Settings.

Here’s what SnowMirror does when archiving is enabled:

  • When Audit Deleting Strategy is used, SnowMirror checks sys_archive_log to find archived records. Then, it deletes the records from mirror database. We use the following columns from sys_archive_log:

    • sys_created_on - to find new records in "sys_archive_log" since the last synchronization.

    • restored - to find whether a record was restored (i.e., is not archived anymore) or not.

    • from_table - to get archived records of a specific table. When a table has child tables, we also look for records related to these child tables. Example:

      • When we look for archived records in "incident", our query contains: from_tableINincident

      • When we look for archived records in "task", our query contains even child tables: from_tableINtask,incident,etc.

  • Checks sys_archive_log table so that it can insert restored records to the mirror table.

  • Synchronization of Archive tables (those starting with "ar_") - New records are picked up using "sys_archived" column. Updated records are picked up using "sys_archive_restored". That’s different from ordinary (non "ar_") tables where we pick up inserted and updated records only using "sys_updated_on" column.

1.10. Consistency Check

In some cases SnowMirror may miss synchronizing some records.

Imagine that you imported data to your table in ServiceNow and all records had sys_updated_on set on some old date. SnowMirror already synchronized newer data hence it will miss the newly imported records. This is when Consistency check comes in handy. After every synchronization run it counts the number of records in ServiceNow and number of records in mirrored table. Then it compares the counts and if it finds out that they are not equal it will put the synchronization into Warning state and send notification email (if configured to do so).

consistency check list

When you encounter such a warning you should start looking for a reason of the inconsistency. To tackle the issue with old dates in imported records you can use differential synchronization. To diagnose any other potential issues you may want to use validate button.