Thursday, May 30, 2013

Azure Wish List

Microsoft really hit the bullseye with Windows Azure SQL Database, delivering an amazingly mature and feature-rich product practically from day one. It's not perfect, though; a number of functions and features that we've been using for years on local SQL Server installs are still missing, as well as a few items that would make Azure easier to use and a more attractive choice for new development work. Here's my wish list:
  1. Make it possible to use the Profiler

    The SQL Server Profiler is one of the most important tools in the database administrator/developer's toolbox, giving detailed, fine-grained realtime insight into database activity. It's unmissable for both performance analysis and tuning and debugging. Use of the tool on Azure databases isn't supported at the moment, and it's a big gap. There's good and useful information available from the online management application, but this needs to be real-time, filterable, sortable and configurable to match the power of SQL Profiler.

  2. Add Pricing analysis tools/reports

    Microsoft offer a number of different pricing plans for their cloud database service, but what they all boil down to is a fixed price per database per month, plus charges based on the amount of outgoing data traffic. What I miss, though, is a simple way to see which queries or database activity in a given database are generating the most billable traffic. In other words, which queries and commands are the most costly in terms of euros and cents rather than in the traditional SQL usage of "costly" meaning resource-intensive. It's relatively easy to find slow-performing queries and memory-intensive commands, for instance, but now we need ways to match recorded database activity to the charges we see appearing on the monthly invoices.

  3. Add the possibility to schedule database operations

    The ability to schedule tasks is unmissable. We don't necessarily need the sophistication of the Jobs functionality offered in traditional offline SQL Server installations, but the basic ability to schedule SQL commands at fixed times or outside production hours is sorely missed. Even the possibility to run command-line tasks like you can with the Windows Scheduler would already be a big improvement.

    These three items seem like "must-haves" to me. In the "nice-to-have" corner, the following:

  4. Make it easier to upload and convert a database.

    Converting an existing traditional offline SQL Server database for use on an Azure cloud database server is by no means impossible, but it's not as easy as it should be. Firstly, the "Extract Data-tier Application" wizard (that name alone!) stops at the first problem or Azure-incompatible usage it encounters rather than analyzing the source database for any and all potential issues at once. This means that arriving at a clean uploadable DACPAC (deployable database package) can involve starting and restarting the wizard many times, while fixing reported issues in between. Worse, the resulting package only contains database objects, no data, meaning that this needs to be scripted or uploaded separately. It's all do-able and these are not insuperable problems, but it should be possible to streamline the process.

  5. Make it possible to edit directly in SSMS

    With an offline traditional SQL Server installation, the SQL Server Management Studio tool we all know and love makes it easy to edit data directly in a table for those quick manual edits. Right-click on a table in the Object Explorer, "Edit Top 200 Rows", and Bob's your uncle. You can't do this with a table in an Azure database though. Stranger still, you can edit records directly in an Azure-hosted table using SQL Server Data Tools, so there doesn't appear to be any really good reason for preventing this in Management Studio. This is by no means an important drawback, but it is one that I encounter (and curse) frequently.

  6. Make it easier to switch database context

    Another minor but daily irritation is the necessity to disconnect a query and reconnect it when you want to switch between databases on the same cloud server, rather than simply selecting a different database from the Management Studio "Available Databases" drop-down. Again, there doesn't seem to be a good reason for this limitation: if you start a new query window in the master database, you can choose any available user database from the list, but once you've chosen, the only way to switch to another database is to disconnect your query.

  7. And finally...

    A snappier name than "Windows Azure SQL Database". What, exactly was wrong with "SQL Azure"?

Labels: , ,