Tuesday, November 1, 2016

Stretch Database in SQL Server 2016

Stretch Database is a Microsoft solution for securely migrating cold data to Azure with remote query processing abilities which does not require any change in your business application code and SQL data components. It provides the most significant benefit from the low cost of Azure rather than scaling expensive, on-premises storage. We have options to choose the pricing tier and configure settings in the Azure Portal to maintain control over price and costs as per our business needs and have the facility to scale up or down whenever it required.
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.
Seamlessly access SQL Server data regardless of whether it is on-premises or stretched to the cloud. We have  advanced security options those also work with Stretch Database and move it into Azure SQL Database in the cloud in a secure fashion.

With Stretch Database, backups for your on-premises data run faster and complete more easily than they did on the un-stretched data and easy to turn on Stretch Database from SQL Server Management studio. Use the Azure portal to configure additional settings and choose a performance level, scaling up or down as needed while maintaining control over cost.
Benefits of Stretch Database
It is easy to turn on Stretch Database from SQL Server Management studio. Use the Azure portal to configure additional settings and choose a performance level, scaling up or down as needed while maintaining control over cost. Stretch Database provides the following benefits:

  • Scale SQL Server 2016 using bottomless cloud storage
  • Make warm and cold data available to users at low cost
  • Access and query stretched data online
  • Move data easily—no query or application changes required
  • Use with advanced security features like Always Encrypted
  • Reduce maintenance and storage costs for on-premises data
What does Stretch Database work?
After you enable Stretch Database for a SQL Server instance, a database, and at least one table, Stretch Database silently begins to migrate your cold data to Azure. There are some conditions which are based on the data availability such as cold and hot data transaction.  

For an example, if we are storing our cold data and hot data in a separate tables then we can migrate the entire table.
In the other hand, if we are storing both hot and cold data in a single table then we can specify a filter function to select the rows to migrate.
In this way, we don't have to change existing data components such as procedures, function, SQL queries and client apps. We can 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 cold data.

Suitable data components for Stretch Database?
Stretch Database targets transactional databases with large amounts of cold data, typically stored in a small number of tables. These tables may contain more than a billion rows. Use Stretch Database Advisor, a feature of SQL Server 2016 Upgrade Advisor, to identify databases and tables for Stretch Database.


References: https://msdn.microsoft.com/en-us/library/dn935011.aspx

No comments:

Post a Comment

Popular Posts

Get Sponsored by Big Brands