I've been working with SQL Azure for some time now and I really like it from a technology perspective. For the uninitiated, SQL Azure, formerly SQL Data Services (SDS), is Microsoft's foray into relational databases in "the cloud". Microsoft runs a special build of SQL Server that operates in a highly-available configuration on the servers in its Azure data centers. From a system administrator's standpoint, there are some radical differences between SQL Server and SQL Azure. How in the world are we going to live without the BACKUP command or the KILL STATS JOB command, after all? When designing SQL Azure, Microsoft took a long look at the list of features that had grown into SQL Server over time and realized that there were a lot of physicality features that had become baked into the T-SQL language that make no sense whatsoever running in a grid type configuration with thousands of other active databases. In my mind, this is a good thing because it forces Microsoft to think critically about what pyhsical and logical assets really make up the database from an administrator's perspective and from a developer's perspective. Companies like Teradata and Netezza have been thinking this way for some time now in an effort to make their database appliances much simpler to manage. I sincerely hope that some of what Microsoft is learning with SQL Azure creeps back into SQL Server 2011 (or whatever it will be called). If so, it will be a great thing for Microsoft and its customers. In the short term, these tradeoffs will make traditional database and network administrators feel off balance, though.
Developers, on the other hand, will find SQL Azure quite comfortable. Much to my surprise, even my NHibernate-based applications work in SQL Azure without modification. One caveat there: If you use the NHibernate Hbm2Ddl utility as part of a Domain-Driven Design (DDD) process, just watch out for the fact that SQL Azure does not support heap tables. Because of this, every SQL Azure table must have a clustered index so make sure that all the tables in your model have a primary key or at least have a clustered index. (I know, I know. Primary keys don't have to be implemented as clustered indexes in SQL Server but you get the point.) A full list of SQL Server unsupported T-SQL can be found on MSDN. And while the list might look really long, the average application is not likely to encounter a lot of problems running against a SQL Azure database. The T-SQL statements that I'll personally miss the most in SQL Azure? Those would be OPENQUERY, OPENXML, SELECT INTO and NEWSEQUENTIALID.
Database Administration
Let's begin by looking at a bit of administration. The SQL Azure portal is the best place to start. You must log in with your Windows Live ID and click on the link for your project page. The screen for managing your SQL Azure databases is rather sparse. I was expecting to see something like SQL Server Management Studio on the web. Perhaps the web interface will evolve to support more features over time. For now, it presents something like this:
I've masked parts of the screen shot to protect my privacy but you get the idea. The section at the top shows basic information about you SQL Azure "server" with an option to reset the administrator password. The section below shows two tabs labeled "Databases" and "Firewall Settings". The databases tab shown above allows you to create databases, see the connection strings required to connect to them or to drop them. The list shows the current size and size limit for each database. As of this writing, SQL Azure limits databases to 1GB or 10 GB maximum sizes. Hopefully, that will change in the future to allow much larger databases to exist in the cloud. I mean, why build a mesh or grid infrastructure for massive database scaling and limit it to 10 gigabytes? The size of the 3 databases shows them to be zero bytes in size because I truncated them before writing this article. After clicking on one of the radio buttons beside a database name and clicking the "Connection Strings" button, you'll see an AJAX popup that looks something like this:
The popup shows what the ADO.NET and ODBC connections strings would look like in an application configuration file. Notice in both connection strings that there's nothing special about SQL Azure. We can use the plain, old SQL Server Native Client 10.0 over TCP/IP to connect to the Azure database. But can just anyone across the Internet connect to your data? Of course not. Microsoft allows you to restrict connections by IP address ranges or from Windows Azure tasks that you may be running in the MicrosoftServices cloud. The Firewall Settings tab on the main screen is where we can do that. The Firewall Settings screen looks something like this:
Again, I've masked out my IP address in the Record Name that I created called VZW shown here. Since I work on the go using my EVDO card a lot, I need to change the IP Address Range every day, sometimes several times a day. There's a way to change the firewall rules through DML but I've yet to try that. I was thinking of writing a Windows Azure or .NET Services service that would have access via the MicrosoftServices checkbox shown above. I could call the service with another form of authentication to have it update my SQL Azure firewall rules automatically. Until I write that, I'll have to use this web-based console interface to set up the firewall rules. One thing I've noticed during the CTP is that the firewall rules don't take effect immediately. When my IP address changes and I make a rule change in the SQL Azure Firewall Settings, it may take up to 10 minutes to push that change to the firewall and execute it. When I'm ready to make a change, I simply press the "Edit Record" button and an AJAX popup that looks like this is rendered:
Notice that the firewall rule for SQL Azure allows you to specify a range of IP addresses, not just one. That would be handy to use if, for example, all of the addresses within a Class C IPv4 Address block were allowed to connect to the SQL Azure databases you manage. I didn't see any support for IPv6 addresses in the Firewall Settings but I'm supposing that Microsoft will have to support IPv6 in the rules in the future. Additionally, I'd expect to see some richer firewall rules type, e.g. the use of subnet masks to further refine the grant or denial rules and rules based on IPSEC/VPN configuration. For now, IP ranges are enough to get started. I could show you the screens for creating and dropping databases in the web-based console but it's really not all that interesting. Besides, we can do that using the SQLCMD command-line tool as shown in the Database Access section below.
Database Access
I admit it. I'm a UNIX hacker from pre-history so whenever I have the chance to master something at the command line, I jump at it. So when I heard that SQL Azure worked well with the SQLCMD line tool, it brought a grin to my face. Here's a screen shot of a Windows Powershell-based exchange between me and my SQL Azure server. Once again, I've blanked out some of my personal information but this time, it's color coded to help you understand what's important.
The first thing to take note of is that I've issued 4 commands here, number 2# through 5#. Command 2# connects to the master database: notice the -d master parameter at the end of the 2# command line? The 1> prompt means SQLCMD is waiting for input from me. I typed "CREATE DATABASE Blog" followed by Return then "GO" followed by return. Until I provide the "GO" statement, all of my commands would be batched on the client side. When the "GO" command is received, it's not sent to SQL Azure. Instead, it's a signal to the SQLCMD client to send the current batch to the remote server. When the results returned from command 2# show no error, the 1> prompt shows to indicate the start of a new batch. The exit command takes me back to PowerShell.
At this point, our new Blog database has been created in the cloud and we're ready to use it. So in command 3#, I typed in a somewhat lengthy table definition. When I used the "GO" command to execute the batch, however, I got an error saying that the remote host (SQL Azure) has closed the connection. I took too long to type the command so SQL Azure, being a good steward of resources like IP connections, dropped the connection. The fact is that the connection was probably already dropped after I entered the "CREATE TABLE" command at prompt 1> and before I entered the "GO" directive at prompt 2>. But because SQLCMD was batching my commands, it didn't sense that the connection had been dropped until it tried to send the batch to SQL Azure. I haven't found a way to make SQL Azure keep the connection open longer and I probably would advise against using that feature if it exists. IP connections are precious to any kind of server that needs to scale to large number of users. Forcing SQL Azure to keep the connection open longer so you can type is just a bad idea because it would severely impede scalability.
Fortunately, I don't have to type that long "CREATE TABLE" command in again. When I ran SQLCMD again in step 4#, I simply pressed the up arrow on my keyboard and it "remembered" the command. Tapping return and issuing the "GO" command, the table is successfully created in the new Blog database. In the same session, I then started to type an "INSERT" command to put some data into the new Article table. Again, I took too long to do it so you can see that the connection was again closed by SQL Azure before committing the batch. No worries, though. Rerunning SQLCMD and using the up arrow trick saved the day again. The values were inserted successfully on the second attempt.
Now let's talk a bit about those masked out values that I color-coded in the screen shot. The yellow masks are where my user ID is inserted. You can create users and logins using the "CREATE USER" and "CREATE LOGIN" commands in SQL Azure just as you can in SQL Server. Once you've done that, you can use the user IDs with access to a given database to do your work. The @ sign trailing the user ID in each SQLCMD is significant. For whatever reason, you must connect to SQL Azure with the user ID (-U) qualified at (@) your server name. The server name shows in the pink or salmon masks. Also notice that the pink/salmon masks show up again in each command in the server name (-S) section. Just remember to specify your SQL Azure server name after the @ in the -U parameter and again in the -S parameter. Also, the -S parameter must contain the Fully Qualified Domain Name (FQDN) of the server so that the SQLCMD tool can resolve to your SQL Azure server's IP address. Now, let's query the data.
Notice that in command 10# that I used SQLCMD's -q parameter to pass a query string to my SQL Azure Blog database. In this case, it's a SELECT statement that dumps the data that I inserted with command 4# earlier. The output format isn't so pretty but you can tell that the data matches what I inserted before. Since I used the -q (lowercase) parameter, I still have to use the "exit" command to leave the SQLCMD interpreter. In command 11#, I used the -q parameter again to DROP the Article table from the Blog database. And in command 12#, I used the DROP DATABASE statement to drop the Blog database from SQL Azure altogether. In this case, I had to specify the master database using SQLCMD's -d parameter. Also note that by using the -Q (uppercase) parameter in command 12#, the exit statement is implied so I didn't have to exit manually as I did in commands 10# and 11#. That's handy.
Coding to SQL Azure
If you are using ADO.NET or ODBC, the connection strings to your SQL Azure database can be obtained from the SQL Azure web console as shown earlier. Let's take a moment to dissect the ADO.NET connection string while we're on the subject. I'll only address the parts that need some special attention below:
Server=tcp:<server name>.database.windows.net; Database=<database name>; User ID=<user name>; Password=<password>; Trusted_Connection=false;
- Server=tcp:<server name>.database.windows.net - this is the fully qualified domain name of your server, prefixed with the tcp: directive. This tells the SQL Server Native Client to use the TCP/IP protocol to connect to the FQDN that you specify. If your client is configured to prefer named pipes or some protocol over TCP/IP, the tcp: directive in the connection string tells it to skip directly to TCP/IP instead.
- User ID=<user name> - unlike SQLCMD, when using ADO.NET, the user name does not have to include @<server name> as the suffix. Just the user name part will do.
- Trusted_Connection=false - this may not be what you think. This directive doesn't mean that the connection won't be secure. Every SQL Azure Tabular Data Stream (TDS) connection is tunnelled through the Secure Sockets Layer (SSL). Instead, this directive means that we won't be using OS-driven authentication like NTLM or Kerberos.
You can construct a connection string in C# quite simply by using the SqlConnectionStringBuilder class as follows:
private const string UserName = "jrsamples";
private const string Password = "m1Nn1ep3@rL";
private const string ServerName = "br549.database.windows.net";
private const string DatabaseName = "Blog";
...
var connBuilder = new SqlConnectionStringBuilder
{
DataSource = ServerName,
InitialCatalog = DatabaseName,
Encrypt = true,
TrustServerCertificate = false,
UserID = UserName,
Password = Password
};
Notice that the Encrypt property is set to true in the connection string builder. This isn't strictly required because SQL Azure will force this value to true even if the client does not specify it. You should also note that SQL Azure does not accept connections on any TCP port other than 1433 at this time. So don't try to use a different port in the connection string builder or the connections using it will fail. When you're ready to use the SqlConnectionStringBuilder, invoke the ToString() method to get the full connection string back for use in your code. I'd show you some ADO.NET code here to do INSERT, UPDATE and DELETE but, to be honest, it would be pretty boring. Your ADO.NET code most likely won't have to be changed when moving from SQL Server to SQL Azure.
Product Availability
As of this writing, SQL Azure is still in CTP (Community Technology Preview) and not available for commercial use. The Microsoft Professional Developer Conference (PDC) coming up in mid-November 2009 is the time that's expected for commercial launch of the product. Right now, it appears as though Microsoft is going to limit databases in the SQL Azure cloud to 1GB or 10GB, so many larger-scale commercial applications may have to wait for a time when 100GB or larger databases may be ported. There's no guarantee that will ever happen but one has to assume that Microsoft, once it has gotten some commercial experience serving real customers, will open SQL Azure up to databases that can really show its capabilities.
Pricing
Check Microsoft's SQL Azure Pricing information page for details about cost and measurement.
Closing Thoughts
Microsoft's first attempt at putting SQL Server into the cloud is fairly impressive. And although SQL Server Management Studio can be used with SQL Azure, there are some known compatibility issues that make using command line tools safer for the time being. I'm betting on the fact that Microsoft will make some rich, GUI-based management tools available in due time. After all, Microsoft differentiated itself in the database space years ago by making network and database administrator jobs much easier through the use of great tools. Why wouldn't they continue that trend with SQL Azure? With respect to the query engine and the storage engine in SQL Azure, this first release is fairly strong. The fact that my NHibernate-based applications run without modification is impressive to say the least. If you're accustomed to writing lots of rich stored procedures that use every trick in SQL Server 2008's book, you may encounter some problems when using SQL Azure, though. There are many subtle changes and omissions in the implementation concerning those features that many of us consider to be on the periphery.
Will SQL Azure be a hit in the marketplace? Who knows? That's the big question now. There's little uptake on relational cloud databases in general so it remains to be seen if the popularity of SQL Server will translate into the cloud well. That will have a lot to do with pricing and Microsoft's target market which isn't fully understood just yet. Imagine a medium-sized company that would have to pay for server hardware and SQL Server 2008 Standard Edition plus the Client Access Licenses to make the system available. Then there are the environmental factors like power and cooling to consider. There's also hardware and software maintenance to add in and the people to manage it all at three nines of uptime per month. What's that worth per year? If Microsoft can convince business managers that it's a safe thing to do, that the development experience is great and that the pricing's right, SQL Azure could be quite popular in the marketplace. Only time will tell. Personally, I'm already thinking of clients who could benefit by shedding their servers in favor of cloud databases. I'm definitely going to start small, though, and work my way up. My clients who are spending between $2,000 and $3,500 US per server (Total Cost of Ownership) with less than 10GB of storage are the ones who could benefit the most by considering the move to SQL Azure.