Thursday , June 13 2024

What is Stretch Database in SQL Server

What is Stretch Database?

If you want to archive your old data to Azure, all you need is StretchDatabase. You can then query as you wish your old data from Azure.

With Stretch Database, you can reduce your disk based costs, backup costs and maintenance costs by reducing your data to grow much on your expensive systems and shorten your backup restore times.

Most importantly, you can query the whole table in the same way without making any changes within the application.

If data is on the Azure, it will be transmitted to the application via Azure, and if data is on your own systems it will be transmitted from your own systems to the application. The control of these operations will be handled by SQL Server.

There are many restrictions on this feature as of now. But I think many of these restrictions will be reduced after a few releases.

Stretch Database Limitations


Create a table with the following script

Then we run the following script for 30 seconds and add some records to our table. The value of the DateInfo column is a random value between two specified dates each time.
Assume that we will archive the data before 2015-01-01 to azure using the stretch database feature. First , find the number of records we have inserted before 2015-01-01 with the help of the script below and note this number anywhere.

First, we enable Stretch Database at Instance level with the following script.

Then right click on the database and click on Tasks->Stretch->Enable.

On the screen that appears, we select our table to archive.

We see EntireTable in the Migrate section. We don’t want to archive the whole table. We just want to archive the data before the 2015-01-01 in our table. That’s why we click EntireTable.

In the screen that appears, click on Choose Rows as follows.

We give a name from Name section.

We select our column named DateInfo from the Where section.

Select “<” from the middle filter section and enter the date we want to archive in the Value section and click Check. Once you see Success, we’re done by clicking Done and Next.

We need to enter your Azure account on the next screen. Click Sign In….

After you sign in with your Azure account, you should see a screen like the one below.

In this screen, I click Existing server to use the server that I created with SQL Database on Azure. See “How To Create SQL Database on Azure”.

You can create a new server on this screen by clicking Create new server.

The next screen says that a master key will be created to protect our database credentials. We are writing a password for this master key.

On the next screen, we determine the IP or IP list that Azure will accept when accessing Azure from SQL Server.

If you want to access from a single IP select “Use source SQL Server Public IP”
If you want to access from an IP Range select “Use subnet IP range”

Microsoft recommends that we use an IP range. I choose the first option because I’m testing from my own machine.

Finally, we see a screen like the following. Click Finish to complete the process.

You can learn the number of rows in local and azure by running the script below.

Disable Stretch Database

If you want to Disable the Stretch Database structure, Disable option will appear in the Enable section.

If you select “Bring Data Back From Azure” in this section, your data in Azure will be returned to your table.

Or, if you select “Leave Data In Azure,” you’ll leave your data in Azure.

You can access other Azure related articles by typing Azure in the Search section of our site.


About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *