We can use the stretch database to place our less-frequently accessed data securely in Microsoft Azure.

Benefits

-> Cost effective – The less-frequently accessed data can be stored on cost efficient and secure Microsoft Azure than storing them on costly On-premise storage.

-> No changes in queries – The queries that access a stretch database can be same and no additional logic is required to incorporate the data moved to Microsoft Azure.

-> Database Maintenance – Database maintenance will be comparably faster as we will touch only the frequently accessed data.

-> Faster queries – Queries touching frequently accessed database will be comparably faster with fewer IO and increased performance.

-> Let’s create the required database and objects for this demo,

create database JB_StretchDB
go
use JB_StretchDB
go
create table Table1(
sno int primary key identity (1,1),
sname varchar(255),
Year int)

-> Populating table Table1 with some data,

set nocount on
insert into Table1 values(replicate(‘A’,25),1980)
go 10000
insert into Table1 values(replicate(‘B’,25),1981)
go 10000
insert into Table1 values(replicate(‘C’,25),1982)
go 10000
insert into Table1 values(replicate(‘D’,25),1983)
go 10000
insert into Table1 values(replicate(‘E’,25),1984)
go 10000

-> Looking at the data,

select sname, year, count(*)
from Table1
group by sname,year

-> The query completes in 60 MS.

-> Select the database that will be part of stretch database,

-> Select the table and click on “Entire Table” under “Migrate”.

-> You can move all the data to cloud or a certain number of rows. I will select “Choose Rows” in the below screen as I want the latest data in On-Premise database and just the old data’s be moved to cloud.

-> I already have a Microsoft azure instance created. Hence I will select “Existing server” and point the instance from the drop down box. You can create one yourself by using the article https://jbswiki.com/2017/05/31/creating-a-sql-database-in-microsoft-azure-portal/ or you can select “Create new server” and proceed.

-> Provide the password for the database master key.

-> This is what I see in the SSMS after setting up the stretch database.

-> Executing the below query and checking the execution plan. The query actually took 19 seconds.

-> It is clear from the screenshot that we are spending most of our time on the remote query as we are querying close to 40000 rows from Azure database.

-> The performance is not that great, but we create stretch database just to move data to cloud that are not frequently accessed.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.