SQL Azure

When I read that all I would need to do to use SQL Azure and have a database in the cloud for my SQL Server based applications was change the connection string, it seemed to good to be true. There's always a gotcha somewhere.

For those of you who don't know, the connection string is the information that a database driver uses to know the names of the server and database to connect to as well as how the user will authenticate with the database. So what that article was saying was that all I needed to do was create a SQL Azure database, change the connection string and voila ConvallisCRM would be able to store it's data in the cloud.

But that's not a claim I'd like to make without testing it first, so that's what I did. I created an account and a SQL Azure database and then hit the gotcha. You see while SQL Azure is based upon SQL Server 2008, it implements only a subset of the full SQL Server feature set, there is quite a bit that isn't as yet supported, such as full text indexes.

However, that wasn't a big problem because you can connect to SQL Azure using SQL Server Management Studio 2008 R2, and like all versions of SSMS that comes with a handy wizard that generates your database creation scripts for you. Since Microsoft want developers to adopt Azure and encourage their customers to use it, they had added a handy setting in the properties of the wizard which means that you can target the script for SQL Azure rather than SQL Server 2008. That meant I didn't have to adapt an existing script to get it to run.

So it ended up being as easy as running the 'Generate Scripts' wizard for a second time and then running the resulting script against the newly created database in SQL Azure. Handily the online SQL Azure management tool also generates the connection string that you need for you, so all I had to was replace the existing one with the new one.

Or so you would think, but ConvallisCRM doesn't store it's connection string in a configuration file as that isn't very user friendly, we have a wizard that the application runs if it can't find its database connection details. All the wizard does is get the details from the user (doing it's best to guess what they might be if it can) and then some behind the scenes code constructs the connection string. So I ran the wizard for my own copy of ConvallisCRM, changed the server name to the quite frankly ugly URI provided by Azure, changed the authentication method to 'SQL Server Authentication' and put in my user details.

Having done all that I double clicked the ConvallisCRM icon on my desktop and ...... it worked, no fuss, no errors it just worked! The only noticeable difference was a delay of a second or so when data was fetched or saved to the database, but that's a small price to pay for the utility that this technology gives a customer.

By hosting the database on SQL Azure, ConvallisCRM can be used anywhere, in the office, on the road or at home. If you don't happen to have a Windows PC or laptop to hand then that isn't the show stopper that it might seem, because the web based version of ConvallisCRM uses exactly the same database (in fact it shares a lot of the same code) and can thus also connect to the same SQL Azure database. That means that if all you have with you is your mobile phone, so long as you can remember your login details then you can access your ConvallisCRM data on the web using your phone instead!