SQL Server Stretch Database to Azure

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 https://lnkd.in/edn6nyY/#AzureSQL

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

Benefits from SQL Server Stretch Database

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
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.
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
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
No Data Loss: most of your cold data will be hosted on Azure and this meaning ZERO loss of data.
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 https://azure.microsoft.com/en-us/pricing/details/sql-server-stretch-database/

How to Configure the SQL Server Stretch Database to Azure

You should have Azure SQL already configured on Azure portal for more information about how to Configure Azure SQL check this link https://mostafaelmasry.com/2020/05/20/azure-single-database-fundamental/
In my on-permies database, I have StackOverflow database I will move all the data in Postlinks table around 1.5 Million of record

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

Even this configuration you can allow any other IP from Azure port Firewall for more information about how to do it check this article https://docs.microsoft.com/en-us/azure/azure-sql/database/firewall-create-server-level-portal-quickstart
Finally, we are ready to Stretch the data to Azure

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