Performing Azure SQL Database Management Tasks by Using PowerShell with REST API

In our recent articles published on this forum, we have been exploring the ability to administer Azure SQL Database by leveraging REST APIs invoked from PowerShell-based scripts. While this seems somewhat redundant considering management capabilities incorporated into the Azure PowerShell module (available via Microsoft Web Platform Installer), such an approach offers more flexibility, facilitating functionality that is not directly exposed in the PowerShell cmdlets. We have already illustrated these advantages in the context of managing Azure SQL Server instances - now it is time to show how similar benefits can be realized when dealing directly with Azure SQL Databases.

We can identify REST API functions not implemented in PowerShell by exploring the MSDN Web site and comparing REST API Operations for Azure SQL Databases against Azure SQL Database Cmdlets to ensure that the ones we choose do not have their PowerShell equivalents. While there is almost a complete overlap between the two (with some of the PowerShell cmdlets handling more than one REST API call), one that stands out from the rest is named Get Database Event Logs. While its sole purpose is to retrieve database log entries, you should note that this task not only does not have the PowerShell counterpart but also is not exposed in the Azure Portal(you can, however, access server-wide logs from the portal by logging on to the server via the Manage shortcut appearing at the bottom of the portal window and switching to the Events tab of its Administration workspace).

As before, we will start by identifying individual components of a REST API call for each of them, including:

• Request Headers: assigned the value of 2012-03-01 (translating into x-ms-version header, which represents the minimum API Service Management version that is necessary to carry out the intended operation).

• Request: consisting of the following subcomponents:

• Method: designating the HTTP verb which dictates action to be taken when invoking the API.
• URI: taking the form of a parameterized URL (with individual parameters enclosed by braces), which is targeted when invoking the designated method.
• HTTP Version: indicating compatibility with a specific version of HTTP protocol specifications (1.1 in our case).
• Body: representing payload intended for data pertinent to the requested operation.

Let's start by retrieving a list of databases for an Azure SQL Server by employing the List Databases REST API request (this is just for illustration purposes, since obviously the same can be accomplished by running the Get-AzureSqlDatabase PowerShell cmdlet. As per Microsoft Azure Online Documentation, such a request must include subscription ID and target SQL Server name. Effectively, this yields the following set of REST API invocation components:

• Request Headers: 2012-03-01
• Request Method: GET
• Request URI: https://management.core.windows.net:8443/{subscriptionId}/services/sqlservers/servers/{serverName}/databases?contentview=generic
• Request HTTP Version: 1.1
• Request Body: not applicable, since we are invoking the GET method

In our PowerShell-based implementation, we will assume that we already have configured certificate-based authentication and use individual variables to store values of parameters of the Invoke-RestMethodcmdlet representing components of the REST API operation (i.e. $method, $headers, and $URI) as well as auxiliary parameters such as $serverName and $subscriptionID):

$method = "GET"
$headerDate = '2012-03-01'
$headers = @{"x-ms-version"="$headerDate"}
$subscriptionID = (Get-AzureSubscription -Current).SubscriptionId
$serverName = 'yourservername'
$URI = "https://management.core.windows.net:8443/$subscriptionId/services/sqlservers/servers/$serverName/databases?contentview=generic"
$certificateThumbprint = (Get-AzureSubscription -Current).Certificate.Thumbprint
$contentType = "application/xml;charset=utf-8"
$databases = Invoke-RestMethod -Uri $URI -CertificateThumbprint $certificateThumbprint -Headers $headers `
							-Method $method -Verbose -ContentType $contentType

As long as the operation is successfully initiated and completed, you should see messages indicating its progress (reflecting a 0-byte GET operation payload and non-zero application/xml response payload), which should resemble the following:

VERBOSE: GET https://management.core.windows.net:8443/subscriptionID/services/sqlservers/servers/yourservername/databases?contentview=generic with 0-byte payload
VERBOSE: received 8527-byte response of content type application/xml; charset=utf-8

As indicated by the message above, the response body is delivered as a System.Xml.XmlDocument object. We can extract its content by querying the value of the OuterXml property of the XML-based $databases object ($databases.OuterXml), which would include a comprehensive listing of properties of all databases hosted by the target Azure SQL Server. To limit the output to database names, you can extract the value of $databases.ChildNodes.ServiceResource.Name.

Now let's leverage this listing to retrive event log entries for one of these databases. As per Microsoft Azure Online Documentation, such request must include subscription ID, target SQL Server and SQL Database names, startDate and intervalSizeInMinutes, as well as an event type you are interested in. Effectively, this yields the following set of REST API invocation components:

• Request Headers: 2012-03-01
• Request Method: GET
• Request URI: https://management.core.windows.net:8443/{subscriptionId}/services/sqlservers/servers/{serverName}/databases/{databaseName}/events?startDate={startDate}&intervalSizeInMinutes={intervalSizeInMinutes}&eventTypes={eventTypes}
• Request HTTP Version: 1.1
• Request Body: not applicable, since we are invoking the GET method

Here as well as in our PowerShell-based implementation, we will assume that we already have configured certificate-based authentication and use individual variables to store values of parameters of the Invoke-RestMethodcmdlet representing components of the REST API operation (i.e. $method, $headers, and $URI) as well as auxiliary parameters such as $serverName, $subscriptionID, $certificateThumbprint, $startDate (2014-08-20T00:00:00 in our case), $intervalSizeInMinutes (60), and $eventTypes (we will look for connection_successful):

$method = "GET"
$headerDate = '2012-03-01'
$headers = @{"x-ms-version"="$headerDate"}
$subscriptionID = (Get-AzureSubscription -Current).SubscriptionId
$serverName = 'yourservername'
$databaseName = 'yourdatabasename'
$startDate = "2014-08-20T00:00:00"
$intervalSizeInMinutes = "60"
$eventTypes = "connection_successful"
$URI = "https://management.core.windows.net:8443/$subscriptionId/services/sqlservers/servers/$serverName/databases/$databaseName/events?" +
        "startDate=$startDate&intervalSizeInMinutes=$intervalSizeInMinutes&eventTypes=$eventTypes"
$certificateThumbprint = (Get-AzureSubscription -Current).Certificate.Thumbprint
$contentType = "application/xml;charset=utf-8"
$events = Invoke-RestMethod -Uri $URI -CertificateThumbprint $certificateThumbprint -Headers $headers `
							-Method $method -Verbose -ContentType $contentType

As long as the operation is successfully initiated and completed, you should see messages indicating its progress (reflecting a 0-byte GET operation payload and non-zero application/xml response payload), which should resemble the following:

VERBOSE: GET https://management.core.windows.net:8443/subscriptionID/services/sqlservers
/servers/yourservername/databases/yourdatabasename/events?startDate=2014-08-20T00:00:00&intervalSizeInMinutes=60
&eventTypes=connection_successful with 0-byte payload
VERBOSE: received 2184-byte response of content type application/xml; charset=utf-8

As indicated by the message above, the response body is delivered as a System.Xml.XmlDocument object. We can extract its content by querying the value of the OuterXml property of the XML-based $events object ($events.OuterXml), which should yield the output in the form:

<ServiceResources xmlns="http://schemas.microsoft.com/windowsazure" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <ServiceResource>
    <Name>60AdventureWorks20122014-09-1T12:00:00connectivityconnection_successful0</Name>
    <Type>Microsoft.SqlAzure.EventLog</Type>
    <State>Normal</State>
    <SelfLink>https://management.core.windows.net:8443/<i>subscriptionID</i>
      /services/sqlservers/servers/<i>yourservername</i>/events/60<i>yourdatabasename</i>
      2014-09-01T12:00:00connectivityconnection_successful0</SelfLink>
    <ParentLink>https://management.core.windows.net:8443/<i>subcriptionID</i>
      /services/sqlservers/servers/<i>yourservername</i></ParentLink>
    <DatabaseName><i>yourdatabasename</i></DatabaseName>
    <StartTimeUtc>2014-09-01T12:00:00</StartTimeUtc>
    <IntervalSizeInMinutes>60</IntervalSizeInMinutes>
    <EventCategory>connectivity</EventCategory>
    <EventType>connection_successful</EventType>
    <EventSubtype>0</EventSubtype>
    <EventSubtypeDescription>connection_successful</EventSubtypeDescription>
    <NumberOfEvents>1</NumberOfEvents>
    <Severity>0</Severity>
    <Description>Connected successfully to database.</Description>
    <AdditionalData i:nil="true" />
    </ServiceResource><ServiceResource>
    <Name>60<i>yourdatabasename</i>2014-09-26T12:00:00connectivityconnection_successful0</Name>
    <Type>Microsoft.SqlAzure.EventLog</Type>
    <State>Normal</State>
    <SelfLink>https://management.core.windows.net:8443/<i>subcriptionID</i>
       /services/sqlservers/servers/<i>yourservername</i>/events/60<i>yourdatabasename</i>
       2014-09-26T12:00:00connectivityconnection_successful0</SelfLink>
    <ParentLink>https://management.core.windows.net:8443/<i>subcriptionID</i>
       /services/sqlservers/servers/<i>yourservername</i></ParentLink>
    <DatabaseName><i>yourdatabasename</i></DatabaseName>
    <StartTimeUtc>2014-09-26T12:00:00</StartTimeUtc>
    <IntervalSizeInMinutes>60</IntervalSizeInMinutes>
    <EventCategory>connectivity</EventCategory>
    <EventType>connection_successful</EventType>
    <EventSubtype>0</EventSubtype>
    <EventSubtypeDescription>connection_successful</EventSubtypeDescription>
    <NumberOfEvents>16</NumberOfEvents>
    <Severity>0</Severity>
    <Description>Connected successfully to database.</Description>
    <AdditionalData i:nil="true" />
  </ServiceResource>
</ServiceResources>

This concludes our overview of managing Azure SQL Database by using Windows PowerShell and REST APIs. In our upcoming articles we will explore more some of the new functionality introduced recently in SQL Database.

See all articles by Marcin Policht