Making data rain down from the Cloud with SQL Azure.

Hopefully you’ve had the chance to get a token [Check for a token here] to check out Microsoft’s Upcoming Cloud computing Platform Windows Azure. I’ll be posting some more information about Windows Azure in the coming weeks, but while i am creating a new website for Guelph Coffee and Code I thought I would bring you some information on connecting to SQL Azure in order to get your database up into the cloud.

Even though it is possible to connect to a database using SQL Server 2008, I’d suggest getting the Nov CTP for SQL Server 2008R2.  Just incase you don’t want to download a CTP here’s a hint as to how you can connect to your database with SQL Server 2008. The main reason I suggest SQL Server 2008 R2 is due to the fact that the Object Explorer of the current version of SQL Server is not compatible with SQL Azure.

Oh, I see you took my hint and downloaded R2, excellent choice. Now that you have your SQL Server Management Studio set up, lets take a look at some of the configurations that we will have to do in SQL Azure in order to connect to our database. [At the time of this blog post SQL Azure was available only to those who requested a token to access it. This post assumes you have access to SQL Azure.]  Here are the steps to take to get to the SQL Azure Cloud Configuration Console.

You should now be in an area that contains an interface much like this one:


As you see I’ve already Created a Database, it’s a very simple process where you define a name and a size [1GB or 10GB] and click on OK to create it.  In order to allow your Azure applications or to Manage your database using your SQL Server Management Studio you will have to allow connections through the Firewall, to change the firewall settings click on the ‘Firewall Settings’ tab.


In order for your Azure applications to be able to connect to your SQL Azure Database you will need to check the ‘Allow Microsoft Services access to this server’ checkbox. This adds the ‘MicrosoftServices’ Definition in the Rules grid.  If you don’t intend on using the SQL Azure database with a Windows Azure Application, keep the box unchecked.  To Allow you to access SQL Azure using SSMS you will have to Add a Rule.


The SQL Azure team did an excellent job making the Rules to be simple to configure.  Enter a name, which is a Label to Identify the IP Range you are allowing to connect.  Then specify an IP Range that you wish to allow access to this database.  You’ll notice that the application displays your IP Address, so if you only wish to add your IP simply copy and paste the IP address into both the Start and End textboxes. Then Click Submit to Save the changes. Repeat this step if you would like to be able to connect to your SQL Azure database from Multiple locations (Work, and Home).

One last piece of information you’ll need from the Azure Services site is the Connection String for your new Database. To get the Connection String, Navigate back to the Databases Tab. Select your new database from the list and click on the ‘Connection Strings’ button.


From here you can either select the entire Connection String and Copy using the Right Click context menu, or click on the ‘Copy to clipboard’ link (which may cause a Security pop-up in your browser) [Note: Choose the appropriate Connection String for your Application. I have only tested the ADO.NET Connection String in SSMS].  Paste your Connection String somewhere it is easy to work with, I prefer using ‘Sticky Notes’ a feature of Windows 7, but you can use notepad if you’re more inclined to do so.

If you haven’t done so Already, Fire up your installation for SQL Server 2008 R2 Management Studio. [The SQL Server instance in the cloud uses the TCP port 1433 so you will have to make sure that your Firewall will allow outbound traffic on TCP port 1433.]  As you normally would, Select Connect to Server from the Object Explorer.


Fill out the Server name, Login and Password information [Note: The Server name, and login are contained in your Connection String that you pasted safely into Sticky Notes or Notepad.]  Your password you would have set while accessing the SQL Azure Menu for the first time. At this point it gets a little bit different than your typical connection to the database. If you were to connect now, you will only have access to the ‘master’ database which you do not have the ability to add tables to, not to mention you’ve created a nice new database to house your data for your current application. Here’s the trick to make sure you connect to the proper database on your SQL Azure account:

  • Click on Options at the bottom of the Connect to Server Screen.
  • Select the Additional Connection Parameter Tab
  • Paste in your trusty connection string [waiting idly by in Sticky Notes]
  • Change the password in the Connection String from myPassword to your actual SQL Azure password
  • Hit the Connect button to Connect to the database.

Now that we’re up and running lets add a few tables.  Being a .NET Developer myself I know that one of the existing .NET Features I’m going to want to leverage will be the ASP.NET Membership Provider. After giving the command-line utility a try, I found out that SQL Azure does not support a number of Commands that are contained in the Utility.  This includes use [cannot select other databases in the cloud], GRANT your login cannot GRANT or DENY privileges on the tables or stored procedures, nor is it able to Query against the master table.

No I wouldn’t bring this up just to let you down, here is a work around. Open the Visual Studio 20008 Command Prompt, and type the following:

aspnet_regsql.exe -A mpr -sqlexportonly <filePath>

-A: Application Services Options

  • P – Profiles
  • M – Membership
  • R – Role Manager

Then Obviously replace <filePath> with a Path where you can Save the SQL Output like “C:\SQL\ASP_NET_MemberShip_Tables.sql”.

This will allow you to run the SQL file against the database in the SSMS Query window. It will throw warnings about the invalid keywords but the tables and stored procedures get created regardless.

I wish you luck with your Deployments to Windows Azure, Stay Tuned for more blog posts on Developing on the Windows Azure Platform.


Are you forgetting something?

For those of us that don’t have the ability to find synergy in our work life balance there is always alot running through our mind.  Personally, I know I have a responsibility to find a speaker on a monthly basis for CTTDNUG, try to think up ways to attract developers out to Coffee and Code, plus learning new technologies (Silverlight, Sharepoint, .NET 3.5, etc).  With all this on my mind on a regular basis floating around while i’m developing, I tend to get distracted from time to time while doing some tasks.

When dealing with Database code in particular I have found myself at a miss, when i try to remember if I have updated all of the Stored Procedures, Tables, and Functions when migrating or upgrading a system to a new server, or newer version.  When you’re under the gun to produce some results you don’t have the time to manually filter through the database to ensure that you’ve migrated all of the changes from your development database to the production server.

Recently I ran into such scenerio, I was updating an internal system that is used quite extensively at my job, and I knew the system couldn’t be down for very long.  Along the way a few of my ALTER SQL Scripts errored out for whatever reason in a bulk update and I gasped.  Quickly I ran off to Bing to find a tool to do a comparison between my development and production database.  I came across xSQL Object and it saved the day.  The UI was relatively easy to follow, all you had to do was add the databases you needed to compare, and run a comparison.  Having used other tools like this before the process is relatively straight forward. Where it differred was the speed of the software.  The comparison was a relatively small database but in a blink of an eye I was staring at a report of the differences in the two databases.

Needless to say I quickly figured out the Stored Procedures that didn’t get updated and was able to run the ALTER statements against the database to get things up and running again.

If you notice a difference between the two versions of an object, you are able to visually compare the SQL Code that has syntax highlighting as well it highlights the sections of code that are either missing or are extra between the two versions.

If you ever find yourself in this situation, I would definately suggest downloading the trial of xSQL Object which is a full 2 weeks. If you find yourself relying on this kind of functionality, the licensing options are available and are reasonable compared to other tools.  Plus they offer a discount if you are to buy a license during the first week of your trial period.

Go Forth and Query!