{"id":1651,"date":"2013-01-11T13:59:16","date_gmt":"2013-01-11T21:59:16","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/bobb\/?p=1651"},"modified":"2013-01-15T11:03:33","modified_gmt":"2013-01-15T19:03:33","slug":"getting-windows-azure-sql-database-diagnostics-using-windows-azure-cmdlets","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/getting-windows-azure-sql-database-diagnostics-using-windows-azure-cmdlets\/","title":{"rendered":"Getting Windows Azure SQL Database diagnostics using Windows Azure cmdlets"},"content":{"rendered":"

I’ve been trying out the new supported Windows Azure PowerShell Cmdlets<\/a> 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…<\/p>\n

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.\u00a0The 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:<\/p>\n

EventLogs – WASD event logs were recently introduced
\nServers and ServerMetrics
\nDatabases and DatabaseMetrics
\nOperations and OperationDetails – these are DAC operations you’ve done<\/p>\n

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.<\/p>\n

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

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

# gets EventLogs enumeration
\n$ctx.EventLogs
\n# or ServerMetrics enumeration
\n$ctx.ServerMetrics<\/p>\n

Since this is really an “enumeration”, you could filter it in a loop like this:<\/p>\n

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

} # end of check for right class<\/p>\n

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\u00a0this by hand,\u00a0it would be nice to have a supported cmdlet)\u00a0or 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.<\/p>\n

Happy scripting, Bob.<\/p>\n

@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[27,21],"tags":[],"class_list":["post-1651","post","type-post","status-publish","format-standard","hentry","category-sql-azure-database","category-powershell"],"yoast_head":"\nGetting Windows Azure SQL Database diagnostics using Windows Azure cmdlets - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/getting-windows-azure-sql-database-diagnostics-using-windows-azure-cmdlets\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Getting Windows Azure SQL Database diagnostics using Windows Azure cmdlets - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/getting-windows-azure-sql-database-diagnostics-using-windows-azure-cmdlets\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2013-01-11T21:59:16+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-15T19:03:33+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/getting-windows-azure-sql-database-diagnostics-using-windows-azure-cmdlets\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/getting-windows-azure-sql-database-diagnostics-using-windows-azure-cmdlets\/\",\"name\":\"Getting Windows Azure SQL Database diagnostics using Windows Azure cmdlets - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2013-01-11T21:59:16+00:00\",\"dateModified\":\"2013-01-15T19:03:33+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/getting-windows-azure-sql-database-diagnostics-using-windows-azure-cmdlets\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/getting-windows-azure-sql-database-diagnostics-using-windows-azure-cmdlets\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/getting-windows-azure-sql-database-diagnostics-using-windows-azure-cmdlets\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Azure SQL Database\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-azure-database\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Getting Windows Azure SQL Database diagnostics using Windows Azure cmdlets\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Getting Windows Azure SQL Database diagnostics using Windows Azure cmdlets - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/getting-windows-azure-sql-database-diagnostics-using-windows-azure-cmdlets\/","og_locale":"en_US","og_type":"article","og_title":"Getting Windows Azure SQL Database diagnostics using Windows Azure cmdlets - Bob Beauchemin","og_description":"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 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/getting-windows-azure-sql-database-diagnostics-using-windows-azure-cmdlets\/","og_site_name":"Bob Beauchemin","article_published_time":"2013-01-11T21:59:16+00:00","article_modified_time":"2013-01-15T19:03:33+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/getting-windows-azure-sql-database-diagnostics-using-windows-azure-cmdlets\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/getting-windows-azure-sql-database-diagnostics-using-windows-azure-cmdlets\/","name":"Getting Windows Azure SQL Database diagnostics using Windows Azure cmdlets - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2013-01-11T21:59:16+00:00","dateModified":"2013-01-15T19:03:33+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/getting-windows-azure-sql-database-diagnostics-using-windows-azure-cmdlets\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/getting-windows-azure-sql-database-diagnostics-using-windows-azure-cmdlets\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/getting-windows-azure-sql-database-diagnostics-using-windows-azure-cmdlets\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Azure SQL Database","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/sql-azure-database\/"},{"@type":"ListItem","position":3,"name":"Getting Windows Azure SQL Database diagnostics using Windows Azure cmdlets"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/1651","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=1651"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/1651\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=1651"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=1651"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=1651"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}