Stretch Database to Azure on SQL Server 2016

Introduction

Stretch Database is a new feature available from SQL Server 2016 onwards. This feature lets you migrate your cold data to Microsoft Azure.

In the past, when database used to grow beyond, the only option was to buy additional drives and let the database grow more. This was a very expensive and cost inefficient solution.

To resolve a problem like this, Microsoft came up with a new feature to migrate data only to Azure. However, you can run your queries against your instance even if data has been migrated to Azure. You can move data back and forth between Azure and local database.

Stretch Database does not affect the application logics and queries.

We will basically create a database, create a table and insert around 1000 rows of data to the table. Then use the stretch feature to migrate the data to Azure.

Background

You will need active Microsoft Azure Subscription and SQL server 2016 to be able to use the stretch feature.

Using the Code

We will first create a database and a table to the database. Then we will add a table and populate it with a lot of data. Then we will migrate the data only to Azure and run a few queries.

//
// -- First Create the Database
USE [master]
GO
CREATE DATABASE [StretchDatabase]
Go
//

Then, create a table in the database that we just created.

//--This code adds a table with 3 columns, SN (autoincremented), Name and Last Name
USE [StretchDatabase]
GO
CREATE TABLE [dbo].[StretchTable](
         [sn] [int] IDENTITY(1,1) NOT NULL,
         [Name] [varchar](50) NULL,
         [Last Name] [varchar](50) NULL
) ON [PRIMARY]
GO

Then, add rows to tables. Since we are attempting migration of cold data to the Azure, we will use for loops to insert 1000 rows of data. You can use as much as you like.

USE [StretchDatabase]

GO

DECLARE @cnt INT = 0;
WHILE @cnt < 1000
BEGIN
  Insert into stretchTable (Name, [Last Name]) values ('Mahesh', 'Dahal')
   SET @cnt = @cnt + 1;
END;

Now, we have to go to the database and use UI to enable and migrate data.

Right click on database -> Tasks ->Stretch and Enable.

This will pop out a new window to configure the stretch feature.

Click on Next and you will find options to select tables on which you want to apply stretch database.

After you select your tables, click on Next and where you have to sign in to your Azure subscription.

If you want to migrate only the desired data from the tables, then click on Entire Table. A window will popout where you can select the data that you desire to migrate.

After you configure the desired rows for migration, then click Done and next. You will be taken to window to configure Azure subscription.

Click on Sign in.
Then insert your account credentials in provided login screen.

Click on sign in then. Then select the region and provide new set of Username and password for the database that will be create in Azure where our data will be stored. The username and password are for the SQL Azure database that will be created in Azure.

Then click on next. You will be asked for confirmation for the configurations.

Click on finish. Then all the settings will be applied and data will be migrated.

After the processes are completed, click on Close.

Now let’s test the migration.

If the stretch database and migration is successful, the database icon will change to the following.

If you check your Azure subscription, you will find a new database created.

Now let’s run a few queries.

Now, when you run this query, you will get all the data that was stored. The data will come after running the query in local database and remote database.

select * from StretchTable 

Run the above code and you will see all the data in from the table.

But if you see the origin of the data using following command, you will see that no data is stored in the local table.
Total storage used by this table can be found using:

Sp_spaceused 'stretchtable';

Whereas the space used by this table in local storage can be found using command:

Sp_spaceused 'stretchtable' , @mode='local_only'

And you can see in the results that just 72KB storage is used.

Whereas if you run the command to find the space used in remote storage, then you can see that all the data is stored in remote database which is our SQL Azure database created earlier.

SQL Stretch is very advantageous to reduce the cost of storage drive whereas maintaining the consistency of application and application logic at the same time. However, SQL Stretch does not support the following:

1. Migrated data will not be enforced for uniqueness. Primary key constraints, and Unique Constraints will be ignored.
2. Update and Delete operations are not supported in the migrated table
3. You cannot create an index for a view that includes Stretch-enabled tables.
4. Filters on SQL Server indexes are not propagated to the remote table.