My personal favorite reason to upgrade to SQL2016: StretchDB
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.
What is it/what it does:
- The ultimate archive solution
- Provides cost-efficient availability for cold data
- Scales up or down as needed
- Doesn’t require changes to queries or applications
- Streamlines on-premise data maintenance
- Keeps your data secure during migration
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?
Limitations – Table
- More than 1,023 columns
- More than 998 indexes
- Tables that contain FILESTREAM data
- Replicated tables
- Tables that are actively using Change Tracking or Change Data Capture
- Memory-optimized tables
Limitations – Datatypes/Column properties
- CLR user-defined types (UDTs)
- Computed columns
- Check constraints
- Default constraints
- Foreign key constraints that reference the table
- Full text indexes
- XML indexes
- Spatial indexes
- Indexed views that reference the table
How to get started
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.
Enable Database for Stretch
- Choose an (existing) Azure SQL Database server for the data that Stretch Database migrates
- The database has to have a database master key
- Provide a credential for Stretch Database to use for communication
- Enable Stretch for a table
- Replicate the entire table
- Write an Inline Table-Valued Function to Select Rows to Migrate
The Stretch Function
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
- Create it
- Alter it
- Remove it
- Replace it (sort of)
- Pauze migration
- Remove a filter predicate from a table
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.
Reverting from StretchDB
To disable Stretch Database for a table, select Stretch for a table in SQL Server Management Studio. Then select one of the following options.
- Disable | Bring data back from Azure. Copy the remote data for the table from Azure SQL Database back to SQL Server, then disable Stretch Database for the table. (Be carefull because unmigrating your data incurs Azure data transfer charges)
- Disable | Leave data in Azure. Disable Stretch Database for the table. Abandon the remote data for the table in Azure SQL Database.
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.