This is the second part of the series introduced at: https://www.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]](file:///C:\Users\cfuller\AppData\Local\Temp\WindowsLiveWriter1286139640\supfiles149ADBC9\image121.png "clip_image001[4]")
Import the management pack adds management pack version number 1.5.4.0.
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]](file:///C:\Users\cfuller\AppData\Local\Temp\WindowsLiveWriter1286139640\supfiles149ADBC9\image129.png "clip_image001[8]")
Rules
![clip_image002[4]](file:///C:\Users\cfuller\AppData\Local\Temp\WindowsLiveWriter1286139640\supfiles149ADBC9\image171.png "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.
Tasks
The tasks available in the management pack make it easier to open the Azure portal from the Operations Manager console.
Templates
The Windows Azure SQL Database management pack template provides the mechanism to configure monitoring for SQL databases in Azure.
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]](file:///C:\Users\cfuller\AppData\Local\Temp\WindowsLiveWriter1286139640\supfiles149ADBC9\image138.png "clip_image001[10]")
And create a new database (a custom database example is shown below).
![clip_image002[6]](file:///C:\Users\cfuller\AppData\Local\Temp\WindowsLiveWriter1286139640\supfiles149ADBC9\image146.png "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]](file:///C:\Users\cfuller\AppData\Local\Temp\WindowsLiveWriter1286139640\supfiles149ADBC9\image187.png "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]](file:///C:\Users\cfuller\AppData\Local\Temp\WindowsLiveWriter1286139640\supfiles149ADBC9\image159.png "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]](file:///C:\Users\cfuller\AppData\Local\Temp\WindowsLiveWriter1286139640\supfiles149ADBC9\image154.png "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. 
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.
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.
Server state:
The health of the server is shown as expected in the server state view shown below.
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)
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)
Diagram view:
The diagram view works as expected showing the various SQL Azure database servers and the databases monitored within them.
Summary: The SQL Azure management pack was straightforward to implement and provided a quick way to see the health of databases in SQL Azure.