Stretching SQL Server 2016 Tables To The Azure Cloud

Introduction

Microsoft SQL Server 2016 is a hybrid cloud environment designed to allow data and services to enable users to perform advanced analytics within their databases and create business insight visualizations.

One of the highly anticipated features new to Microsoft SQL Server 2016 is Stretch DB, a feature that migrates your historical data transparently and securely to the Microsoft Azure cloud.

Moreover, it can be configured to migrate a complete table to the cloud, or to migrate only part of a table based on certain condition. For example, one won’t migrate its complete transaction table, but migrating the data which is older than five years makes sense!

Benefits of Stretch DB

Ability to determine which rows to migrate to the cloud and which rows to keep On-Premise by using predicates.
Stretch DB ensures that no data is lost if a failure occurs during migration. – It also has retry logic to handle connection issues that may occur during migration.
Data Migration can be paused to troubleshoot problems on the local server or to maximize the available network bandwidth.
Existing Applications does not have to be changed. The change will be done seamlessly.

Source: http://wikidba.net/

Is Stretch DB for you?

Stretch Database targets transactional databases with large amounts of historical data, typically stored in a small number of tables. These tables may contain more than a billion rows. Some cases where Stretch DB can be used are:

Transaction data is stored for a long time.
Historical Data is queried occasionally.
The size of tables are exponentially increasing and needs more storage.

To identify databases and tables that can be “stretched,” Use Stretch Database Advisor, a feature of SQL Server 2016 Upgrade Advisor.

Stretch DB Advisor
Download and install Upgrade Advisor from here. This tool is not included on the SQL Server 2016 Release Candidate (RC0) installation media.

To verify if a database and table is eligible for Stretch DB, open upgrade advisor and click on “Run Stretch Database Advisor.”

Select the server where the Database resides.
Select the Database to be stretched.
Select the tables to stretch and view the results.

Configure and Use Stretch DB

Enable Stretch DB at server level

Before a Database can be stretched, Stretch DB needs to be configured at the server level.

EXECsp_configure’remote data archive’,’1′;  
GO  
RECONFIGURE;  
GO  

This operation requires sysadmin or serveradmin permissions.

Enable Stretch Database on a database

The easiest way to get started is to use the wizard.

Go to Tasks, Stretch, then click Enable.
This will lead to a Stretch DB Intro Page.
Then, select the tables that needs to be stretched.
Sign in to your Microsoft Azure Account.
Once signed-In, you may create a new server or connect to an existing SQL Server.
Create a Master Key.
The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database.
The wizard will then create the server and perform the configurations to stretch the Database and the required tables.

Monitor the Data Migration

To monitor the Data Migration progress, at the database level, go to Tasks, Stretch, then click Monitor.

The previous operation would migrate all the data to the cloud.

Query & Verify the Execution Plan
After a few mins, when querying the table transactions, all the data will be fetched from the cloud and no predicate was specified when enabling stretch on the table.

Migrate only Specific rows
Bring the data from the cloud back to on premise

To migrate only specific rows, start by first bringing all the data from the cloud to the local database.

To do so, click on Stretch, Disable, then click “Bring data back from Azure”.

All the records would be migrated back to on-premise gradually.

Define the filtering function

The inline table-valued function required for a Stretch Database filter function looks like the following example.

CREATE FUNCTION dbo.fn_stretchpredicate(@column1 datetime)  
RETURNS TABLE  
WITH SCHEMA BINDING  
AS  
RETURN SELECT 1 ASis_eligible  
WHERE @column1 <CONVERT(datetime,’4/1/2016′, 101)  
GO  

The parameters for the function have to be identifiers for columns from the table. Schema binding is required to prevent columns that are used by the filter predicate from being dropped or altered.

If the function returns a non-empty result, the row is eligible to be migrated; otherwise – that is, if the function doesn’t return any rows – the row is not eligible to be migrated.

Alter the table

The next step is to alter the table and set remote_archive on while specifying the predicate.

ALTER TABLE [dbo].[TRANSACTIONS]  
SET ( REMOTE_DATA_ARCHIVE =ON (  
FILTER_PREDICATE =dbo.fn_stretchpredicate(DATE),  
MIGRATION_STATE =OUTBOUND ));    

Monitor Migration

There are 67620 records which are dated before 4/1/2016 which should be migrated to the cloud.

Query & Verify Execution Plan

i. Select data where Date < 4/1/2016, as expected, data will be fetched from the cloud.

ii. Select data where product if = 4139. This is a more interesting scenario, where both the On-Premise and Cloud data is queried and the result is merged and returned to the user.

Limitations of Stretch DB

The development of Stretch DB is still in progress and at the time of writing, there are lots of properties that are not yet supported by Stretch DB, for example, tables with more than More than 1,023 columns, and More than 998 indexes. The complete list of Stretch DB Limitations can be consulted here.

Conclusion

Stretch Database is an easy way to migrate archive data to Microsoft Azure, if your database supports it. Currently in SQL Server 2016 there are several limitations as described above. It would be great if those can be supported gradually for a higher adoption of this feature. But this is a great initiative from Microsoft, powering the local databases with cloud capabilities is awesome!

References