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

Azure databases

I've been working intensively with Windows Azure SQL for the past six months, and it's been tremendously interesting - exciting, even - to be in near the beginning of a technology that's maturing at such an astonishing speed. It feels similar to the sort of gold rush excitement that surrounded the web around 1994, when the possibilities of the web were only being hinted at. Who could have predicted then that we'd already live in a world of mobile internet, streaming video, and omnipresent social media?

Similiarly, it's difficult to predict all the uses to which Microsoft's cloud architecture solutions will be put in the next few years, but the impact is likely to be seismic. I smile when I see reports predicting Microsoft's demise just because Windows Phone hasn't taken off, or Windows 8, when it's obvious to me that what they're doing in the background with cloud engineering is where the real action is. It's difficult to overstate how impressive the various Azure services are, how well implemented, and the sheer value for money offered.

Just from the point of SQL databases, I'd be inclined to say that I will never willingly implement a physical SQL server again, or recommend the same to any client. The sheer ease, speed and cost-effectiveness of creating a new cloud database server just crush the limited benefits of a physical machine. The ability to fine-tune database files and queries to match the hardware of a physical database server remains, of course, but even this is a dying art these past few years since virtualization and SANs became commonplace. Who gets to install SQL Server on bare metal any more?

The learning curve is shallow. Most existing tools just work, and most existing applications that communicate with a traditional on-premises SQL Server database will do the same with an Azure SQL database without difficulty. The biggest change for me as a developer was reusing skills I'd learned long ago, when LAN bandwidth was limited and expensive; keeping data traffic lean and to-the-point, and pushing as much work as possible back to the (cloud) server rather than processing large data sets on the client workstation in code.

There are obviously a few things still missing. Top of my wishlist is the SQL Profiler, for instance, even though the online toolset is surprisingly thorough in the amount of data and metadata offered. I'd also like to see some sort of analysis tool that checks your existing offline database for potential issues and offers to fix them, or script the fixes. As is, deploying an existing database to Azure is a question of trial-and-error where the wizard fails a number of times, failing on each encountered problem until all issues are resolved.

Even with these caveats, though, it's an astonishingly mature platform. Last week, someone asked me if I had a USB stick and I had to think for a moment, because between SkyDrive and Google Drive I haven't copied files to physical media in an age. It's these changes, the ones you barely notice while they're happening, that are fundamental. Windows Azure services feels like one of these shifts to me, but on a far grander scale, and I'm really happy to be involved. I know what I'm going to be doing for the rest of my working career.

Slow Progress

I spent a frustrating morning with a client recently trying to use a Progress database as the source for an Integration Services package. The fix, as is so often the case, was simple, but I figured it was worthwhile documenting it here. I'm afraid that it's a more technical post than I usually put here, but if I can save someone else the same trouble we had then it'll be worthwhile.

The problem was as follows: we were trying to automate importing data from a Progress database to an MS SQL database using an SSIS package built on a 64-bit Windows 7 machine. We had an ODBC link set up (using the 32-bit OpenEdge drivers) that appeared to be working fine for the Import/Export Wizard and source previews and the likes - we could see and query data - but failed with ODBC error messages when run as an SSIS package from within the Business Intelligence Development Studio. "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" 

Google searching the problem wasn't very encouraging. We tried a dozen different workarounds, from editing the connection strings to bypass the ODBC DSN, registry hacks, trying to fake a 64-bit ODBC connection, but nothing helped. Then, as sometimes happens, I remembered a setting in BIDS that I'd seen a long time ago, never used and then forgot about.

In BIDS, select "Properties" from the "Project" menu. Select "Debugging" on the left-hand side of the Property Pages dialog, then change the value of Run64BitRuntime under "Debug Options" to "False". That simple. Once I'd done that, the import package ran like a train.

Read more »

Windows 8: Neither fish nor flesh?

Now that the Windows 8 Consumer Preview has arrived, we have a better idea of what Microsoft has in mind with the next version of the OS. I say "version", but this is quite obviously more than just a version, it's a radical break with the past at every level from the UI surface to the technical underpinnings.

The Developer Preview release seemed so far from finished that it was hard to draw any conclusions from it. The major impression it left was of the yawning gap between the new Metro interface and the old desktop metaphor, and the jarring jumps between the two. With the Consumer Preview release, this gap, while still present, seems to have been smoothed over to some degree.

It's very clear that the new UI is aimed squarely at tablets and notebooks with touchscreen interfaces. This is no bad thing, but it's difficult to pass judgement when testing on a traditional desktop PC. Using a mouse with the Metro UI definitely requires some adjustment, but is logical. The biggest single mental adjustment is in seeing the traditional Windows desktop reduced to a mere app. We've been trained since Windows 95 to view the Desktop as the root of all activity on our machines - if you start Windows Explorer, your computer, your network locations, the Control Panel and everything else appear as nodes of the desktop - and it requires a big mental shift to see it differently.

That's not to say that it doesn't make sense, though, especially considering what's going on behind the scenes. The old and trusted Win32 engine is still present for backwards-compatibility, but the new WinRT engine with the Metro UI on top is obviously the way forward. It's all of a piece with the WPF and XAML technologies that Microsoft have been promoting at developer events for years now, and now they're deeply rooted in an OS written from the ground up with them in mind. This is earth-shaking for developers (in the best sense, I think).

What does it mean for the rest of us? For the home user, Windows as a tablet OS may prove to be an attractive alternative to iOS, but as I say, without having the chance to try out the touch interface it's difficult to judge. It certainly looks extremely polished, and in both form and function makes the ancient iOS interface with its undistinguished uninformative rows of icons look very dated.

For the business user, it's more difficult to see the benefit in the absence of killer Metro-style apps. Windows 8 will support any application that runs on Win7, but Windows 7 is such a supremely capable OS that it's difficult to see the business benefit of upgrading. I'm inclined to think that businesses will only upgrade once a critical mass of useful Metro-style apps is available, and by the time these apps are available and offer functionality superior to the traditional equivalents we're probably looking at Windows 9...or further.

Which is not to say that Windows 8 is in any way a failure. This feels to me like the right move, the radical break with the past that Microsoft have been talking about since Longhorn, while backwards compatibility is crucially preserved for now by the inclusion of the Win32 engine. This is an architectural argument, however, and it remains to be seen whether the new OS will be a sales success. I certainly hope so; everyone (not just Windows users) will benefit from a strong and convincing bold step forward in the OS space. Microsoft are to be applauded for their daring.

Back to the drawing board

Basecamp is an exceptional online collaboration tool that I've been using for some years now to communicate with clients and co-workers. It's clean, simple and does exactly what you want and no more. The good people behind this most excellent product have launched a new mobile version this week for use on smartphones, tablets and the likes. It's written in HTML5 and it's quite astonishing.

I've been a little bit underwhelmed by HTML5 up to now. There are lots of pretty demos out there, for sure, but I didn't really see anything transformative until I called up the new Basecamp mobile site. The thing about it is, it's a website that doesn't behave like a website. It has all the features, look and feel, and convenience of a conventional mobile app, but it's all implemented in the browser and is compatible with all (HTML5-compliant) devices. It's light-years ahead of the effects you can realise with AJAX and the likes.

This, to me, feels huge. Not Basecamp itself, worthy as it is, but the potential it displays. It means we developers can create applications on the web that mirror or better the user experiences we offer on desktop applications, and we don't have to rewrite them for Android, or iOS, or desktop browsers. We're not beholden to the likes of Apple's piratical 30% cut of everything, and we can reach the widest possible group of users without compromising our interfaces and user experiences.

Time to get studying.

Surrogate vs. Natural Keys

There's no shortage of topics relating to data and data management to write about (only a shortage of time in which to do so!), and whenever a potentially interesting topic occurs to me I note it down with the intention of writing about it here. One such topic which I jotted down many months ago was the evergreen subject of Surrogate vs. Natural Keys, but I avoided it for a long time because it always arouses such passions (Yes! Database designers have feelings too!) and life is too short for arguments.

However, a recent and decent article on the subject on SQLTeam.com prompted me to finally break cover and nail my colours to the proverbial mast. What's the controversy about? Put simply, you always need a means of uniquely identifying each record in your data set, so each record needs a key.

A natural key is an identifier that has a "real-world" meaning, a functional value independent of its use in a database. So, if you have (for instance) a list of employees in a payroll database table, you could choose to use their Social Security number as a key. It's unique per person, and you have to record it anyway, so you might as well re-use it as your database table key, right?

It's certainly possible, yes, but in my opinion almost always a bad idea. Firstly, you'll need to record a (probably large/long and thus costly) digit sequence (or, worse still, string) throughout your database. The key will be used to identify the employee in pay history log tables, for instance, and department lists, and so on and so forth. More importantly though, because it's externally created and assigned, it's out of your control, so should the government ever decide to change the format or content of the number you're automatically forced to update your entire database structure and content, or risk the integrity of your data.

The answer then is to use a surrogate key. This is a purely technical key, used only by and for the database itself, probably invisible to the user and ideally automatically assigned by the system. It's unchanging, can (nearly always) be a simple integer for performance reasons and has no functional meaning in and of itself. It's merely a number, unique to the data entity, that is used throughout the database to denote a single record. It's my strong opinion that surrogate keys should be used by default for all data entities unless very good reasons exist not to do so.

What might those good reasons be? Well, one fairly obvious one is when data from a system needs to be interfaced with other systems. Perhaps you're in the fortunate position of being able to dictate standards for data management in your own company, but you may need to share data with suppliers or customers. They've all got their own systems with their own databases with their own set of keys, so you might choose to identify books by ISBN number to facilitate communication with your suppliers instead of using your own system-generated keys.

Opponents of surrogate keys also cite the abstractness of the data as a reason to choose natural keys instead. For instance, a "Countries of the World" table might use "FR" as a natural key for the "France" record, whereas a table with a surrogate key would theoretically allow two or more records to exist for "France", one with key 50 and the other with key 29. Because the numbers don't "mean" anything, it's harder to see that they refer to the same entity.

To my mind, though, this is a misuse of keys. Functional controls should be implemented as constraints or unique indexes, independent from the technical underpinnings of the database structure. The business meaning of data is obviously sacred, and every element of the design and development of the database should be aimed at preserving that meaning. This is best achieved, in this writer's humble opinion, by keeping the technical relationships of database objects strictly separate from the functional content of line-of-business data  by using surrogate keys.

To err is human

It's surprising how often database developers - or, at least, developers who are forced to develop a database - get really basic stuff wrong. Like choosing the correct data types, for instance.

I was asked to investigate a problem in an application database recently which, among other things, stored customer invoice details for the company. I was surprised to see that all the invoice line amounts, the VAT amounts, the discounts and so on were stored as float type data. I racked my brain to see if I could think of a good reason, but I couldn't. An invoice, and all the information on it, is a representation of real-world money, so you're never going to need anything more fine-grained than precision to two decimal places.

I can imagine, for instance, that you might have an Inventory table in your database, and you might record in it that each Yellow Widget weighs 0.023545 grams, or that the current sale price for each one is $0.1367, but by the time you're invoicing someone for 1256 of them, you're charging them $171.70 and not $171.6952.

We all make mistakes, though. Even in the future - check out this screen shot from The Terminator for another example of inappropriate data typing...

Cheat sheets

Or, to be more accurate, handy lists of SQL shortcuts, syntax, functions and other useful reference topics covering a multitude of common situations. And they're all here...

Credit where credit's due

Many IT professionals have an unfortunate tendency to look down their noses at MS Access databases, and to treat the product and the applications running on it as being beneath their notice. This is a shortsighted and unhelpful point of view, however, as rightly or wrongly many, many businesses are using Access to help them get work done.

As IT experts, that should always be our primary focus, too - how do we help people get work done? - so it's pointless to merely dismiss Access as being beneath our interest.

I'd go even further: Access is a valuable and worthy addition to your toolkit, provided you make the most of its strengths and are aware of its limitations. A tractor will not do 120 km/h on the motorway, but that doesn't mean it's a bad tractor.

Access is still pretty well unequalled as a truly rapid RAD environment. If you need to prototype something quickly to illustrate to a customer how a feature might work, Access is the way to go. Reporting (as long as you're focussed on paper-based traditional reports and documents) are supremely easy and flexible, and form building couldn't be simpler. Time-consuming standard tasks like record navigation, search and filtering are built-in, and VBA allows for complex control-of-flow and object manipulation behind the scenes.

As a pure data store, Access is at it's weakest in my opinion, but by linking an Access front-end to a SQL Server/SQL Server Express database, rich and powerful functionality connects to secure, fast and reliable data stores and you get the best of both worlds. Intensive calculations and operations can be carried out on the backend while the user enjoys the rich user interface features of the Access front-end.

Since the run-time engine has become available, there's not even the limitation that the customer needs MS Office Professional installed to use your application. Package your application with a copy of the Access runtime and they're good to go at no additional cost to them or you. What's easier and cheaper than free?

The price is right

You don’t get to be a world-dominating software juggernaut like Microsoft by just giving stuff away for free, so it’s little wonder that most of their most popular products are relatively expensive. Good value for money in most cases, but not cheap.

It’s fair to say, though, that there’s an astonishing amount of top-notch full-functioning software from Microsoft that’s available absolutely free. Microsoft themselves don’t always make a big song-and-dance about the fact, which is perfectly understandable – they’d rather you paid for full or professional versions of their products, but there are a number of tools and applications I’d like to draw your attention to. They’re all free, with no onerous licence restrictions and completely redistributable.

• My absolute favourite is SQL Server Express. This is nothing less than a very-slightly-limited version of MS SQL Server Standard edition, as used by businesses the world over. It’s 100% compatible with all the paid editions and supports databases (in the SQL 2008 iteration) of up to 4GB in size. It comes with a management interface application and makes it possible to do almost everything (as a developer) that you can do in the Standard edition. If your database or the performance demands upon it grow to such an extent that the (paid) Standard edition does become necessary, then upgrading the database is as painless as restoring a backup. It’s a winner.
  
  
• Second on the list is Visual Web Developer. This is a full-featured development environment for designing ASP.NET web sites and applications, and offers an identical experience to the (paid and relatively expensive) Visual Studio, as far as web development is concerned. It’s powerful and supports technologies like AJAX and full CSS. And it’s completely free.
  
  
• The last hidden gem I’d like to mention is the Access 2007 Access Runtime. This is a real boon to those of us who still have to ship Access applications. You want to work in Access 2007 yourself, but your customers may not yet have upgraded from their old Access version, or they may not have Access installed at all. The Runtime engine is the answer; you install it license-free on your client’s workstation PCs and they can start your Access 2007 solution without any other Office or Access installation needing to be present. As a side benefit, because they can’t change the application or create new databases with it, your app is partially protected by default (though you’d be crazy to rely solely on the fact).
  
  Another nice extra is the fact that you get a professional packager for your solutions, so you can provide your client with a proper SETUP.EXE with your own text, logos, etc and make his or her setup experience trouble-free. See, I’m giving away all my secrets here.

A lot of people think Microsoft-based solutions are automatically expensive, but a little research and investigation will bring you a long way without spending any money at all, and tools and platforms like these are always my default choice when I’m beginning new projects.

A penny saved is a penny earned, right?

Blogging about blogging

Setting up the 360Data blog was just phase one of the operation; "spare" time this week has been spent integrating the blog with the regular web site front page so that new posts appear immediately in the sidebar area without any manual intervention on my part.

This sounds like something that should be easy, but in practice turned out to be a little more challenging than expected, but very interesting. Configuring the blog's Atom feed was easy, and I reckoned that consuming it was just a question of finding someone's pre-cooked RSS reader control and dropping it in, but I couldn't find one that did everything I wanted and that worked properly with the Atom XML format.

What did I want? Nothing too fancy, in theory: just a sidebar element that would read the blog's feed and deliver the newest posts. I only wanted the two most recent posts to appear (otherwise the sidebar content became too long) and only the first few hundred or so characters of each post were necessary to give a flavour of the entry (but I didn't want to have text cut in mid-word). I also needed to display the publication date, the title and link the title back to the entire post.

Eventually, after several sidetracks and false starts I ended up using the plain-vanilla ASP.NET XML Data Source control to call the feed, parsing out the two newest items from the resulting XML document with XPath, and writing my own functions to reformat the publish date and to only show the first couple of lines for the post in the sidebar instead of the whole lot. So as a side benefit to improving the site's maintainability and currency I learned some new things about XML processing in ASP.NET and my new favourite control is the Repeater (until my web hosting provider supports the ListView, at least).

I can feel a full-scale technical article coming on...