By: Daniel Farina | Comments (1) | Related: > 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.
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.
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.
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.
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.
Change Tracking
Use this page to enable and configure Change Tracking for the Azure Database.
Permissions
You can assign Database Level permissions the same way you do with On Premise databases.
Query Store
This page lets you administer Query Store behavior. Of course, you must have an Azure SQL Database V12.
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.
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.
Change Tracking
This page shows the current configuration of Change Tracking and allows you to modify its status.
Security Predicates
In this page you can see the security predicates that reference the table and its status
Extended Properties
Personally I don't use this feature, but if you do this page will be useful.
Next Steps
- You can download a copy of SQL Server Management Studio 2016 for free from this link: https://msdn.microsoft.com/en-us/library/mt238290.aspx.
- If you want to know the benefits of this new version of SQL Server Management Studio, you can read my previous tip: New Features in SQL Server Management Studio for SQL Server 2016.
- For more tips about SQL Server Management Studio check out SQL Server Management Studio Tips Category.
- Stay tuned on SQL Server 2016 Tips Category for more information about this release of SQL Server.
- Also check out Azure Tips Category to read more Azure Database related tips.
About the author
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
