One of the interesting feature announced in SQL Server 2016 and later to move your cold data to Azure and the data will be accessible from your APP with ZERO development cost no change in the APP level at all and no change on the Query level in SQL Server it is just configuration and implementation for Stretch Database from on-premises to Azure, IF you are DBA and you have tables with big size and you need to reduce it, if you have Cold data and the APP not accessing it frequently, If you need to reduce the backup and restore time at this time this feature for you.

Before starting on the benefits of this feature and How to implement it I highly recommend you to take a look into this page to learn more about Azure SQL

  • Benefits from SQL Server Stretch Database
  • How to Configure the SQL Server Stretch Database to Azure
  • Validate the Database Stretch configuration and Data movement
  • How to Disable the Stretch data
  • References
  • Keep Following
  • Cloud Tech Website blog survey
SQL Server Stretch Database

Benefits from SQL Server Stretch Database

  1. Reducing the cost: With SQL Server Stretch feature the data will be always online and accessible from Query and APP this means we can take benefits from this feature by moving the cold data from on-permies to Azure that is costing us big size on our database in on-premises, instead of increasing the size on the on-premises server we can move it to Azure and reduce the cost
  2. Reduce Backup and maintenance plan window: After moving the cold data to Azure the database size will be reduced and the backup will be faster and any other maintenance plan will be faster also.
  3. Fast Migration: while migrating the database it will be faster because the database size reduced and after the migration, you can enable the SQL Server Stretch Database to connect to your cold data on Azure
  4. Securing the data: when you move the cold date to Azure it will be more securable because on Azure we have Always on Encrypted, Row-level security and advanced SQL Server features 
  5. No Data Loss: most of your cold data will be hosted on Azure and this meaning ZERO loss of data.
  6. Reduce the History of Temporal table: if you are using Temporal table feature on your database you can move the history data of the temporal table to Azure using this feature

With SQL Server Stretch Database you can move all data in a table or specific data inside the table using T-SQL and for more information about  SQL Server Stretch Database Pricing  Check this link

How to Configure the SQL Server Stretch Database to Azure

  • Right Click on the database > Tasks > Stretch then Enable
  • SQL Server will do Evaluating for the tables to chek is it synced with this feature and can be moved to Azure or no
  • Select the tables you need to Stretch it to Azure I will select Postlinks table only in my demo
  • In case if you need to move certain data from PostLinks table you should click on Entire table to write your T-SQL query to select the data you need to move it
  • Then the SQL Server will ask you to connect the Azure SQL Existing server or create a new one
  • After this, you need to write a new password for master key
  • Then you can enter the Subnet of IP that you need them to access Azure SQL or you can select the source of SQL Server IP only
  • Few minutes the Database stretch enabled and the data now moved to Azure

Validate the Database Stretch configuration and Data movement

After the configuration completed you can find now the changes on the database and you can find new External data source created

And when you login to Azure SQL you will find new database created and it contains the table of Postlinks and the data now under the movement process because if you try to select the count from the table on Azure you will find the number of the rows as changing from time to time this means the moving process still working

And you can validate this count also using the build-in Stretch data monitoring dashboard ( Right Click on database > tasks > Stretch > Monitor you will find the number of rows in Azure and this number is auto-refresh

Even from the potion of View Stretch database Health Events, you can check the events using the Extended events

How to Disable the Stretch data

  • On your table, you do the stretch on it right-click and select Disable and you have the choice here to keep the data moved to Azure in Azure or return it to your local database from Azure
  • Then Right Click on the database select tasks and Stretch then disable.


Keep Following

Cloud Tech Website blog survey

IF you found this blog is helpful and sharing useful content please take few second to do rate the website blog from here

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.