SQL Server 2016 Stretch Database
Stretch Database, a new feature in SQL Server 2016 often called StretchDB, lets companies “stretch” their databases to store infrequently used data in the cloud while keeping heavily used data on-premises.
The Stretch Database feature securely and transparently archives your cold or historical data from a local SQL Server database to Azure SQL Database (the SQL Database service in Microsoft Azure Cloud is provided as a Platform as a Service [PaaS]) with remote query processing capability. Once you enable this feature for a table, SQL Server silently and transparently moves/migrates data to Azure SQL Database. These are some of the benefits of using this feature:
- Secure and transparent movement of cold or historical data without writing a data movement module; makes local queries and other database operations run faster as they have to work on hot data or local data most of the time
- Storage of cold data in Azure SQL Database is cost effective; that is, there is reduced cost and complexity in keeping cold data online in Azure SQL Database
- Archived data remains online and they are query-able like any other table in local SQL Server database
- No application change is required to access these archived tables or data; You can still have a single query accessing these two types of data or tables at a given time or in the same query
- You can pause data migration to troubleshoot any issues (or to minimize load on network bandwidth) and resume it once you are done
- 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 your local data (re-index, performance tuning etc.)
- Reduced time for backup and restore of your local database as it deals with only hot data
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.
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.
How To Enable Data Stretch in SQL Server 2016?
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.
Best and Cheap SQL Server 2016 Hosting
To help you find the very best Windows hosting provider that fully assistance SQL Server 2016, we’ll give you our recommendation. Following we experienced reviewed 50+ providers, we found that HostForLIFE.eu is one of the most effective. They have specialist who usually ready to help your difficulty, you also won’t get any issue with 99.9% uptime.