This is the second of a two part series on the recently upgraded SQL Azure Management Portal. Click here to view Part I. You can also view a short video demonstration of the upgraded portal here. This blog post will cover the new Administration and Design Workspaces in detail including:
- Database creation and upgrade via DAC
- Query monitoring, query plans and query performance statistics
- Database schema and query designers
- Database dependency views
Database Creation Enhancements
This release of SQL Azure added support for larger databases. The largest database that you can create in SQL Azure is now up to 150 GB. Additionally we have also added support for choosing the collation for your database. The new database creation view shown below highlights these new features.
Figure 1. Create Database
Database Upgrade Enhancements
New administration functionality in the portal includes the ability to upgrade schema for the database using DAC technology. After selecting a database, choose the upgrade button in the ribbon which will walk you through a set of steps that will ask for the DAC package file to upgrade the database schema and data in place. This workflow will warn you of any changes in the schema since the last DAC was applied and also will warn of any potential data loss that might result from an upgrade. The page also allows you to view the T-SQL script that will be run as part of the upgrade, as well as save html reports that describe the upgrade.
Figure 2. Upgrade a database
Database Summary
You can quickly navigate to the database summary view in the Administration Workspace. Each database tile has an inline task that leads you there. The database summary page provides a snapshot of information about the database. This includes database properties like size, free space and active connections as well as a snapshot of CPU usage for queries for the past three months.
Figure 3. Database summary
Query Monitoring and Troubleshooting
Along with the database summary page there is another tab in this view that is the entry point for query troubleshooting in the portal. The query performance tab lists 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). You can also look at the graphical query plan for the query. The plan statistics, graphical details and the query text together provide you with enough information to perform some basic query plan performance troubleshooting.
Figure 4. Query performance
In the graphical plan details, you can zoom in and out to get to the relevant information about plan operators and clicking on any of the operators provides even more details. The control to the right of the graphical plan provides the ability to filter and highlight certain types of operators. Query hints provided by the SQL Azure engine show up as informational messages. Remember that these hints only consider the particular query in question, and that you should consider the suggestion in the context of the entire database workload before implementing the suggestion (for example adding an index will incur more cost to insert, update and delete queries in the workload, while improving select queries). The query editor window shown here is read only. Use the Edit ribbon button to open the same query in editable mode and you can make changes and compare an estimated plan with this plan snapshot.
Figure 5. Query plan
Design Workspace: Database Schema and Data
The Design Workspace has been enhanced with many new features. The Query editor now supports syntax colorization that highlights T-SQL keywords and strings similar to the SQL Server Management Studio query editor. The Query editor also adds new functionality to view actual or estimated query plans. Messages returned as part of running the T-SQL query, the result sets and the query plan are available in tabs. Clicking on any of the tabs (Results) brings up the information corresponding to the first query in the batch. Right above the result set is a drop down list that allows one to switch to result sets corresponding to other statements in the batch, if there were multiple T-SQL queries as part of the batch.
Figure 6. Query Editor & multiple result sets
The portal now has full support for all the data types supported in SQL Azure. For example, the portal displays spatial data in the spatial data tab in the results view which shows spatial data on a live Bing map. You can choose to view the map using different map backgrounds and you can also choose to show tool tips for each of the records in the spatial data set.
Figure 7. Spatial data support
The new index and foreign key management experience supports a drag and drop interface and also provides a better visualization of the index and keys.
Figure 8. Foreign key and index management
Dependency Viewer
One of the most requested features was to add the ability to view object dependencies within a database. We have now added that functionality to the portal. In the Design view selecting a database object like a table or stored procedure and then clicking on the dependencies task takes you to a view that shows the various dependencies to that particular schema object. The dependency graph view can be zoomed in and out to show more or fewer details. The zoomed view results in a tile for the object that has details about that object as well as actions inline like directly navigating to an edit view. Right on top of the view is a button that enables the view to show all object dependencies. This view is useful as a navigation tool and enables an experience where one can get a snapshot of the complex dependencies within the database.
Figure 9. Object dependency graph
Figure 10. Inline action
Figure 11. All dependencies
Federations
With the SQL Azure Q4 2011 Service Release we introduced a new feature to enable applications at massive scale. Federations bring the sharding design pattern to SQL Azure and the portal provides a very simple and easy management experience for federations. Federated root databases are listed in the Administration workspace under “Databases with Federations” and the database summary view highlights the number of federation members in a federation and enables quick navigation to the federated view where you can manage the federation member that has any kind of size pressure. For more details on this, please refer to the SQL Azure Federations blog by Cihan Biyikoglu.
Figure 12. Managing federation members
We will continue to listen to your feedback, and add many new requested capabilities in future upgrades. Please feel free to provide your feedback and feature requests. To submit a feature request or provide general feedback:
1. Navigate to https://connect.microsoft.com/SQLServer/Feedback.
2. You will be prompted to search our existing feedback to verify your issue has not already been submitted.
3. Once you verify that your issue has not been submitted, scroll down the page and click on the orange Submit Feedback button in the left-hand navigation bar.
4. On the Select Feedback form, click SQL Server Suggestion Form.
5. On the bug form, select Category = SQL Azure Database.
6. Complete your request.
7. Click Submit to send the form to Microsoft.
For community-based support, post a question to the SQL Azure MSDN forums. The product team will do its best to answer any questions posted there. If you have any questions about the feedback submission process or about accessing the portal, send us an e-mail message: sqlconne@microsoft.com.