On Premises Storage Cost Troubling You? ‘Stretch’ to Azure based storage.

One of the features of SQL Server 2016 is the ability to the Stretch to the cloud at the touch of a few buttons. This could be very helpful when your on premises storage costs are burning a hole in your pocket. The stretched data is accessible without any downtime. The prerequisites are having a Azure SQL Server 2016 (PaaS) you can stretch to.

The stretch can be applied to any table/ tables in a database. You can move part of a table (based on a predicate) to the cloud. This ‘cold data’ even though physically located in another location (read Azure) is accessible and there is no change in the end user experience. It’s all seamless and SQL Server does all the hard work of fetching data from the remote server.

Once the data is stretched you can find execute the following queries:

EXEC sp_spaceused <Table_Name>,@mode = ‘LOCAL_ONLY’


EXEC sp_spaceused <Table_Name>,@mode = ‘REMOTE_ONLY’

The above two queries will give you a difference in the counts based on the predicate.

This is a great feature as I am sure customers would be more happier paying for cheaper cloud based storage than expensive Solid State disk storage. What is even better is one can turn ‘AlwaysEncrypted’ so that the data is encrypted while on the cloud.

What do you think of this feature? Please let me know if you want any help in configuring this. In case you have done this at a client do let me know of your experience.