Menu
Menu
StretchDb provides cost-effective availability for cold data Stretch warm and cold transactional data dynamically from SQL Server to Microsoft Azure with SQL Server Stretch Database. Unlike typical cold data storage, your data is always online and available to query. You can provide longer data retention timelines without breaking the bank for large tables like Customer Order History. Benefit from the low cost of Azure rather than scaling expensive, on-premises storage. You choose the pricing tier and configure settings in the Azure Portal to maintain control over price and data access speeds.
After you enable Stretch Database for a SQL Server instance, a database, and at least one table, it silently begins to migrate your historical data to an Azure SQL Database. If you store historical data in a separate table, you can migrate the entire table. If your table contains both historical and current data, you can specify a filter predicate to select the rows to migrate.
Stretch Database ensures that no data is lost if a failure occurs during migration. It also has retry logic to handle connection issues that may occur during migration. A dynamic management view provides the status of migration. You can pause data migration to troubleshoot problems on the local server or to maximize the available network bandwidth.
You don’t have to change existing queries and client apps. You continue to have seamless access to both local and remote data, even during data migration. There is a small amount of latency for remote queries, but you only encounter this latency when you query the historical data.
Is it for you?
Column types
Constraints
Indexes
Before you configure a database for Stretch, we recommend that you run the Stretch Database Advisor to identify databases and tables that are eligible for Stretch. The Stretch Database Advisor also identifies blocking issues. Stretch Database migrates data to an Azure SQL Database. Therefore you have to have an Azure account and a subscription for billing.
Have the info you need to create a new Azure SQL Database or to select an existing SQL Database, and to create a firewall rule that lets your local server communicate with the remote server.
Creating a Stretch Function
CREATE FUNCTION dbo.fn_stretchpredicate(@column1 datatype1, @column2 datatype2 [, ...n])
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS is_eligible
WHERE
What can you do with a Stretch Function
To migrate the entire table instead of selected rows, remove the existing FILTER_PREDICATE by setting it to null. After you remove the filter predicate, all rows in the table are eligible for migration. After you bind the function to the table as a predicate, the following things are true.
The next time data migration occurs, only the rows for which the function returns a non-empty value are migrated. The columns used by the function are schema bound. You can’t alter these columns as long as a table is using the function as its filter predicate.
You can replace a previously specified filter predicate by running the ALTER TABLE statement again and specifying a new value for the FILTER_PREDICATE parameter. The new function has to be less restrictive than the previous function. All the operators that existed in the old function must exist in the new function.
The new function can’t contain operators that don’t exist in the old function and the order of operator arguments can’t change. Only constant values that are part of a <, <=, >, >= comparison can be changed in a way that makes the predicate less restrictive.
To disable Stretch Database for a table, select Stretch for a table in SQL Server Management Studio. Then select one of the following options.
After you disable Stretch Database for a table, data migration stops and query results no longer include results from the remote table. Before you can disable Stretch Database for a database, you have to disable Stretch Database on all the individual Stretch-enabled tables in the database.
