How to enable Strecth Tables in SQL 2016

Howdy!

SQL Server 2016 CTP 2.0 has been launched today, so I want to take some time to show you how to enable the new feature called Stretch Tables. Stretch Tables will allow you to save your table in a Microsoft Azure SQL Database without any application code change to retrieve the information stored in the cloud. Below I will describe what you need to do to get Stretch Tables working.

1) Once you have logged into SQL Server, you need to enable a setting on sp_configure named “Remote Data Archive”

clip_image001

After that is done, you need to go to the database, right click on it, go to tasks and select the below option.

clip_image002

2) A wizard will show up to you and all you need to do is follow the steps. As you see below, you need to login into your/company Azure account.

clip_image003

After you have logged, you need to pick up a valid subscription and click on next.

clip_image004

3) Create a credential to log into the Azure SQL Database. For this testing I am adding the current server IP, but for production purposes you need to add a fixed IP address to avoid future issues.

clip_image005

Click on next, and then finish. Then you need to get the following screen afterwards.

clip_image006

Going to my Azure account I can see the following.

clip_image007

4) So, until here you got Stretch enabled at the Database Level, and now you need to enable for the tables you might want to stretch over Azure SQL Database.

clip_image008

To prove the table has been copied to the Azure SQL Database, let me connect on the Azure SQL Instance and show you. The below image is a select * from in the table that I just moved to the Cloud.

clip_image009

Something to keep in mind with Stretch Tables is that the data movement is asynchronous to avoid any bottlenecks that might come in network for example. While the data is moved you can still query the whole table, the Query Engine and Query Processor takes care of joining both tables and deliver the right results to the user. To see the progress of the data movement you can query the following DMV

image

In addition, the table is fully durable, which in case of a restore the data is moved again to Azure SQL Database.

I’ve found a few bugs already, but I am not going to describe them here, so feel free to test it. I will be reporting to Microsoft the same.

As the time permits I will be posting several contents about SQL Server 2016. Stay tuned!

Sobre Marcos Freccia

MVP em SQL Server (Data Plataform) , especialista em SQL Server, e atualmente trabalhando com Microsoft Azure!

Publicado em maio 28, 2015, em SQL Server, SQL Server 2016, VirtualPass e marcado como , , , . Adicione o link aos favoritos. 1 comentário.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: