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
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


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

Featured Database Articles

MS SQL

Posted August 7, 2014

Management of Windows Azure SQL Databases via PowerShell with REST APIs

By Marcin Policht

Management of Azure SQL Databases has been greatly simplified by introduction of the Azure PowerShell module, which includes a collection of cmdlets that handle the most common administrative tasks targeting cloud-resident SQL Server instances and their databases. Their comprehensive documentation is readily available as part of the MSDN Library, and obviously can also be explored via standard PowerShell built-in methods (primarily, by using the Get-Help cmdlet). As we have pointed out in our previous article published on this forum, one of their primary benefits is an extra layer of abstraction, shielding from relatively cumbersome Microsoft Azure Service Management APIs. In general, these take the form of REST (Representational State Transfer) operations, performed over SSL with mutual X.509 v3 certificate based authentication. However, from time to time, it might be necessary to deal with these less friendly Azure REST APIs directly, whenever a particular programmable function does not have an equivalent PowerShell wrapper. Our objective is to describe the principles of such an approach and offer a few examples illustrating its use.

Before we start, you should note that throughout our presentation we will assume that you have followed our earlier advice regarding configuring Azure authentication and deployed a certificate with its private key residing in your personal store and the public one uploaded to the cloud. This is critical, since unlike Azure cmdlets (which rely implicitly on existing account or certificate-based credentials) PowerShell-based direct REST API calls need to explicitly reference a management certificate (such reference takes the form of the CertificateThumbprint parameter of the Invoke-RestMethod cmdlet, which provides the ability to execute individual REST API methods from a PowerShell session).

In order to assess the range of capabilities exposed by Azure SQL Database Management REST APIs, you should examine the content of documentation posted on the Microsoft Azure MSDN portal, which lists them in five separate categories, including Operations on Azure SQL Database Servers, Operations on Server-Level Firewall Rules, Operations on Azure SQL Databases, Operations on SQL Database Copies, and Operations on SQL Database Restore Requests. While it is not realistic to provide comprehensive coverage of each of them on this forum, we will attempt to describe a general methodology that can be applied when invoking them from PowerShell. To accomplish this, we will take advantage of the Invoke-RestMethod cmdlet, which submits arbitrary REST API calls via HTTP and HTTPS requests to REST web services and returns the outcome typically in the form of System.Xml.XmlDocument, System.String, or a custom PowerShell object (as we'll see in our examples).

For the sake of simplicity, we will start with one of the more straightforward methods named List Servers, equivalent to the Get-AzureSqlDatabaseServer cmdlet (obviously our choice in this case is driven purely by the intent to present relevant concepts in the most simplistic fashion, rather than to promote the use REST APIs instead of their PowerShell counterparts). We will follow with a somewhat more complex example involving the Create Server request, matching the functionality of the New-AzureSqlDatabaseServer cmdlet.

As you can determine by reviewing MSDN documentation, constructing a REST API call requires identifying appropriate values of several of its components:

    • Request Headers - in our List Servers and Create Server examples, this must be 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 - consists of the following subcomponents:
      • Method - designates the HTTP verb, which dictates action to be taken when invoking the API. For List Servers, this is set to GET (retrieving a list of existing SQL Server instances), while Create Server employs POST (creating a new SQL Server instance).
      • URI - takes the form of a parameterized URL (with individual parameters enclosed by braces), which is targeted when invoking the designated method. In our first example, there are two available entries (both relying on providing appropriate Subscription ID and targeting port 8443 at https://management.core.windows.net) in the format https://management.core.windows.net:8443/{subscriptionId}/services/sqlservers/servers?contentview=generic and https://management.core.windows.net:8443/{subscriptionId}/services/sqlservers/servers. The second example utilizes https://management.core.windows.net:8443/{subscriptionId}/services/sqlservers/servers only.
      • HTTP Version - indicates compatiblity with a specific version of HTTP protocol specifications (1.1 in our case).
      • Body - payload intended for data pertinent to the requested operation. For obvious reasons (since the desired outcome can be fully determined based on the other components), this is not part of the List Servers syntax. On the other hand, it is required when calling Create Server to facilitate the need for such settings as administrative credentials (Admin_Account and Admin_Password in the example below) of the new SQL Server instance or its target Azure data center (East US) and takes the following format:
<?xml version="1.0" encoding="utf-8"?>
<Server xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
  <AdministratorLogin>Admin_Account</AdministratorLogin>
  <AdministratorLoginPassword>Admin_Password</AdministratorLoginPassword>
  <Location>East US</Location>
  <Version>2.0</Version>
</Server>

Equipped with this knowledge, let's put together a sample PowerShell script that will enumerate Azure-resident SQL Server instances within our subscription. As mentioned before, we will assume that we already have configured certificate-based authentication (for details regarding this configuration, refer to our previous article). We will use individual variables to store values of parameters of the Invoke-RestMethod cmdlet representing components of the REST API operations listed above ($method, $headers, and $URI) as well as auxiliary parameters such as $subscriptionID and $certificateThumbprint:

$method = "GET"
$headerDate = '2012-03-01'
$headers = @{"x-ms-version"="$headerDate"}
$subscriptionID = (Get-AzureSubscription -Current).SubscriptionId
$URI = "https://management.core.windows.net:8443/$subscriptionID/services/sqlservers/servers"
$certificateThumbprint = (Get-AzureSubscription -Current).Certificate.Thumbprint
$serverList = Invoke-RestMethod -Uri $URI -CertificateThumbprint $certificateThumbprint -Headers $headers -Method $method -Verbose 

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

VERBOSE: GET https://management.core.windows.net:8443/abcdefgh-1234-5678-1011-lkjihgfedcba/services/sqlservers/servers with 0-byte payload
VERBOSE: received 583-byte response of content type application/xml; charset=utf-8

As mentioned before (and indicated by the message above), the response body is delivered as a System.Xml.XmlDocument object. We can extract individual names of SQL Server instances by querying values of $serverList.Servers.Server.Name elements or, alternatively, display entire XML content by leveraging the OuterXml property ($serverList.OuterXml), which should yield the output in the form:

<Servers xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
  <Server>
    <Name>abcdefghij</Name>
    <AdministratorLogin>Ub3rm3n$ch</AdministratorLogin>
    <Location>East US</Location>
    <FullyQualifiedDomainName>abcdefghij.database.windows.net</FullyQualifiedDomainName>
    <Version>1.0</Version>
  </Server>
</Servers>

Now let's apply the equivalent approach to deploy a new SQL Server instance by implementing the Create Server operation. While $headers, $URI, and $certificateThumbprint will remain the same, $method will change to POST. In addition, we will need to populate the body of the request and set its content type (stored, respectively, in the $body and $contentType variables). As we mentioned earlier, request body is an XML formatted document, which in this case must include elements specifying administrative credentials and target data center location:

<#
<?xml version="1.0" encoding="utf-8"?>
<Server xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
  <AdministratorLogin>Ub3rm3n$ch</AdministratorLogin>
  <AdministratorLoginPassword>F@hrv3rgnug3n</AdministratorLoginPassword>
  <Location>East US</Location>
  <Version>2.0</Version>
</Server>
#>

To assign this value to the $body variable, we will leverage the so called Here-Strings construct (starting with @" and ending with "@), which allows us to encapsulate an arbitrary, multi-line string of characters. The expected content type is application/xml (as you will quickly find out if you attempt to run the Invoke-RestMethod without assigning it to the -ContentType parameter). Effectively, this gives us the following script:

$method = "POST"
$headerDate = '2012-03-01'
$headers = @{"x-ms-version"="$headerDate"}
$subscriptionID = (Get-AzureSubscription -Current).SubscriptionId
$URI = "https://management.core.windows.net:8443/$subscriptionID/services/sqlservers/servers"
$certificateThumbprint = (Get-AzureSubscription -Current).Certificate.Thumbprint
$body = @"
<#
<?xml version="1.0" encoding="utf-8"?>
<Server xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">
  <AdministratorLogin>Ub3rm3n$ch</AdministratorLogin>
  <AdministratorLoginPassword>F@hrv3rgnug3n</AdministratorLoginPassword>
  <Location>East US</Location>
  <Version>2.0</Version>
</Server>
#>"@
$contentType = "application/xml;charset=utf-8"
$serverCreate = Invoke-RestMethod -Uri $URI -CertificateThumbprint $certificateThumbprint -Headers $headers -Method $method -Body $body -ContentType $contentType -Verbose

This time as well, we can verify the progress of execution by observing verbose messages (representing POST and receive actions) and retrieve details of the completed operations by querying the value of $serverCreate.OuterXml, which should display the output in the format <ServerName FullyQualifiedDomainName="jihgfedcba.database.windows.net" xmlns="http://schemas.microsoft.com/sqlazure/2010/12/">jihgfedcba</ServerName>, identifying the name of the newly created Azure SQL Server instance. In our next article, we will provide additional, more involved examples of using REST APIs to deliver SQL Database-related functionality not available directly in Azure PowerShell module.

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