Tag Archives: Stretch DB

ByHariharan Rajendran

Stretch Database – Filter

We can easily filter the rows to migrate the data to azure  through T-SQL statement. Need to associate that filter function with the script to enable the data migration to azure.

This article explains the same filter concept through wizard. we can easily apply the filter with few clicks.

1. Right click your database->Tasks->Stretch->Enable

2. Choose the table and look for a column “Migrate” and click the hyperlink “Entire Table”

image

3. Filter the rows in the below window

image

image

image

ByHariharan Rajendran

Stretch in SQL Server RC0

SQL Server Release Candidate 0 version has been released. We have been discussed about stretch database in CTP 3.3. There are many improvements in RC0. This article is about, how to enable the stretch in RC0.

image

Go through Stretch in SQL Server 2016 CTP 3.3.

Steps to enable the Stretch in RC0 Version,

Step 1:  Right click on your database and go to Tasks->Stretch->Enable

image

image

Step 2: Choose the tables you want to stretch.

image

Step 3: Provide your Azure subscription to configure SQL Server and database for stretch.

image

Step 4: Set up master key for your database.

image

Step 5: Provide your IP, in case if you have configured.

image

image

Step 6: Configuration process started to enable stretch for database and table.

image

image

As you can see, there are some difference in steps to configure when compare with CTP 3.3 version. There is no changes on Monitoring the stretch.

Enable Stretch for Tables

Previously, we need to select Reconfigure option to add new tables to stretch but that “Reconfigure” option has been removed. Now, we have “Enable”, “Disable” & “Monitor”.

To add new tables to stretch, follow the below steps.

Choose the new table to add into stretch. Right click –> Stretch –>Enable

image

image

image

image

image

Disable Stretch

To Disable the stretch for the database, we need to disable the stretch for tables first. We don’t have option to disable in one short if multiple tables are in stretch. Need to manually disable all the tables from stretch.

If we try to disable stretch for database without disabling tables then below message will be appeared.

image

There are two options in disable on table,

1. Bring data back from Azure

2. Leave data in Azure.

Once stretch is enabled for a table, data migration will be initiated from local to azure through batch process. If we decided to disable the stretch then make sure whether we want to bring data back to local or keep them on azure it self. Based on  that choose any of the above option.

Note: The table in Azure database will not be deleted by process, you need to delete it by going to azure management portal.

If you are selected “Bring data back from Azure” then below message will be prompted.

image

Once disable the stretch for all your tables, then disable the stretch on your database.

image

ByHariharan Rajendran

Issues in Stretch Database–SQL Server 2016 CTP 3.3

In latest version of SQL Server 2016 CTP 3.3, we have issues in Stretch option. I have listed out the issues, hope this will be sorted out before GA (General Available).

Issue 1:  No option to move only set of rows to Azure Database. The default option is “Migrate All Rows”. Using inline function, we can apply the condition to split the records but available only through T-SQL Scripts.

image

Issue 2: Stretch option is not enabled for table even though I enabled the stretch on database with selected tables.

image

Issue 3: I have enabled the stretch for the database, there is no option to disable in GUI. We have to disable through T-SQL Script

image

Issue 4: Almost all the time, all the data got migrated to Azure database, in local only 0 record.

image

ByHariharan Rajendran

Limitations in Stretch DB in SQL Server 2016 CTP 3

As per Microsoft article, we have many limitations that we need to understand before start enabling the stretch database in your environment.

I have discussed few limitations here and also what will happen when we override those limitations.

Limitations

1. We cannot perform UPDATE and DELETE operations on stretch enabled tables

image

2. We cannot create a index for a view that includes the stretch tables. “New Index” option is disabled.

image

3. We cannot perform update or delete a view that includes stretch enabled tables however insert is possible.

image

1