10 SQL Azure Gotchas
Another list! Honestly, I am perfectly capable of stringing words together into a semi-coherent self-contained piece with a beginning, middle and end, but this, I'm afraid is going to be another list. This one focusses on some of the differences you'll face if you're used to working with a traditional local SQL Server server or database and you're faced with working in the cloud for the first time. Don't be afraid, though: once you get used to it, SQL Azure (or Windows Azure SQL Database as we're somewhat tiresomely supposed to refer to it these days) rocks.
Without further ado, the list:
- Can't connect? Make sure your current external IP address is in the permitted list.
- It's perfectly possible to have a view or a stored procedure in a traditional SQL database that
refers to a non-existent SQL table or column. You can't run this view or SP successfully, but it'll sit there quite happily and be included in backups, restores, etc. However, the Azure Management Portal will report errors if you attempt to back up a database (export to BACPAC file) that includes such a view.
- Much the same happens when you try to upload a local database DACPAC to Azure. Views with external references (objects in other databases or on linked servers) aren't allowed and will need to be dropped before uploading.
- DACPAC upload doesn't like extended properties, either.
- Make sure the connection string or connection options for your application specify encrypted connections. Azure doesn't allow unencrypted connections.
- No database context switching is allowed in a script, so use of USE isn't permitted.
- The upload doesn't like a SQL Server 2012 database as source, either. (Note: I haven't tried this very recently, and I can't imagine in any case that it's a situation that'll continue very much longer even if it's still the case).
- Don't try uploading a DACPAC containing logins that don't (or can't - like Windows authentication AD
logins) exist on the Azure server. You'll be sorr-eeeee.
- Working with ADODB recordsets can be tricky; if Azure doesn't maintain the connection, your recordset object is suddenly null. There's a good article here on handling dropped connections in your application. My experience, however, is that connections are very stable in general, and when your application is dropping the connection there's usually a problem in the underlying data source or query. This is a topic I may revisit later, because it's a whole series of gotchas all on it's own.
- Tables with varchar/nvarchar(max) fields can't be reindexed online with the REBUILD WITH ONLINE option...but a simple index REORGANIZE isn't supported in Azure. Think about whether you really need those (MAX) length fields, or if you can use restricted-length fields instead.
< Posted 06.11.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:
- 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.
- 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.
- 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:
- 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.
- 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.
- 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.
- And finally...
A snappier name than "Windows Azure SQL Database". What, exactly was wrong with "SQL Azure"?
< Posted 30.05.2013