One of the most important new features in the SQL Server 2016 release is the support for Stretch Databases. If you haven’t had a chance to keep up with the latest enhancements in SQL Server 2016, Stretch Databases refer to a feature that enables you to seamlessly extend a database from your on-premise SQL Server instance to Azure. Stretch Databases transparently migrate your historical data to Microsoft Azure. Unlike typical cold data storage, your historical data on Azure is always online and available to query. Stretch Databases can make your operations more efficient by allowing you to provide longer data retention times using low cost Azure storage without the need to scale up your more expensive on-premises storage.
So what situations are Stretch Databases good for? Stretch Database are definitely a feature you should look into if:
- You have large amounts of data that you need to keep on-line for a long time
- You need to query that historical data
- You want to save money on local storage requirements
- You can’t get your historical data tables backed up and restored within your allotted backup and recovery times
You can see an overview of how SQL Server 2016 Stretch Databases work in the illustration here.
In this figure, you can see that user queries are directed to your on-premise SQL Server instance. The Stretch Database feature has transparently moved a portion of the data from the on-premise instance to an Azure SQL Database. SQL Server 2016 will seamlessly retrieve both the local data as well as the remote data in response to the user query. There is some latency for remote queries, but this latency only happens when your queries need to access the remote data.
Microsoft provides an Enable Database for Stretch wizard that allows you to select the database tables that you want to extend to Azure. You can run the new wizard from SQL Server Management Studio (SSMS) by opening Object Explorer, and then selecting the database where you want to enable the Stretch Database feature. Right-click the database, select Tasks and then select Stretch to launch the wizard. You can choose to migrate an entire table or you can specify a date-based filter. You provide your Azure credentials and specify where the data is stored on Azure. The SQL Server 2016 Stretch Database feature will take care of the data movement in the background. The Stretch Database feature ensures that no data is lost even if there is failure during migration. It has retry logic that can handle connection issues. The table is always online and can be queried. You can pause data migration to troubleshoot problems on the local server or to maximize the available network bandwidth. A dynamic management view sys.dm_db_rda_migration_status provides the status of migration.
Stretch Databases are completely compatible with Transparent Data Encryption (TDE) as well as the new Always Encrypted and Row-level Security features. The Stretch Database feature does require a Microsoft Azure account. There is free trial and Azure subscriptions are based on the compute and storage that you actually use. You can learn more about SQL Server Stretch Database pricing and Azure at SQL Server Stretch Database Pricing.
Underwritten by HPE and Microsoft
