Data Replication

Data Replication

What is Data Replication?

Data Replication is a near real-time copy of the Resource or Contact Record data stored in the iCarol servers to an external local or cloud-based Microsoft SQL Server Instance. Using SQL Server transactional replication, as changes are made to the Resource or Contact Records in iCarol, the records are copied to the external servers within minutes. Data replication is available using the 'standard relational iCarol schema' (the same format used for your Data Export files) for Contact Records, Resources (Standard), and Resources (HSDS).
This feature may be useful for your organization if you wish to copy your Resource or Contact records and create any of the following outside your iCarol system:
  1. Resource Directories
  2. Statistical Reports
  3. Dashboards
  4. Any other integrations outside of iCarol

Setup Data Replication

First, your organization will need to set up a destination database for the replicated data. You will need to provide to iCarol:
  1. Destination Database Connection String, including the server address (public IP address), port, etc. of the server hosting the database
  2. The fully qualified domain name of the server
  3. Credentials to the server (username & password) - Please ensure the account created for iCarol has permission enabled to allow for the connection and writing to the database.
  4. Allow access through firewalls for iCarol to connect to your server, consider whitelisting the IP addresses listed in our "iCarol IP Addresses and Ports" Knowledge Base
Once you provide the necessary details, iCarol will attempt to connect to your server. Once a successful connection is made, we will replicate tables then data into those tables.


Note, for additional security replicated data can be configured to travel to your server using a Virtual Private Network (VPN)tunnel.

Install Encryption Key for Contact Record Replications

When Contact Record data is replicated to your server, some fields are encrypted in the database to help protect help-seeker personally identifiable (PI) information. Encrypted fields include names, phone numbers, addresses, and any free-text standard or custom Group/question added to the Contact Form Version. When Contact Record data is replicated, iCarol will share with your administrator an encryption key that can be used to decrypt your data on your server. This key will include a .dll.
Notes:
  1. Encryption keys are not needed or provided for Resource record Replications as Resource records are not encrypted.
  2. Free text Groups/questions entered into Contact Records prior to 2016 cannot be decrypted once Replicated. Once replicated, referrals, follow ups, and single/multiple choice answers can be used externally, while free text and personally identifiable information will remain encrypted.
  3. The SQL Server must support user-defined assemblies to allow for decryption.
    1. As of 2023-01-25 Azure Managed SQL Databases do not support user-defined assemblies, while records can be replicated to these database, values cannot be decrypted. We recommend that the customer confirm with your provider if Managed SQL Databases can support user-defined assemblies before enabling replication.
Here is an example of the steps needed to use the DLL file:
  1. Enable Common Language Runtime (CLR) integration.  Documentation here.
  2. Create an assembly statement.  Documentation here.
  3. Create a scalar-valued function (SVF).  Documentation here.
    1. Example:
    2. create function RetVal(@key nvarchar(max), @source nvarchar(max))
    3. returns nvarchar(max)
    4. as external name [iCarol.ClientDatabaseDecryption].[iCarol.ClientDatabaseDecryption.Client].Decrypt
  4. Execute the function.  
    1. Example:
    2. select top 1 CallerName, dbo.RetVal('<Your key>',CallerName) CallerName_Decrypt from iCarol_Rep.dbo.tReportActivities
Note, this feature may have an additional cost associated with it. If your organization is interested in enabling this feature, then a designated Support Contact from your organization can go to 'Help' in the left side menu and submit a case to request more information.

    • Related Articles

    • Data Confidentiality and Security

      Data Confidentiality All your sensitive caller and calls information which is typed into text boxes on the call report form and in caller profiles, is encrypted in our database. This way not even an iCarol staff member can read that information ...

    • Standard Data Export

      Export all your data inside iCarol using Standard Data Exports Admins and those users with special data export permission can export the following information from iCarol. Volunteer and Staff Caller Profiles Outbound Calls Chatboard Events News Sign ...

    • Scheduled Data Exports

      What are Scheduled Exports? When you subscribe to iCarol you have access to your raw data within several areas of iCarol by manually requesting a data export be created. This existing feature to manually request Data Exports takes effort, and ...

    • Training Webinar: Standard Data Exports Part 1

      This training will cover: This training is meant to be high-level overview How to export your data Export formats & delivery expectations How to grant non-Admin uses access to exports and how they will access the data Types of Exports Available

    • Understanding Caller Profile Data Export Files

      Understanding Caller Profile Data Export Files When you use the standard data export tool to extract your Caller Profile data. The data is exported into three (3) files, which each contain different information, as follows: Repeat Caller Profiles: ...