Database replication is an optional service provided by IDI Billing Solutions where a copy of the customer’s data is continually sent to a server at the customer site.
Once the inital snapshot of data has been established all subsequent updates and changes to the data in production are replicated to the copy.
What Data is Replicated
The data that is replicated includes information about:
customer accounts
financials
Follow-ow Ups/ Journals / Tickets
inventory
invoicing
orders
POS
Product Catalog
Not included in the data replication is data on:
Audit logs
Communications
Document Storage
Security
Workflow
Please Note
These lists are a high level summary of what is and what is not replicated. Individual data elements may not be included due to restrictions outlined in the section below “Why some data is not replicated”
Replicated Environments
By default data is only replicated from your IDI production environment. Requests to replicate non-production environments can be made however a separate service charge is applied for each environment that is replicated.
Customer Requirements
Database replication requires the customer to host a Microsoft SQL Server of their own to serve as the target for the replicated data (called a subscriber). Customers with existing SQL Servers in their network can create a replication target database as long as the security requirements detailed in this document are met.
Data changes are replicated to the customer’s subscriber database every 1 minute. Data Replication is always available except during scheduled maintenance activities.
Resumption of the service after a maintenance activity may require the initial snapshot of the data to be re-sent to the subscriber. Until this process is completed the data on the customer server may be unavailable.
Architecture
Configuration
IDI Billing Solutions will host Microsoft replication distributor and publisher services within the SaaS datacenter.
A non-updatable transactional publication will be created for the customer’s data. The database articles to be published will include all data tables except ones containing:
The customer must supply IDI Billing Solutions a valid Internet IP address for the destination server.
IDI will provide the customer with source IP addresses so that they may program their own firewalls to restrict access. It is highly recommended that the customer only allow inbound traffic into their network from the origination IP address supplied by IDI Billing Solutions.
Customer provides a Microsoft SQL Server running SQL Server 2017 or later. The server must be configured as follows:
Accept inbound traffic for TCP port 1433
Empty database created to be used as replication target.
SQL Security database login created with db_owner rights to the database and the VIEW SERVER STATE server permission. This login and password must be supplied to IDI Billing Solutions.
IDI Billing Solutions will configure a push subscription to the database designated by the customer.
The distribution agent will be scheduled to run every minute.
A VPN tunnel provided with the replication service and is required to ensure that the replicated data is encrypted in transit over the Internet. The VPN tunnel requirements are as follows:
Phase 1 VPN Mode: Tunnel Mode: Main Security Protocol: ESP Method: Pre-shared key Key TBD PFS DH Group: Group 2 Encryption: 3-DES-CBC HASH: SHA-1 Lifetime: 28800 sec | Phase 2 PFS DH Group: Group 2 Security Protocol: ESP Encryption: 3-DES-CBC Authentication: SHA-1 Lifetime: 3600 |
IDI only supports device to device tunnels. VPN tunnels needing a client application or install in the IDI datacenter cannot be used.
Replication Service Details
IDI Billing Solutions makes our best effort to have the replication service available and to respond to issues in a timely fashion as outlined in our Customer Support agreement. Please note however that IDI Service Level Agreements only apply to elements of the service which are controlled by IDI Billing Solutions. Your actual service experience will also depend on the availability and the maintenance of the subscriber system maintained by you.
Replication of changes from your IDI environment to your onsite database may be delayed due to situations like high transactions volumes, available bandwidth limitations, or software maintenance activities. Integrating replicated data into real-time business systems should be done with the understanding that the data may not be representing what is available in the IDI platform.
Support for database replication is provided during normal IDI Customer Support hours.
In order to prevent transaction delays it is advised to have a minimum of 5 MB of inbound Internet bandwidth available for replication.
The VPN Tunnel provided with the service provides security for your data as it is sent over the Internet. This tunnel is required to utilize the Data Replication service.
IDI Billing Solutions does not replicate any end-consumer data which is encrypted within the source datacenter database. This includes data such as credit card numbers, social security numbers, data of birth and credit scoring information.
Customer is required to have production level equipment for Internet routers, firewalls and servers at customer site that will be involved in replication.
Customer is required to provide a capable I.T. resource to work with IDI Billing Solutions during the implementation of the database replication and for any troubleshooting.
Any changes or maintenance to your network or infrastructure hosting the replication SQL Sever should be communicated to IDI before hand to ensure continuous availability of your service.
Certain conditions such as database upgrades or attempts to troubleshoot replication failures may require IDI Billing Solutions to reinitialize or rebuild the database publication and/or subscriptions. In these cases, a new database snapshot will have to be applied to the customer’s on-site subscription database. Applying the database snapshot may take many hours depending on the size of the database and the amount of bandwidth available. During this time, the customer will not be able to access the replicated data. IDI Billing Solutions will attempt to inform the customer before taking actions that may cause this situation.
Any hours spent by IDI Billing Solutions troubleshooting replication issues may be billable if the problem cause is determined to be the customer’s equipment or configuration.
Hardware Requirements for Replication Target Server
IDI Billing Solutions does not publish minimum hardware requirements for the customer’s onsite replication target server.
The minimum and recommended requirements for SQL Server are listed by Microsoft for each version of SQL Server.
It is difficult for IDI to provide any definitive guidance on how to size their replication SQL Server since each customer has their own requirements for how they plan to utilize the replicated data. The hardware requirements can vary greatly between customers.
If your goal for the replicated data is peace of mind and the occasional ad-hoc query then you could run SQL on a server with a single CPU (or vCPU) and 4 GB of memory.
If you plan is to have multiple people and systems running complex, processor intensive queries against the system requirements could scale to a server with 4 or more CPUs (or a single processor with 4 or more cores) and 16 GB of memory.
Based on what IDI has seen regarding how a typical customer uses the replicated data we can say a customer should order at least a 2 processor box with 8 GB of RAM, high-speed SCSI/SAS controller with 512 MB cache and enough disks to split up the OS, database, and transaction log files onto separate drives.
However, this configuration could be greatly over-powered, or under-powered, depending on your individual needs.
Dell and HP both have SQL Server sizing tools that can at assist customers in determining how much processing power and memory they may need. Disk space needed will at minimum be the size of their core plus any other databases (i.e. usage) they may put out there plus ample room to grow. These sizing tools are highly recommended and will typically start with questions regarding the sort of work loads you will be sending to the server.
Why some data is not replicated
IDI strives to replicate all customer data however in some instances it is not advisable or technically possible. Typical reasons why a particular set of data is not replicated could include:
The table or column contains sensitive data. This data is typically encrypted and cannot be read out of the database in a useful manner. In addition, some sensitive data IDI is prohibited from transmitting via replication due to regulations or partner agreements.
The data in the table/column is considered IDI system data, not customer data, and is only used by the system to perform specific functions.
Data is stored only in a temporary table and is removed when the process that utilizes is finished.
The data is stored in a large object data type (text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml) that is not supported by SQL replication. IDI does not use these data types for new development however older IDI platform functionality may still have these types in use.
The tables need to have a primary key in order to be replicated. If the primary key does not exist, the table cannot be replicated (this is typically only found in tables that contain internal application data like the second bullet point).
Data is contained in a data store that does not support data replication. Examples of this are Usage, Workflow, Security, and Communications.
Customer Monitoring of Replication Status (Optional)
Microsoft SQL Server replication does not provide any status indicators on the target subscription database server for the customer to view. Without this, it can be difficult to determine how old the replicated data is and when the last time updates were applied.
In cases where customers need to monitor replication status, IDI Billing Solutions, upon request, can add a process to the replication service to write a time stamp into the database every time replication runs. The time stamp will be replicated to the target database and can be used by the customer to verify the age of the data on their end.
The time stamp will have the following parameters:
Table Name: ReplicationTime
Column Name: CurrentDateTime
There will always be a single row in this column.