So after the long and torturous wait, you're now in the SQL Azure CTP and you are ready to get the ball rolling with your fabulous, shiny new cloud-based SQL database server. Now what? Well, the first thing you're going to need to do is create a database.

To do that, you'll go to sql.azure.com and follow the directions to sign in - if you haven't already supplied the invite key you'll need to supply it after you sign in the first time. Select your project and click on it. At this point you'll see a pretty sparse management screen with two tabs: databases and firewall settings. On the database tab, create a new database (its up to you whether you create a 1GB or a 10GB max database). After you've created it, you'll be able to click the Connection Strings button to get an ADO.NET connection string for the database as well as an ODBC connection string.

That's great, but how do you manage the schema? Can you just fire up SQL Server Management Studio (SSMS) and right-click on the "Tables" node and create new tables the old fashioned way? Unfortunately, no. You cannot connect to SQL Azure directly with SSMS the way you can to a regular SQL Server instance.

To connect to SQL Azure with SSMS:

1. Open SSMS (this part should be pretty obvious)
2. When you are prompted to connect to a database, cancel that dialog.
3. You should be left with an empty management console. Click New Query.
4. For the server name, take the fully qualified host name from your SQL Azure connection string. It should look something like (blah).database.windows.net.
5. Choose SQL Server Authentication. Supply the username and password that you used for your database. Parts of this information should also be visible on your connection string.
6. Click on the Options button on the bottom right of the dialog box
7. Set the database name, manually, to the name of your database. It will not show up in the drop-down list.
8. Force the network protocol to be TCP/IP (this step may not be necessary, but I do it out of habit)
9. Make sure Server type is set to Database engine (this is the default, but might not be if you use SSMS for more than your average bear)
10. Now you can click Connect.

At this point, if everything worked well, you should FAIL to connect to SQL Azure :) You should get some horrid message about a connection from your public IP address not being allowed. This is because this version of SQL Azure has a built-in firewall and, by default, it doesn't allow anything through.

Go back to the sql.azure.com portal and click on the Firewall Settings tab. Check the Allow Microsoft Services to Access this server box. Click the button to add a new record. At this point it will conveniently show you what it thinks your public IP is so you can create a new rule to allow your IP through. Keep in mind this is only required to allow your home computer to access your SQL Azure server. If you have checked the Allow Microsoft Services... checkbox, then connections from within the Azure fabric (like an ADO.NET call from inside an ASP.NET app in an Azure Web Role) will pass through the firewall unhindered. This firewall is specifically to keep the communication safe and give you a "DMZ-like" experience where only the people (IPs) you trust will be able to hit that server from outside the Azure cloud fabric.

Now, after adding the firewall rule, you will need to wait up to 15 minutes or more. When I did it, it took over 20 minutes. The reason is that the portal where you hit the submit button is not the same physical machine as your SQL Azure server. It takes a few minutes for your new firewall rule to make it over to the actual data center where your SQL Azure server has been provisioned (at least that's my best guess to explain this delay). So don't be alarmed if 10 seconds after you add the firewall rule you still can't get into your database.

Now you are free to write T-SQL until you are blue in the face. What's that, you don't love hand-writing T-SQL schema change scripts without any assistance from an IDE? Neither do I. This is why I created a Visual Studio 2008 "Database Project" that references a local database with the same schema as the one I want to have in the cloud. This is useful for multiple reasons. The biggest of which is that with a "Database project" I can version control my scripts. Secondly, I automatically gain the benefit of a local development copy of my database. Finally, this allows me to, from within VS, right-click any schema element and script it into the project. I can then open that script, do some cleaning up, and then execute that script in the aforementioned SSMS query window. The cleaning up I'm referring to means stripping out the plethora of extraneous options on the ends of the CREATE TABLE statements and things like that. A lot of those options aren't available in SQL Azure so just strip them out after you script the table, proc, view, whatever. It's a pain in the butt, but it's certainly better than having to hand-craft all that T-SQL if you aren't into that sort of thing. I know some developers that like to get all kinky with their T-SQL and would never let VS script it for them. Me, I like to wear protective gear when I talk to the database schema so letting VS give me a head start suggestion as to the script I should run on SQL Azure works just fine for me.

Read the original blog entry...