New SQL Server Management Studio Azure Integration

By:   |   Comments (1)   |   Related: > SQL Server 2016


SQL Server Management Studio for SQL Server 2016 adds enhanced support for Azure integration. In this tip we are going to take a look at these new features.


Every new release of SQL Server goes one step further into hybrid cloud scenarios by adding more features to Azure SQL Server Databases and the related cloud services. Usually when new features are included, we see those features reflected in SQL Server Management Studio. But there were some aspects that weren't taken into consideration on previous versions of SQL Server Management Studio. Although that wasn't a limiting factor, it made more differences between On Premise installations and Azure SQL Database more apparent. For instance, the contextual menus in Object Explorer for Azure Databases was very skimpy compared to On Premise SQL Servers.

In this new release of SQL Server, Microsoft made SQL Server Management Studio a product on its own and addressed the most requested enhancements. Let's take a closer look at those enhancements.

SQL Server Management Studio Database Diagrams

Now you can create Entity-Relationship Diagrams for Azure SQL Databases in SQL Server Management Studio without the need for an external tool. Of course, like with the On Premise versions of SQL Server, you are allowed to define new relations in the diagram and when you save the changes you made, they will be applied to your data model.

Database Diagram Editor on Azure SQL Databases.

SSMS Object Explorer Contextual Menus

If you right click on an Azure Database you will see two new items have been added to the contextual menu and also the Reports sub-menu is enabled in comparison with the previous version of SQL Server Management Studio.

  • Open in Management Portal...: Opens the Azure Management Portal in your default web browser.
  • Reports: Allows you to view the new report named Transaction Performance Analysis Overview.
  • Properties: Shows the new Database Properties Dialog. Further on I will give you more details on this topic.

In the next image, you can see a comparison of the Azure database contextual menu in SSMS for SQL Server 2016 versus SQL Server 2014.

Azure Database Conextual Menu Comparison with Previous SSMS Version.

Also when you right click on a table you will discover that the new contextual menu has new options. Let's enumerate those new, but already known menu items.

  • Design: Opens the Table Designer, now supported on Azure Databases.
  • Select Top 1000 Rows: Creates and executes a SELECT statement in a new query window.
  • Edit Top 200 Rows: Opens the Table Editor.
  • Full-Text Indexes: Gives you a graphical interface to create and administer Full-Text indexes.
  • Properties: Shows the new Table Properties Dialog. Further on I will give you more details on this topic.

The next image is a comparison of the Table Contextual Menu with the previous version of SQL Server Management Studio.

Azure Database Table Conextual Menu Comparison with Previous SSMS Version.

SSMS Database Properties Dialog

If you are new to Azure SQL Databases administration, something you will miss is the absence of the Database Properties Dialog in previous versions of SQL Server Management Studio. Now with this release of SQL Server Management Studio you can change database settings with a few clicks just like you do with On Premise databases. Let's take a look to the available property pages.


On this page you can see read only properties like available space, number of users and database collation.

Database Properties General Page.


Now things are starting to get interesting. If you take a look at the next screen capture you will see that there is an Azure section that allows you to change the database edition, the Service Level Objective and the maximum database size.

Database Properties Options Page.

Change Tracking

Use this page to enable and configure Change Tracking for the Azure Database.

Database Properties Change Tracking Page.


You can assign Database Level permissions the same way you do with On Premise databases.

Database Properties Permissions Page.

Query Store

This page lets you administer Query Store behavior. Of course, you must have an Azure SQL Database V12.

Database Properties Permissions Page.

SSMS Table Properties Dialog

Like with the Database Properties dialog, the introduction of this feature is very helpful when you need to configure Table Security and Change Tracking. This dialog is pretty much the same we are used to with On Premise databases, but there is a missing Property Page that I believe is very useful to quickly see the space usage for a table. I am referring to the Storage Page.


Here you can see table information like creation date and schema.

Table Properties General Page.


In this page you can assign table level permissions to users and roles. This is especially useful when you are implementing Row Level Security. Just in case you didn't know, Azure Database V12 supports Row Level Security.

Table Properties Permissions Page.

Change Tracking

This page shows the current configuration of Change Tracking and allows you to modify its status.

Table Properties Change Tracking Page.

Security Predicates

In this page you can see the security predicates that reference the table and its status

Table Properties Security Predicates Page.

Extended Properties

Personally I don't use this feature, but if you do this page will be useful.

Table Properties Extended Properties Page.
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, June 20, 2016 - 11:58:21 AM - David Shiflet Back To Top (41728)

 There's an "Azure SQL DB - missing features" list on the SSMS Trello board:

Add card on that list for anything related to Azure SQL DB that you feel is missing from SSMS that should be there, and vote. 


get free sql tips
agree to terms