Microsoft SQL Server (MSSQL)

warning

This is an article from the Shaped 1.0 documentation. The APIs have changed and information may be outdated. Go to Shaped 2.0 docs

Preparation

To allow Shaped to connect to your MSSQL database, you need to create a read-only user and share those credentials through the Create Dataset endpoint.

You can create a read-only user on a MSSQL database with the following commands:

-- 1. Create a new login (server-level).
CREATE LOGIN [username] WITH PASSWORD = '[password]';

-- 2. Create a user (database-level) and map it to the login.
USE [database_name];
CREATE USER [username] FOR LOGIN [username];

-- 3. Grant read-only access by adding the user to the db_datareader role.
ALTER ROLE db_datareader ADD MEMBER [username];

-- 4. (Optional) Restrict access to specific tables instead of granting db_datareader role.
GRANT SELECT ON [schema].[table_name] TO [username];

Dataset Configuration

Required fields

Field	Example	Description
schema_type	MSSQL	Specifies the connector schema type, in this case "MSSQL".
table	movies	The name of the table to sync.
user	your_user	Access account username.
password	pAssw0rd1!	Access account Password.
host	my-mssql-db.xxxxxxx.us-east-2.rds.amazonaws.com	Database hostname.
port	1433	Database port (the default for MSSQL is 1433).
database	movielens	The name of the database that contains table to sync.
replication_key	updated_at	The name of the column that contains a datetime key or ascending id for ordering data during incremental syncs.

Optional fields

Field	Example	Description
columns	["productId", "color", "brand", "stockLevel"]	The name of the columns you wish to sync from MSSQL into Shaped. If not specified, all columns will be synced.
unique_keys	["productId"]	Specify a list of columns that uniquely identify a row in the table, if duplicate rows are inserted with these keys, the latest row will be used.
batch_size	100000	The number of rows to fetch from the database in each batch, changing this can improve throughput for large tables. The default is 10000.

Dataset Creation Example

Below is an example of a MSSQL dataset connector configuration:

name: your_mssql_dataset
schema_type: MSSQL
table: movies
user: your_user
password: pAssw0rd1!
host: my-mssql-db.xxxxxxx.us-east-2.rds.amazonaws.com
port: 3306
database: movielens
replication_key: updated_at

The following payload will create a MSSQL dataset and begin syncing data from Shaped using the Shaped CLI.

shaped create-dataset --file dataset.yaml