Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

New SQL Server Management Studio Azure Integration


By:   |   Last Updated: 2015-10-22   |   Comments (1)   |   Related Tips: More > SQL Server 2016

Problem

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.

Solution

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.

General

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

Database Properties General Page.

Options

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.

Permissions

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.

General

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

Table Properties General Page.

Permissions

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


Last Updated: 2015-10-22


next webcast button


next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 There's an "Azure SQL DB - missing features" list on the SSMS Trello board: https://trello.com/b/M9NmFPfv/sql-server-management-studio-2016-enhancements

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. 

 


Learn more about SQL Server tools