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
   (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);

Enable CT for each table you want to replicate:

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

If you need changed-column metadata, you can enable:

   ALTER TABLE [&lt;schema&gt;].[<table>] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);

Grant VIEW CHANGE TRACKING permissions for each table that has 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
  • Change Data Capture (CDC)
  • Change Tracking (CT)
  • Cursor Based

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

CT retention behavior

CT checkpoints are stored as table sync versions. If a saved version falls behind CHANGE_TRACKING_MIN_VALID_VERSION(...) due to retention cleanup, Matia automatically falls back to a full refresh for that table in the next run.

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