Featured Database Articles

How to Manage Microsoft Azure SQL Database Using PowerShell with REST APIs

In our previous article published on this forum, we have presented a somewhat unorthodox (from the perspective of a typical DBA or IT Pro - but certainly not that of a developer) method of managing Azure SQL Database that involves directly invoking REST APIs, providing an alternative to PowerShell-based scripting, which relies on Azure module cmdlets to run the underlying managed code. While considerably more challenging, such an approach offers more flexibility, facilitating functionality that is not directly exposed via Azure Portal or Azure PowerShell cmdlets. We have illustrated the principles of this methodology through a couple of fairly straightforward examples, enumerating all SQL Database servers in a given subscription and, subsequently, creating a new server (effectively, emulating Get-AzureSqlDatabaseServer and New-AzureSqlDatabaseServer cmdlets). Now, it is time to show some of the unique benefits of REST APIs by demonstrating their unique capabilities, not available via PowerShell.

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 the 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 compatiblity with a specific version of HTTP protocol specifications (1.1 in our case).

Body: representing the payload intended for data pertinent to the requested operation.

Let's start with setting an administrative password (note that this task can also be carried out by clicking on the Reset Administrator Password link on the Dashboard page of each individual server in the Azure Management Portal). As per Microsoft Azure Online Documentation, our individual components will take the following format (where NewPassword is the one which you want to apply):

With these settings identified, we are ready to put together a PowerShell script that will reset the administrative password of an existing Azure-resident SQL Server instance within our current subscription. As before, we will assume that we already have configured certificate-based authentication (for details regarding this configuration, refer to one of our earlier articles). We will use individual variables to store values of parameters of the Invoke-RestMethod cmdlet representing components of the REST API operation (i.e. $method, $headers, $URI, and $body) as well as auxiliary parameters such as $serverName, $newPassword, $subscriptionID and $certificateThumbprint:

We can track the progress of execution by observing verbose messages (representing the POST and receive operations).

Now, let's turn our attention to the second operation, which allows you to extract content of the SQL Server event logs (note that you can obtain this data via Azure Portal, by logging on to the server and switching to the Events tab of its Administration workspace). As per Microsoft Azure Online Documentation, the request must include subscription ID, target SQL Server name, startDate and intervalSizeInMinutes, as well as an event type you are interested in. Effectively, this yields the following set of REST API invocation components:

Here as well, 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-RestMethod cmdlet 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):

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:

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:

This concludes our overview of the managing SQL Server instances hosting Microsoft Azure SQL Databases by using Windows PowerShell and REST APIs. In our upcoming articles, we will explore more common database-level administrative tasks that can be implemented in the similiar manner.