Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted October 6, 2014

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

By Marcin Policht

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM