Configure easily your Stretch Database

In this blog, I will present you the new Stretch Database feature in SQL Server 2016. It couples your SQL Server On-Premises database with an Azure SQL Database, allowing to stretch data from one ore more tables to Azure Cloud.
This mechanism offers to use low-cost hard drives available in Azure, instead of fast and expensive local solid state drives. Indeed SQL Database Server resources are solicited during data transfers and during remote queries (and not SQL Server on-premises).

First, you need to enable the “Remote Data Archive” option at the instance level. To verify if the option is enabled:
USE master
GO
SELECT name, value, value_in_use, description from sys.configurations where name like 'remote data archive'

To enable this option at the instance level:

EXEC sys.sp_configure N'remote data archive', '1';
RECONFIGURE;
GO

Now, you have to link your on-premises database with a remote SQL Database server:
Use AdventureWorks2014;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'masterPa$$w0rd'
GO
CREATE DATABASE SCOPED CREDENTIAL Stretch_cred
WITH IDENTITY = 'dbi' , SECRET = 'userPa$$w0rd' ;
GO
ALTER DATABASE AdventureWorks2014
SET REMOTE_DATA_ARCHIVE = ON
(
SERVER = 'dbisqldatabase.database.windows.net' ,
CREDENTIAL = Stretch_cred
) ;
GO

The process may take some time as it will create a new SQL Database in Azure, linked to your on-premises database. The credential entered to connect to your SQL Database server is defined in SQL Database. Previously you need to secure the credential by a database master key.

To view all the remote databases from your instance:
Select * from sys.remote_data_archive_databases

Now, if you want to migrate one table from your database ([Purchasing].[PurchaseOrderDetail] in my example), proceed as follows:
ALTER TABLE [Purchasing].[PurchaseOrderDetail]
SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = OUTBOUND) ) ;

Of course repeat this process for each table you want to stretch. You can still access to your data during the migration process.

To view all the remote tables from your instance:
Select * from sys.remote_data_archive_tables

To view the batch process of all the data being migrated: (indeed, you can filtrate by the a specific table)
Select * from sys.dm_db_rda_migration_status

It is also to easily migrate your data back:
ALTER TABLE [Purchasing].[PurchaseOrderDetail]
SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = INBOUND ) ) ;

Moreover, you can select rows to migration by using a filter function. Here is an example:
CREATE FUNCTION dbo.fn_stretchpredicate(@column9 datetime)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS is_eligible
WHERE @column9 > CONVERT(datetime, '1/1/2014', 101)
GO

Then when enable the data migration, specify the filter function:
ALTER TABLE [Purchasing].[PurchaseOrderDetail] SET ( REMOTE_DATA_ARCHIVE = ON (
FILTER_PREDICATE = dbo.fn_stretchpredicate(ModifiedDate),
MIGRATION_STATE = OUTBOUND
) )

Of course in Microsoft world, you can also use a wizard to set up this feature. The choice is up to you!