Tag Archives: Stretch

ByHariharan Rajendran

Basic Errors in Stretch Database

This post explains that what are the basic errors that you could encounter during the configuration of stretch database in SQL Server 2016 and how can you overcome those errors.

Check the below error,

Check my article on Technet wiki for a solution for the above issue.

https://social.technet.microsoft.com/wiki/contents/articles/37457.beginner-s-errors-in-stretch-database-sql-server-2016.aspx

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

Monitor Stretch in SQL Server 2016

Go through my previous article to enable the stretch for your database and table. This article is about how to monitor the stretch. You can monitor the stretch through GUI or DMVs.

We have below DMVs available to monitor the status of the stretch.

select * from sys.remote_data_archive_databases

 

The above script will provide the details about what is the database name in Azure used for stretch.

image

 

select * from sys.remote_data_archive_tables

 

This will tell you, what is the table name in azure used for stretch.

image

The above result has column called “filter_predicate”. It means, if apply any predicate function while configure the stretch then you can see that predicate function here. unfortunately, we don’t have option to apply the predicate function in GUI method. It is available when we enable stretch through T-SQL Script.

select * from sys.dm_db_rda_migration_status

This script will tell you about data migration. Data will start migrate from the table once stretch is enabled. Migration will start  from on-premises SQL Server to Azure. It is using batch process so it will migrate maximum of  4999 records in a batch. The batch id will be available in Azure table.

image

To see the live migration, we can use “SP_SPACEUSED”. By seeing the rows and size we can identify the data migration.

image

 

You can monitor the stretch using GUI,

image

image

ByHariharan Rajendran

Enable Stretch Database in SQL Server 2016

We can enable the stretch feature either through GUI or T-SQL Script. This article will talk about GUI method.

To enable the stretch for your database,

1. You should have Azure subscription

2. If you are using existing SQL Server in Azure then firewall should be enabled.

Steps to enable the stretch,

Step 1: Choose your database which you need to enable stretch and right click –> Tasks –>Stretch –>Enable

image

Step 2:  Create a database credentials, you need to provide the master key. you should use this key whenever you disable or enable the stretch for this database.

image

Step 3: Select the table which you want to do stretch, you can select multiple tables. This screen will give the information  about your table and records in that table and also it will tell you that whether stretch is already enabled or not (Stretched = False).

image

Step 4: SQL Server settings will be validated here.

image

Step 5: You need to sign in using your credentials where azure subscription is active. Once sign in, then you need to select Azure SQL Server. You can create a new server or select from existing server.

image

image

Step 6: Provide your Azure SQL Server credentials.

image

Step 7: Finally , SQL Server will configure stretch for your database and table. To verify, check your database icon.

image

image

 

image

 

If you get an error while configuring stretch like below, then check the log file. Mostly you will get an error when you are not configured the firewall in Azure SQL Server.

image

Log file,

image

1