Overview

Information in this article is related specifically to monitoring and automating SQL Azure databases with CloudMonix. To learn more about general features of CloudMonix, kindly read articles in this section.
This article is organized into a number of sections.
  • what is needed to connect to and monitor a SQL Azure database

  • what happens during the monitoring cycle

  • what specifically can CloudMonix track, visualize and monitor

  • what are all automated actions that can be executed by CloudMonix


Connectivity

Connect CloudMonix to your SQL Azure databases by running Setup Wizard or using the “Add New” functionality from the dashboard.



While the Setup Wizard will detect SQL Azure databases, in order for CloudMonix to connect to and monitor these databases, it needs username/password credentials for each database.




Username & Password

Before providing usernames/passwords, ensure that you are familiar and are comfortable with security precautions taken by the CloudMonix team to safeguard your credentials.

Vast majority of metrics that CloudMonix collects for SQL Azure require that the username has DATABASE VIEW STATE permissions in the given database. Some specific metrics, such as server-based performance counters and blocking (deadlock) queries, require that the username is a server administrator.


Creating username with VIEW STATE permission

If SA (system administrator) credentials cannot be provided, it is simplest to create a special login with DATABASE VIEW STATE permissions for CloudMonix to use. This login can later be restricted from being able to access sensitive data.
-- in the master database
CREATE LOGIN CloudMonixClient WITH PASSWORD = 'your_password_here'
CREATE USER CloudMonixClient FROM LOGIN CloudMonixClient;
 
--in each database that needs to be monitored
CREATE USER CloudMonixClient FROM LOGIN CloudMonixClient;
GRANT VIEW DATABASE STATE TO CloudMonixClient;

 


SQL Azure Firewall

CloudMonix monitoring services are hosted in Azure and utilize static IPs to perform monitoring. Those SQL Azure users who restrict access to their databases even to Azure-hosted services, will need to white-list in their firewall IP addresses specified in this article.


Configuration Dialog

When adding SQL Azure database resource manually, without the wizard, ensure that correct server name is provided. Valid credentials with VIEW DATABASE STATE permissions and correct server name will allow CloudMonix to detect and populate name of all monitor-able databases into the dropdown on configuration dialog.



Monitoring Cycle

During each and every monitoring cycle, CloudMonix will attempt to connect to monitored database and retrieve data behind configured metrics. Most of the metrics are driven by data from Dynamic Management Views (DMVs). By default, CloudMonix will attempt to connect up to 4 times, timing out within 30 seconds and with delay of 500ms between connection attempts. Frequency of monitoring cycles, connection timeout and number of attempts at reconnection are configurable on the Advanced tab. Wait period between reconnection attempts (if failures occur) is currently hardcoded at 500ms.




Depending on the metrics configured (ie: Database Operations and Event Logs), CloudMonix may also attempt to establish connection to the master database.

CloudMonix does not utilize connection pooling and will completely close any established connections upon completion of every monitoring cycle.

Metrics

When utilizing the provided sample monitoring profile, a number of metrics will already be predefined. Adding, removing, and customizing metrics can be done on the Metrics tab of configuration dialog.

Type of the metric is determined by its Category.



Important note: in addition to a number of predefined metrics that CloudMonix can track, users can also define metrics driven by their own custom SQL queries.

ResourceStatus

This is a critical metric that is captured for most types of resources that CloudMonix tracks.

When CloudMonix cannot establish connection to monitored database, ResourceStatus is Down. When connection was established successfully, ResourceStatus is Ready. Other usual values for ResourceStatus, such as Unknown or Stopped are not utilized

In addition to alerts and automation, this metric is used to drive the Uptime reports.

  • Data type: string

  • Possible values: Ready, Down

  • Desired state: Ready

  • Included in sample profile: Yes, tracked as metric named Status

  • Included in default alerts: Yes, as alert named Resource Outage

ResponseTime

Tracks the amount of time in milliseconds it took to establish a successful connection to monitored database. If connection was retried, only the last connection time counts toward the measurement. Slow response times usually indicate busy database or poor connectivity to the database.

  • Data Type: double

  • Desired state: under 1000ms

  • Included in sample profile: Yes, tracked as metric named ResourceTime

  • Included in default alerts: Yes, as alert named Slow Response

DatabaseActiveSessionCount

Tracks number of open sessions against the database.

  • Data Type: int

  • Desired state: depends on the service tier of the database. Refer to this MSDN article for Azure session limits by tier.

  • Included in sample profile: Yes, tracked as metric named ConnectionsActive

  • Included in default alerts: Yes, but as a disabled alert Open Sessions Approaching Max Limit. It is recommended that this alert is customized with a limit based on specific service tier

  • Retrieved from sys.dm_exec_sessions DMV


DatabaseSize

Tracks the size of the database in megabytes.

  • Data Type: double

  • Desired state: depends on the service tier of the database. Refer to this MSDN article for Azure session limits by tier.

  • Included in sample profile: Yes, tracked as metric named ConnectionsActive

  • Included in default alerts: Yes, but as a disabled alert named Database Approaching Max Size. It is recommended that this alert is customized with a limit based on specific service tier

  • Retrieved from sys.dm_db_partition_stats DMV

DatabaseSqlAzureDtu

Tracks overall DTU % utilization of the monitored database. Calculated as the maximum value between DatabaseSqlAzureResourceStatsCpuUtilization, DatabaseSqlAzureResourceStatsDataIOUtilization, and DatabaseSqlAzureResourceStatsLogWriteUtilization.

  • Data Type: double

  • Possible values: between 0% and 100%

  • Desired state: under 80%

  • Included in sample profile: Yes, tracked as metric named DTU

  • Included in default alerts: No, but effectively it is a part of High Utilization alert

  • Retrieved from sys.dm_db_resource_stats DMV

DatabaseSqlAzureResourceStatsCpuUtilization

 Tracks overall CPU % utilization of the monitored database.

  • Data Type: double

  • Possible values: between 0% and 100%

  • Desired state: under 80%

  • Included in sample profile: Yes, tracked as metric named Cpu

  • Included in default alerts: Yes, it is a part of High Utilization alert

  • Retrieved from sys.dm_db_resource_stats DMV


DatabaseSqlAzureResourceStatsDataIOUtilization

Tracks overall Data I/O % utilization of the monitored database.

  • Data Type: double

  • Possible values: between 0% and 100%

  • Desired state: under 80%

  • Included in sample profile: Yes, tracked as metric named DataIOUtilization

  • Included in default alerts: Yes, it is a part of High Utilization alert

  • Retrieved from sys.dm_db_resource_stats DMV


DatabaseSqlAzureResourceStatsLogWriteUtilization

 Tracks overall Log Write % utilization of the monitored database.

  • Data Type: double

  • Possible values: between 0% and 100%

  • Desired state: under 80%

  • Included in sample profile: Yes, tracked as metric named LogWriteUtilization

  • Included in default alerts: Yes, it is a part of High Utilization alert

  • Retrieved from sys.dm_db_resource_stats DMV

DatabaseSqlAzureResourceStatsMemoryUtilization

Tracks overall Memory % utilization of the monitored database. Due to the fact that SQL Azure likes to occupy maximum amount of memory possible, optimally performing database may still track with near 100% utilized memory.

  • Data Type: double

  • Possible values: between 0% and 100%

  • Desired state: under 100%

  • Included in sample profile: no

  • Included in default alerts: no

  • Retrieved from sys.dm_db_resource_stats DMV

DatabaseSqlAzureOperations

Tracks server-level database operation jobs performed in the last 24hrs. Examples are: creation of databases, deletion of databases, upgrades/downgrades in service plans, etc. Operation jobs are filtered to specific monitored database.

  • Data Type: collection of job objects with a number of properties each

  • Desired state: lack of jobs with Failed status

  • Included in sample profile: no

  • Retrieved from sys.dm_operation_status DMV


DatabaseSqlBlockingQueries

Tracks queries that are currently blocking each other, with specific SQL and session IDs. This is a useful metric to track that unfortunately required highest level credentials (Server Admin).

  • Data Type: collection of query objects with a number of properties each

  • Desired state: 0 records

  • Included in sample profile: no

  • Retrieved from sys.dm_exec_sessions, sys.dm_os_waiting_tasks, sys.dm_exec_sessions, sys.partitions, sys.dm_exec_connections, sys.dm_exec_sql_text DMVs


DatabaseSqlBlockingQueryCount

Tracks the number of deadlocking queries.

  • Data Type: int

  • Desired state: 0

  • Included in sample profile: Yes, tracked as metric named BlockingQueryCount

  • Included in default alerts: Yes, in a Blocking Queries alert

  • Retrieved from sys.dm_exec_requests DMV


DatabaseSqlConnections

Tracks currently open connections and their associated information (host, username, etc) to the monitored database. This metric can help with RCA for other alerts as it simply provides visibility as to who is currently connected to the database.

  • Data Type: collection of connection objects with a number of properties each

  • Desired state: any

  • Included in sample profile: yes, tracked as a metric named ConnectionList

  • Included in default alerts: no

  • Retrieved from sys.dm_exec_sessions, sys.dm_exec_connections DMVs


DatabaseSqlCurrentQueries

Tracks currently executing queries and their associated information (host, username, SQL, etc.) This metric can help with root-cause analysis (RCA) for other alerts as it provides visibility as to who is currently executing queries in the database.

  • Data Type: collection of query objects with a number of properties each

  • Desired state: any, but the less the better

  • Included in sample profile: yes, tracked as a metric named QueriesInProgress

  • Included in default alerts: no

  • Retrieved from sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_sql_text DMVs


DatabaseSqlEventLog

Captures latest event logs from the monitored database. This metric is skipped for SQL Azure databases version 12, due to ongoing performance issues with the DMV that contains this data.

Note: This metric is retrieved from the master database.

  • Data Type: collection of event log objects with a number of properties each

  • Desired state: any records with connection_successful type. Records with type connection_failed, connection_terminated, and especially throttling, deadlock, and throttling_long_transaction are not desired. More information about possible types here.

  • Included in sample profile: yes, tracked as a metric named EventLogs

  • Included in default alerts: no

  • Retrieved from sys.event_log DMV


DatabaseSqlNumeric

Captures numeric value from custom user-defined SQL statement. This metric is very useful in cases user-owned data value is required to be alerted on. Alternatively, this metric can track specific values in DMVs that existing set of DMV-based metrics does not track.

It is important the user-defined SQL statement can execute quickly. It is also important that the statement returns a single scalar numeric value.

Example:

 

SELECT COUNT(*) FROM Customers

 

Queries returning NULLs, non-numeric data, multiple rows, and etc. will not work properly.

Note: it is sometimes a case when data from this metric needs to be evaluated side by side with data in other resources. (For example, current count of customers may be used as a factor in scaling actions for Azure Web Roles). In such a case, use LinkedMetric to import data from SQL Azure metrics into other resources.

  • Data Type: double

  • Desired state: N/A

  • Included in sample profile: no

  • Included in default alerts: no

  • Retrieved from wherever user-defined SQL


DatabaseSqlQuery

Captures a table-based collection of data retrieved via user-defined SQL statement. This metric is very useful in cases user-owned data value is required to be alerted on. Alternatively, this metric can track specific values in DMVs that existing set of DMV-based metrics does not track.

Note: It is important the user-defined SQL statement can execute quickly. It is also important that the size of the result set is reasonable (no more than a few hundred rows and within 20-30kb). Only one result set can be returned. Furthermore, ensure that column names of the result set do not contain spaces, start with numeric characters, and contain non-alphanumeric characters. Column names should also not duplicate. Basically, column names of the resulting dataset will be convert to C# object properties before being visualized on the dashboard.

  • Data Type: collection of rows with properties as defined by SQL statement

  • Desired state: N/A

  • Included in sample profile: no

  • Included in default alerts: no

  • Retrieved from wherever user-defined SQL


DatabaseSqlTopQueries

Tracks top 10 (by combined utilization) queries and their associated information (SQL, utilization, etc). This metric can help DBAs optimize ineffective queries. Presence of queries in this list is not a problem in itself (there will always be queries in this list, if the database is being used). Top 10 queries provides a good place to investigate for possible poorly performing queries.

  • Data Type: collection of query objects with a number of properties each

  • Desired state: any

  • Included in sample profile: yes, tracked as a metric named TopQueries

  • Included in default alerts: no

  • Retrieved from sys.dm_exec_query_stats, sys.dm_exec_sql_text DMVs


WindowsPerformanceCounter

Tracks thousands (approx. 3000 at the time of this writing) SQL-server based performance counters exposed thru DMVs. Overall, the topic of performance counters is vast and far beyond the scope of this article. SQL Azure exposes performance counters thru special sys.dm_os_performance_counters DMV.

Difference between WindowsPerformanceCounter and WindowsPerformanceCounterMultiInstance is that the former is captured as a single numeric value matching specific counter and instance.

Note: performance counters can only be captured from v12+ SQL Azure databases with server administrative credentials.

  • Data Type: double

  • Desired state: depends on the counter

  • Included in sample profile: no

  • Included in default alerts: no

  • Retrieved from sys.dm_os_performance_counters DMV


WindowsPerformanceCounterMultiInstance

Tracks thousands (approx. 3000 at the time of this writing) SQL-server based performance counters exposed thru DMVs. Overall, the topic of performance counters is vast and far beyond the scope of this article. SQL Azure exposes performance counters thru special sys.dm_os_performance_counters DMV.

Difference between WindowsPerformanceCounter and WindowsPerformanceCounterMultiInstance is that the latter is captured as a collection-based metric tracking all instances of a particular counter.

Note: performance counters can only be captured from v12+ SQL Azure databases with server administrative credentials.

  • Data Type: collection of counter values with associated instance names

  • Desired state: depends on the counter

  • Included in sample profile: no

  • Included in default alerts: no

  • Retrieved from sys.dm_os_performance_counters DMV


AggregatedMetric

Aggregated allow for averaging, summing, counting, and performing other math calculations on existing metrics over a period of time. Learn more about Aggregated Metrics here.


LinkedMetric

Evaluation of metric values from other resources in the monitored environment alongside currently configured resource is done thru linking of those metrics. LinkedMetrics are the mechanism to link metrics from other resources. Learn more about Linked Metrics here.


Actions & Automation

Automation features (Actions) allow users to setup powerful reactive, proactive and scheduled actions. For SQL Azure resources, CloudMonix is capable of executing user-defined SQL scripts against the monitored database. Keep in mind that actions can be executed on any resource in the monitored environment – type of actions depend on the resource. For SQL Azure, there is only one: SQL statement.

Using SQL actions, users are able to Scale-Up/Scale-Down their database, clean up tables, rebuild indexes, and perform various administrative and business tasks.