Getting Windows Azure SQL Database diagnostics using Windows Azure cmdlets

I’ve been trying out the new supported Windows Azure PowerShell Cmdlets this week and I’d have to say I’m impressed. I’ve only used the ones for setup (Get-AzureSettingsFile and friends) and the ones for Windows Azure SQL Database (WASD) so far, and there’s a new one for WASD I really like. The original Codeplex WAAPS cmdlets only supported WASD Server and Firewall rule manipulation, the new ones also support a series of *-*AzureSqlDatabase ones, like Get-AzureSqlDatabase for example. I thought I should be able to “navigate” from what’s returned from the “Server” cmdlets down to the database, but unless I’ve missed something, that’s not possible as the Server cmdlets return SqlDatabaseServerContext. To get to Database cmdlets you need an IServerDataServiceContext and SqlDatabaseServerContext doesn’t implement that interface. Instead you need to use the cmdlet New-AzureSqlDatabaseServerContext, which requires a Server name and a PSCredential. The database commands work well, but…

The more interesting bit is what you can obtain through the context. You see, New-AzureSqlDatabaseServerContext does return a class that implements IServerDataServiceContext but that class is really ServerDataServiceSqlAuth. You should check that this is true in your script before using it by using the PowerShell “-is” operator. The ServerDataServiceSqlAuth includes 15 properties that return enumerations (actually they are WCF Data Services DataServiceQuery<T> objects) of interesting things. In fact there is an entire WCF Data Services object model for this which you can retrieve using RetrieveMetadata method on ServerDataServiceSqlAuth. Some of the interesting things are:

EventLogs – WASD event logs were recently introduced
Servers and ServerMetrics
Databases and DatabaseMetrics
Operations and OperationDetails – these are DAC operations you’ve done

So you can “monitor” your WASD through PowerShell, without ever hitting the Windows Azure Portal. Very Nice. If you didn’t understand that whole alphabet soup of class library references, here’s what it looks like in code. Remember, first you must use Get-AzureSettingsFile and Import-AzureSettingsFile to “configure” your administrative certificate.

# Get a SQLDataServicesSqlAuth (context), substitute name of your server
$cred = Get-Credential
$ctx = New-AzureSqlDatabaseServerContext -ServerName “[myserver]” -credential $cred

# check to see if you have the right class before going further
if ($ctx -is [Microsoft.WindowsAzure.Management.SqlDatabase.Services.Server.ServerDataServiceSqlAuth]) {

# gets EventLogs enumeration
$ctx.EventLogs
# or ServerMetrics enumeration
$ctx.ServerMetrics

Since this is really an “enumeration”, you could filter it in a loop like this:

# filter only events for master database
$ctx.EventLogs | Foreach-Object -Process { if ($_.DatabaseName -eq “master”) { $_ } }  #or whatever logic you want
# or even simpler
$ctx.EventLogs | where { $_.DatabaseName -eq “master” }

} # end of check for right class

So what else could one want? Well, if a had to say, it might be additional operations you can do with the portal, like wrapping the DAC Import-Export service (although I’ve been able to do this by hand, it would be nice to have a supported cmdlet) or setting up Windows Azure SQL Data Sync. Those may show up later on in time. Or the ability (this is probably do-able with WCF Data Services, I haven’t looked that hard yet) to filter the EventLogs or other enumerations before the fact. In the last line of code above, I’m fetching all the event log lines and filtering them at the client, rather than asking WCF Data Services for a subset. But for now, I’m happy to have the set of useful information and also the base functionality that these cmdlets provide. But of course, I do have an aversion to using web-based GUI for repeatable tasks.

Happy scripting, Bob.

@bobbeauch

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.