Infrastructure at your Service

Nathan Courtine

SQL Server 2016 CTP2: Stretch database feature – Part 2

In my previous blog SQL Server 2016 CTP2: Stretch database feature – Part 1, I explained how to prepare your instance and your database to enable the Stretch feature for your table.

Now I present you how to enable the feature for your tables!

Prerequisites

Enabling Stretch Database at the table level requires ALTER permissions on this table.

 

Limitations

For the moment in the SQL Server 2016 CTP2, there are several and important limitations, which drastically reduces the scope of use of Stretch Database feature.

These limitations include Table Properties, Data Types, Column Properties, Column Types and Constraints and Indexes.

You can obtain the detailed list with the following Microsoft article: Requirements and limitations for Stretch Database.

 

Enable Stretch for a Table

First, I create a new table to avoid all limitations I explained above. Here is the SQL script:

Use AdventureWorks2014;
CREATE TABLE Stretch_Table
(
Column1 nvarchar(50),
Column2 int,
Column3 nchar(10)
)

If you have followed all steps in my previous blog, you must be ready for enabling Stretch feature for your table!

As all have been pre-configured, you just need to enable the feature for the targeted table.

enable_table_for_stretch.png

 

If we take a look at the SQL Database server in Azure, we must be able to visualize the “Stretch_Table” table:

SQL_azure_20150618-090340_1.png

 

You can notice that a new column named “batchID” has been included in the original table. Indeed, a non-null bigint is incremented each time a batch is performed to insert data in the migrated table. It means “batchID” value can be non-unique.

Moreover, your table still remains visible on your on-premise instance and you can perform your normal work.

 

Next step

Performing backup of the database with Stretch feature enabled will not include the data stored in Azure. It means you also needs to perform a backup in Azure.

I will detail this part in my next blog.

Leave a Reply

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

Nathan Courtine
Nathan Courtine

Senior Consultant