With such a bombastic title, anyone will wonder about what I am going to write, but of course, in the same sentence there is a good clue, my much appreciated SQL Server. Mention that this feature is only available from version 2016 and running on Windows machine.
After the brief introduction is over, let’s start by talking about what SQL Server Stretch is and what you could use it for. Well, as we know, the movement to the Cloud is a process in which many organizations are immersed. So far, nothing new except in the case that your move to the cloud is being carried out in a smooth way, in which resources coexist both on-prem and in the Cloud. This is usually very common, for example in the case of databases, so many times, you have to look for synchronization formulas between both worlds to avoid the loss of information or outdated.
To be sure, there are many ways to articulate solutions to properly handle this, but as I want to present SQL Server Stretch today, he will be our protagonist. So let’s get started.
Initially I will download the Adventure Works sample database in its light version, you can download it from the link . But before that, you must know the version of the SQL server that you have, that is executed with a simple SELECT @@ version.
Once downloaded, we proceed to its “restoration” following the example below.
We already have the database, now we only have to enable SQL Server Stretch both on the server and in the different tables to be configured. To do this, we must include the following code.
We run the script and wait for the response.
The next step is to configure the SQL Server Stretch service in the database, which in our case is AdventureWorksLT2016 by executing the following SQL script.
Now what you have to do is create a new credential for the AdventureWorksLT2016 database using the following script.
NOTE : The identity name used in the creation of the credential must have owner permissions in the Azure SQL Database.
We continue with the configuration of the Stretch Database, it would only be necessary to link the on-premises database with the Azure server. This is done by the following script.
Once you get here, you just have to enable SQL Server Stretch at the table level. To do this, we select SalesLT.Address. When it is selected, click on the right button and the drop-down menu will appear, now we only have to click on Stretch and again on Enable.
When you press, the following screen will appear, where unfortunately it tells us that we cannot activate the function. Why? Well, because SQL Stretch has some limitations, which can be consulted here.
In our eagerness to complete the video we decided to create a new table with the name of SalesLT.Address2 without the previous limitations. As well as including a minimum set of data in it, highlighting the ModifiedDate field that will be the one that we are going to use as a filter to move the data prior to August 1, 2021 to our Azure SQL Database.
To do this, we click on Stretch again and this time, the table will be selectable and we will decide that we are not going to move the entire table to Azure, but only the data prior to our first of August. Click on Next.
We move forward and the summary is shown, we check that everything is correct and click on Finish.
And finally the configuration process is executed, which usually takes time, we will wait for it to finish.
Achieved, the cool data that for us will be those prior to August 1, 2021 will be moved to Azure.
Now it only remains to check the time spent in the execution of a simple query that returns all the data from both address tables. We see that the original with 450 records located 100% on-premise does not reach the second compared to the almost 4 that it takes to return the information of the eight records of our Stretch Database. Normal has to go to Azure, take the four migrated records and come back to paste them with the other four that are on-premises.
Well, finally to check, we go to our Azure subscription and there is our brand new Stretch Database .
Saving the serious problem of limitations such as FK, default values, field types, indexes, etc., in order to be a selectable table, the service is really interesting, since it allows you to ignore data movements. While it is true that in my case I prefer some other ways of moving the cool data, but for taste the colors
About Alberto Alonso Marcos
My name is Alberto Alonso. Actually I work with Sogeti Spain in Business Intelligence Department with Microsoft Technologies. My profile is very orientated to customer, and how the DATA can improve the organization. My first steps in the data management were in the Pharmaceutical Sector. (I´m pharmaceutical too). I worked hard to extract and built procedures for gathering all the information across the organization. Measurement all kind of events. Aggregating different sources like ERP, LIMS, HVAC, OEE tools, and productivity machine reports.
More on Alberto Alonso Marcos.