How Does Stretch Database Works in SQL Server 2016

SQLServerF1

New feature which was introduced with SQL Server 2016 is Stretch Database which migrates our historical data transparently and securely to the Microsoft Azure cloud. Stretch Database provides many benefits to the users, but also has its own limitations which make it less likely to be used as of now, unless Microsoft comes up with significant improvements. Some of the benefits to decide on using SQL Server 2016 Stretch Database feature are, Provides cost-effective availability for cold data(historical data which is not accessed much, but still available to support user queries from Azure SQL database). Using this feature does not require any changes to the applications, this feature takes care of it internally and transparently. Moving cold or not frequently used data to Azure SQL database will reduce the maintenance efforts on the production data like less times required for backups, indexing statistics updates, etc. Stretch Database in a SQL Server instance requires at least one table. It then silently begins to migrate the historical data to Azure SQL Database. If we are storing historical data in a separate table, then we can migrate the entire table. If our table contains both historical and current data, then we can specify a filter predicate to select the rows which need to be moved to Azure SQL database. Also, importantly, Stretch Database ensures that no data is lost if a failure occurs during migration. There is also retry logic to handle intermittent connection issues that may occur during migration.

Before deciding to this new feature in production environment, it is important to understand how does Stretch Database works and its behavior. First, to enable stretch database, we need to enable the instance level configuration option “remote data archive”, after which we will be able to Enable a Stretch Database for a database or table. Once we enable stretch database for atleast one database and one table, it will internally start to migrate our historical data to Azure SQL database. If the historical data is stored in a separate archive table already, then we can migrate the entire table, if table contains both historical and current data then we need to specify a filter predicate to select the rows which are to be migrated to the Azure SQL database. Some of the cool features with the Stretch Database is that it ensures that no data is lost in case a failure occurs during the data migration to Azure SQL database. It also has retry logic to handle connection issues that may occur during migration. A dynamic management view also provides us with the status of migration.

Also there us option to pause data migration to troubleshoot problems on the local server or to maximize the available network bandwidth or to migrate data only during non-business hours or during low activity period. Another cool thing about using stretch database is that we don’t have to change existing queries or client applications. 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 historical data.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings

 

New Stretch Database Feature in SQL Server 2016

SQLServerF1

Many new features gets introduced with each new version of SQL Server releases. Even with SQL Server 2016 many new features were introduced, one of which is Stretch database, which migrates our historical data transparently and securely to the Microsoft Azure cloud. Stretch Database provides some benefits to the users, but also has its own limitations which make it less likely to be used as of now, unless Microsoft comes up with significant improvements. Some of the benefits to decide on using SQL Server 2016 Stretch Database feature are, Provides cost-effective availability for cold data(historical data which is not accessed much, but still available to support user queries from Azure SQL database). Using this feature does not require any changes to the applications, this feature takes care of it internally and transparently. Moving cold or not frequently used data to Azure SQL database will reduce the maintenance efforts on the production data like less times required for backups, indexing statistics updates, etc. Stretch Database in a SQL Server instance requires at least one table. It then silently begins to migrate the historical data to Azure SQL Database. If we are storing historical data in a separate table, then we can migrate the entire table. If our table contains both historical and current data, then we can specify a filter predicate to select the rows which need to be moved to Azure SQL database. Also, importantly, Stretch Database ensures that no data is lost if a failure occurs during migration. There is also retry logic to handle intermittent connection issues that may occur during migration.

Stretch database will help Microsoft in making many of the customers to buy Azure SQL subscription. Following are more details regarding the benefits of using this new feature Stretch database in SQL Server 2016.
cost-effective availability for cold data – Stretch database feature allows us to transfer warm or cold data dynamically from SQL Server to Microsoft Azure SQL database and this entire process is transparent to end users, application developers and DBAs. Unlike typical cold data storage, our data will be always online and available to run queries against it. We can also specify data retention timelines to keep as much of data as required on the Azure SQL database and query it. Azure SQL databases are considered as less cost options compared to on-premise servers, so this will benefit using the low cost of Azure rather than scaling expensive, on-premises storage. We can choose the pricing tier and configure settings in the Azure Portal to maintain control over price and costs and we can scale up or down as needed.

No changes required to queries or applications – This is very important part of this feature. In traditional archiving plans, there are lot of changes required from the application side, but this is greatly reduced with stretch databases. Microsoft claims that there are no changes required at all with using this feature, because the data access to cold data on Azure SQL database is handled by SQL Server internally.
Reduced on-premises data maintenance – Because we are moving old data to Azure SQL database, we will have less amount of data on our on-premise databases, which will reduce the amount of time takes for tasks like backups, Index and statistics maintenance, etc. Also, the storage requirements on-premise will be greatly reduced this maintenance or storage will be reduced and can also use the additional storage available on-premise for other databases.

Hope this was helpful.

This is applicable for below versions of SQL Server

SQL Server 2016

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, Frequently asked questions, SQL Server Trainings