Monitoring Azure in Operations Manager 2012 using the Azure SQL management pack (#SCOM, #SYSCTR, #Azure)

This is the second part of the series introduced at: http://blogs.catapultsystems.com/cfuller/archive/2013/07/01/monitoring-and-windows-azure-scom-sysctr-azure.aspx. The focus of this blog post is to provide information on what is included within this management pack, how to create a SQL database in Azure and how to monitor SQL in Azure with Operations Manager, and what see in the Operations Manager console after monitoring is in place for SQL databases in Azure.

 

Management Pack details:

To get this management pack download the Azure Applications management pack from: http://www.microsoft.com/en-us/download/details.aspx?id=38829

The MSI installs to:C:\Program Files (x86)\System Center Management Packs\Windows Azure SQL Database

And it includes a EULA.RTF, and a Microsoft.SQLServer.Azure.mpb file.

clip_image001[4]

Import the management pack adds management pack version number 1.5.4.0.

image

 

What does this management pack add to Operations Manager?

This management pack includes views, rules, monitors, tasks and a management pack template. This management pack creates the “Windows Azure SQL Database” folder in the monitoring view.

Views

clip_image001[8]

Rules

clip_image002[4]

Monitors

The set of monitors for this management pack are shown below. The monitors which are disabled by defaulted are grayed out in the screens.

clip_image003

Tasks

The tasks available in the management pack make it easier to open the Azure portal from the Operations Manager console.

clip_image004

Templates

The Windows Azure SQL Database management pack template provides the mechanism to configure monitoring for SQL databases in Azure.

clip_image005

 

How do I create a SQL database in Azure?

From the Azure portal (https://manage.windowsazure.com/), create a new SQL database as shown below by opening the SQL databases section.

clip_image001[10]

And create a new database (a custom database example is shown below).

clip_image002[6]

You will also need to specify credentials to be able to access the database, and then create a database on the new SQL server to monitor with Operations Manager.

clip_image003[5]

 

How to monitor SQL in Azure with Operations Manager:

In the authoring pane use the Windows Azure SQL Database management pack template wizard to define the properties for your Azure database. The name of the server needs to be added as well as a run as account needs to be created in the administration pane with appropriate credentials to the SQL database in Azure.

clip_image004[5]

From within the Azure portal, the name field can be taking from the ManageURL field show below, and credentials need to be created for the administrator login listed on this page as well.

clip_image005[5]

Finally specify a proxy agent which will communicate with the Azure SQL database. This server will need to be able to connect to the Internet and specifically to the Azure portal website.
clip_image006

If you receive a notification that there is an error that the firewall needs to allow this communication such as this:

Watcher Node: abc
Server name: q75vyby2mc.database.windows.net
Error: Cannot open server ‘q75vyby2mc’ requested by the login. Client with IP address ‘xyz’ is not allowed to access the server. To enable access, use the SQL Azure Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.
Login failed for user [abc]

 

It’s required to configure Azure on the portal on the configure tab for the SQL Azure to allow the client IP address to communicate with your SQL Azure database.

clip_image007

Once the SQL Azure database is monitored it will appear within the “Windows Azure SQL Database” folder in the monitoring pane.

 

What do we see in the Operations Manager console after monitoring is in place for SQL databases in Azure?

Database state:

The health of the database is shown as expected in the database state view shown below.

image

Server state:

The health of the server is shown as expected in the server state view shown below.

image

Performance counters:

And a large number of performance counters are available for the database. These performance counters include: (the full list is included so it’s easy to find these by counter name)

  • External Network Egress (KB)
  • External Network Ingress (KB)
  • Free Space (%)
  • Free Space (MB)
  • Number of Databases
  • Number of Sessions
  • Sessions Average Memory Consumption (MB)
  • Sessions Rows Returned
  • Sessions Total CPU Time (ms)
  • Sessions Total Memory Consumption (MB)
  • Sessions Total Read/Write Operations
  • Total Space Quota (MB)
  • Transactions Locks Count
  • Transactions Max Log Usage (MB)
  • Transactions Max Running Time (minutes)
  • Used Space (%)
  • Used Space (MB)

 

image

What’s the health model look like for the database?

The health model for the database is assessed using availability and performance. There are several monitors available which are not active by default (the monitors below with the empty circle are not monitored by Operations Manager by default)

image

Diagram view:

The diagram view works as expected showing the various SQL Azure database servers and the databases monitored within them.

image

Summary:  The SQL Azure management pack was straightforward to implement and provided a quick way to see the health of databases in SQL Azure.

2 thoughts on “Monitoring Azure in Operations Manager 2012 using the Azure SQL management pack (#SCOM, #SYSCTR, #Azure)

  1. Pingback: 使用 SCOM 2012 R2 監控 Azure SQL Database 的運作狀況 - TechNet Taiwan 官方部落格 - Site Home - TechNet Blogs

  2. Pingback: 使用 SCOM 2012 R2 監控 Azure SQL Database 的運作狀況 - Microsoft Azure 中文部落格 - Site Home - TechNet Blogs

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.