Azure SQL Data Warehouse Destination
Azure’s SQL Data Warehouse is a fast, flexible, and secure analytics platform for the enterprise.
There are four main steps to get started with Azure SQL Data Warehouse and Segment:
- Have an Azure subscription
- Provision SQL Data Warehouse
- Give Segment access to your SQL Data Warehouse
- Configure the Destination in Segment
Please follow the links above to follow Azure’s documentation on setting up these prerequisites. Following that, the guide below should take you through the rest.
Give Segment access to your SQL Data Warehouse
First, create a server login for Segment to use. This can be accomplished by running the following SQL command on your SQL Server’s
CREATE LOGIN Segment WITH PASSWORD = '<strong password>';
Once this is done, no more action is needed for the
master database. Next, connect to your SQL Data Warehouse’s database in order to do some further configuration.
Segment uses Azure Blob Storage to hold data that is being loaded into Azure SQL Data Warehouse. In order to facilitate this, a
MASTER KEY is needed in order for credentials that Segment saves to the database to be encrypted.
CREATE MASTER KEY;
NOTE: If you are using your SQL Data Warehouse for things besides Segment, it is possible this is already done. Either way, running the command another time will not hurt anything.
Next, create a new database user using the server login that was created previously:
CREATE USER Segment FOR LOGIN Segment;
This new user will need permissions to load data, and manage the resources it needs. Run the following command to accomplish this:
GRANT CONTROL TO Segment;
Lastly, assign this new user a resource allocation class. The default (
smallrc) likely will not give Segment enough memory to perform bulk loads, so we recommend starting with
largerc. The larger “Dynamic Resource Classes” give more memory, while allowing fewer concurrent queries, which is a better fit for Segment’s loading strategy:
EXEC sp_addrolemember 'largerc', 'Segment';
By default, Azure SQL Data Warehouse cannot be connected to from the public internet. In order for Segment to connect, a server-level firewall rule that allows connections from the Segment IPs is needed.
Configure an Azure SQL Data Warehouse Destination in Segment
In order to set up the necessary destination in Segment, you’ll need the following pieces of information:
- Server Name: the name of the SQL Server resource that houses your SQL Data Warehouse
- Database: the name of the SQL Data Warehouse database resource
- Username: the name of the user you created above
- Password: the password of the user you created above
All of these fields are required in order for Segment to load data into your SQL Data Warehouse. The username and password can be obtained during steps of the previous section, while the server and database names can be found in the Azure Portal.
Making sure Segment has enough resources to load your data
The default resource allocation class (ie:
smallrc) likely will not give Segment enough memory to perform bulk loads, so we recommend using a larger class (eg:
largerc). Larger classes allocate more memory, but limit the number of concurrent queries, which is a better fit for Segment’s loading strategy.
Segment is not able to connect to my Azure SQL Data Warehouse
Questions? Problems? Need more info? Contact us, and we can help!