MSSQL : Identifying compatibility issues when implementing Stretched Databases

Introduction

Ever heard of Microsoft SQL Stretch Database? No… You should really check it out! Basically you can extend your OnPremise database towards Azure. By doing this, you can offload cold data whilst still being able to query it if needed.

microsoft-mssql-stretched-database

How does it look in reality?

Enabling it is quite easy… In the SMMS, just right click on the database. Select “Tasks”, “Stretch” and “Enable”.

2016-07-04 14_47_33-sqllocal - 40.114.136.86_3389 - Remote Desktop Connection

Now run through the wizard and you are set! Please do remember that this feature isn’t exactly cheap

2016-07-04 14_33_36-sqllocal - 40.114.136.86_3389 - Remote Desktop Connection

And afterwards you can follow-up your state via monitoring.

2016-07-04 14_40_26-sqllocal - 40.114.136.86_3389 - Remote Desktop Connection

 

Compatibility issues

There are quite some limitations when using this feature. Be sure to check them out before embarking on your journey! If you don’t do this, you’ll end up seeing a screen like this… Where all, or a big majority of, the tables have been grayed out.

2016-07-04 16_17_44-sqllocal - 40.114.136.86_3389 - Remote Desktop Connection

The nice icon indicates that the tables are not compatible with “Stretch Database”…

 

Proactively identifying those compatibility issues

If you want to identify those compatibility issues beforehand, I would suggest you run the “Stretch Database Advisor” from the “Upgrade Advisor“-tool. This will analyze your database and identify compatibility issues!

2016-07-04 16_46_33-sqllocal - 40.114.136.86_3389 - Remote Desktop Connection

Otherwise you can keep on guessing what went wrong… šŸ˜‰

 

TL;DR

  • Stretch Database is a hybrid way that enables you to offload cold data to Azure
  • This implementation has its limitations
  • Do a compatibility test beforehand by using the “Stretch Database Advisor”

2 thoughts on “MSSQL : Identifying compatibility issues when implementing Stretched Databases

  1. Does it use DocumentDB under the hood as referential integrity is not supported?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.