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:

Database_Console

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.

Database_Firewall_AllowMSServices

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.

Database_Firewall_AllowIPRange

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.

Database_ConnectionString

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.

SSMS_ConnectToServer

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.

 

  • http://www.syfuhs.net/ Steve Syfuhs

    Oh wow…Thats pretty cool. I didn’t realize how in depth the control was for applications. Seems pretty straightforward too. Curious what the security implications are though. Excellent post!

  • Blair

    Excellent post! I have recently gone through pretty much the same process, and this is definitely EXTREMELY helpful. Thanks very much, Cory!