Azure SQL Database Stretch Database Overview

Windows Azure Platform Family of articles Catalog

Stretch databse Usage Scenarios:

　　The author has a fast consumer products, the daily production of millions of orders data. These order data are stored in a very powerful data center physical machine.

For these order data, there are two categories:

　　1. Thermal Data : Order data generated in the last 1 months.

For the thermal data, the enterprise needs statistical analysis of these data, convenient to query.

　　2. Cold Data : Order data generated over the past 1-3 years.

In traditional IDC operations, cold data is often backed up and archived, such as with a tape library.

But the archived data is actually offline, that is, if we need to query these cold data, we also need to restore data from the tape library to the database, and then query.

　　In this case, we can use the Azure SQL Stretch database feature to migrate some data rows (or all rows of data) from the data table to azure SQL database

　　

The advantages of using Azure SQL database Stretch are the following:

1. Reduce the query cost of cold data

Stretch DATABSE supports the migration of warm and cold data from traditional SQL Server databases to cloud-ready databases.

Unlike traditional tape library backups, your database backup content is always online, and you can perform read operations on cold data on the cloud.

Using stretch database on the cloud is less expensive than traditional IDC to save these cold data.

2. No need to modify query statements and applications

When using stretch database, you do not need to modify query statements and applications.

When we execute the query, the local SQL Server will decide whether to execute the query locally or execute the query statement in the cloud. Users don't need to know that data is saved locally and in the cloud.

　　　　

3. Reduce the cost of local data operation and maintenance

Because most of the temperature and cold data are kept in the cloud, we don't need to buy a lot of storage to keep the historical data in the local data center.

4. Ensure data security

The security features supported by local SQL Server, such as row level security (RLS) and other security features, are supported in the cloud stretch database.

　In the following scenario, we need to use the azure Stretch Database:

(1) need to keep historical data for a long time

(2) Sometimes need to query the historical data

(3) Front-end applications need access to these historical data, and front-end applications are not refactored

(4) Reduce the cost of purchasing storage

　　

which databases support Azure Stretch database?

We recommend that users use the SQL Server 2016 database to start the stretch feature.

In addition, we recommend that you download the SQL Server Upgrade Advisorto define which data tables can be migrated to stretch database.

　　

　　　

　Stretch Database limit:

https://azure.microsoft.com/en-us/documentation/articles/sql-server-stretch-database-limitations/

　Constraints:

Data that needs to be migrated, and if a unique constraint and PRIMARY KEY constraint are included, these constraints are not enabled

Uniqueness is isn't enforced for UNIQUE constraints and PRIMARY KEY constraints in the Azure table that contains the migrate D data.

DML operations:

1. SQL data that can be migrated to the cloud (but not migrated), or SQL data that has been migrated to stretch cloud, cannot perform update and delete operations.

　　

　　

　　

Reference:https://msdn.microsoft.com/en-us/library/dn935011.aspx

　　

Azure SQL Database Stretch Database Overview