Use Custom Reports to close the Azure SQL gap in SSMS

Database management tools, such as SQL Server Management Studio (SSMS) are mature products in daily use by database professionals around the world. This makes me wonder why Microsoft bothered producing their Silverlight-based Azure SQL Database Management Portal. I am assuming it is going to be deprecated because it is not available for Azure SQL Update V12 databases and the 'Manage' link has been dropped from the Azure Portal's dashboard page. I never found a use for it myself, but if you did then it can still be accessed directly using the Azure SQL server's web address.

The latest version of the Azure Management Portal is now encouraging you to use Visual Studio 2013, and more specifically SQL Server Data Tools (SSDT), to manage your database. I am a big fan of SSDT for designing and developing databases because it has allowed us to apply Microsoft's Application Lifecycle Management principles to database development.

Having said all that, you still can't beat SQL Server Management Studio (SSMS) for managing and administering your databases– or writing and debugging queries, for that matter. I still find myself jumping out of SSDT into SSMS to write my queries and T-SQL scripts.

The problem with SSMS is when you connect to an Azure SQL database, it becomes evident very quickly that it is a second-class citizen. I am not talking about the limitations inherent in the architecture of Azure SQL databases; I am talking about the SSMS tools that have been disabled because they were (presumably) not Azure-aware or compatible. I know Microsoft are closing these gaps and we will eventually have a seamless experience when switching between Azure SQL and SQL Server in SSMS. Until that happens, developers are left with an exaggerated sense of the differences between them. Two recent improvements have been the CU5 update for SQL Server 2014 which enabled some more dialogs and utilities for Azure SQL Databases in SSMS (more details), and Azure SQL Database Update V12 (more details), which added more dynamic management views (DMVs) and functions (DMFs).

I have done a quick comparison between the set of DMVs and DMFs in Azure SQL and Azure SQL V12. By my reckoning, the number of DMVs (and DMFs) has increased from 34 to 151. Once you exclude categories that are not applicable (such as Change Data Capture or AlwaysOn) then we now have near parity between SQL Server 2014 and Azure SQL. This is critical for performance tuning and troubleshooting. I was particularly pleased to see the inclusion of commonly used DMVs, such as 'sys.dm_io_virtual_file_stats', 'sys.dm_os_wait_stats', 'sys.dm_os_waiting_tasks'.

SQL Server database developers and administrators are used to switching between different versions of SQL Server and having to remember which features are available in which versions. For development purposes, I tend to regard Azure SQL Database as just another version of SQL Server, and I have been working with it long enough to know the differences and to know when SSMS is selling me short.

One recent frustration was expressed by a colleague, who simply wanted to check which database permissions had been assigned to a particular login in SSMS. With SQL Server, we could just display the login properties, which lists user mappings, and then display the mapped user's properties to get a list of explicit and effective permissions. With Azure SQL these object property dialogs are not available. So the plan was to create a custom report that would display all mapped users and their assigned permissions for a selected login.

SSMS custom reports are just locally stored Report Definition Language files (RDL), as used by SQL Server Reporting Services (SSRS) that take a prescribed set of input parameters. These provide the context server, database, object type, and object name (plus a few others). These reports connect to the relevant database and run queries against them to get the result sets. Provided you have the right permissions it makes no difference whether you are connecting to SQL Server or Azure SQL. To demonstrate this point I took some of the standard SSMS report RDL files, stored them in my SSMS custom report folder, selected an Azure database, and ran the report. In practice I had to select the Tables folder rather than the Database object because it was the first node in Object Explorer that had the 'Reports' context menu item enabled. But it worked fine as you can see from the following screenshot.

The first challenge for the Login custom report was the fact that Azure SQL does not support cross-database queries, so I couldn’t query all the related principals and securables in the other databases. The solution to this was to take advantage of expressions in properties to allow dynamic connection strings that are resolved at run time. This allowed me to query the list of databases and pass the database name as a parameter (together with the login SID) to a subreport embedded in a list. This subreport could then dynamically connect to the database and query the permissions for database users mapped to that login. As a tip, when dealing with SIDs (or binary values) that need to be converted to and from strings, use 2 as the style argument value with the CONVERT function to strip off the leading ‘0x’.

The next challenge was the fact that custom reports in SSMS do not support subreports, so I had to change the embedded subreport to a linked report. This means that the report user has to drill into each database in turn. The solution, as shown in the following screenshot, is not perfect but it provided the functionality that was needed.

As a final tip, once you have a custom report you can quickly create a variation on it by directly editing the RDL file and changing the SQL query, which will be stored in the CommandText attribute for the Data Set.