Reduce your storage costs with Microsoft SQL Server’s latest feature Stretch Database

Tech Machinist
Aug 24, 2017 · 2 min read

Data is accumulating in our databases, day by day, and this data growth leads to an increase in storage costs and a reduction in performance of operational databases.

In databases, in general, there are three types of data:
1) Hot Data
2) Warm Data
3) Cold Data

Cold data not accessed very often, but we need to maintain that data as per compliance. cold data increases storage costs and impacts performance while retrieving hot data from the same table.

Stretch databases is a new feature in SQL Server 2016 with which we can migrate cold data to Microsoft Azure.

No need to make any changes to existing source code

We can stretch full tables and even partial tables based on filter predicate to Azure.

Query optimizer analyzes and decides whether the data is on premise or in cloud or mix of on premise and cloud.

improved performance of the hot data on premise, when compared to the earlier models where we have everything residing on premise. but, small amount of latency in querying the cold data on cloud.

Maintenance costs are also reduced as the backup is automatic for stretched data.

Most users want to see trends in the historical data,we can take advantage of Azure data analytics.

Key Notes to take:
Migration of data to Azure is done with a single thread so the migration is slow .Single threading allows migrating only one table at a time in a round robin fashion, with a fixed batch size of 10K rows.
Stretch migration’s single thread switches between all eligible tables.
This reduces speed in migration as the thread switches and checks other stretched table eligible rows although those table are not stretching at that movement.

stretched data is stored in Azure on premium storage instead of lower tier storage.

Bottom Line:
Cold data to Azure using Stretch database is not a viable option at this time as migration is slow for multiple large tables due to single thread migration.

)