Skip to content

Latest commit

 

History

History
104 lines (66 loc) · 5.48 KB

microsoft-sql-server.md

File metadata and controls

104 lines (66 loc) · 5.48 KB
description
Step-by-step guide to set up SQL Server as a destination in RudderStack

Microsoft SQL Server

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

RudderStack allows you to configure SQL Server as a destination to which you can send your event data seamlessly.

{% hint style="info" %} Please check our Warehouse Schemas guide to know how events are mapped to the tables in PostgreSQL. {% endhint %}

{% hint style="success" %} Find the open-source transformer code for this destination in our GitHub repo. {% endhint %}

Setting Up a Microsoft SQL Server User

After setting up your MS SQL Server database, the next step is to create a user with the required privileges in order to create schemas and temporary tables on this database.

Run the following commands to create a SQL Server instance on Docker:

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"]

where the user is SA (System Admin) and password is Test@123.

The SQL queries below let you create a user and assign the above-mentioned privileges to that user:

Below are the SQL queries that let you create a user and grant the above-mentioned privileges (creating schemas and temporary tables on the database) to that created 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 above via Azure Studio or using cmdline sqlcmd.

Configuring SQL Server in RudderStack

In order to enable sending data to SQL Server, you will first need to add it as a destination on the RudderStack dashboard and connect it to your data source. Once the destination is enabled, the tracked source data will automatically start flowing to SQL Server via RudderStack.

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

  • Configure your data source on RudderStack if you haven't already. You can also simply create a destination and connect it to a source later.

{% hint style="success" %} Please follow our Adding a Source and Destination guide to know how to add a source in RudderStack. {% endhint %}

  • After choosing a source, click on the Add Destination button and select SQL Server from the list of destinations.
  • Assign a name to your destination and then click on Next. You should then see the following screen:

  • Enter the following credentials in the Connection Settings:
    • Host - The host name of your SQL Server service.
    • Database - The database name in your SQL Server instance where the data is to be sent.
    • User - The username which has the required read/write access to the above database.
    • Port - Enter the port number associated with the SQL Server database instance.
    • Password - The password for the above user.
    • SSL Mode - Enter the SSL modes for connecting to your SQL Server instance.
    • Bucket Provider - Select the intermediate storage for storing the staging files. RudderStack currently supports Amazon S3, MinIO, Google Cloud Storage, and Azure Blob Storage.

{% hint style="success" %} You can also set the Sync Frequency to set the frequency of the data import into your SQL Server database. Optionally, you can also specify the time when you want this synchronization to start, by choosing the time under the Sync Starting At option. {% endhint %}

FAQs

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'.

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.

Contact Us

If you come across any issues while configuring SQL Server with RudderStack, please feel free to contact us or start a conversation on our Slack channel. We will be happy to help you.