Azure SQL

How to set up the Gravity Azure SQL Destination

This Destination is currently in beta

To load data into your Azure SQL database in the best (fast, and in the background) way possible, you will need a few things.

What you'll need

• An Azure Storage account and Storage account URL with:

  • (Recommended) a separate container for Gravity to use

  • A shared access signature with List, Read, Write and Create permissions

Gravity does not currently support Azure Data Lake Storage Gen2 due to known limitations

• An Azure SQL Database and JDBC URL

• To add Gravity's IPs to your Azure SQL IP firewall rules

• An Azure SQL Database user and password with:

  • (Recommended) a separate database for Gravity to use

  • Permissions to ADMINISTER DATABASE BULK OPERATIONS and either OWNERSHIP of the database or permissions to CREATE TABLE, CREATE SCHEMA, DROP TABLE, SELECT, INSERT, UPDATE, DELETE on the database

• An external data source pointing to your Azure Storage account which requires:

  • A master key on the database

  • A database scoped credential for the shared access signature

Steps

If you don't have an Azure Storage account, container or shared access signature, please follow our Azure Storage guide

1. Log in to the database with an admin user through SQL Server Management Studio (SSMS) or Azure Data Studio

2. In the following T-SQL, replace any placeholders surrounded by curly braces { ... }

3. Execute the script

The T-SQL script assumes you haven't already created these database objects for Gravity to use. If you have, amend the script as required. Some placeholders may not be self-explanatory. They are:

• {signature} this is the shared access signature you should have created for the Azure Storage account earlier on. It may start with a question mark ?, if so, please remove it

• {url} this is the URL that points to the Azure Storage account and container to use. It looks like this: https://{storageaccount}.blob.core.windows.net/{container}

USE DATABASE {database};

-- Create a database user and grant permissions
CREATE LOGIN {login} WITH PASSWORD '{password1}';
CREATE USER {user} FROM LOGIN {login};

ALTER AUTHORIZATION DATABASE::{database} TO {user};
GRANT ADMINISTER DATABASE BULK OPERATIONS TO {user};

-- Create an external data source
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '{password2}';

CREATE DATABASE SCOPED CREDENTIAL {credential} 
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', 
SECRET = '{signature}';

CREATE EXTERNAL DATA SOURCE {source} 
WITH ( LOCATION = '{url}', CREDENTIAL = {credential}, TYPE = BLOB_STORAGE);

Complete Gravity setup

Enter the required settings:

1. The Storage account URL pointing to your Storage account and container. It's the same as the {url} you used when creating the external data source from the SQL script earlier

2. A Shared Access Signature key with the necessary permissions. It may start with a question mark ?, if so, don't remove it

3. The name of the External Data Source you created earlier on. It's the {source} from the earlier SQL script

4. Under Connection parameters, the JDBC URL of the Azure SQL Database. If you're not sure what it is, see How do I find the JDBC URL?

5. The username and password of the database user Gravity can use. If you create a user and password using the SQL script from earlier, it's the {user} and {password1}

6. Click Save -> Test to validate your setup

   • Test checks connectivity to Azure SQL and permission to create schemas and tables on your Azure SQL database.

Frequently asked questions

Why is an Azure Storage account required?

Before data can be loaded into your Azure SQL database by a third party - such as Gravity - it has to be stored somewhere that the database can access. Azure Storage is a convenient and secure storage option for that purpose. It also means your data does not reside on our systems after your Jobs have completed.

Why is a separate container recommended?

Because it logically separates the data (blobs) your Jobs in Gravity create from the rest of your data stored in the same account. It also reduces the chance of data being overwritten accidentally and makes it easier to monitor Gravity's usage of your storage.

How do I find the JDBC URL?

One of the easiest ways is by logging in to https://portal.azure.com, go to SQL databases and click on your Azure SQL database instance. In the left-side menu, go to Settings -> Connection strings and click on the tab JDBC. The URL should start with jdbc:sqlserver//.

Why do you need bulk operations permissions?

Gravity produces data files which are loaded into your Azure SQL database. It does that using the T-SQL command BULK INSERT and it's this that requires bulk operations permissions.

This is different to the T-SQL INSERT command you may be more familiar with. That's because the data's in a file. Compared to an INSERT statement, we can load much larger volumes of data faster and more efficiently this way.