Stretch Database SQL Server 2016

Introduction to Stretch Database:

What?

Stretch Database is one of the highly anticipated new feature in Microsoft SQL Server 2016.

This feature archives your historical data (Cold data) to Azure SQL database (PaaS Cloud) securely.Once you enable this feature, SQL Server silently and transparently moves/migrates data to Azure SQL Database.

Why?

Evergrowing databases with millions & Billions of rows.

Cost reduction

small latency
-------------------------------------------------------

Terminologies:

Cold Data/Historical data (> 10 years)
Warm Data (> 5 years)
Hot data/live data/on premise (< 5 years)
-----------------------------------------------------

How does StretchDB work?

Once you enable this feature for a table, SQL Server silently and transparently moves/migrates data to Azure SQL Database. When you query those tables, the query optimizer knows which rows are on your server and which rows are in Azure, and divides the workload accordingly. The query processing on the Azure rows takes place in Azure so the only latency is for the return of the rows over the network.

--------------------------------------------------------------------------------

Limitations for Stretch Database


Not all tables will be eligible for being Stretch-enabled. Certain table properties, data and column types, constraints, and indexes are not supported.
Memory-optimized and replicated tables.
Tables that contain FILESTREAM data, use Change Tracking or Change Data.
Data types such as timestamp, sql_variant, XML, geography or columns that are Always Encrypted.
Check and default constraints or foreign key constraints that reference the table.
You cannot run UPDATE or DELETE statements, or run CREATE INDEX or ALTER INDEX operations on a Stretch-enabled table.

-------------------------------------------------------------------------------
Backup and Restore of a Stretch Database

Currently in SQL Server 2016 when a database that is Stretch-enabled is backed up, a shallow backup is created which does not include the data that has been migrated to the Azure SQL database. It is expected that with the RTM release of SQL Server 2016 backing up a Stretch-enabled database will create a deep backup that will contain both local and stretched data.

When restoring a database that is Stretch-enabled, you'll have to reconnect the local database to the remote Azure SQL Database. You do this by running the stored procedure sys.sp_reauthorize_remote_data_archive as a db_owner.

If I now back up the Stretch-enabled AdventureWorks2014 database and restore it, I will no longer be able to query the StretchTest table until I reconnect to Azure SQL Database by running:


USE [AdventureWorks2014];

GO

EXEC sys.sp_reauthorize_remote_data_archive @azure_username,

@azure_password;

GO

----------------------------------------------------------------------------------
Un-migrate Data

To migrate data back to local storage from an Azure SQL Database you will need to create a new local table with the same schema as the Stretch-enabled table. You then have to copy the data from the Stretch-enabled table into the new local table. Once the data is copied you drop the Stretch-enabled table and rename the new local table to the name of the Stretch-enabled table that was just dropped.

You can only disable Stretch for a database once all Stretch-enabled tables have been dropped. If you drop a database enabled for Stretch, the local database is removed but the remote data is not; you will have to drop the remote database from the Azure management portal.
---------------------------------------------------------------------------------

Some Queries used on StretchDB

To enable Stretch Database on an instance run:


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

RECONFIGURE;

GO

-------------------

To monitor the migration of data to Azure you can query 


sys.dm_db_rda_migration_status

---------------------

When restoring a database that is Stretch-enabled, you'll have to reconnect the local database to the remote Azure SQL Database. You do this by running the stored procedure 


USE [AdventureWorks2014];

GO

EXEC sys.sp_reauthorize_remote_data_archive @azure_username,

@azure_password;

GO

--------------------------
Check the status of data migration in a dynamic management view


sys.dm_db_rda_migration_status to see how many batches and rows of data have been migrated.

---------------------------

Tools

StretchDB Migration advisor

Stretch Database monitor.