SQL Azure development, inside and out

Learn the basics of SQL Azure development and how it’s different from regular SQL Server. Here also are guidelines for choosing the cloud as well as some of its limitations.

Last month, my article Getting Started with Microsoft SQL Azure Database gave an overview of SQL Azure and the basics to get you started on creating an account and evaluating this new way of hosting your databases. This next article in the SQL Azure series will cover the basics of SQL Azure development and what’s different from regular SQL Server. It will also describe guidelines for choosing the cloud as well as some limitations that could prevent you from using the cloud for high-performing applications.

SQL Azure technology is fully based on SQL Server, so the good news is that you can leverage your existing SQL Server skill set. The bad news, as I stated last month, is that SQL Azure is still in its first version, and you don’t get much beyond the database engine and not-totally complete T-SQL language. From that standpoint, your biggest obstacle is not so much learning feature details of SQL Azure; instead, you will have to do extensive research to learn what does not work in Azure from the technologies you use in your SQL Server development.

Once you’ve done your review and research, start re-architecting your application if needed and implement workarounds to implement the same functionality with what’s available in SQL Azure. It goes without saying that the more complex and feature-rich your database code and technology, the harder it will be to port to SQL Azure. You may even find that it’s not worth the effort for now and that you are better off waiting for the features you’ve been using to be implemented in SQL Azure.

I’ll mention several potential issues you may run into as a result of SQL Azure having only a limited feature set. If your solution uses multiple databases that talk to each other, either on the same server or even on another server, you will not be able to do that because cross-database access is not supported, and neither are distributed transactions. So in this case, you may have to merge your databases into a single database.  

Another issue could be using Service Broker. In that case, you may have to change the implementation to create a queuing table, insert a row there instead of sending a message to the broker queue, and use another mechanism to process messages in the table.

And there’s the CLR. Since it is not supported in SQL Azure, you would have to either limit the functionality of your application or implement the CLR stuff in T-SQL. It’s sometimes doable, but it could result in slower performance.

Also, if you use SMO in your applications, there is only limited support, so thoroughly test the entire application and make sure that whatever SMO objects are used by your application are supported by SQL Azure. You may also find it disappointing and limiting that SQL Azure does not support SQL Profiler at the moment. If you are used to this tool for troubleshooting and debugging, you will have to resort to PRINT statements, inserting data into tables and other techniques to work around this limitation.

On a similar note, you cannot debug your T-SQL code, either. I imagine these last two features are difficult to implement in SQL Azure, but let’s hope it won’t take long before they are added to the existing toolset. In the meantime, you might be better off keeping your development environment in SQL Server, do your profiling and debugging there, and then migrate your database objects, data and code to SQL Azure.

Remember that SQL Azure has resource throttling in place to make sure that a single resource intensive application does not affect the performance of other applications on the server. Your connection may be closed if the process uses extensive resources, if it has long-running queries or long-running single transactions or if the connection sits idle for a long time.

Given this, Microsoft recommends using TRY/CATCH to detect that a connection has been closed and includes code that implements retry logic to try again. While this is doable, this limitation is a clear red flag for us pointing out that SQL Azure is not, at least in this version, suitable for high-volume, mission-critical solutions. This throttling issue will likely decrease in future versions, but for now, it could be a showstopper for many companies.

Another thing to consider is your connection string. Any even moderately experienced developer knows that in SQL Server, Windows Authentication is highly preferred over SQL Authentication. There are several reasons for that, and one of the most important is that SQL Authentication requires either giving away passwords to users or storing them in a config file. Unfortunately, SQL Azure only supports SQL Authentication, so you have to use username and password to send to server. As a best practice, you should not put clear text passwords in the application config files of your SQL Azure applications. You could implement your own encryption/decryption code and read the encrypted password from the config file, decrypt it, generate a connection string and use it to connect to SQL Azure.

You have another option, though it is more complex and time-consuming to set up. You can follow this four part article on the SQL Azure Team Blog titled Securing Connection Strings in Windows Azure. The link brings you to part 1; for parts 2, 3 and 4, click on the Microsoft SQL Azure Team Blog for September 2010. This series of blogs will walk you through a set of steps to create a certificate in the Windows Azure Certificate Store and setting up SQL Azure and your config files to be able to transparently encrypt and decrypt your password.

Speaking of connection strings, your development portal has a handy feature that generates your connection string in the browser. In your Server Administration console, select your database and click on the Connection Strings button.

The application will display two connection strings, one for ADO.NET and the other for ODBC. Then place them in your application after changing the password to match what you are using. Here is an example of a generated connection string:

Server=tcp:auzxsdk8w8.database.windows.net;Database=AdventureWorks;User [email protected];Password=myPassword;Trusted_Connection=False;Encrypt=True;

Make sure you keep the Encrypt option to true because you don’t want your password to be sent to SQL Azure as clear text.

This article covered some of the differences between developing SQL Azure applications compared with regular SQL Server development. Although there are still quite a few restrictions and limitations, this gap should become narrower in future versions of SQL Azure.

Roman Rehak is a senior database architect at MyWebLink.com in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. Roman also serves as the technical chair for the SQL Server track at the annual DevTeach conferences in Canada and is the president of the Vermont SQL Server User Group.

Dig Deeper on SQL Server Migration Strategies and Planning