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"?
   

Wish I'd discovered this years ago

One of the tasks I'm most frequently confronted with is updating or converting database queries. These are very often formatted as a fairly indigestible single block of text with no line breaks, just one solid word-wrapped block of code. This is mostly the result of people using WYSIWYG query editors where the underlying query code is invisible. The first thing I do, then, is to try to tidy up the code to make it readable and get a sense of what the query is  supposed to accomplish, and how. This involves, among other things,  inserting line breaks so that each field gets its own line, and formatting joins so that the joined tables and columns are distinct and easily readable. As you can imagine, this can be an tedious process, particularly if you're doing it a lot.

After spending most of a day last week doing nothing else but tidying up and converting Access queries to SQL views, I got fed up with doing it by hand and did a bit of Googling, and discovered this brilliant trick. In SQL Management Studio (and any other Visual Studio-based editor) you can use the Find and Replace dialog to replace any character with line breaks. Simply tick the Use check box and select the "Regular Expressions" option, then use "\n" as the replacement string. In the example below, I replace each comma (which occur after each field name in a query SELECT list) with comma + line break (", \n"), click Replace All and half the work in reformatting my query is done. Replacing with tab characters is simply a case of using "\t" instead of "\n" (so replacing "JOIN " with "JOIN \t" begins tidying up the table join clauses).

It's obviously possible to do much more sophisticated replace actions with this capability (there's a good list here), but this is already a life-saver. Hallelujah!