Stretch database is a new feature of SQL Server 2016 that enables us to migrate historical data to MS Azure Storage.
Beginning with SQL Server 2016, we will have the ability to store portions of a database in the MS Azure cloud. This feature will be useful to those needing to keep old data for long periods of time and those looking to save money on storage. Once we have enabled Stretch Database, it will silently migrate your data to an Azure SQL Database. Moving data to Azure cloud is secure.
If you need to access data in a 5 TB database and restoring that database would take valuable time. Furthermore, you may not need all of the data, which would make total database restoration and accessing data in that database complicated and expensive.Stretch DB allows a user to selectively migrate the data needed, saving users time and providing a cost-effective approach for working with archived data.
When we have to enable Stretch feature for a database ?
When a tables contains large amounts of historical data, it might benefit from enabling that table for stretch. Table with hundreds of millions or billions of rows with 90% of cold data(old data) that users need to maintain. However, most of the time, only 30% of hot data(Latest data) gets accessed but cold data also needs to be maintained even though they are not accessed frequently.
We might expect to have a single table with both hot(latest) and cold(old) data and you can enable the Stretch DB feature on that specific table by specifying a filter predicate to move only old data from that table to Azure SQL database.
When we have old data in azure storage and querying for latest data from local SQL Server the query execution time for that particular database will be fast.
- Storage of historical data in Azure SQL Database is Cheap
- Archived data remains online and they are query-able like any other table in local SQL Server database
- It has a inbuilt retry mechanism, which ensures no data is lost if a failure happens during data migration and does the data reconciliation automatically
- Reduced time for maintenance for our local data
- Reduced time for backup and restore of our local database
Querying from Azure Storage will take small amount of time that is almost negotiable. Very little Latency. We cannot run UPDATE or DELETE statements, or run CREATE INDEX or ALTER INDEX operations on a Stretch-enabled table.
Enabling Data Stretch in SQL Server :
In SSMS object explorer right click on the database and go to tasks. From that select “Enable database for Stretch”.
To enable Stretch for a table, we should have a azure cloud subscription.
After enabling a stretch feature it will create a SQL Server instance in Azure cloud like an on premise(Local SQL Server) SQL Server and it will create only a stretch table in that remote database.
Architecture of Stretch Database feature
SQL Server will migrate data to cloud by creating a Linked Server locally, which has a remote endpoint as target.
- Local Data – represents the data in local on-premise table(s), which are not supposed to move to Azure SQL Database or they are supposed to be in local on-premise table(s) only.
- Eligible Data – represents the data in local on-premise table(s), which are still to be migrated to Azure SQL Database based on configurations defined.
- Remote Data – represents migrated data in table(s) in Azure SQL Database in Microsoft Azure Cloud.
Got questions? Email us at: vembu-support@vembu.com for answers.
Follow our Twitter and Facebook feeds for new releases, updates, insightful posts and more.
