SQL Azure Monitoring

The actual setup of SQL Azure monitoring is fairly straightforward.  Upon successful upgrade to the latest version of Control Panel (v2.0 at the time of this post), simply visit the newly available SQL Azure option in your Control Panel explorer.

 

Add databases to be monitored by right clicking anywhere in the SQL Azure Instances window.

 

 

Fill out connection information for your SQL Azure database

 

 

Upon successfull setup of a SQL Azure database, AzureWatch will automatically instrument the capture of five key metrics: Database size, # of Open connections, # of Blocking Queries, # of Open Transactions and general Response Time.

 

It is time to aggregate the metrics over some periods of time, so that AzureWatch can properly alert you in case your Rules trigger.

 

Setting up of aggregations is performed via the next option in explorer: Aggregate Metrics.

 

 

Color ranges will help AzureWatch highlight key metrics on the dashboard

 

And finally, the setup of alerts can be performed from the Rules screen located underneath Aggregate Metrics

 

 

Congratulations! We just instructed AzureWatch to notify us if in the last 5 minutes our SQL Azure database was running any blocking queries.

We are pleased to announce the release of AzurePing: a free Azure resource-monitoring utility.  AzurePing is a simple Windows Service that pings any number of Azure Storage resources, SQL (Azure) databases, and web URL's on a continuous basis.  Any errors are logged through log4net framework via a variety of appenders, such as email, SQL, flat files, Trace, etc.  For those not familiar with log4net, it is a popular open-source logging framework that can store logging entries to a variety of extendable appenders.

To find more information about AzurePing, visit our website at http://www.paraleap.com/azureping

And please help us spread the word about AzurePing!

This article will describe techniques that we use to manage configuration changes among various projects in a large distributed Azure-based solution called AzureWatch and keep settings in sync as various environments are targeted during development, testing, and debugging sessions.  AzureWatch is an auto-scaling and monitoring system for Windows Azure applications.  By its nature it utilizes multiple WCF services, a few Windows-based clients and services, a number of web-based projects.  As readers can imagine, keeping all configuration settings in sync and "shifting" them together on demand without making a mistake can be challenging.

Visual Studio technologies utilized

• Using Visual Studio Configurations to define target debugging environments
• Using ConfigSource to split configuration files
• Pre/Post build events
• Add existing files as Links to a Project
• Config Transformations

Approach at a high level

For every debugging environment, create Visual Studio Configuration.  Break out sections from every .Config file that vary by environment and include them back via ConfigSource settings.  Include these sections as unique files into a separate Config project that has folders for every Configuration.  Also, copy *.csdef and *.csccfg files from Azure-related projects into these folders as well. In each folder, customize the broken off partial configuration files (that are included via ConfigSource) and azure configuration files. Create Post or Pre-Build event of the Config project to xcopy the files from a folder that matches current Configuration environment to root folder of Config project.  Include files from root folder of Config project into other projects as needed via “Add as Link” command.

Utilize Config transformations to instrument Production (and other environments if needed) specific configuration changes that deal with security, debugging, tracing, etc.

Now, let's look at these steps in detail:

Step 1

Create as many Visual Studio Configurations as necessary to support number of different environments that developers must be able to debug from Visual Studio

Step 2

Create a separate empty project that is a part of the overall solution called Config.  Include into the project folders, one per matching Visual Studio Configuration environment that were created in Step 1

Step 3

Break-off Connection strings, end-point configuration, and other environment-specific sections from existing .config files into separate “include-only” config sections.

Source example web.config:

  <connectionStrings configSource="config\connectionStrings.config"/>

Broken off example connectionStrings.config:

  <connectionStrings>

    <add connectionString="..." name="db_connection"></add>

  </connectionStrings>

May want to make sure that web-projects have "bin" folder as a part of the relative path: "\bin\config\partialConfigFileName.config"

Step 4

Include separate customized partial .config files and Azure-specific .cscfg files under every sub-folder within Config project

Step 5

Customize Pre-Build event for Config project to copy config files for current configuration to root folder:

xcopy /Y /R "$(ProjectDir)$(ConfigurationName)\*.config" "$(ProjectDir)"

xcopy /Y /R "$(ProjectDir)$(ConfigurationName)\*.cscfg" "$(ProjectDir)"

Step 6

Add-as-Link partial .config files into main projects from the ROOT of the Config project.  To keep things organized, feel free to include partial .config files under Config sub-folders.  Do the same with Azure-specific .cscfg files

Files added as links have a shortcut icon in Solution Explorer

Step 7

Manually add project dependencies to Config project from the other projects in the solution, that need partial config files to function.  This will ensure that Config project is built before other projects.  Project Dependencies can be found inside Solution Properties window, under "Project Dependencies" tab.

Do not forget to specify that partial config files set "Copy to Output Directory" as "Copy if Newer"

Conclusion

Now, every time you debug and run, the pre-compile event from Config project will copy all the files from its particular environment folder into its root folder.  And all other projects that link to this root folder for their partial .config files will automatically get updated .configs switched to proper environment in unison.

Revisions

This blog was revised on 06/10/2012 to include the extra XCOPY step that keeps Azure cscfg/csdef files in sync with the Config project

The strategies discussed in this article can be applied to any cloud platform that has an ability to dynamically provision compute resources, even though I rely on examples from AzureWatch auto scaling and monitoring service for Windows Azure

The topic of auto scaling is an extremely important one when it comes to architecting cloud-based systems.  The major premise of cloud computing is its utility based approach to on-demand provisioning and de-provisioning of resources while paying only for what has been consumed.  It only makes sense to give the matter of dynamic provisioning and auto scaling a great deal of thought when designing your system to live in the cloud.  Implementing a cloud-based application without auto scaling is like installing an air-conditioner without a thermostat: one either needs to constantly monitor and manually adjust the needed temperature or pray that outside temperature never changes.

Many cloud platforms such as Amazon's EC2 or Windows Azure do not automatically adjust compute power dedicated to applications running on their platforms.  Instead, they rely upon various tools and services to provide dynamic auto scaling.  For applications running in Amazon cloud, auto-scaling is offered by Amazon itself via a service CloudWatch as well as third party vendors such as RightScale.  Windows Azure does not have its own auto scaling engine but third party vendors such as AzureWatch can provide auto scaling and monitoring.

Before deciding on when to scale up and down, it is important to understand when and why changes in demand occur.  In general, demand on your application can vary due to planned or unplanned events.  Thus it is important to initially divide your scaling strategies into these two broad categories: Predictable and Unpredictable demand.  

The goal of this article is to describe scaling strategies that gracefully handle unplanned and planned spikes in demand.  I'll use AzureWatch to demonstrate specific examples of how these strategies can be implemented in Windows Azure environment.  Important note: even though this article will mostly talk about scale up techniques, do not forget to think about matching scale down techniques. In some cases, it may help to think about building an auto scaling strategy in a way similar to building a thermostat.

Unpredictable demand

Conceptualizing use-cases of rarely occurring unplanned spikes in demand is rather straight forward.  Demand on your app may suddenly increase due to a number of various causes, such as:

• an article about your website was published on a popular website (the Slashdot effect)
• CEO of your company just ordered a number of complex reports before a big meeting with shareholders
• your marketing department just ran a successful ad campaign and forgot to tell you about the possible influx of new users
• a large overseas customer signed up overnight and started consuming a lot of resources

Whatever the case may be, having an insurance policy that deals with such unplanned spikes in demand is not just smart.  It may help save your reputation and reputation of your company.  However, gracefully handling unplanned spikes in demand can be difficult.  This is because you are reacting to events that have already happened.  There are two recommended ways of handling unplanned spikes:

Strategy 1: React to unpredictable demand

When utilization metrics are indicating high load, simply react by scaling up.  Such utilization metrics can usually include CPU utilization, amount of requests per second, number of concurrent users, amounts of bytes transferred, or amount of memory used by your application.  In AzureWatch you can configure scaling rules that aggregate such metrics over some amount of time and across all servers in the application role and then issue a scale up command when some set of averaged metrics is above a certain threshold.  In cases when multiple metrics indicate change in demand, it may also be a good idea to find a "common scaling unit", that would unify all relevant metrics together into one number.

Strategy 2: React to rate of change in unpredictable demand

Since scale-up and scale-down events take some time to execute, it may be better to interrogate the rate of increase or decrease of demand and start scaling ahead of time: when moving averages indicate acceleration or deceleration of demand.  As an example, in AzureWatch's rule-based scaling engine, such event can be represented by a rule that interrogates Average CPU utilization over a short period of time in contrast to CPU utilization over a longer period of time

(Fig: scale up when Average CPU utilization for the last 20 minutes is 20% higher than Average CPU utilization over the last hour and Average CPU utilization is already significant by being over 50%)

Also, it is important to keep in mind that scaling events with this approach may trigger at times when it is not really needed: high rate of increase will not always manifest itself in the actual demand that justifies scaling up.  However, in many instances it may be worth it to be on the safe side rather than on the cheap side.

Predictable demand

While reacting to changes in demand may be a decent insurance policy for websites with potential for unpredictable bursts in traffic, actually knowing when demand is going to be needed before it is really needed is the best way to handle auto scaling.  There are two very different ways to predict an increase or decrease in load on your application.  One way follows a pattern of demand based on historical performance and is usually schedule-based, while another is based on some sort of a "processing queue".

Strategy 3: Predictable demand based on time of day

There are frequently situations when load on the application is known ahead of time.  Perhaps it is between 7am and 7pm when a line-of-business (LOB) application is accessed by employees of a company, or perhaps it is during lunch and dinner times for an application that processes restaurant orders.  Whichever it may be, the more you know at what times during the day the demand will spike, the better off your scaling strategy will be.  AzureWatch handles this by allowing to specify scheduling aspects into execution of scaling rules.

Strategy 4: Predictable demand based on amount of work left to do

While schedule-based demand predictions are great if they exist, not all applications have consistent times of day when demand changes.  If your application utilizes some sort of a job-scheduling approach where the load on the application can be determined by the amount of jobs waiting to be processed, setting up scaling rules based on such metric may work best.  Benefits of asynchronous or batch job execution where heavy-duty processing is off-loaded to back-end servers can not only provide responsiveness and scalability to your application but also the amount of waiting-to-be-processed jobs can serve as an important leading metric in the ability to scale with better precision.  In Windows Azure, the preferred supported job-scheduling mechanism is via Queues based on Azure Storage.  AzureWatch provides an ability to create scaling rules based on the amount of messages waiting to be processed in such a queue.  For those not using Azure Queues, AzureWatch can also read custom metrics through a special XML-based interface.

Combining strategies

In the real world, implementing a combination of more than one of the above scaling strategies may be prudent.  Application administrators likely have some known patterns for their applications's behaviour that would define predictable bursting scenarios, but having an insurance policy that would handle unplanned bursts of demand may be important as well.  Understanding your demand and aligning scaling rules to work together is key to successful auto scaling implementation.

A question frequently asked during architectural stages of a new Azure-based project: should we choose SQL Azure or Azure Table Storage to store our data?

Answer is typically: YES. Both of these storage technologies compliment rather than compete with each other.  If you are looking to gain maximum scalability, performance, and flexibility while at the same time paying the least amount of money, the trick is to understand the strong points of each technology and utilize both effectively.

Azure Table Services (ATS) is a new storage technology from Microsoft and is specifically designed to handle mega-scalability for applications and websites of Twitter/Facebook/Amazon's capacity.  Storage space is super cheap with ATS.  To offset the low cost and mega-scalability there are a few negative impacts that one must be aware of.  You are not only paying for storing the data, but also for accessing the data that lives in ATS.  There is also only limited support for transactions.  This is key to understand and design around.  ATS also is a new paradigm for developers to grasp.  Lastly, ATS forces your compute nodes to become mini-relational servers.  It simply does not do any of the relational processing we are all used to.  JOINs, GROUP BY's, ORDER BY's all have to be designed around or performed manually. 

I would say that ATS is best suited for large amounts of data that only rarely needs to be accessed or massaged.

On the other hand, SQL Azure is a fast, lighter-weight cloud-based relational storage.  Your developers will know how to code against it right away, because (barring a few small gotchas) coding for it simply like coding for any other SQL database.  On the negative side: SQL Azure is not meant to support huge applications like Facebook, Ebay, Twitter, etc.  Azure, stores your SQL Azure databases along with others in a multi-tenant environment on same servers and thus it has to throttle access should your application become too hot and impacts other databases on the same SQL Azure node.  SQL Azure is also somewhat pricey, at $10/gigabyte/month.  Having said, there is no cost to access the data stored in SQL Azure and there is plenty of CPU power dedicated to perform relational functionality on your data.  

I would recommend that SQL Azure for smaller amounts of frequently accessed data.

A few typical use-cases to illustrate the points:

Data in a banking system that stores customer account information along side a large amount of financial transactions would best divided across SQL Azure and ATS in the following way: Customer account information in SQL Azure, Financial Transactions in ATS.

Content for a large blog site should probably live in SQL Azure until it reaches a certain age and can be archived to ATS.

Here is a tip that most administrators of Azure websites and applications will find invaluable.  Thanks to the scheduling features of AzureWatch, application administrators can setup up to receive regular email updates with all the key metrics that AzureWatch is monitoring for their Azure applications.  All it takes is a subscription to AzureWatch and a minute or two.

If you would like to receive email with latest peformance indicators values for your Azure application at certain intervals, say at 9am and 12pm and 3pm, simply configure three rules that always evaluate to true (ie: '1=1') to run between the hours of 8:59-9:01am, 11:59am-12:01pm and 2:59-3:01pm (assuming your rule-checking frequency is every 60 seconds).  Mark the rules as "Notification only" instead of "Scaling based", move them to the top of the sequential list and publish your changes. Take care to not make the time-ranges too wide, to avoid getting flooded with emails. AzureWatch will now execute new rules between the very narrow time ranges and email you values of all the variables it is tracking.

Cloud-computing providers in general and Windows Azure in particular offer nearly infinite scalability, virtually unlimited capacity, blazing performance and extremely quick provision times.  However, to properly take advantage of these great benefits, teams need to plan ahead and understand all potential pitfalls and challenges.  One of the more significant differences between development of on-premise applications and cloud applications is a rather direct correlation between choices made during construction of an application and its support costs after deployment.  Because of the Windows Azure pricing model, every inefficient architectural decision and every inefficient line of code will show up as an extra line item on your Azure invoice.

This article will focus on a few actionable items that you can do today to minimize the cost of your Windows Azure application tomorrow.  The list of items is by no means exhaustive, but it will get you and your team thinking about the impact of your choices in a new dimension.

First, let's analyze the popular moving blocks when it comes to Windows Azure pricing. While seemingly straightforward individually, it is their combination together - and with obvious influence of already existing non-functional requirements for scalability, performance, security, availability, etc. - that make architecture in the cloud be a complex jigsaw puzzle.

Compute hours - quantity and size of nodes (servers) and charged by hour when online
Transfer costs - data that crosses Microsoft's data center boundaries is subject to transfer charges.
Azure Table Storage (ATS) costs - charge by gigabyte per month for the amount of space used
ATS transaction costs - charges for the amount of requests to ATS your application will make
Size of the SQL Azure databases - every database that you host in SQL Azure is charged by size

There are costs for other less frequently used services like Azure AppFabric or Content Delivery Network (CDN) that are not covered in this article.

Tip 1 - Avoid crossing data center boundaries

This is fairly straightforward.  Data that does not leave Microsoft data center is not subject to Transfer charges.  Keep your communication between compute nodes, SQL Azure, and Table Storage within the same data center as much as possible.  This is especially important for applications distributed among multiple geo-locations.  If you must communicate between different geo-locations, limit communication to non-transactional, batch calls that occur less frequently while utilizing compression where it makes sense to cut down on the amount of data transferred.  Employ caching technologies where possible.

Tip 2 - Minimize the number of compute hours by using auto scaling

Compute hours will likely make up the largest part of your Azure bill and thus need to receive the greatest amount of attention.  It is important to remember that Windows Azure does not automatically scale down the number of compute nodes, even if there is little or no demand on your application.  Architect for and plan to have an automatic scaling strategy in place, where the amount of nodes increases when demand spikes up and decreases when demand tapers off.  This can easily cut your bill for compute hours in half.  Implementing a comprehensive auto-scaling engine can be more complex than it sounds.  While there are a number of open-source examples that show the basics of how this can be done, it is also a perfect opportunity to outsource the auto-scaling to third party services such as AzureWatch.

In order for auto-scaling to be most effective, group your system components by their scaling strategies into Azure Roles.  It is important to keep in mind that if you need high availability of your components and want to take advantage of Azure SLA, you will need to maintain at least two online nodes for each Azure Role you have deployed.

Tip 3 - Use both Azure Table Storage (ATS) and SQL Azure

Try to not limit yourself to a choice between ATS or SQL Azure.  Instead, it would be best to figure out when to use both together to your advantage.  This is likely to be one of the tougher decisions that architects will need to make, as there are many compromises between relational storage of SQL Azure and highly scalable storage of ATS.  Neither technology is perfect for every situation.

On one hand accessing SQL Azure from within the boundaries of a data center is free and SQL Azure offers a familiar relational model which most developers will be comfortable with, transactions that assure data integrity, integration with popular ORM frameworks such as Entity Framework or NHibernate, and compatibility with numerous tools that work with relational databases.  On the other hand, ATS offers vastly greater scalability than SQL Azure and can hold a nearly infinite amount of data at a fraction of SQL Azure's cost.  You are charged, however, for every request made to ATS, even within the boundaries of a data center.

From a cost perspective, SQL Azure makes sense when access to data is not required to be highly scalable and when the amount of data is limited.  ATS makes sense for large amounts of data or when serious scalability is needed.

Tip 4 - ATS table modeling

If you have made the choice to use Azure Table Storage, you have essentially committed to converting parts of your data access components into mini database servers.  Setting Blob storage aside which is primarily used for media files or documents, ATS provides three levels of data hierarchy (Table, PartitionKey, and RowKey) that can be accessed and navigated extremely efficiently.  However, anything beyond that will require custom code and CPU cycles of your compute nodes.  This is the key difference to work around.  It would be prudent to spend a significant amount of time modeling table storage with appropriate Blobs, Tables, PartitionKeys and RowKeys to accommodate for efficient data storage and retrieval strategies.  This will not only speed up your transactions and minimize the amount of data transferred in and out of ATS, but also reduce the burden on you compute nodes that will be required to manipulate data and directly translate into cost savings across the board.

Tip 5 - Data purging in ATS

Because you are charged for every gigabyte stored in ATS, it may be prudent to have a data purging strategy.  However, while it may seem like a straightforward problem in a world of relational databases, this is not the case with ATS.  Since ATS is not relational, deletion of each and every row from an ATS table requires two transactions.  In certain cases it may be possible to delete a single row using only one transaction.  Either way, this is extremely slow, inefficient and expensive.  A better way would be to partition a single table into multiple versions (e.g. Sales2010, Sales2011, Sales2012, etc.) and purge obsolete data by deleting a version of a table at a time.

Conclusion

Shift to cloud computing represents a major leap forward and enables almost everyone, from small companies to large enterprise, reduce their capital expenses, minimize time to market and significantly decrease support costs.  By investing even small effort into planning ahead, cloud computing can result in meaningful savings and benefits.

So you are deploying your application on Windows Azure cloud platform.  One of the key features of a cloud platform like Azure is the ability to consume your compute resources with a utility model.  Pay for only what is used, whether storage space, compute power, or amount of data transferred. Dynamic allocation of more space or bandwidth is built-in to Azure, but with respect to compute power, Windows Azure allows you to issue scale up or scale-down commands relatively easily.  However, deciding when to do so can be a challenging task.  This blog entry describes a service AzureWatch and how it can dynamically scale Windows Azure applications.

Part One - Introduction

At its core, AzureWatch aggregates and analyzes performance counters, queue lengths, and other metrics and matches that data against user-defined rules. When a rule produces a "hit", a scaling action or a notification occurs.  You will need an account to install and use AzureWatch.  Follow this link to fill out a simple registration form.  After registration, download link for Windows-based configuration utility will be provided.

AzureWatch currently ships in two flavors: desktop edition and server-side edition.  Server-side AzureWatch will monitor and auto-scale your Azure applications from its cloud-based servers.  Desktop edition requires a special AzureWatch Monitoring agent to be installed on your premises.  In desktop-edition the agent is responsible for gathering metrics and initiating scaling events.

Part Two - Start Control Panel

After installation is complete, start AzureWatch ControlPanel and login with your newly created account.  You will be presented with a wizard to enter your Azure connection information. 

Subscription ID can be found on your Windows Azure developer portal.  If you do not already have the X.509 certificate, AzureWatch can create one for you.  Follow the hyperlink on the wizard to get detailed instructions on creation certificates.  It is a good idea to visit AzureWatch page to understand how your certificates and storage keys are kept secure.

After entering your account SubscriptionID and specifying a valid X.509 certificate, press Connect to Azure.  You will be presented with a list of storage accounts.  Storage account that is monitored by your Diagnostics Monitor is required.

On the next wizard page you can validate default settings for such things as throttle times, notification email, etc.

After the connection wizard is completed, AzureWatch will figure out what services, deployments and roles are present.  For each role found, you will be offered a chance to create simple predefined rules.

The few sample rules offered are simple rules that rely upon basic metrics. We will come back to these rules in a short while.  For now, wizards need to be completed.

Part Three - First time in Control Panel

After wizards complete, you are presented with a dashboard screen.  It likely contains empty historical charts since no data has been collected so far.  Navigation Explorer on the left shows various parameters that can be customized, while Instructions tab on the right shows context-sensitive instructions.

It is a good idea to visit the the Rules section to see the rules that have been defined by the wizard.  Two sample rules should be present and can be edited by double-clicking on each.  Rule Edit screen is simple yet powerful.  You can specify what formula needs to be evaluated, what happens when the evaluation returns TRUE, and what time of day should evaluation be restricted to.  To make formula entry easier, a list of already defined aggregated metrics is provided.  Hovering over the formula box will display allowed operands.

One last place to visit before starting the monitoring process is the screen that contains safety limits for the number of instances that AzureWatch can scale up to or down to.  By clicking on the appropriate Role name in the navigation explorer, you will be presented with a chance to modify these boundaries.

This is it.  If you are ready, press "Publish Changes" button.  Provided your AzureWatch Monitor service is running, it will pick up these configuration settings in the next iteration of its internal loop and instruct Azure Diagnostics Manager to start capturing the metrics required for formulas to work.  Windows Azure will need a few minutes to instruct your instances to start capturing those metrics afterwards, and then a few minutes more before these metrics will be transferred to your storage.  Thus, give AzureWatch at least 5-10 minutes before expecting to see anything on the Dashboard screen.

Part Four - A few tips and tricks

Some things to keep in mind while using AzureWatch

If you just started using AzureWatch and have not accumulated enough metric data, evaluation of your rules may be suspect as your aggregations will lack sufficient data.  It maybe prudent to disable scaling inside Rules in the beginning so that your scaling actions do not trigger unexpectedly.

Metric transfer occurs only when Monitor is running.  If you stopped Monitor service for an hour and then restarted it, it does not "go back" and send the missing hour's worth of metrics to AzureWatch.

When deploying new packages into Azure, you must use do it thru the same storage account that is specified in your DiagnosticsConnectionString and in AzureWatch System Settings.  Failure to do this, will result in incomplete or incorrect metrics.

AzureWatch will always instruct your instances to capture metrics that are defined in the Raw Metrics screen.  You do not need to do anything special with existing or newly started instances.  It may be worthwhile, however, to visit the System Settings screen to further configure how metric enforcement and gathering works.

AzureWatch will send a notification when it scales your instances up or down.  In it, it will provide values for all the aggregated metrics it knows about to help you understand why the scaling event occurred.

Locally installed AzureWatch components automatically self-update whenever a new version is released.