Introduction to Stretch Databases with SQL Server 2016

Its been a long time since I have written a blog and what better way to start again than by writing about one of the most interesting features being introduced in SQL Server. In a series of blogs over the next few weeks, I will try and get into the details of how Stretch Databases are implemented and some of the inner workings of the feature to watch out for.

Before, we delve into the feature its important to understand that SQL 2016 is still in the CTP phase and some of the things can change once the product RTM’s. I will blog about the changes if any in another post when it happens.

Ok, so without wasting any more time, Stretch Databases, as the name suggests, stretches your databases/tables to a remote storage, while masking the implementation details from the end user. You continue to access the table in the same way as before , while SQL Server internally traverses the local and the remote storage to get the requested data set. In the current release, the Stretch option moves the entire table to the remote storage, which is a V12 Azure SQL Database (Standard S3 Tier).

Use cases for Stretch Databases

Stretch Databases are useful in scenarios where there is a ton of transactional data, which needs to be stored in your environment for historical querying or maybe government regulations. You only query/work with a small subset of data in these tables (mostly latest transaction data) on a frequent basis. Even with the modern day archiving, manageability and compression techniques (like ColumnStore Indexes, partitioning, using combination of tables/views) the storage cost or the man hours required to update/change your application are prohibitively high, when compared to the Azure Storage utilized by the Azure SQL Database.

Implementing Stretch Databases

Implementing Stretch Databases is really simple. In order to enable a database for Stretch, the ‘Remote Data Archive’ configuration option needs to be set. This can be done using the sp_configure command

1: sp_configure 'remote data archive', 1

2: Reconfigure

Next, right click on the DB, on which stretch needs to be enabled, go to task->‘Enable Database for Stretch’. This would launch the wizard for stretch configuration. Sign in with your Azure credentials

The window let’s you choose the subscription in case there are more than one. The next screen allows you to select the Stretch Settings (essentially the location of the Azure SQL Database, the admin login for the WASD server and setting the IP exception rule for your current SQL Server IP).

Once you have made sure the settings are current and the click on finish, the following steps are executed on the SQL Server and the Azure SQL Database.

Provision a New SQL Azure Database Server (fixed naming convention)

Configure the firewall exceptions for the WASD server

Create a Credential on the current SQL Environment for the WASD

Create a linked Server to the Azure SQL Database Server

Create the SQL Azure Database.

Stretch DB creation creates a log in the “\Users\<current_user>\AppData\Local\SQL Server\Stretch Database to SQL Azure” folder, which can be used for troubleshooting purposes.

Once the option is enabled for the database, the following entries are logged in the SQL Error Log.