We are excited to introduce support for active monitoring of SQL Azure Federations!  Federations is one of the newer members to SQL Azure family.  Capable of mega-scale and unlimited size, SQL Azure Federations allow customers to distribute their data and processing power across multiple federated members (shards). While each federated member is a unique database, connecting to and keeping an active monitoring eye on all of these databases is challenging.  By using AzureWatch, database administrators can now actively monitor all federated members and be immediately notified when any one of them is getting too large, too slow, or needs other special attention.  

Experienced AzureWatch users and newcomers alike will find the setup of SQL Azure Federations to be relatively simple.  A new option on the navigation explorer enables entry of federations that need to be monitored.  

Enter the required credentials and make sure that your desktop PC has firewall access to your SQL Azure federated databases, so that the locally running AzureWatch Control Panel can connect and verify the information

Once the monitoring is running, AzureWatch will query the root database and all federated members once per minute in order to store, aggregate and evaluate vital statistics.  At the time of this writing AzureWatch will gather the following:

  • Database Size
  • Number of open transactions
  • Number of open connections
  • Number of blocking queries
  • Number of federated members (root database only)

 

In order to send out alerts when important events happen, AzureWatch will need to know how information needs to be aggregated, for how long and what are the rules that govern the generation of each alert. Users will want to decide if metrics need to be aggregated across all federated members together or by individual federated member.  For example, it may be important to know when any one of federated members has exceeded a certain size as this may be an indicator to consider a SPLIT of that federated member.  Alternatively, users may want to know when the total number of blocking queries against all federated members exceeds a certain threshold as this may indicate a problem with the application itself and would require further investigation.

This article will walk through the configuration of an alert that would trigger when any one of the federated members has exceeded 10GB in size.

Alerts generated by AzureWatch will trigger when Rules evaluate to TRUE.  Each Rule consists of a custom boolean formula that compares aggregate metrics.  For the example at hand, an aggregate metric defined by Federation Member must be setup to aggregate the Database Size raw metric.  To do so, create a new aggregate under the By Federation Member\Aggregate Metrics section as shown in the example below.

Final step, the configuration of the actual Rule that would send out an alert

The alert Rule is represented by a formula that uses aggregate metrics as variables and sends out an Email notification when its formula evaluates to TRUE.  In order to prevent a barrage of emails, setting a threshold for how frequently such emails would be sent might be prudent.  (In our example no more than once per 20 minutes)