Microsoft SQL Server (MSSQL)

Microsoft SQL Server or MSSQL, is a popular and widely used relational database management system (RDBMS). Developed by Microsoft, SQL Server offers robust data storage, management, and retrieval capabilities. It uses structured query language (SQL) as its primary language for interacting with the database.

Features

🔧 Setup Guide

Step 1 - Get credentials to setup the connection

In the Weld app, add a new Microsoft SQL Server connection and fill in:

• Name: the name you want to use for this connection in the Weld app
• Server: the hostname or IP address of your SQL Server
• Port: the TCP port SQL Server listens on, 1433 by default
• Database: the name of the database you want to sync from
• User and Password: a SQL Server login with read access to the tables you want to sync

If you are not sure of the server name, you can look it up by running the following in SQL Server Management Studio or any other client connected to your server:

SELECT @@SERVERNAME;
SELECT SERVERPROPERTY('MachineName') AS machine_name,
       SERVERPROPERTY('InstanceName') AS instance_name;

If you run a named instance, enter the host and the TCP port that instance listens on, since a named instance usually does not use 1433.

Create a read-only login for Weld

We recommend creating a dedicated read-only login for Weld rather than reusing an administrator account. Weld signs in with SQL Server authentication, so the server needs to allow SQL Server and Windows Authentication mode.

-- Run in the master database
CREATE LOGIN weld_reader WITH PASSWORD = '<a-strong-password>';

-- Run in the database you want to sync
USE <your_database>;
CREATE USER weld_reader FOR LOGIN weld_reader;
ALTER ROLE db_datareader ADD MEMBER weld_reader;

If you want Weld to read only specific tables rather than the whole database, grant SELECT on those tables instead of adding the user to db_datareader. Weld only lists the tables and views the login can read.

Encryption and certificates

Connections to SQL Server are encrypted, and Weld validates the certificate your server presents. If your server uses a self signed certificate, tick the option to implicitly trust connection certificates on the connection form.

SSH tunnel

If your server is not reachable from the internet, use the Connect through an SSH Tunnel option on the connection form and point it at a bastion host that can reach the database.

Step 2 - Data To Sync

1. Select the tables you wish to include in the sync.
   You can view the schema, remove columns or hash sensitive information.
2. Update Sync mode:
   By default the MSSQL connector is set to run always full syncs. To optimize the sync time and reduce processing overhead on your MSSQL server we recommend for you to set up the syncs to run incrementally.

We currently support both Merge and Append mode for incremental syncs.

Merge

To have your table running incrementally using the merge configuration you need a table primary key and a cursor timestamp (updated_at is preferred).

When a sync is run, Weld will select only the new or changed rows since the last update.

Append

If a row updated_at timestamp is not available on the table then another option is to run an incremental sync using append mode. Append mode uses cursor to keep track of how far the sync got on the last run. It will use that cursor to append new entries at the end of the table on the next run.

Append is not widely used as it does not capture updates in the previous rows.

Full Sync at Midnight

A limitation of both Merge and Append modes is that they do not capture deleted rows. If you expect rows to be deleted often from your tables then you can set a table to run a full sync at midnight which will remove those deleted rows.

Step 3 - Configure Sync

1. Select how often you would like the data to sync.
2. Provide a unique destination table name.

Weld will take over from here and commence syncing data from your MSSQL database.