After defining the requirements for my application, I’m going to start from the beginning, which means creating a database to store my data.
So, after going through the process of activating my MSDN Premium subscription to access the Windows Azure Platform, I went into the very clean and simple control panel for SQL Azure.
Using the Create Database, I specify a name for my database, create it and immediately tried to do a connectivity test.
Oops, it failed, but fortunately there is some information on what is going wrong: apparently there is a firewall blocking my connection, which is provided by SQL Azure as a way for you to control accesses to your database. We are taken to this link for further information: http://msdn.microsoft.com/en-us/library/ee621783.aspx
So, secure by default and a nice user experience as it was actually suggesting me to follow the explanatory link before I even tried to connect. Sounds good so far. So, let’s add the necessary firewall rules:
I added a rule for my current IP address so I can interact with the database and also allowed access to Microsoft Services (which will be needed since I want to host a WCF service on Azure to interact with this database).
Success! I rule! Well, I guess it was easy… The control panel shows everything is up and running:
So, now that I have an empty database, how do I actually create tables? Reading the documentation quickly, it seems that I have to do it through T-SQL. I do have the SQL Client Tools, but how to connect to my database in the first place? Well, selecting the database enables a button labeled “Connection Strings”…let’s try that:
Ok, so this has all the information I need to connect to my database. But from where? Apparently, I can’t use SQL Server Management Studio (it throws an error) so I decided to follow the documentation (severe case of RTFM here) and try SQLCMD utility:
sqlcmd -S yniq7s0yws.database.windows.net -U nunos@yniq7s0yws -P <mypassword> -d TechFAQ
1> CREATE TABLE table1 ( id int primary key, someValue varchar(20))
1> select * from table1
(0 rows affected)
Again, I rule! Well, that was also easy…
Now that my test is complete, it’s time to drop this test table and create some meaningful table(s) that I will use in my application:
CREATE TABLE Technologies (id int primary key identity, name varchar(50), url varchar(100))
insert into technologies values ('Silverlight','http://www.silverlight.net')
insert into technologies values ('Windows Azure','http://www.microsoft.com/windowsazure’)
However, I tend to not like command line utilities that much so I decided to try and access my database from inside Visual Studio. Using Server Explorer window, I configured the connection to my database using the same values and it worked! Happy times!
After my initial joyful reaction, I tried to add more tables to the database through Visual Studio, but I was presented with a rather annoying (though enlightening) message:
Bah! However, opening the existing table works nicely:
Maybe in the future they will add support to either SQL Management Studio or Visual Studio so we can have a more seamless experience. But for now, this will do nicely. I will probably just create a dummy database using SQL Server Express, generate the script to create all my tables and foreign keys, and just use SQLCMD to replicate that model in my SQL Azure database... so it’s not a problem even if I’m not the sharpest T-SQL guy out there.
Very nice indeed: the whole process took my about 30 minutes to setup, including the time needed to activate my MSDN Premium subscription to access Azure services AND do a couple of Bing searches to find out what to do as I went along. That’s a lot faster than setting up a SQL Server instance to host my database in a server, and that’s not even accounting the time needed to setup a Windows Server from scratch.
I’m going add a bit of complexity to my database model to suit my business needs and after that I will dedicate myself to part 2 of this scenario: building a WCF service that interacts with the database and hosting it on Azure.
Until then, have fun!