Select Page

StretchDB Got Easier With SQL Server 2016

Author: Eric Russo | | May 25, 2016

The world is moving towards the cloud and when I talk with decision makers around the world, there is one thing that comes up on top of the mind – how can I embrace the cloud adoption within my company. Yes, there are a select few who still love being on the on-premise infrastructure because of the investments they have already made. But that number is reducing faster than you can ever think off. I am amazed that the day for learning something completely new seems to be coming really faster than I really thought. I like it when customers push the boundaries of technology to achieve a larger objective.

Coming back to our topic of discussion for us. The next version of SQL Server is loaded with features and capabilities where the cloud technologies are playing a very important and vital role. This cannot be negated with the fact being this is evolving and changing to customer needs.

With SQL Server 2016, we have the luxury of integrating seamlessly with the Cloud offering of SQL Azure databases from our onpremise with the help of Stretch Database (or simply StretchDB). Here parts of your older data is moved seamlessly to the cloud (Azure SQLDB) based on how we configure things. This got simpler with the fact that we can enable this using the wizard with SQL Server Management Studio.

We have a number of ways to do this, let us use the simplest wizard format for this task. We can select the database -> right click -> Tasks -> Stretch -> Enable.

This starts the wizard and will take us through a number of steps wherein we need to feed some vital information from Azure account and the passwords for the setup to complete.

Creating a database master key is one of the important steps and make sure you remember the passwords being used. It is easy to get confused with the credentials being used for Azure and your on-premise default SQL Server instance. So keep track of this.

Once your firewall and IP addresses have been given access, the database is configured for stretch. At the instance level we can go ahead and use the TSQL command of doing the same with:

EXECsys.sp_configureN’remote data archive’,’1′;



As you can see, the same above steps are available on TSQL with Configure server/instance setting, Database master key, IP settings, enabling stretch at the DB level etc. Once done, we can go ahead and stretch our table to cloud seamlessly using a command as shown below:

ALTERTABLE [tblStretch]



If during the wizard you selected the table too, then the wizard will add another line item as shown below:

There is no point in adding a stretch if you are not able to track what data is moving and what is the current status of the data movement from various databases. This can be identified using the DMV as shown below:

–Check the migration status


StretchDB is a powerful feature and can enable new workloads to be configured with Azure SQLDBs seamlessly. Using the power of cloud and building such hybrid architectures with the comfort of working inside SQL Server Management Studio is the key to success as far as SQL Server 2016 is concerned. I am glad to see such innovations happening inside SQL Server 2016 and this is pushing the comfort zone of traditional DBA’s who need to learn these things every single day. Do let me know if you explored working with StretchDB in your organization. Are you interested in utilizing this hybrid approach for storing historical / archive data? What are your thoughts on this capability?

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.