Home > Azure, Cloud, Development, SQL > How to monitor your SQL Azure databases sizes

How to monitor your SQL Azure databases sizes

The Web edition of SQL Azure has a hard maximum limit of 5GB, once you hit that limit the server will stop accepting any writes and your application may well go down. It’s important to make sure you have an understanding of what sizes your databases are and how fast they are growing.

At NewOrbit we needed a way to view all our 80+ databases many of which many are dynamically created across multiple subscriptions. Unsurprisingly, it was Powershell to the rescue. There are plenty of weird things about Powershell, but it’s ability to work with XML is fantastic.

So first let’s get our configuration information so we can connect to Azure

Next, lets loop through our data setting the azure subscriptions. Powershell lets us just use a . notation to walk through nodes. We grab the data for our subscriptions and set our subscription using Set-AzureSubscription. This means that you will need to have the certificate installed on the machine that you are running the script from.

And finally create a function which connects to the Azure Server and loops through the databases (except Master) calculating the percentage of the capacity used

Once you have access to the databases, there is plenty more you can do including check the database type (Web or Business) and even increasing the size and type  automatically using Set-AzureSqlDatabase

Next we will look at using Powershell dynamic objects to sort and export our data so we can find which databases are closest to the maximum capacity



Categories: Azure, Cloud, Development, SQL Tags: ,
  1. February 15, 2014 at 5:05 pm

    Looks really nice, Kev! I had it in my head that powershell syntax was really obscure, but your samples here make me think I should give it a second go – just started playing around with it again now. I’ve got a small side-project that I’m hoping to host in Azure at some stage, and I’m reading about continuous deployment at the moment, so you’ve got me thinking that powershell might be the way forward! I’m just after downloading that Azure SDK with Powershell – so thanks Kev – 2 more technologies to add to my list of things to learn! Like I didn’t have enough things on that list already! 🙂

    • Kev Hunter
      February 15, 2014 at 5:40 pm

      We have continuous deployment into Azure from Powershell as part of our build process when code hits the develop branch in mercurial, I’ll blog about it at some point.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: