Microsoft SQL Server

Microsoft SQL Server is a popular relational database management system (RDBMS). ideal for a variety of data workloads - from small-scale, single-machine data applications to large data applications with thousands of concurrent users.

Refer to the Warehouse Schema guide for more information on how the events are mapped to the tables in SQL Server.

Find the open source code for this destination in the GitHub repository.

Setting user permissions in SQL Server

After setting up your SQL Server database, create a user with the necessary privileges to create schemas and temporary tables in this database.

To create a SQL Server instance on Docker, run the following commands:

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Test@123" -p 1433:1433 --name rudder_mssql -h rudder_mssql -d mcr.microsoft.com/mssql/server:2019-latest

docker exec -it rudder_mssql "bash" /opt/mssql-tools/bin/sqlcmd -S localhost -U SA [-P "Test@123"]

In the above commands, the user is SA (System Admin) and password is Test@123.

The following queries let you create a user and grant the above-mentioned privileges (creating schemas and temporary tables on the database) to that user:

CREATE LOGIN testuser WITH PASSWORD = 'Test@123';
CREATE USER testuser FOR LOGIN testuser ;

CREATE DATABASE test_db
USE test_db
-- GRANT individual permissions like this or
GRANT CREATE TABLE TO testuser
-- Provides owner permissions to user
EXEC sp_addrolemember N'db_owner', N'testuser'

Using AWS RDS instance

You can execute the same commands via Azure Studio or using the sqlcmd utility (cmdline sqlcmd).

Configuring SQL Server destination in RudderStack

To send event data to SQL Server, you first need to add it as a destination in RudderStack and connect it to your data source. Once the destination is enabled, events will automatically start flowing to SQL Server via RudderStack.

To configure SQL Server as a destination in RudderStack, follow these steps:

1. In your RudderStack dashboard, set up the data source. Then, select Microsoft SQL Server from the list of destinations.
2. Assign a name to your destination and then click Next.

Connection settings

• Host: The host name of your SQL Server service.
• Database: The database name in your SQL Server instance where the data will be sent.
• User: The name of the user with the required read/write access to the above database.
• Password: The password for the above user.
• Port: The port number associated with the SQL Server database instance.
• Namespace: Enter the schema name where RudderStack will create all tables. If you don’t specify any namespace, RudderStack will set this to the source name, by default.

You cannot change the namespace later.

• SSL Mode: Choose the SSL mode through which RudderStack will connect to your SQL Server instance. RudderStack provides three options - disable, true, and false.
• Sync Frequency: Specify how often RudderStack should sync the data to your SQL Server database.- Sync Starting At: This optional setting lets you specify the particular time of the day (in UTC) when you want RudderStack to sync the data to the warehouse.
• Exclude Window: This optional setting lets you set a time window when RudderStack will not sync the data to your database.

Advanced settings

• Warehouse Append: This setting is turned on by default - RudderStack appends your incoming Event Stream data to the existing data in your warehouse. Turning it off causes RudderStack to merge your incoming data into your warehouse to ensure 100% non-duplicate data.

The append operation helps to achieve faster data syncs while reducing warehouse costs. However, note that it may increase the number of duplicates in the warehouse, especially if the existing data is older than two weeks. A common scenario where duplication might occur is when the SDKs retry sending events in case of failures.

A merge strategy ensures deduplication but can lead to longer sync times and increased warehouse costs.

• Skip User Table: Toggle on this setting to send events exclusively to the identifies table and skip the users table, eliminating the need for a merge operation on the users table.
• Skip Tracks Table: Toggle on this setting to skip sending events to the tracks table.

Configuring the object storage

RudderStack lets you configure the following object storage configuration settings while setting up your Microsoft SQL Server destination:

• Use RudderStack-managed object storage: Enable this setting to use RudderStack-managed buckets for object storage.

This option is applicable only for RudderStack-hosted data planes. For self-hosted data planes, you will have to specify your own object storage configuration settings.

• Choose your storage provider: If Use RudderStack-managed object storage is disabled in the dashboard, select the cloud provider for your object storage and enter the relevant settings:

  • Amazon S3 bucket storage settings
  • Azure Blob Storage settings
  • Google Cloud Storage bucket settings
  • MinIO bucket storage settings

See How RudderStack stores data in an object storage platform for more information.

IPs to be allowlisted

To enable network access to RudderStack, allowlist the following RudderStack IPs depending on your region and RudderStack Cloud plan:

All the outbound traffic is routed through these RudderStack IPs.

FAQ

How does RudderStack handle cases when loading the data into SQL Server?

RudderStack converts the event keys into lower case before exporting the data into SQL Server, so that it does not create two tables if the event name has two different cases.

How are reserved words handled by RudderStack?

There are some limitations when it comes to using reserved words in a schema, table, or column names. If such words are used as event names, traits or properties, they will be prefixed with a _ when RudderStack creates tables or columns for them in your schema.

Also, it is important to note that integers are not allowed at the start of the schema or table name. Hence, RudderStack prefixes such schema, column or table names with a _.

For instance, '25dollarpurchase' will be changed by RudderStack to '_25dollarpurchase'.

What are the SSL mode options provided by RudderStack?

While setting up the SQL Server destination, RudderStack provides the following three SSL options:

• disable: The data sent from RudderStack to your database is not encrypted.
• false: The data sent from RudderStack to your database is not encrypted beyond the login packet.
• true: The data sent from RudderStack to your database is encrypted.

For a more comprehensive FAQ list, refer to the Warehouse FAQ guide.

Questions? Contact us by email or on Slack