Mssql Setup Guide

Prerequisites

 

We use two of the SQL server's built-in tracking mechanisms CDC (Change Data Capture) and CT (Change Tracking) to replicate your new and changed data. These mechanisms automatically detect data changes and allow us to sync them into your destination.

 

Configure your access control systems to allow inbound traffic from the following IPs: 23.21.86.124/32 and 52.20.96.22/32

 

Create a dedicated Matia user, who must be a SQL database user:

   USE [<database>];
   CREATE LOGIN <matia_user> WITH PASSWORD = '<password>';
   CREATE USER <matia_user> FOR LOGIN <matia_user>;

 

To use the SQL built-in mechanisms, the Matia user needs permission to access the databases, schemas and tables.

 

You can grant access to everything in a given database:

   GRANT SELECT on DATABASE::<database> to <matia_user>;

Or to all tables in a given schema:

   GRANT SELECT on SCHEMA::<schema> to <matia_user>;

Or to a specific table:

   GRANT SELECT ON [&lt;schema&gt;].[<table>] TO &lt;matia_user&gt;;

Enable CT (Change Tracking)

Enable change tracking at the database level:

   ALTER DATABASE [<database>] SET CHANGE_TRACKING = ON;

For enabling CDC on an RDS DB instance:

   EXEC msdb.dbo.rds_cdc_enable_db '<database name>'

Enable CT for each table you want to replicate:

   ALTER TABLE [&lt;schema&gt;].[<table>] ENABLE CHANGE_TRACKING;

Grant VIEW CHANGE TRACKING permissions for each of the tables that have CT enabled:

   GRANT VIEW CHANGE TRACKING ON [&lt;schema&gt;].[<table>] TO &lt;matia_user&gt;;

Enable CDC (Change Data Capture)

Enable change data capture at the database level:

   Enable USE [<database>];
   EXEC sys.sp_cdc_enable_db;

Note if your are using Amazon RDS for SQL Server:

   EXEC msdb.dbo.rds_cdc_enable_db '<database name>'

Enable CDC for each table you want to replicate:

   Enable USE [<database>];
   EXEC sys.sp_cdc_enable_table
   @source_schema           = N'<source_schema>',
   @source_name             = N'<source_name>',
   @role_name               = N'<role name>'

Ensure the SQL Server Agent is running

• MSSQL relies on the SQL Server Agent to execute the essential jobs required for CDC. Ensuring that the Agent is running is essential for CDC to function effectively. You can verify the SQL Server Agent's status using the following:

   EXEC xp_servicecontrol 'QueryState', N'SQLServerAGENT';

• If the status is anything other than 'Running,' please refer to the Microsoft documentation to start the service.

Enable Cursor Base

• When creating an integration select the Cursor Base option in Incremental sync mode dropdown.
• Choose the desired cursor field in the schema tab before running the integration

Setup Guide

1. Fill in the Username, Password, Hostname, Port and Database for your Microsoft SQL Server database.
2. Enter a Asset Name for the connector.
3. (Optional) Enter a Description for the connector.
4. Select the Owner of the connector.
5. (Optional) Verify that your Microsoft SQL Server database is successfully connected by clicking on Test Connection.
6. Click Connect.

Supported Sync Modes

• Full Refresh
• Incremental (Using CDC and CT Or Cursor Base)

ℹ️ A table must have a primary key to use Incremental mode.

Supported MSSQL Data Type Mapping

MSSQL	Destination
int	integer
bigint	bigint
smallint	smallint
tinyint	smallint
numeric	numeric
decimal	numeric
bit	boolean
money	numeric
smallmoney	numeric
float	double precision
real	real
date	date
datetime	timestamp
datetime2	timestamp
datetimeoffset	timestamp with time zone
smalldatetime	timestamp
time	time
char	char
varchar	varchar
text	varchar(max)
nchar	char
nvarchar	varchar
ntext	varchar(max)
binary	bytea
varbinary	bytea
image	bytea
uniqueidentifier	uuid
xml	xml
cursor	Not directly supported
hierarchyid	Not directly supported
rowversion	Not directly supported
sql_variant	Not directly supported
geometry	Not directly supported
geography	Not directly supported