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.
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 "[email protected]" -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 "[email protected]"]
where the user is
SA (System Admin) and password is
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 = '[email protected]';CREATE USER testuser FOR LOGIN testuser ;CREATE DATABASE test_dbUSE test_db//GRANT individual permissions like this orGRANT CREATE TABLE TO testuser//Provides owner permissions to userEXEC sp_addrolemember N'db_owner', N'testuser'
You can execute the same commands above via Azure Studio or using cmdline sqlcmd.
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.
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.
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
'25dollarpurchase' will be changed by RudderStack to
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.