Sync Tool

The Metrix Sync CLI’s Sync tool provides users with the ability to create a local copy of their Metrix data, within a locally configured SQL Server database environment. The sync processes can be configured to run periodically through Scheduled Tasks (or other cron-job runners) and each sync operation will update only the records that have been altered since it was last run.

Using the Metrix Sync CLI Sync tool

The following sections are detailed within this section of the help-guide:

  1. Creating a compliant database: Instructions for setting up a SQL Server database with the required collation for the Metrix Sync CLI tool.
  2. Configuring Sync: One time configuration of the Metrix Sync CLI tool, including setting up the connection to the local SQL Server database and specifying any required parameters.
  3. Running Sync Pull: Pulling the latest data from the Metrix platform into the local SQL Server database, ensuring that only the records that have changed since the last sync are updated. If no previous sync has occured, the tool will perform a full initial sync, pulling all available data into the local database.
  4. Rebuilding the Local Database: A troubleshooting option that fully rebuilds your local SQL Server database, without pulling any new data from the Metrix platform. This can be useful if a configuration adjustment has not come through as expected.

Subsections of Sync Tool

Database Requirements

The Metrix Sync CLI tool handles all of the database initialisation, and data population. The only pre-requisite is that you have a locally configured SQL Server database prepared for the tools use. The only requirement we have around this database is for the specific collation that is compatible with the Metrix Sync CLI tool - SQL_Latin1_General_CP1_CS_AS. This collation is case-sensitive and accent-sensitive, ensuring that string comparisons within the database are performed accurately according to the Metrix Sync CLI tool’s requirements.

Creating a compliant database

To create a compliant database, you can use the following SQL command:

CREATE DATABASE MetrixSync
COLLATE SQL_Latin1_General_CP1_CS_AS;

Securing the database

We leave the security configuration of the database to each individual organisation. The only requirement from the side of the Metrix Sync CLI tool, is an account that has sufficient privilges to create and maintain the neccesary schema, and populate the database with the necessary data. As a minimum, the tool’s user account needs to be a member of the following roles:

  • db_datareader
  • db_datawriter
  • db_ddladmin

To achieve the above, you can run:

ALTER ROLE db_datareader ADD MEMBER [metrix_cli_user];
ALTER ROLE db_datawriter ADD MEMBER [metrix_cli_user];
ALTER ROLE db_ddladmin ADD MEMBER [metrix_cli_user];

Replace [metrix_cli_user] with the actual username of the account you have created for the Metrix Sync CLI tool to uses.

Configure Sync Tool

Configuring the Metrix Sync CLI Sync tool is (typically) a one-time process that involves supplying the connection parameters for the SQL Server database that will store the locally synced Metrix data. To configure the tool, simply run the following command and follow the prompts with connection details:

./metrix-sync.exe sync configure

You will be asked for the following details:

  • SQL Server Host: the name of the database server where the SQL Server instance is running.
  • Instance Name: the name of the SQL Server instance to connect to - leave this blank in most cases.
  • Port Number: the port number on which the SQL Server instance is listening - leave this blank to use the default port (1433).
  • Database Name: the name of the database where the Metrix data will be stored. Ensure that this database has the required collation (SQL_Latin1_General_CP1_CS_AS).
  • Geometry EPSG: the EPSG code for the spatial reference system to be used for storing geometry data in the database. This should match the EPSG code used within your Metrix environment for geometry data.
  • Authentication Mode: the authentication mode to be used for connecting to the SQL Server instance. This can be either ‘integrated’ for Windows Authentication or ‘sql’ for SQL Server Authentication.
  • Username (if using SQL Server Authentication): the username to be used for connecting to the SQL Server instance.
  • Password (if using SQL Server Authentication): the password to be used for connecting to the SQL Server instance.

Once all the required details are provided, the Metrix Sync CLI will attempt to connect to the specified SQL Server instance. If it cannot connect, an error will be returned. If no error is returned, you’re good to go.

Note

Much like your API token, the configuration for the SQL Server connection is specific to your user account. If another user logs onto the machine, they will need to configure the sync tool separately with their own connection details.

Running the Sync Tool (Pull)

Once you have configured the Metrix Sync CLI, you can use it to pull data from the Metrix servers into your local SQL Server database. To do this, run the following command:

./metrix-sync.exe sync pull

For the first time you run this command, it may take a few minutes as it will pull all the relevant data from the Metrix servers and triage that into your local SQL Server database. Subsequent runs will be faster as only new or updated data will be pulled.

Note

When ‘automating’ the sync process, it is this command that you will be running as the regular process to keep your local SQL Server database up to date with the latest data from the Metrix servers.

Rebuilding the Local Database

In the case where you feel that the local SQL Server database is not reflecting the correct state of your Metrix data, you can use the rebuild command as a first pass troubleshooting step. The rebuild command will fully reconstruct the database - without running a sync pull step.

./metrix-sync.exe sync rebuild