Database Stretching

02 Sep 2019 26508 views 0 minutes to read Contributors

Database stretching is a SQL Server feature where data can be splitted between on-premises and cloud storage.

This is commonly used for cold, historical data that still needs to be accessible.

stretching cold data to the cloud keeps the active data on-premises which in most cases can be relatively small to have maximum performance on-premises.

 

When to stretch your database?

Stretching a database has several benefits:

- Cold, Historical data needs to be accessible.

- Large retention periods are required.

- Large tables that have huge amounts of rows and contain historical data.

- Due to large datasets it is a common occurence to add extra resources.

- Maintenance jobs/tasks take longer due to large size of databases and tables (e.g. creating indexes, index rebuilds, database integrity checks, backups)

- if there is need to save costs on storage facilities.

- SLA Requirements, e.g. downtime must be minimal but due to a large database containing historical data the restore process will take more time.

 

if any of the above matches your situation it might be worthwhile to consider database stretching.

 

 

Pre-requisites

Before database stretching can be implemented there are a few actions that need attention first.

-SQL Server version 2016 or higher.

-Microsoft Azure subscription.

-Microsoft Azure account

-Check limitations on tables (constraints, DML operations, Indexes, Properties)

-Check limitations on datatypes (column types, keys)

 

Without a Azure subscription or account it is not possible to implement database stretching to the cloud.

The Subscriptions start from 100 DSU(Database stretching unit) up to 2000 DSU. 

When implementing a stretch database there are several other factors that need to be accounted for as the initial setup might affect your production performance, and depending on the table sizes it can take a considerable amount of time before it is completed.

 

 

Report a Bug

In this article