Thursday, August 27, 2009

Taking SQL Azure for a test spin (it’s dirt simple)

Earlier this week I received my invite to the SQL Azure CTP so I obviously threw myself at taking it for a test spin, and I must say that it was a sweet experience (a part from the fact that my SQL DDL is a little rusty). Anyway I figured that I should write a few lines about the experience but first let's just get some basic facts out of the way, what is SQL Azure?

Microsoft® SQL Azure Database is a cloud-based relational database platform built on SQL Server® technologies. With SQL Azure Database, you can easily provision and deploy relational database solutions to the cloud, and take advantage of a globally distributed data center that provides enterprise-class availability, scalability, and security with the benefits of built-in data protection, self-healing and disaster recovery.

The about quote is taken from the Windows Azure Platform site and as it states you can now deploy a relational database in the cloud and accessing it via the TDS protocol using ADO.NET and ODBC (there is even a driver for PHP if you need that).

Anyway enough with the chit chat let's get our hands dirty in some code... The app we will run through here is an extremely simple an silly notebook in the cloud which you can insert some reminders in a then go back and see what you should do (the whole application took less than one hour to get up and running, writing this post took far longer than that).

First out you have to activate your invite (if you haven't signed up you can do it here) and get your SQL Azure database provisioned. When you have done that you get to access the management portal:

Currently you can only manage your connection strings and create and drop databases, but since you can’t manage logins we might as well drop straight into the sqlcmd tool (in this version you can’t use the Management Studio it is not supported yet) and start by creating  a small database. When you create your databases and logins you need to be logged in to the master database.

Once you have fired up a command prompt we issue the following command to connect to the master database:

sqlcmd –U <admin>@<servername> –P <password> –S <servername>.ctp.database.windows.net –d master

Now let’s create a database called MyCloudDB and a user called MyCloudUser:

CREATE DATABASE MyCloudDB
GO
CREATE LOGIN MyCloudLogin WITH password='1tsCloudyInSeattle'
GO

Let’s switch over to our new database and create some tables and grant our user some access:


sqlcmd –U <admin>@<servername> –P <password> –S <servername>.ctp.database.windows.net –d MyCloudDB


Then we issue the following statements create a user and a table where we can store our notes:


CREATE TABLE CloudNotes (ID int IDENTITY(1,1) PRIMARY KEY, Note nvarchar(500))
GO
CREATE USER MyCloudLoginUser FROM LOGIN MyCloudLogin
GO
GRANT SELECT,INSERT,UPDATE,DELETE ON CloudNotes TO MyCloudLoginUser
GO

Now we are ready for some code, first let’s write the snippet needed to put the reminders up in the cloud:


    public void InsertNote(string note)
{
SqlConnection cn
= new SqlConnection("Server=tcp:<yourservername>.ctp.database.windows.net;Database=MyCloudDB;User ID=MyCloudLogin;Password=1tsCloudyInSeattle;Trusted_Connection=False;");
using (cn)
{
SqlCommand cmd
= new SqlCommand("INSERT INTO CloudNotes Values (@Note)");

cn.Open();

cmd.Connection
= cn;
cmd.Parameters.Add(
new SqlParameter("@Note", note));
cmd.ExecuteNonQuery();
}
}

And finally the code required to retrieve the notes so we don’t forget to but that milk on our way home:


    public List<string> ListNotes()
{
List
<string> result = new List<string>();

SqlConnection cn
= new SqlConnection("Server=tcp:<yourservername>.ctp.database.windows.net;Database=MyCloudDB;User ID=MyCloudLogin;Password=1tsCloudyInSeattle;Trusted_Connection=False;");
using (cn)
{
cn.Open();

SqlCommand cmd
= new SqlCommand("SELECT ID, Note FROM CloudNotes ORDER BY ID", cn);
SqlDataReader dr
= cmd.ExecuteReader();
while (dr.Read())
{
result.Add(dr.GetInt32(
0).ToString() + " - " + dr.GetString(1));
}
}

return result;
}

That’s all there is to it as I said in the title dirt simple :) if you want to read more about the limitations and how to get going checkout MSDN documentation on SQL Azure. I’ll probably post more as I dabble around with it.

No comments:

Post a Comment