Documentation

Setting Up Change Tracking Replication for SQL Server

Note

All SQL Server tables you want to sync must have a primary key defined.

To configure Change Tracking replication on SQL Server you need to:

Step 1 - Create a user

Connect to your SQL Server database as the admin and create a user for Alooma. The name of the user you create can be anything. In this example, "your_db" is the database you're syncing and "alooma" is the user being created (don't forget to provide a secure password):

USE your_db;
CREATE LOGIN alooma WITH PASSWORD = 'my_secure_password';
CREATE USER alooma FOR LOGIN alooma;
Step 2 - Configure SELECT permissions

All of the objects being synced with Alooma require SELECT permission. Depending on the objects you want to sync, you will need to give your new user SELECT access to one or more of the following: database, schema, table, or column. For example, to grant the alooma user SELECT access to the entire your_db database, run this command:

GRANT SELECT on DATABASE::your_db to alooma;

You may want to grant SELECT permission to a subset of a database depending on what you're syncing with Alooma. See the Permissions section of the SQL Server documentation for more information about configuring permissions.

Step 3 - Set database compatibility

Check to see if the database compatibility for your database is greater than 90:

USE your_db;
GO
SELECT compatibility_level
FROM sys.databases WHERE name = 'your_db';
GO

If the value returned is less than 90, increase it:

To set the level:

ALTER DATABASE your_db
SET COMPATIBILITY_LEVEL = 100;
GO

For more information about database compatibility, see the SQL Server documentation.

Step 4 - Enable Change Tracking

Enable change tracking so Alooma can avoid having to sync an entire table each time a change is made and instead just update the changed rows.

To properly configure Change Tracking, you'll need to first enable it at the database level, then enable it for each table you're syncing, and finally you'll need to grant VIEW CHANGE TRACKING permission for your user for each of those tables.

First, enable change tracking at the database level:

ALTER DATABASE your_db SET CHANGE_TRACKING = ON;

Next, you need to enable it for each table you are syncing:

ALTER TABLE your_schema.your_table ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON) 

And finally, for each of the tables you're tracking grant VIEW CHANGE TRACKING permission for your user:

GRANT VIEW CHANGE TRACKING ON your_schema.your_table TO alooma;

That's it, now you're ready to configure a SQL Server input!

Search results

    No results found