Archive for the ‘SQL’ Category

How to monitor your SQL Azure databases sizes

February 15, 2014 2 comments

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: ,