Since SQL Server 2016 we could leverage Microsoft Azure to dynamically move “cold” portions of data away from on-premises storage for longer retention time periods. Whilst in theory being a great idea the cost was a blocker for some and with a cumbersome setup process. SQL Server 2019 addresses this by making the costs of storing the data in the cloud more competitive and making the setup more streamlined with the use of the Data Migration Assistant (DMA) tool and SSMS (SQL Server Management Studio).

Note: DMA tool replaces the older Upgrade Advisor tool. To install DMA please see the following link https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver15.

Easier Setup

Using DMA, it will ultimately state whether your database will benefit from this feature or not.

Once you have installed the application and created a new project where the source and target types have been set to SQL Server you would instruct the tool to carry out a “New Features Recommendation” analysis. Under the storage tab you will see what potential tables that could benefit from enabling stretch database whilst also considering some of the potential blockers that may exist, which is something that will obviously need to be addressed.

As you can see below the table Sales.Sales.OrderDetailEnlarged is ready for stretch database.

 

This is important to do because if you dont use tools such as DMA to carry out an analysis then if you try to enable a table for stretch you will see greyed-out options.

Setup

There are multiple ways to set this up now from using T-SQL to an in-built Wizard within SSMS. For simplicity purposes the wizard will be used to show you how quick and efficient setup really is.

Select enable (shown above) – then work through the wizard.

You will have the option of migrating the whole table to Azure or a selection of rows. For this example, I decided to move “older” data based on a date column where I wanted to move 2018 data to Azure only, a use case that is quite common in real world scenarios.

The setup process assumes that you have a relevant Azure account and have the correct IP addresses in place via a firewall rule.

Then you state where you want the data to reside.

Next the DMK (Database master key) is needed.

Next you will want to select an IP address range – rather than the public IP.

Results page shown below.

Once successful setup and enabled you may be wondering what has actually happened under the covers? The wizard has created a new Azure SQL Database within the server specified during setup which contains the “cold” data, i.e. our rows based on the year 2018 only.

The image below confirms a subset of the 2018 data only.

The below image shows the database created by the wizard is not a classic compute size for Azure SQL Database (DTU or vCore) but a specialised one for stretch databases in the form of DS100 (1.86GBP/month), you can independently scale this but for an extra cost.

Going back to the on-premises SQL Server (via SSMS) you will notice a different icon highlighting the fact that we are leveraging cloud services for the on-premises SQL Server database.

If you issue a query that touches the data based in Azure the execution plan will show this fact in the form of a remote query operator.

Not only have we moved “cold” data to cloud-based storage but we did not change application code to cater for this whilst also leveraging benefits of what Azure SQL Database offers such as TDE (Transparent Data Encryption). DBAs will be pleased to know that Microsoft Azure automatically backs up the remote data that Stretch Database has migrated from SQL Server to Azure.

The other benefit of using the wizard is that it also makes migrating off Azure easy thus disabling stretch database easy. This can be done by either bringing the data back from Azure or leaving it there.