In this article we would explore the Stretch database feature present in Ms SQL Server in depth
Amongst the range of new features launched with the 2016 edition of SQL Server, the Stretch Database comes across as a remarkable option, which offers the users a solution for dealing with extremely large databases. This feature provides a ‘Dynamic stretch’ for the warm data (active data) and the cold data (archived data), to be stretched to Microsoft Azure – a cloud based storage platform exclusively for Microsoft SQL Server clients. This is used for migrating particular tables from large databases, so that restoring the whole database is not required. This saves lot of time and also keeps user data safe without making any changes in the application.
Working of Stretch Database
Stretch Database uses Azure cloud to store selected portions of data, and helps cut costs by reducing data storage and processing expenses. This forms the basic working principle of Stretch Database. Its primary function is to safely store tables or parts of tables in Azure SQL Database. While querying data, the query optimizer equally divides the workload to query tables on Azure as well as those on the server, the resulting rows of both queries are then delivered on network.
Data stored on Azure SQL Server can be accessed any time and the retrieved information can safely be transferred to another application, if required. The user can then decide which part of the data is supposed to be stretched say a single table perhaps. After making the selection, trickle data migration transfers data to Azure. The data can then be sent back to the original storage location, this exchange of data is transparent and bidirectional.
The characteristics of the data do not change during data exchange. Code, stored procedures and user access control also remains same. Thus the data integrity remains, continuing to enable staff to work with the data.
Benefits of Stretch Database
One of the greatest benefits of stretch database is acquiring hyperscale cloud capability and exploring new hybrid scenarios effectively facilitated with SQL Server 2016. In case of growth in the core transactional tables, due to archiving of historical data, for low cost database operations and maintenance of its performance, the user can still have access to warm and cold data using Stretch Database. This has multiple business advantages, as stretch database also has scope for configuration. All these operations done using Azure, lead to significantly reduced operational cost.
The user also has the option of using stretch database along with the new Always Encrypted feature for ensuring data security. This feature of Stretch Database can be applied to data at rest, as well as active data being transferred. Transparent Data Encryption is used to secure data. The keys involved are not shared either with the data system or the cloud service provider. This feature can also be used along Row level security.
There are multiple scenarios during which a company might choose to opt for Stretch Database; these include numerous data retention tasks like – ensuring regulatory compliance, auditing, or business planning.
Consider a recovery plan while working on a SQL Server database solution
If you are running a SQL Server solution, you should always have a detailed recovery plan in place. Apart from enforcing recovery options available in the software, you can also invest in a sql server recovery tool like DataNumen SQL Recovery application to ensure zero data loss. This tool can help you recover the data stored between the time of the last backup and the SQL crash which standard backup options will not be able to restore. It incidentally can also be called in to recover deleted data in quick time.
Author Introduction:
Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit https://www.datanumen.com/
Leave a Reply