SQL Server 2016 Stretch Database

July 12th, 2016

On June 1, SQL Server 2016 was released. It brought a large number of novelties into normal development, including the long-announced Stretch Database technology that allows migrating data from SQL Server dynamically to Azure.

SQL Server 2016 Stretch Database
From the marketing point of view, Stretch Database has been well promoted. Reasonable arguments were stating that with the growth of historical data the complexity and cost of its maintenance grows as well. As a rational solution to the problem, the automatic migration of obsolescent archival data to the cloud was introduced. Frankly speaking, I enjoyed the idea.

As from SQL Server 2016 RC0, I began testing the Stretch Database technology on two projects I’m helping to develop. The first one is TMetric, a free time tracker with OLTP load, and the second one is an internal project with DW load.
Let’s go into Stretch Database through a simple example. The first thing to do is to allow using Stretch Database since this functionality is disabled on the server by default:

There is no need to reload the server. Next, let’s create a test database:

…and call the Stretch Database wizard from SSMS 2016:

The wizard cautions us at once that we must have:

1) admin privileges
2) current Azure subscription
3) allowed access to SQL Server to the Internet

On the next page of the wizard, we face the first disappointment:

We cannot migrate data from our table to Azure because of some limitations in Stretch Database technology. I will list basic ones (limitations that are not mentioned in MSDN are highlighted in bold).

Data store in Azure is not supported for the tables which:

1) contain more than 1,023 columns and more than 998 indexes
2) include the FILESTREAM data
3) are involved in replication or use Change Tracking or Change Data Capture
4) are optimized for memory layout (In-Memory table)
5) have the Always Encrypted columns

Besides, you cannot use the following in tables:

1) all user data types (CLR, user-defined types)
5) XML
9) COMPUTED columns
10) SEQUENCE in a column
11) DEFAULT and CHECK constraints
12) Full-Text Search, XML and Spatial indexes
13) there should be no index views on table
14) external keys referring to the table (for example, you can enable Stretch for the OrderDetail child table, but you can’t do the same for the Order parent table).

As it turned out, our problem was in the DEFAULT constraint. Let’s recreate the table and try once more:

Now, the wizard allows to select the table:

But the wizard cautions, that: Uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints in the Azure table that contains the migrated data. That’s why, for the Stretch tables, it’s better to create PRIMARY KEY on the IDENTITY column, or at least, to use UNIQUEIDENTIFIER.

Next, we are prompted to log in and create SQL Server in Azure:

Let’s create SQL Server on Azure Portal manually:

Then, we need to select the created server in the wizard:

Let’s set the master password, that we will need in future:

Specify the exceptions to be added to Firewall:

And here we are on the homestretch:

But during the setup, another exception is waiting, and again, it is not mentioned in the help:

During database creation, I didn’t specify collation explicitly, and it turned out that Cyrillic_General_CI_AS is not supported in addition to many other things. During testing, it turned out, that Stretch Database won’t work if collation is used in the database properties:

1) any Windows collations (Latin1_General_100_CI_AS, …)
2) any AI and BIN collations
3) there are particular problems with CS collations

To make everything work consistently, it is better to use only SQL Server collations. I haven’t checked them all, but when I used SQL_Latin1_General_CP1_CI_AS, I didn’t have any troubles:

After the COLLATE modification for the database, I ran the wizard once more and voila:

Now, let’s try to track how data is migrated to Azure through the monitor in SSMS 2016:

… or use a separated system view sys.dm_db_rda_migration_status:

There was a bug in RC3, and if COLLATE of columns did not coincide with COLLATE of the database, sys.dm_db_rda_migration_status would grow without being cleared.
In our example, one of the columns has COLLATE that does not coincide with the one that is set in the database properties. That’s why data migration will fail from time to time by error:

But after several failed attempts it will be migrated successfully:

Therefore, we may conclude that the bug has not been completely fixed. Therefore, it is highly recommended to set the same COLLATE for columns as for the database.
On this step, we tried one of the ways of creating a Stretch table. By the way, you can do it a way simpler and faster with the script:

The main thing is to set permissions for our Azure Server before:

Now, let’s take a look at changes in the table…

Whenever data comes to the table, it is physically stored on the local server for some time, and then it is migrated to Azure automatically. You can easily see it with help of the Live Query Statistic feature implemented in SSMS 2016:

After 5-10 seconds:

If we take a look at the execution plan, all functionality of Stretch Database is no longer a secret anymore: a linked sever is created together with a separate component that migrates data to Azure.

We get the following limitations:

1) you can’t UPDATE or DELETE rows that have been migrated, or rows that are eligible for migration, in a Stretch-enabled table or in a view that includes Stretch-enabled tables
2) you can’t INSERT rows into a Stretch-enabled table on a linked server

That is, we can only insert data into a table:

Because any DELETE, UPDATE, TRUNCATE operation results in the error:

Let’s try to create a Stretch table with the filter determining which data should be on the local machine, and which should be migrated to Azure:

Next, we create a function and a column that will store an attribute indicating that data can be migrated to cloud:

When the command work is over, let’s see the changes. Firstly, you can do anything you like with local data till the migration condition is not specified. You can delete, update it:

And when we need to selectively migrate data to Azure:

We need to remember this: what is already located in cloud cannot be returned with a simple UPDATE:

Let’s see how many space our data takes:

Now, let’s try to use a filter in our queries in order to make a call to Stretch tables more effective:

If we look at the execution plan, in the first case we don’t need to make connection to the linked server which should work a way faster:

Did we really find a variant when the usage of Stretch Database would be feasible? In real life, everything is not that good, because both queries are both slow because of the bug:

At every call to the Stretch table, the connection to Azure takes place:

Because of such behavior, any queries with the Stretch table will freeze or fail because of time-out. The following limitations will seem ridiculous against the performance problems described above:

1) you can’t create an index for a view that includes Stretch tables
2) filtered indexes are not propagated to the remote table


As I wrote above, I tried to use Stretch Database for two projects. There was a large table with spans of work-time in TMetric time tracking app. Call to this table goes quite actively, that’s why Stretch Database simply could not do well because of its slow performance. The size of the table was treated in a simple way: all external keys were deleted and the table was recreated with the clustered COLUMNSTORE index (after all, the table was reduced in 12 times).

As for the inner project, a trick with sections switch came in handy, and I really liked to use it. The point is that two tables (A and B) are created. We work with table A actively, and then switch section to table B, which is a Stretch table:

In fact, we deal with the demarcation between historic and operative data, and in result, we delicately sidestep the performance problem. This post covers all problems I faced for two-three moths of working with Stretch Database. I do hope it will be useful to readers.

Leave a Comment