“Stretch Db” in Sql Server 2016 CTP2 – Quick Start

“Stretching” the database now means migrating some or all data from local database tables into the Azure Cloud. The result is a hybrid design: each local table then has a counterpart in the cloud. Any query against the local table will be transparently re-written to include data from the “cloud table”.

Highly active OLTP tables shouldn’t be stretched because of the overhead of retrieving the data from the cloud. It’s intended for “cold” rows, which will not be updated or deleted. Only whole tables can be stretched in the CTP2 version, but the final release will permit rows to be marked either for migration or for keeping locally. The migration process happens asynchronously at regular intervals to avoid heavy loads on the local machine.

Here’s a good video featuring George Li, Program Manager in the Sql Server team.

If you’ve installed Sql Server 2016 CTP2 with Management Studio to match you can give it a try. I tried it out and it worked fine for me. This walkthrough is clear and comprehensive so I won’t repeat it.

Here’s a very simple demo. You’ll need to run the statements one-by-one, and go into the Management Studio GUI when indicated.


CREATE DATABASE StretchTest
GO
USE StretchTest
GO

--DROP TABLE StretchTest
CREATE TABLE StretchTest
(
C1 sysname not null
)

INSERT INTO
StretchTest
(
C1
)
SELECT
name
FROM
sys.objects
GO

EXEC sp_configure 'remote data archive';
GO
EXEC sp_configure 'remote data archive' , '1';
GO
RECONFIGURE;
GO

Now you need to go into the Management Studio GUI per the walkthrough instructions.

--view progress of the data migration--
SELECT * FROM sys.dm_db_rda_migration_status

To confirm that you now have the database in the cloud select the following query and hit Control-L to see the execution plan:
SELECT * FROM StretchTest
--DROP DATABASE StretchTest

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.