Skip to content

Stitch: Access to MS SQL Server Databases

Stitch loads smaller tables, mainly the source of Dim tables, from Backend-owned databases that are not publicly accessible. The Infra team is responsible for making any changes to the BE DBs.

Cheat sheet

Assumes databases are already set up (true for all databases in the Azure Rebtel tenant).

  1. Ask the Infra team to run the following, adjusting database and table names, as a reference for what the change needs:
use [your_database];
alter table [your_database].dbo.your_table_name enable change_tracking with (track_columns_updated = on);
grant select on [your_database].dbo.your_table_name to rebteldatateamsqlreader;
grant view change tracking on [your_database].dbo.your_table_name to rebteldatateamsqlreader;
  1. Add the configuration in Stitch

Full setup

Login and user (once per server)

One login is shared across all servers: rebteldatateamsqlreader, with credentials in AWS Secrets Manager.

create login rebteldatateamsqlreader with password = '<your_password>';

Per database:

create user rebteldatateamsqlreader
    for login rebteldatateamsqlreader
    with default_schema = db_datareader

Note

Stitch needs access to all databases on the server to work properly, so create the user in every database. The Stitch IPs for our account region are whitelisted on the Azure side for the connections to work.

Database

Compatibility level must be above 100 and change tracking enabled:

select * from sys.databases where name = '<database_name>';
alter database "<database_name>" set compatibility_level = 100;

select * from sys.change_tracking_databases;
alter database "<database_name>" set change_tracking = on (change_retention = 3 days, auto_cleanup = on);

Tables

select * from sys.change_tracking_tables;
alter table dbo.<table_name> enable change_tracking with (track_columns_updated = on);
grant view change tracking on dbo.<table_name> to rebteldatateamsqlreader;

Tip

For many tables, put one name per line in an editor with regex support. Search (\w+), replace with the two statements using $1. Only include tables that need changes.

Troubleshooting

Missing SELECT permission

Fatal Error Occured - The SELECT permission was denied on the object .... Grant it:

grant select on dbo.<table_name> to <stitch_user>;

Missing primary key

Log-based replication needs a primary key. Without one, use key-based incremental replication in Stitch Table Settings and set the key manually.