Management Portal for Azure SQL Database


The Azure SQL Database provides multiple options to connect to the database. The Management Portal being one of them. In my last post, I had talked about what options the Object Explorer offers you for an Azure SQL Database.

Connecting using the Management Portal

The simplest way to connect to an Azure SQL database is to use the management portal. The management portal link will be https://&lt;servername>.database.windows.net/?langid=en-us#$database=<database name> for any Azure SQL Database. When you log onto the management portal, you will need to provide the details shown below in Screenshot 1. If you have multiple Azure SQL databases hosted on the same server, then it is recommended that you provide the database name as the USE command is prohibited for switching database connections.

image

Once you login into the Management Portal, you will be provided with three tabs:

1. Administration

2. Design

3. Overview

Out of these the Overview tab gives you a host of useful articles which you need to work with your Azure SQL Database.

Administration Tab

The Administration tab gives you a Summary (see Screenshot 2) view of your current database where you get to see when the database was created and it’s other key properties like space used, free space etc.

image

Query Performance

The Query Performance tab shows you the query plans that have the highest costs in various metrics such as CPU and I/O (the data is a snapshot of the query plan cache). You can sort these by various query performance metrics. Drilling down into the query details provides you the relevant details of that particular query (snapshot of information about the query plan and its statistics over its lifetime in the cache). Screenshot 3 gives you a view of what you will see in the Query Performance tab.

image

The Query Plan

When you click on any of the queries, you will be provided with a view similar to what you see in SQL Server Performance Dashboard when you drill through into the query details. The notification at the top shows you the Query Plan warnings if any (see Screenshot 4). This is a quick way to gauge if something requires immediate attention. Then you can have a look at the graphical query plan using the tab which gives you’re a boxed representation of each operator involved in the plan. The graphical query plan in the management portal is a bit different from what you are used to in the management studio.

The Query Plan Details view which is the default view when you click on a query in the Query Performance tab shows your various kinds of information. You get a table showing the resource usage w.r.t. the reads, execution count, writes, minimum and maximum values. This output is derived from the sys.dm_exe_query_stats DMV. Then you get additional information like the time when the plan was cached, plan handle and sql handle. If you want to find out similar queries and plans in the cache, then you also get the query hash and query plan hash.

image

The Query Editor

imageFinally, let’s talk about the Query Editor. This can be launched using the New Query (see Screenshot 5) option available at the top of the page.  This is the equivalent of opening up a new connection to your SQL Server database using the New Query option in Management Studio. The first thing that you will notice in the Query Window, is that a USE statement is not permitted. If you want to change connections to another database, you will have to establish a connection using the Management Portal for the new database and initiate a new query.

If you attempt to use the USE statement from a query window, you will be presented with the following error:

Msg 40508, Level 16, State 1, Line 1
USE statement is not supported to switch between databases. Use a new connection to connect to a different Database.

The query editor does provide you with the same kind of query writing interface that you are used to in Management Studio. But do keep in mind that F5 is not executing a query! It is for refreshing your web page! Since you are using a query editor on a webpage, this is the most common hurdle that I have learnt to jump!

You also have the option of retrieving the actual and estimated query plan using the buttons provided or you could use SET STATISTICS XML or SET SHOWPLAN_XML commands.

The above was a small attempt to provide a view of what is available in the Management Portal for the Azure SQL Database. More about Azure SQL Databases in future posts.

Reference:

Azure SQL Database General Guidelines and Limitations
http://msdn.microsoft.com/en-us/library/azure/ee336245.aspx

Azure SQL Database Tools and Utilities Support
http://msdn.microsoft.com/en-us/library/azure/ee621784.aspx#ssms

SQL Server Feature Limitations (Azure SQL Database)
http://msdn.microsoft.com/en-us/library/azure/ff394115.aspx

Advertisements

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s