Microsoft Azure SQL Database Step by Step

Chapter 9. Monitoring and management

—Eric Boyd

Any service you intend to use in a production application must provide monitoring and management capabilities. Monitoring should provide insight into the health of the service and, ultimately, the health of your application. So, in the first half of this chapter, you will learn how to monitor SQL Database using the management portal, the Service Dashboard, and built-in dynamic management views and functions.

In previous chapters, you managed SQL Database using graphical user interface (GUI) tools such as the Microsoft Azure management portal, SQL Database management portal, SQL Server Management Studio (SSMS), and SQL Server Data Tools (SSDT). These GUI tools are convenient when you are getting started, but as your applications mature and you move toward production, you identify processes that are frequently repeated and GUIs become inconvenient. To save time and reduce human error, you need to automate these processes. Thus, the second half of this chapter teaches you how to manage SQL Database using the Microsoft Azure Service Management Application Programming Interface (API).

Creating the sample database

The procedures throughout this chapter work with a server that already has a database on it, such as the WineCloudDb database you worked with in other chapters. If you haven’t created this database, or you deleted it, you can create it by following the steps in this section. If you already have a WineCloudDb database created from work in any other chapter, you can skip the steps in this section and proceed to the “Monitoring” section.

For the exercises in this chapter, use the script shown in Listing 9-1 to create WineCloudDb and populate it with a few wines and customers.

LISTING 9-1 Script to create the sample WineCloudDb database

CREATE TABLE Wine (
WineId int PRIMARY KEY,
Name nvarchar (50),
Category nvarchar (15),
Year int,
Price money)

CREATE TABLE Customer (
CustomerId int PRIMARY KEY,
FirstName nvarchar(50),
LastName nvarchar(15),
FavoriteWineId int,
CONSTRAINT FK_Customer_Wine FOREIGN KEY (FavoriteWineId) REFERENCES Wine (WineId))

INSERT INTO Wine VALUES
(1, 'Chateau Penin', 'Bordeaux', 2008, 34.90),
(2, 'McLaren Valley', 'Cabernet', 2005, 48.50),
(3, 'Mendoza', 'Merlot', 2010, 42.00),
(4, 'Valle Central', 'Merlot', 2009, 52.00)

INSERT INTO Customer VALUES
(1, 'Jeff', 'Hay', 4),
(2, 'Mark', 'Hanson', 3),
(3, 'Jeff', 'Phillips', 2)

To create the WineCloudDb database, follow these steps:

1. From the Windows Start screen, launch SSMS. You can either scroll through the app tiles to find it (in the Microsoft SQL Server 2012 category) or just type sql server management studio to run a search, and then click on the tile. After a brief moment, the Connect To Server dialog appears.

2. In the Connect To Server dialog, do the following:

a. For Server Name, type <servername>.database.windows.net. This is the fully qualified name to the SQL Database server, where <servername> should be replaced by the name assigned to your server.

b. For Authentication, select SQL Server Authentication from the drop-down list. (SQL Database does not support Windows Authentication.)

c. For Login and Password, type the user name and password you assigned the server when you created it.

d. Click the Connect button.

3. In the Object Explorer, expand the Databases node.

4. If the WineCloudDb database exists from a previous chapter, delete it now.

a. Right-click the WineCloudDb database, and choose Delete.

b. In the Delete Object dialog, click OK.

5. Create the database.

a. In the Object Explorer, right-click the server name and choose New Query to open a new query window connected to the master database.

b. In the query window, type CREATE DATABASE WineCloudDb.

c. Press F5 (or click the Execute button in the toolbar) to create the database.

d. Close the query window without saving the script.

6. In the Object Explorer, right-click the Databases node and choose Refresh. The WineCloudDb database you just created should now appear.

7. Right-click the WineCloudDb database, and choose New Query to open a new query window connected to the WineCloudDb database.

8. Type the code shown in Listing 9-1 into the query window (or paste it in from the listing file downloaded from the book’s companion website).

9. Press F5 (or click the Execute button in the toolbar) to create the database schema and populate some data.

You now have a new WineCloudDb database you can use for the exercises in this chapter.

Note

It was necessary to create the database and populate it in two separate query windows because SQL Database does not support the USE statement found in SQL Server for switching the connection from the master database to the WineCloudDb database. See Chapter 3, “Differences between SQL Server and Microsoft Azure SQL Database,” for more information on differences between SQL Database and SQL Server.

Monitoring

SQL Database provides multiple options for monitoring the health and operations of your servers and databases. In this section, you will learn how to monitor SQL Database using the Microsoft Azure management portal, Microsoft Azure Service Dashboard, SQL Database management portal, and dynamic management views and functions. Using a combination of the tools described in this section, you will be able to get a comprehensive view of the health of your servers and databases in SQL Database.

Using the management portal

The Microsoft Azure management portal is the centralized place to manage all your Microsoft Azure services, including SQL Database. Using the management portal, you can create and delete SQL Database servers, create and delete databases, and manage your servers and databases, just as you’ve seen throughout this book. In addition, the management portal also provides monitoring and dashboard capabilities that help you understand the usage and health of your servers and databases. If you are managing multiple databases in SQL Database, you might want to quickly view how many databases you have deployed and how many databases you have remaining in your SQL Database quota. Just as with database size limits, SQL Database has limits on the number of databases each server can contain. At the time of this writing, the quota is 150 databases per server.

Follow these steps to view SQL Database server usage:

1. Log in to the Microsoft Azure management portal at https://manage.windowsazure.com. This brings you to the main portal page showing ALL ITEMS.

2. Click SQL DATABASES in the vertical navigation pane on the left.

3. Click the SERVERS link at the top of the page. This displays a list of your Microsoft Azure SQL Database servers.

4. In the NAME column, click the server that contains the WineCloudDb database. This opens a page with links for the selected server.

5. Click the DASHBOARD link at the top of the page. This displays the SQL Database server usage overview as shown in Figure 9-1.

FIGURE 9-1 Viewing SQL Database server usage

The SQL Database server usage overview displays a bar that shows you the number of databases that are currently in use and the number of databases remaining in your server’s quota. Figure 9-1 is showing the usage overview for a SQL Database server named kdhlvxlmhi. The server has one database in use out of the 150-database quota.

In addition to showing server usage and quotas, the Microsoft Azure management portal displays usage and operational metrics for each database. The database dashboard in the management portal displays the allocated size of a database, the space that is currently used, and the remaining free space.

To view database usage metrics, follow these steps:

1. If you closed the Microsoft Azure management portal since the last procedure, log in to the Microsoft Azure management portal at https://manage.windowsazure.com. This brings you to the main portal page showing ALL ITEMS.

2. Click SQL DATABASES in the vertical navigation pane on the left. This displays a list of your databases.

3. Click the WineCloudDb database.

4. Click the DASHBOARD link at the top of the page.

5. Scroll the page down a bit, and find the “Usage Overview” section as shown in Figure 9-2. This displays the used and available storage for your database.

FIGURE 9-2 Viewing SQL Database size usage

A chart of select operational metrics is also displayed at the top of the dashboard page, as shown in Figure 9-3.

FIGURE 9-3 The metrics chart on the dashboard of a SQL Database

By default, deadlocks, failed connections, and successful connections are displayed for the past hour. You can change the reporting period from one hour to 24 hours, 7 days, or 14 days. You can also toggle the chart between showing relative values, which display the actual values relative to each other, or absolute values, which display the actual values relative to zero as displayed on the Y axis. Both can be configured using the drop-down lists above the chart on the right side. By clicking the check mark to the left of each named metric above the chart, you can also show and hide the metrics plotted on the chart. When you click the refresh button in the upper-right corner above the chart, the metrics displayed on the chart are updated.

The monitor page displays the details for each metric and allows you to customize which metrics are displayed in the details list and on the chart. In addition to the deadlocks, failed connections, and successful connections (which are displayed by default), you can choose to display metrics for connections that were blocked by the firewall, current database size, and throttled connections.

More Info

For more information on the SQL Database firewall, see Chapter 2, “Configuration and pricing,” and Chapter 5, “Security and backup.” For more information on connection management and throttled connections, see Chapter 8, “Designing and tuning for scalability and high performance.”

Follow these steps to display additional metrics:

2. Click SQL DATABASES in the vertical navigation pane on the left. This displays a list of your databases.

3. Click the WineCloudDb database.

4. Click the MONITOR link at the top of the page. This displays the metrics chart at the top and the list of metrics with their minimum, maximum, average, and total values in the list below, as shown in Figure 9-4.

FIGURE 9-4 SQL Database Monitor page, with the chart and details for the configured metrics

5. Click the ADD METRICS button at the bottom of the page to display the CHOOSE METRICS dialog.

6. Select the Blocked By Firewall check box at the top of the list as shown in Figure 9-5.

FIGURE 9-5 The Choose Metrics dialog of the Monitor page for a SQL Database

7. Click the check mark in the lower-right corner to close the dialog.

You have now added the Blocked By Firewall metric to the list of metrics displayed at the bottom of the Monitor page. By default, the newly added metrics are not added to the chart at the top. To display the metric on the chart, click the gray circle to the left of the metric name in the list of metrics. After you click the gray circle icon, the icon will be changed to a colored circle with a check mark and the metric will be displayed in the chart, as shown in Figure 9-6.

FIGURE 9-6 A newly added metric in the list of metrics on the Monitor page of a SQL Database

To delete a metric from the list of displayed metrics and the chart, select the metric in the list. This displays a DELETE METRIC button at the bottom of the page to the right of the ADD METRICS button. Click the DELETE METRIC button, and the metric will be removed from the list and the chart.

Microsoft Azure Service Dashboard

The health of your SQL Database servers and databases depend on the health of the overall Microsoft Azure SQL Database service. Data centers, servers, and cloud services can experience health issues and service outages for various reasons. Knowing when there’s an issue with the overall service can save you a lot of time and frustration when troubleshooting your application issues. The Microsoft Azure Service Dashboard (shown in Figure 9-7), is a website that shows the current and historical health of all the services in Microsoft Azure. Browse tohttp://azure.microsoft.com/en-us/support/service-dashboard/ to display the Microsoft Azure Service Dashboard.

FIGURE 9-7 The Microsoft Azure Service Dashboard

Each Microsoft Azure service is listed on the Service Dashboard page. To the left of each service name is an icon that represents the current state of each service. If the service has a green check mark next to it, the service is operating normally. If it has an orange triangle warning icon, the performance of the service is not normal and is currently running with degraded performance. If a red circle error icon is displayed, the service is experiencing an outage. Clicking the plus sign to the left of the service name expands the list of regions where the service is deployed, and you can view the health of the service in each region. Clicking the RSS icon on the right side of each row displays an RSS feed with a descriptive status history, as shown in Figure 9-8. You can subscribe to the Service Dashboard RSS feeds with an RSS reader, and you can also create a custom operations dashboard using the RSS feeds as an API.

FIGURE 9-8 The SQL Database Service Dashboard RSS feed

By default, the Service Dashboard automatically refreshes the service statuses every 10 minutes. The refresh interval can be set to 1 minute, 2 minutes, 5 minutes, 10 minutes, or Off to disable the automatic refresh. You can also filter the displayed data center regions to show only the regions that are relevant to you. When you use Microsoft Azure for managed production workloads, the Service Dashboard is a website that can be displayed on a monitor in your operations center so that you can stay informed of any service interruptions in Microsoft Azure.

SQL Database management portal

The SQL Database management portal (which was introduced in Chapter 1, “Getting started with Microsoft Azure SQL Database”) is another useful tool when monitoring SQL Database usage and performance. Allocated database capacity, current database size, and available free space are displayed on the database summary page, along with active users and connections, as shown in Figure 9-9.

FIGURE 9-9 The SQL Database management portal database summary page

Monitoring and troubleshooting query performance can be a difficult task. Tools that help you identify bottlenecks and pinpoint improvement opportunities can help you be more effective when optimizing queries. The SQL Database management portal provides tools to help you identity inefficient queries.

To view performance of recent queries, follow these steps:

2. Click SQL DATABASES in the vertical navigation pane on the left. This displays a list of your databases.

3. Click the WineCloudDb database.

4. Click the MANAGE button at the bottom of the page to open the SQL Database management portal.

5. Type the username (for example, saz) and password you assigned when you created the server, and click Log On. This displays the Summary page for the database, as shown earlier in Figure 9-9.

6. Click the Query Performance link at the top of the page to display the list of high-cost queries, as shown in Figure 9-10. You can sort the table of queries by clicking on the headers at the top.

FIGURE 9-10 The Query Performance page in the SQL Database management portal

7. Click on one of the queries in the list to display the Query Plan Details. The portal displays the query, the resources used by the query, and the details of the query’s execution plan, as shown in Figure 9-11.

FIGURE 9-11 The Query Plan Details area in the SQL Database management portal.

Note

The query displayed on the Query Plan Details page is read-only. To edit the query, click the Edit button in the ribbon at the top of the page.

8. Click the Query Plan link below the query to display the execution plan, as shown in Figure 9-12. Using the buttons on the right, above the graphical query plan, you can toggle the display of the execution plan display between the graphical, grid, and tree views. The icons on the left side enable you to highlight the operations of the execution plan based on CPU or IO cost and the types of operations.

FIGURE 9-12 The Graphical Query Plan in the SQL Database management portal

9. Click an operation in the execution plan to view the details and performance cost of the operation, as shown in Figure 9-13. If you are viewing a large execution plan with many operations, the graphical execution plan can be zoomed in and out.

FIGURE 9-13 The Operation details in the SQL Database management portal graphical execution plan

You have now used SQL Database management portal to identify queries in your database with a high performance cost. Using this portal, you can drill into a query’s execution plan, identify expensive operations, and review the details of a specific operation. The SQL Database management portal gets the expensive queries displayed in the Query Performance page from SQL Database dynamic management views, which are explained in the next section.

Dynamic management views and functions

Dynamic management views (DMV) and dynamic management functions (DMF) give insight into operations and resource consumption inside Microsoft Azure SQL Database. DMVs were first introduced in SQL Server 2005 and are designed to provide an alternative to using system tables and functions to monitor SQL Server. SQL Database includes a subset of these SQL Server dynamic management views that help monitor servers and databases, and that help diagnose performance problems caused by blocked or long-running queries, inefficient execution plans, and resource bottlenecks. SQL Database includes support for three categories of DMVs: database DMVs, execution DMVs, and transaction DMVs. All DMVs are defined in the sys schema.

Database

Database-related dynamic management views and functions provide database focused insight and metrics. The database-related DMVs included in SQL Database are displayed in Table 9-1. The database-related DMFs are displayed in Table 9-2.

TABLE 9-1 Database-related dynamic management views

TABLE 9-2 Database-related dynamic management functions

One database metric that is particularly important to monitor is database size. If the size of your database reaches the size quota, your queries will return error code 40544. (See Chapter 2 for more information about the database size quota.) When you reach your size quota, you cannot insert or update data or create new database objects until you increase the maximum size of your database or delete data. Using DMVs, you can monitor the size of your database proactively and take the appropriate actions before your applications receive errors resulting from a database that has reached its size quota.

More Info

SQL Database raises errors unique to SQL Database, and each error is identified with an error code or error number. These errors include general errors for features, objects, and syntax not supported by SQL Database, errors that occur when copying databases, and connection errors. For more information and a list of the SQL Database error codes, visit http://msdn.microsoft.com/en-us/library/ff394106.aspx.

To calculate the size of your database using database DMVs, follow these steps:

2. Click SQL DATABASES in the vertical navigation pane on the left. This displays a list of your databases.

3. Click the WineCloudDb database.

4. Click the MANAGE button at the bottom of the page to open the SQL Database management portal.

5. Type the user name (for example, saz) and password you assigned when you created the server, and click Log On. This displays the Summary page for the database.

6. Click New Query in the ribbon at the top.

7. Type SELECT SUM(reserved_page_count) * 8.0 / 1024 AS ‘MBs Used’ FROM sys.dm_db_partition_stats, and click Run in the ribbon at the top. This executes the query and displays the current database size, as shown in Figure 9-14.

FIGURE 9-14 Getting database size using DMVs in the SQL Database management portal

Obtaining the current database size, as you did in the preceding steps, is an example of using database DMVs. The other database DMVs can be used to get insight into index usage, indexes that are needed, and wait time encountered during query execution.

Execution

Execution-related DMVs provide insight into connections, sessions, and the requests that your SQL Database servers receive. The execution-related DMVs that are included in SQL Database are listed in Table 9-3. Execution-related DMFs are listed in Table 9-4.

TABLE 9-3 Execution-related dynamic management views

TABLE 9-4 Execution-related dynamic management functions

Database connections are finite resources that get managed both on-premises and in the cloud. Using execution DMVs, you can view all the active SQL Database connections and see connection properties such as the login, how much CPU time the session is consuming, when the last request occurred, and more.

To view the active SQL Database connections and sessions using execution DMVs, follow these steps:

2. Click SQL DATABASES in the vertical navigation pane on the left. This displays a list of your databases.

3. Click the WineCloudDb database.

4. Click the MANAGE button at the bottom of the page to open the SQL Database management portal.

5. Type the user name (for example, saz) and password you assigned when you created the server, and click Log On. This displays the Summary page for the database.

6. Click New Query in the ribbon at the top.

7. Type SELECT e.connection_id, s.session_id, s.login_name, s.cpu_time, s.last_request_end_time FROM sys.dm_exec_sessions s INNER JOIN sys.dm_exec_connections e ON s.session_id = e.session_id, and click Run in the ribbon at the top. This executes the query and displays the connection and session results, as shown in Figure 9-15.

FIGURE 9-15 Viewing SQL Database connections and sessions using execution DMVs in the SQL Database management portal

Execution-related DMVs and DMFs provide information about connections, sessions, requests, and execution performance. These DMVs and DMFs are useful when troubleshooting connection problems and poor query performance.

Transaction

Transaction-related DMVs provide information about database transactions and locks. The transaction-related DMVs included in SQL Database are listed in Table 9-5. SQL Database does not contain transaction-related DMFs.

TABLE 9-5 Transaction-related dynamic management views

Database contention, locking and deadlocks can cause application unresponsiveness, hangs, and errors. You can use transaction-related DMVs to view current locking activity and blocking in your SQL Database.

Event tables

Dynamic management views are very helpful tools when monitoring and troubleshooting current activity, but if you are trying to research issues that occurred in the past, dynamic management views cannot help with that because they provide information about current activity. To help investigate issues that occurred in the past and are no longer occurring, SQL Database introduced two catalog views in the master database, named sys.database_connection_stats and sys.event_log. These event tables, as they are commonly referred to, collect and store database events that can be used to troubleshoot past behavior.

The sys.database_connection_stats view provides a summary of database connections, both successful connections and failed connections. The sys.event_log view contains the details of connectivity-related events. Together these views can be used to troubleshoot database activity, including failed connections, terminated connections, throttled connections, and deadlocks. Both of these views exist in the master database and can be queried using your tool of choice, including SQL Server Management Studio, SQL Server Data Tools, or the SQL Database management portal.

Programming the Service Management REST API

The ability to monitor the health of services like SQL Database is essential when using them for production workloads. In the previous section, you monitored SQL Database using the Microsoft Azure management portal, Microsoft Azure Service Dashboard, SQL Database management portal, and the SQL Database dynamic management views. Using these GUIs to manage SQL Database can be easy to get started, but automating the management of frequently repeated tasks becomes important as you get more comfortable with SQL Database and your applications mature. In this section, you will learn how to automate management of repeated SQL Database tasks using the Service Management REST API.

Note

The Azure PowerShell cmdlets can also be used to effectively automate the management of SQL Database. See Chapter 2 for detailed information on downloading, installing, and using the PowerShell cmdlets for Microsoft Azure SQL Database.

The Service Management API lies at the core of all the services in Microsoft Azure. The Service Management API can be used to create new SQL Database servers and databases, manage the SQL Database firewall rules, and even reset the administrator password. This API is based on HTTP and REST, is publicly accessible, and can be used by any device or platform that can issue HTTP requests. SQL Database APIs can be grouped into three major categories: APIs for managing SQL Database servers, APIs for managing databases, and APIs for managing server-level firewall rules.

To use the Service Management API, you must authenticate and be authorized to manage the Microsoft Azure subscription. You can authenticate using an X.509 v3 certificate (referred to as a management certificate) or using OAuth 2.0 with Microsoft Azure Active Directory. To authenticate with a management certificate, you must either have a management certificate that is already added to your Microsoft Azure subscription or create a new X.509 v3 certificate to use as a management certificate.

Follow these steps to create a new management certificate:

1. Launch the Developer Command Prompt as an administrator. An easy way to do this is to press the Windows key, type Developer Command Prompt, right-click on the Developer Command Prompt result that is displayed, and click Run As Administrator in the toolbar at the bottom of the screen. This will launch the Developer Command Prompt as an administrator.

2. Type makecert -sky exchange -r -n “CN=Microsoft Azure Service Management Certificate” -pe -a sha1 -len 2048 -ss My “<file-path>\MicrosoftAzureServiceManagementCertificate.cer”, and press Enter. This will create a new X.509 v3 certificate, add it to your personal certificate store, and save your certificate in a file named MicrosoftAzureServiceManagmentCertificate.cer in the folder you specified.

Note

Replace <file-path> with the path to the folder where you want your new X.509 certificate saved.

Now that you have created a new X.509 v3 certificate to use for your Microsoft Azure management certificate, you need to upload it to your Microsoft Azure subscription.

To upload your management certificate to Microsoft Azure, follow these steps:

2. Click SETTINGS at the bottom of the vertical navigation pane on the left.

3. Click the Management Certificates link at the top of the page to display your management certificates.

4. Click the Upload button at the bottom of the page to display the Upload A Management Certificate dialog, as shown in Figure 9-16.

FIGURE 9-16 The Upload A Management Certificate dialog in the Microsoft Azure management portal

5. Click the folder icon, and locate the MicrosoftAzureServiceManagementCertificate.cer file you created in the previous procedure.

6. Click the check mark in the lower right corner to upload your management certificate. Once the upload is finished, your management certificate will be displayed in the list of management certificates, as shown in Figure 9-17.

FIGURE 9-17 The list of management certificates in the Microsoft Azure management portal

You have now created an X.509 v3 certificate and uploaded it to your Microsoft Azure subscription’s management certificates. With the certificate in place, you can authenticate to the Microsoft Azure Service Management API and use the API to manage SQL Database.

In the following procedure, you will create a console application that makes HTTP requests to the Service Management API to create a database. The console application code shown in Listing 9-2 retrieves your management certificate from your local certificate store by the thumbprint of the certificate. It then builds an HttpWebRequest for the Service Management REST API, adds the management certificate to the request, and executes the request asynchronously. The results and response for the request are written to the console once the request has completed.

LISTING 9-2 The console application code using the Microsoft Azure Service Management REST API

using System;
using System.IO;
using System.Net;
using System.Security.Cryptography.X509Certificates;
using System.Text;

namespace AzureServiceManagementApi
{
class Program
{
static void Main()
{
var subscriptionId = "<subscription-id>";
var certThumbprint = "<certificate-thumbprint>";
var serverName = "<server-name>";
var databaseName = "<database-name>";

var certificateStore = new X509Store(StoreName.My, StoreLocation.CurrentUser);
certificateStore.Open(OpenFlags.ReadOnly);
X509Certificate2Collection certs = certificateStore.Certificates.Find
(X509FindType.FindByThumbprint, certThumbprint, false);

if (certs.Count == 0)
{
Console.WriteLine
("Couldn't find the certificate with thumbprint:" + certThumbprint);
return;
}

certificateStore.Close();

var request = (HttpWebRequest)HttpWebRequest.Create(new Uri(
"https://management.core.windows.net:8443/" +
subscriptionId +
"/services/sqlservers/servers/" + serverName + "/databases"));

request.Method = "POST";
request.ClientCertificates.Add(certs[0]);
request.ContentType = "application/xml";
request.Headers.Add("x-ms-version", "2012-03-01");

var sb = new StringBuilder("<?xml version=\"1.0\" encoding=\"utf-8\"?>");
sb.Append("<ServiceResource xmlns=\"http://schemas.microsoft.com/windowsazure\">");
sb.AppendFormat("<Name>{0}</Name>", databaseName);
sb.Append("<Edition>Web</Edition>");
sb.Append("<MaxSizeGB>1</MaxSizeGB>");
sb.Append("<CollationName>SQL_Latin1_General_CP1_CI_AS</CollationName>");
sb.Append("</ServiceResource>");

var formData = UTF8Encoding.UTF8.GetBytes(sb.ToString());
request.ContentLength = formData.Length;

using (var postStream = request.GetRequestStream())
{
postStream.Write(formData, 0, formData.Length);
}

Console.WriteLine("Creating Database: " + databaseName);

try
{
RequestState state = new RequestState();
state.Request = request;
IAsyncResult result = request.BeginGetResponse(RespCallback, state);
}
catch (WebException ex)
{
var error = new StreamReader(ex.Response.GetResponseStream()).ReadToEnd();
Console.WriteLine("Error: " + error);
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex.Message);
}
Console.ReadKey();
}

public static string EncodeToBase64String(string original)
{
return Convert.ToBase64String(Encoding.UTF8.GetBytes(original));
}

private static void RespCallback(IAsyncResult result)
{
var state = (RequestState)result.AsyncState;
var request = state.Request;

var response = (HttpWebResponse)request.EndGetResponse(result);

var statusCode = response.StatusCode.ToString();
var reqId = response.GetResponseHeader("x-ms-request-id");

Console.WriteLine("Creation Return Value: " + statusCode);
Console.WriteLine("RequestId: " + reqId);
}
}

public class RequestState
{
const int BufferSize = 4096;
public StringBuilder RequestData;
public byte[] BufferRead;
public WebRequest Request;
public Stream ResponseStream;
public Decoder StreamDecode = Encoding.UTF8.GetDecoder();

public RequestState()
{
BufferRead = new byte[BufferSize];
RequestData = new StringBuilder(String.Empty);
Request = null;
ResponseStream = null;
}
}
}

To create a console application that creates a new SQL Database using the Service Management API, follow these steps:

1. Launch Visual Studio 2013 as an administrator. From the Windows start screen, you can either scroll through the tiles to find it or just type visual studio 2013 to run an app search. Right-click on the Visual Studio 2013 tile or result, and click Run As Administrator in the toolbar at the bottom of the screen. This will launch Visual Studio 2013 as an administrator.

Note

Running Visual Studio 2013 as an administrator is required because the console application needs to get a certificate from the local certificate store to authenticate with the Microsoft Azure Service Management API. To debug the console application as an administrator, you need to run Visual Studio as an administrator.

2. Click the FILE menu, and then choose New | Project to display the New Project dialog.

3. On the left of the New Project dialog, expand Templates, Visual C# and choose Console Application.

4. Name the solution and project AzureServiceManagementApi, and choose any desired location, as shown in Figure 9-18.

FIGURE 9-18 Creating a new console application in Visual Studio 2013

5. Replace the template code in the Program.cs with the code shown earlier in Listing 9-2, and change the values of the variables at the top as follows:

a. Replace <subscription-id> with your Microsoft Azure subscription Id. This can be found under Settings | Subscriptions in the Microsoft Azure management portal.

b. Replace <certificate-thumbprint> with the thumbprint of your management certificate. This can found under Settings | Management Certificates in the Microsoft Azure management portal.

c. Replace <server-name> with the name of the server that was assigned when you created your SQL Database server.

d. Replace <database-name> with WineCloudDbMgmtApi. This is the name of the database that will be created by the console application using the Service Management API.

6. Press F5 or click Debug | Start Debugging. This opens the console application and creates the database, as shown in Figure 9-19.

FIGURE 9-19 Creating a database in SQL Database using the Service Management API

You have successfully created a SQL Database using the REST-based Microsoft Azure Service Management API in a console application. You can verify your database was created using the Microsoft Azure management portal by finding your new database in the SQL Database server that you used in the previous procedure. In addition to creating databases, you can create, update, delete, and view SQL Database resources, including servers, databases, and firewall rules. For more information on the available Service Management APIs for SQL Database, visithttp://msdn.microsoft.com/en-us/library/gg715283.aspx.

Summary

This chapter introduced you to monitoring SQL Database using graphical user interfaces and tools like the Microsoft Azure management portal, Microsoft Azure Service Dashboard, and SQL Database management portal. You also peeked inside the operations and performance of your SQL Database using dynamic management views and functions, and SQL Database event tables. Services you intend to use in production applications must provide monitoring capabilities, and Microsoft Azure SQL Database provides numerous monitoring options.

You then automated the management of your SQL Database using the Service Management APIs. The Service Management APIs are a collection of REST web APIs that enable you to programmatically manage your services in Microsoft Azure. These REST APIs are central to Microsoft Azure management and provide the foundation for graphical management tools like the Microsoft Azure management portal, and even command-line interfaces like the Microsoft Azure PowerShell cmdlets. As your applications mature and you move toward production, you identify frequently repeated operations that are great candidates for automating using the Service Management API (or PowerShell). It’s important to automate these processes to save time and reduce human error.

This chapter introduced you to SQL Database monitoring and management capabilities, and it provided a foundation for you to build upon. Now you can dig deeper into monitoring SQL Database by creating your own monitoring queries using DMVs and DMFs. You can also further explore Service Management REST APIs (as well as the PowerShell cmdlets covered in Chapter 2) that enable you to automate management of SQL Database servers, database, firewall rules, and more.