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!
Enabling Stretch Database at the table level requires ALTER permissions on this table.
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:
CREATE TABLE Stretch_Table
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.
If we take a look at the SQL Database server in Azure, we must be able to visualize the “Stretch_Table” table:
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.
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.