Microsoft SQL Server

How to set up the Gravity Microsoft SQL Server Destination

This Destination is currently in beta

Gravity does not currently support SQL Server database editions earlier than SQL Server 2017 (14.x) CTP 1.1

To load data into your Microsoft SQL Server 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

• The Microsoft SQL Server JDBC URL

• Allow Gravity's IPs access to your database through the database server's firewall

• A 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 Microsoft SQL Server and permission to create schemas and tables on your Microsoft SQL Server 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 you have 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?

You likely will have to build the URL yourself. You can find out more through Microsoft's official documentation but, we've included the important bits that Gravity needs below.

Because your database is remote to Gravity, you will need to know either its IP address (1.2.3.4) or hostname (myserver.mydomain.com). This is the serverName you will need to include in the URL.

Assuming your SQL Server is configured using defaults, the final URL should look similar to this, replacing placeholders surrounded by curly braces:jdbc:sqlserver://{serverName};databaseName={name};

Why do you need bulk operations permissions?

Gravity produces data files which are loaded into your Microsoft SQL Server 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.