Creating a SQL Server database on Windows Azure

(Just to address a point of confusion. In the old days, Microsoft called their cloud-based SQL Server cloud service ‘Windows Azure’. Then they made their cloud service do virtual machines and apps and whatnot, and they renamed the SQL Server bit ‘Windows Azure Sql Database’ or something. This post is about the SQL Server thing, whatever it’s called. On with the show, anyway.)

“Just give me a connection to a SQL instance”. That’s the promise of Windows Azure Sql Database. Out there somewhere in the cloud, Microsoft will give you a SQL server instance, into which you can create databases, and it’s supposed to function exactly like a SQL Server instance you’ve installed on a local machine. Or you can use it as the back-end store for an app running on Azure, of course, but for now, let’s just consider the use of it as a raw SQL Server instance.

The story is pretty complete. You create your instance through the Azure control panel, tell it some admin credentials, and then you’ve got an instance. You can open up Management Studio now if you want, and connect to your instance by typing in something like

w2490825.database.windows.net 

and you now have Management Studio; you can now CREATE DATABASE or whatever you want to do. (Technically, you get a thing called a `TDS endpoint`. Google it.)

So databases created on Azure are replicated twice — you have a master copy and two slaves, and writes have to hit the master and at least one slave. That adds a little bit of latency, so the suggestion is that you write chunky, unchatty commands. Seems reasonable, I guess. But you get replication for free.

Now, don’t go confusing that with backups. If you `TRUNCATE TABLE PROJECTS` on your master you can’t switch to a slave to restore to an earlier state — you just truncated all three copies. This is just for redundancy in case a meteor strike hits the rack with your master DB on it. You still need to do regular backups.

And I say backups, but you don’t get those the same way. Actually, technically, you can’t do backup or restore. Not with `.bak` files. Instead, there is an import/export service that you can use. The .bak files contain transaction logs, and those would contain sensitive data from other users of the datacentre, so you can’t get ’em.