How to enable Strecth Tables in SQL 2016
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”
After that is done, you need to go to the database, right click on it, go to tasks and select the below option.
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.
After you have logged, you need to pick up a valid subscription and click on next.
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.
Click on next, and then finish. Then you need to get the following screen afterwards.
Going to my Azure account I can see the following.
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.
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.
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
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!