Why Windows Azure SQL Database May Be Right For You

Grande. Bold. No room.

It’s my standard order when at Starbucks. Despite there being thousands of ways to have my coffee prepared I prefer the simplest.

It’s probably why I enjoy working with Windows Azure SQL Database (WASD) so much. The number of configuration options we have with WASD is limited. By limiting the options available to users WASD becomes a simpler option for everyone.

I like simple. Most people do, too.

CREATE DATABASE Statement In WASD

Let’s take a look at the CREATE DATABASE statement in WASD:

CREATE DATABASE database_name [ COLLATE collation_name ]
 {
 ( [, ...n])
 }
  ::=
 {
 (MAXSIZE = {1 | 5 | 10 | 20 | 30 … 150} GB)
 |(EDITION = {'web' | 'business'})
 }
[;]

I get to select the name of my database, the collation, and the size. Tossing aside the number of available collations (and because we only get one default collation anyway) that means I have a grand total of 17 options for the CREATE DATABASE statement in WASD.

Those 17 options are for size and edition only. In short, they allow me to specify how much I want to pay. That’s it, nothing more.

Grande. Bold. No Room. My name? Keyser Söze, thanks.

CREATE DATABASE Statement in SQL Server 2012

Let’s compare the above CREATE DATABASE statement in WASD to the options for creating a database in SQL 2012:

CREATE DATABASE database_name
[ CONTAINMENT = { NONE | PARTIAL } ]
[ ON
[ PRIMARY ] [ ,...n ]
[ , [ ,...n ] ]
[ LOG ON [ ,...n ] ]
]
[ COLLATE collation_name ]
[ WITH
[,...n ] ] [;]

::=
{
FILESTREAM (  [,...n ] )
| DEFAULT_FULLTEXT_LANGUAGE = { lcid | language_name | language_alias }
| DEFAULT_LANGUAGE = { lcid | language_name | language_alias }
| NESTED_TRIGGERS = { OFF | ON }
| TRANSFORM_NOISE_WORDS = { OFF | ON}
| TWO_DIGIT_YEAR_CUTOFF = 
| DB_CHAINING { OFF | ON }
| TRUSTWORTHY { OFF | ON }
}

 ::=
{
NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }
| DIRECTORY_NAME = 'directory_name'
}

::=
{
(
NAME = logical_file_name ,
FILENAME = { 'os_file_name' | 'filestream_path' }
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ]
)
}

::=
{
FILEGROUP filegroup_name [ CONTAINS FILESTREAM ] [ DEFAULT ]
[ ,...n ]
}
[;]

That’s not even the whole list, really, because I can use the ALTER DATABASE command to then configure additional options such as auto-shrink. I’m not allowed to run the ALTER DATABASE command in WASD with one exception: When I want to increase the size.

But for the initial CREATE DATABASE command the options in SQL 2012 work out to be…um…a lot. A mad genius could create a database with 32,767 files, of varying sizes, even multiple log files, each file with non-default logical and physical names as well as filegrowth…you get the idea. It’s a lot more than 17 is my point, really.

What Are You Missing?

Here’s a quick list of the things you DON’T get to configure when creating a database in WASD:

• Containment
• Files
• Filegroups

The options in WASD are so simple that if I had to create a flowchart for creating a database in WASD it would be something like this:

Think about each and every database that you are responsible for right now. How certain are you that the databases have been properly configured for an optimal physical design? In other words, have you created filegroups for system and user data? Have you placed tables and indexes on optimal filegroups as well?

I’m willing to bet that a majority of you have NEVER gone out of your way to create distinct filegroups for user and system data. Heck, I still see installs that have the data and log files on the same drive. You are probably not taking advantage of 1/100th of all the available options that SQL Server 2012 allows you to have. The default options have served you well for years, right? Why change now?

That’s why WASD should be an attractive option for you.

Fewer options. Lower costs. Less administrative overhead. More time to look at pictures of cats on the Internet.

What’s not to love?

Why Windows Azure SQL Database May Be Right For You

While some feel that the lack of options is a weakness for WASD, I see it as a strength. Fewer options means I have fewer areas to troubleshoot. That’s a good thing. Well, I think it’s a good thing. I’d rather be able to check 10 things quickly when troubleshooting than 1,000s of things. Being handed a server to administer shouldn’t require me to spend hours running scripts and submitting change requests to make everything falls into my comfort zone. I should be able to walk in and be effective right away and not have to spend time turning countless knobs.

I don’t care about the hardware, I really don’t. I have little interest in knowing there is 5.0 GHz in the CPU, or the difference between hard and soft NUMA. I want my data to be highly available but I don’t want to be responsible for fixing replication when it breaks. I know that 98% of all performance issues are due to sub-optimal code and database design and that is why I prefer having a platform that allows me to focus on those aspects and less on the things I often don’t control.

That’s why WASD is the right choice for someone like me who just wants a cup of coffee, black, filled to the top.

I bet WASD is the right choice for many of you, too.

I’m not saying you need to convert all your existing applications to WASD immediately. What I am saying is that as new projects crop up you should think twice about WASD.

There is going to come a time when WASD will be the right choice for you.