The following article introduces the concept of Stretch Database (Stretch DB), a new feature of SQL Server 2016 and demonstrates examples of its usage.
Microsoft SQL Server 2016 is a hybrid cloud environment designed to allow data and services to be enable users perform advanced analytics within their databases and create business insight visualizations.
One of the highly anticipated features new to Microsoft SQL Server 2016 is Stretch DB, a feature that migrates your historical data transparently and securely to the Microsoft Azure cloud.
Moreover, it can be configured to migrate a complete table to the cloud, or to migrate only part of a table based on certain condition. For example, one won’t migrate its complete transaction table, but migrating the data which is older than 5 years makes sense!
Source: http://wikidba.net/
Stretch Database targets transaction databases with large amounts of historical data, typically stored in a small number of tables. These tables may contain more than a billion rows. Some cases where Stretch DB can be used are:
To identify databases and tables that can be “stretched”, Use Stretch Database Advisor, a feature of SQL Server 2016 Upgrade Advisor.
Download and install Upgrade Advisor from here. This tool is not included on the SQL Server 2016 Release Candidate (RC0) installation media.
To verify if a database and table is eligible for Stretch DB, open upgrade advisor and click on “Run Stretch Database Advisor”.
Before a Database can be stretched, Stretch DB needs to be configured at the server level.
EXEC
sp_configure
'remote data archive'
,
'1'
;
GO
RECONFIGURE;
This operation requires sysadmin or serveradmin permissions.
The easiest way to get started is to use the wizard.
To monitor the Data Migration progress, at the database level, go to Tasks > Stretch > Monitor
The previous operation would migrate all the data to the cloud.
After a few minutes, when querying the table transactions, all the data will be fetched from the cloud and no predicate was specified when enabling stretch on the table.
To migrate only specific rows, start by first bringing all the data from the cloud to the local database.
To do so, click on stretch > Disable > Bring Data back from Azure
All the records would be migrated back to on-premise gradually.
The inline table-valued function required for a Stretch Database filter function looks like the following example.
CREATE
FUNCTION
dbo.fn_stretchpredicate(@column1 datetime)
RETURNS
TABLE
WITH
SCHEMABINDING
AS
RETURN
SELECT
1
is_eligible
WHERE
@column1 <
CONVERT
(datetime,
'4/1/2016'
, 101)
The parameters for the function have to be identifiers for columns from the table. Schema binding is required to prevent columns that are used by the filter predicate from being dropped or altered.
If the function returns a non-empty result, the row is eligible to be migrated; otherwise - that is, if the function doesn't return any rows - the row is not eligible to be migrated.
The next step is to alter the table and set remote_archive on while specifying the predicate.
ALTER
[dbo].[TRANSACTIONS]
SET
( REMOTE_DATA_ARCHIVE =
ON
(
FILTER_PREDICATE = dbo.fn_stretchpredicate(
DATE
),
MIGRATION_STATE = OUTBOUND ) );
There are 67,620 records which are dated before 4/1/2016 which should be migrated to the cloud.
The development of Stretch DB is still in progress and as at the time of writing, there are lots of properties that is not yet supported by Stretch DB. For example, tables with more than More than 1,023 columns, and More than 998 indexes. The complete list of Stretch DB Limitations can be consulted here.
Stretch Database is an easy way to migrate archive data to Microsoft Azure, if your database supports it. Currently in SQL Server 2016 there are several limitations as described above. It would be great if those can be supported gradually for a higher adoption of this feature. But this is a great initiative from Microsoft, powering the local databases with cloud capabilities is awesome!