As more people turn their attention to Microsoft’s cloud database SQL Azure and begin hosting applications there, one of the first things they’ll discover is that the service has at best a subset of SQL Server’s full functionality.
That subset is in some ways deeply restrictive -- so much so that people quickly find they can’t just upload their existing databases into Azure and fire them up. Applications written in SQL Server will almost certainly not work the same way in Azure, because there are a number of features that are partially or entirely missing.
Here are five commonly used SQL Server features that aren’t in SQL Azure and ways you can work around their absence. Note that Microsoft has not ruled out the possibility of adding these features to SQL Azure over time, but don’t bank on it.
For more on SQL Azure
Is the buzz behind SQL Azure just hype? Find out
Learn the ins and outs of SQL Azure development
Discover why managing SQL Azure database is close to no-fuss
SQL Server provides in-database data encryption through symmetric and asymmetric keys to protect sensitive data columns. Microsoft calls it Transparent Data Encryption, or TDE for short. I use this for things like password fields and email addresses, and while it’s not perfect, it makes it that much harder for sensitive data to casually leak out.
Unfortunately, SQL Azure doesn’t support TDE mechanisms in SQL Server. This isn’t to say that Azure has no cryptography support at all -- there’s an MSDN Magazine piece that discusses some of the technologies available (through the .NET libraries, by way of Azure’s software development kit) -- but they are not the same as what SQL Server itself supplies.
This means if you want to host data with encrypted columns in the SQL Azure database, the encryption-decryption functionality needs to be hosted in your application -- not in the data store. In some ways this isn’t a bad thing: Data sent from the database store to the application is encrypted in transit to the application, and the encryption mechanisms are separate from your data. If you’ve already built some of your application around TDE, however, you’ll have to wait until it’s supported in Azure, re-implement the same encryption in a different fashion or remove it entirely.
Full-text indexing and search
Another major missing feature is full-text indexing and search, useful even for people who aren’t storing gigabytes of data in SQL Azure. Full-text search drastically cuts down the amount of work needed to perform even trivial lookups, which helps all the more if you have a good deal of this sort of thing going on in parallel.
One possible solution is the Azure Library for Lucene.Net, which uses Lucene.NET (a .NET port of open source Lucene’s full-text indexing engine) to do the needed heavy lifting. Unfortunately, it’s not directly supported by Microsoft -- although it was written by a Microsoft engineer -- and implementing it most likely requires rewriting your application logic to some degree.
Common Language Runtime (CLR)
If you use CLR assemblies in your SQL Server database application, find a way around that as well. CLR isn’t supported in Azure, and neither are the CLR user-defined types (for instance, NEWSEQUENTIALID()), so any database that relies on CLR can’t be ported into Azure as is. You’ll either need to move the same logic out of the database and into your application -- with all the pain that implies -- or find in-database workarounds, such as using conventional functions as replacements for CLR creations.
Azure doesn’t allow the use of file groups simply because there’s no direct way to replicate their behavior in a cloud environment, where everything is virtualized anyway. Those of you who have relied on file groups for physical partitioning of data may appreciate this change (it’s one less thing to worry about) or resent it (it’s yet another hurdle to overcome before getting your data and logic migrated into Azure).
System tables, views and stored procedures
This is another omission that will either affect you a great deal or not at all. Most of the SQL Server logic I’ve seen that uses these functions exists because of some tight interaction with the system on which SQL Server is running in the first place. Move everything to Azure and those dependencies vanish -- and with them, the need to depend on the features.
That said, I can think of some very useful things that may break because of this -- for example, any tools that query the system catalog to retrieve the names of columns in a table (such as a meta-function or stored procedure). If you have anything like this in your database, find a way to do without it or move its functionality into your application logic.
One of the toughest things about dealing with these missing features is gambling on whether Microsoft is going to include them in SQL Azure. I believe that in the long run, all of the truly crucial things will be, while other things will have their functionality emulated, so they can be removed gracefully. But if you’re just moving to SQL Azure now, or starting anew in Azure, remember that what’s not available now may force you to do things differently than you would in SQL Server natively.
ABOUT THE AUTHOR
Serdar Yegulalp has been writing about computers and IT for more than 15 years for a variety of publications, including InformationWeek and Windows Magazine.
This was first published in February 2012