How to Administer a SQL Azure Database?

Like This Blog 1

Added by Don Jones May 7, 2013

As Microsoft continues to develop and promote their cloud-based SQL services, database administrators will have to become adept at administering their company’s Azure-based assets. If you’re a “reluctant DBA” – someone tasked with managing databases just because you’re “the Microsoft person” in your environment and not because you have any DBA training – Azure can seem especially intimidating.

Don’t worry. SQL Azure really is good old SQL Server, just with a few tweaks to make it work better in the cloud. You’ll use essentially the same tools that you always have, including the graphical SQL management console that you’d use to manage an on-premise SQL installation.

There are just a few differences. For example, in SQL Server, you manage server-level security through the “Security” folder in the graphical SQL Server Management Studio; in SQL Azure, you make server-level security changes directly to your organization’s “master” database. That means you’ll need to become more accustomed to the Transact-SQL commands used to manage security. For example, rather than graphically creating login accounts, you’ll have to use the CREATE LOGIN T-SQL command. Keep in mind that SQL Server Management Studio can help teach you T-SQL! For example, I always keep a local copy of the databases that I work with, so that I have something to test with. When I need to create a login, I create one locally, and then right-click the finished login to script the object. Doing so displays the T-SQL command that can be run on Azure to create the same login, with the same options.

There are a few administrative tasks you’ll never have to worry about with Azure. Because it’s designed to abstract the underlying hardware, you won’t worry about things like hard drives, file groups, and so forth. You’ll create your databases on a single file, and Azure will worry about where that file physically lives. For that same reason, you’ll never have to worry about an NTFS-level defragmentation of your databases – although you will have to worry about keeping the database internally defragmented, just as you do with a local SQL Server install.

Most database tasks will be exactly the same, such as reorganizing and rebuilding indexes, index tuning for performance, and so forth. Backups work pretty much the same, too, although you’ll need to carefully consider your use of full and differential backups since those will consume bandwidth, which you pay for in Azure.

Some high-level administrative tasks – such as creating new databases – is handled via Azure’s Web-based management interface. This is where you can get the virtual server name that has been assigned to you – and you’ll need that in order to hook up your traditional management tools. You’ll also get to see where your server is physically housed, such as in the “Anywhere Europe” datacenter. It can be important that you provision all of your organization’s virtual servers in the same datacenter as much as possible; you’ll pay for data transfer fees if servers in different datacenters need to communicate.

You also pay for disk space in Azure, meaning you should provision your databases with the very smallest file size they’ll need in order to accommodate your organization’s needs. Don’t provision a 80GB database if you only need 10GB! You pay for the entire database size, even if you’re only using a portion of it. Also keep in mind that database billing is in specific increments – a 22Gb database is billed at 30GB, for example.

That Web-based console also enables you to manage your virtual servers’ firewall settings – the Azure equivalent of the Windows Firewall, essentially. You can define rules that enable your users, and nobody else, to communicate with your databases.

You’ll find additional resources – including some great administrative walkthroughs – on sites like SQLServerPedia – SQL Azure Administration and Microsoft – Windows Azure SQL Database Overview. With a little bit of practice, you’ll be commanding SQL Azure just as confidently as you do your on-premise SQL Server instances.

Enjoy,

Don Jones
PowerShell and SQL Instructor – Interface Technical Training
Phoenix, AZ