Skip to content

SyncSQL Overview & Requirements

SyncSQL Overview

Sync SQL is available for all clients using the Exigo Platform as a Service (PaaS) and can take advantage of our SQL database replication for Business Intelligence (BI) and Reporting.

You cannot improve or grow what you cannot measure, so we give you the tools to monitor your business with near real-time updates in order to make the most informed decisions possible. Your BI team will have full access to mine the data and generate reports, as necessary.

This replicated SQL database, referred to as SyncSQL, synchronizes in near real-time with your Exigo production database and provides tables formatted specifically for simplified reporting and aggregation for BI queries. All core Exigo tables are synchronized one way into your replicated SyncSQL environment. For any custom tables you require, data will be synchronized two-way—both from and to your Exigo production database.

SyncSQL is available as Self-Hosted using one of the following hosting methods:

  • Self-Hosted edition hosted with our preferred partner Epic Servers—either on their virtual machines or your own dedicated, co-located hardware at their hosting facilities—require no additional bandwidth or cross-connection charges. Check https://epic.io or call 866-606-3742 to inquire about current rates.
  • Self-Hosted edition database(s) with any other hosting provider you choose such Microsoft Azure, AWS, or Rackspace. You will be responsible for supplying all necessary servers and licenses, and Exigo will bill you at 25 cents ($0.25) per GB of transfer/bandwidth.
  • Azure SQL Database & Azure SQL Managed Instance — Business Critical tier only

Because you have full control over your destination database, if you will create custom objects (tables outside of SyncSQL, views, functions, stored procs, jobs, etc.), please consider backing up the database so you do not lose these custom objects in the case of a failure.

Exigo also offers a Database Managed Services solution, where our expert team of Database Administrators can help maintain your destination SQL Servers. Everything from monitoring to performance tuning your code, DMS can be as hands on as you require. To inquire about this service, please open a ticket.

Requirements

  1. Microsoft SQL Server 2019 Enterprise edition or higher (sandboxes can run Developer edition)
  2. Database Collation must be set to SQL_Latin1_General_CP1_CI_AS (no other collations are currently supported)
  3. Enable "Allow Snapshot Isolation"
  4. Enable "Is Read Committed Snapshot On"
  5. Create a Filegroup under MEMORY OPTIMIZED DATA.
  6. Create a File of Filestream Type under the Filegroup created on previous step.
  7. Execute the following command to complete the setup for Memory Optimized tables:
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
  1. We recommend setting MAXDOP to 1 at the database configuration level
  2. Create a SQL user called "ExigoSync" that has db_owner privileges on the sync database to be used for the sync connection
  3. Run the SQL Server Service as a service account (can be local or AD, but not Local System or equivalent) and add the service account to the following rules in the Local Security Policy of the OS:
    • Lock Pages In Memory
    • Perform Volume Maintenance

  4. Whitelist the following IP addresses for Exigo's Sync Service to function properly:

    199.83.113.102
108.141.128.128
172.202.161.223
172.202.208.83
52.160.87.59
4.151.217.75
13.84.218.218
13.85.28.253
23.102.183.16
40.84.158.115
51.136.35.109
52.171.209.10
57.151.59.1
20.194.5.254
20.249.186.136
208.90.224.3
208.90.227.149
208.90.227.21
208.90.227.30
208.90.227.31
208.90.227.91
208.90.227.92
208.90.227.105
208.90.227.106
208.90.224.10

* We do not support adding any constraints on this database. If you add constraints, it will cause problems with the sync process. This will include defaults, custom triggers, etc.

** SQL Server Replication is not supported, however Always On Availability Groups are.

*** In order to add a sync connection to a sandbox, the sandbox must be online.

Minimum baselines

We suggest the following minimum baselines if you will host your SQL Instance over a Server/Virtual Machine.

Storage

Set Block Size Configuration for Data, Log & TempDB Drives to 64K:

  • System Drive — 150 GBs
  • Data Drive
  • Log Drive
  • TempDB Drive

Size of Data, Log and TempDB Drive will vary for each client.

Client tiers

Commission Lite, Standard, Nano, Express

Phase Memory RAM CPUs
Implementations 16 GBs 2
Maintenance/Production Active 32 GBs 4

Enterprise 1–10

Phase Memory RAM CPUs
Implementations 16 GBs 2
Maintenance/Production Active 64 GBs 4

Enterprise > 10

Phase Memory RAM CPUs
Implementations 32 GBs 4
Maintenance/Production Active 128 GBs 8

How to add a sync connection

  1. For production, login to https://www.exigo.com. For sandboxes, login to the corresponding sandbox (https://sandbox1.exigo.com...) that you would like to add the sync connection to.
  2. Navigate to Company Portal → Sync Management
  3. From here, you can add or edit your connection.
  4. Once you have added a new destination, make sure to enable it.