SQL Server 2016 – Identifying Tables to Stretch to Azure Cloud

Yesterday I covered what’s strecthing a database to cloud. Today I’m going to cover how to identify the tables which can stretch to Cloud. In most of the cases you knew which table we can move however if you are not sure when one then you can make use of this tool. I’m going to use one of the famous tool used in assessment before SQL Server upgrade to new version. Yes you are right, it’s SQL Server upgrade advisor!!

SQL Server 2016 upgrade advisor is revamped with a new GUI. Moving between the tabs works similar to the new Azure portal. SQL Server 2016 upgrade advisor will help you to choose the tables for stretching.

Pre-Requisite

• Install SQL Server 2016 Upgrade Advisor

Identifying the tables using SQL Server 2016 upgrade advisor

• Open SQL Server 2016 upgrade advisor. Current preview version is 1.2 which got released in September 2015, hope it will get upgraded during RTM release.

• In the top menu you can find Scenarios, just click on that you can find “Run Stretch Database Advisor”.

• Click on “Run Stretch Database Advisor” and then click on “Select Database to Analyze”
• Click on “SQL Instance”, it will pop up another tab.
• Now provide the SQL Server 2016 server name and click on “Connect”.

• It will list all the databses, just select the database you want to analyze and click on “Run”.

• Once the analysis gets over you will see similar screen like below.If you notice it analyzed 7 tables.

• In the right hand side you will find a graph and the list of tables which you can stretch it.
• If any of the table doesnt qualify for stretch, it will provide you the details as why. (Example, Replicated tables can’t participate in stretching).
• The graph provides you a basic details about the benefit you will be getting if you stretch it to cloud.
• For any smaller tables, the benefits are lower as the cost for Azure will be high compared to keeping the data in inhouse server itself.
• Just go through the data and pick the table based on the total benefit you will attain if you stretch it.
• It wont actually perform any changes but it will help you to identify the tables which can be stretched and what’s the benefit from it.