Scale Out Sql Server with Azure Federations


More and more businesses look into scaling solutions for their DB. That is for good reasons! Usually the number of clients or sales or licenses grows over time as a linear function. As a result we have a linear function for number of DB Transactions, and amount of data accumulated by the business. However the query response time grows exponentially. Thus, the performance of your single DB solution will yield a bad user experience.

DB Transaction Time

Fig.1, DB Query Response Time

As we may clearly see from the diagram, eventually the business will run into the DB bottleneck.

DB Scaling solutions: Scale UP vs. Scale OUT

When scaling the DB you may consider two approaches: Scale Up and Scale Out.

Scale UP

This approach relates to improving the hardware solution of the DB. That means use faster processor, more RAM, faster Hard Drive, etc. This solution is works very well when you have to improve DB performance without changing the architecture. I.e. when dealing with a legacy system. It is not difficult however to see that this approach has limitations, including associated price for the upgraded units.

Scale OUT

The Scale OUT approach relates to implementing a DB solution in which the I/O process will be distributed across multiple DB partitions.  All options of Scale OUT assume some amount of re-work. What option to choose? Most of the choices revolve around the nature of your data and the nature of use of the data. Let us review the possible options of Scale OUT:

  • Table partitioning
  • Master/Slave configurations
  • Custer Computing
  • Sharding

Table Partitioning. In the case of Table Partitioning a large table is split into two or more physically separate partitions. For instance you may have clients who live on the east coast to be stored in the table CustomersEast and clients who live on the West Coast to be stored in the table CustomersWest. In case of such distribution the queries will end up hitting two separate tables. The tables can also be located in different DB files which helps the I/O performance.

Master/Slave configurations. In the Master/Slave solution we have one database which is called “Master” and multiple databases called “Slaves”. The Master DB is set up for read/write access; the Slave databases are read-only. There is a data replication between the Master and the Slaves. Master/Slave configuration is suggested by Microsoft when doing Peer-to-Peer Replication. The drawback of this solution relates to the situation in which the Master fails. The Master/Slave approach does not need complex changes and provides good performance result. Microsoft recommends this solution when you expect moderate amount of data updates.

Cluster Computing. Similar to the Master/Slave in the DB Cluster we have one database node which is a DB available for read/write and multiple nodes which are in the read-only mode. All data updates are received by the “Master” node and replicated into the read-only nodes. The DB Cluster Computing solution addresses the case of the failing Master. In such scenario one of the read-only nodes becomes the new “Master”. Thus the DB Cluster solution is more reliable than the Master/Slave solution.

Sharding. The term “Sharding” or “Shared nothing” was coined by Google engineers and implies a solution in which the application operates with “Shards” which can be physically separate databases. With Sharding there are queries which run inside the shard and queries which are distributed across multiple shards. It is important to design your data model to minimize the amount of distributed queries.

Fig.2, Scaling OUT: Sharding

SQL Azure Federations

Azure Federation is a Sharding technology which allows distributing the DB transactions across multiple databases which are called Federation Members.

Fig.3, SQL Azure Feredation

Let us review some terminology. The SQL Azure Federation have following essential objects:

  • Federation object
  • Federation Key
  • Federation Member
  • Atomic Unit


Is an object stored in SQL Azure database. This object is responsible for the distribution schema of the Federation Members as well as the type of the Federation Key. The Federation object is of a Range value.  It covers the possible values of the Federation Key.

Federation Key

Federation Key is a column in the Federated Table. The value of the Federation Key determines in which Federation Member the data row is stored. As you can see from the listing, the CREATE TABLE instruction for the Federated table differs from the regular CREATE TABLE by “FEDERATED ON” clause.

Federation Member

Federation Member is a physically separate SQL Azure database. The Federation Member holds data related to a range of Federation Keys, configured for this Federation Member in the Federation object. The Federation Member can have two types of tables: Federated tables and Non-Federated tables.

Atomic Unit

Atomic Unit represents all rows of data from Federated Table(s) which have a particular value of the Federation Key.

How does it work?

Once you connect to the SQL Azure DB you create a Federation object. The Federation object is created and stored in the DB you connected. This database becomes Federation Root. When you create a Federation object your first Federation Member, a separated SQL Azure DB, is also created. The size and the type of the newly created Federation Member SQL Azure DB is inherited from the size and type of the Federation Root SQL Azure DB.

Fig.4, Azure Federation Databases and Tables

Management of Azure Federations

Azure Federation is an out-of-the box solution. As such it provides an interface for following manipulation of the Federation object:

  • Split/Drop Azure Federation
  • Monitor Azure Federation

Split/Drop Azure Federation

Once you create the Federation object, your first Federation Member is created. Hopefully your business picks up and your Federation Member DB is not enough to handle the load. In such case you need to add more databases. That is achieved by splitting your Federation Member. On contrary, if you need to downsize, you may want to drop the Federation member.

I would like to provide the example listing which you may use to explore Azure Federation. Run this code segment by segment. Be sure to establish a connection to Azure DB first.

Monitoring Azure Federation

You can monitor your Azure Federation through a set of Diagnostic Views and tables:

  • Federation Operation Views. These system diagnostic views contain information about the current operations on Azure Federation Objects
  • Federation History Tables. The Federation History Tables contain the historical information about the operations which affect the Federation object.

Federation Metadata Tables – contain the current distribution of federation members in the Federation.

Fig.5 Azure Diagnostics Views and Tables

Pricing and Billing

The pricing of Azure Federations is no different than pricing of SQL Azure:

  • DB Charges are prorated to a day.

Example: Cost of 1Gb Web edition is $0.33/day

  • All DB that exist in the day are charged
  • Only DB that are in ready state are charged

Following operations related to Azure Federations will affect your bill:

  • Federation Creation
  • Federation Repartitioning Operations

Modification of Federation Billing properties. Please see below how you may alter your SQL Azure DB to reduce/upgrade the size and the edition of your DB.


SQL Azure Federations provide a powerful OUT-OF-THE box solution for scaling out your SQL Azure DB. The solution is based on SQL Azure and comes with all good things of SQL Azure including two backup copies, pay-as –you-go pricing model. Finally the bulk of the db partitioning complexity is implemented “under the hood”. That is very convenient as it allows the developer to concentrate on creating the scalable solution.


Instrumenting your Azure app with Performance Counters:

In this article I would like to describe monitoring the health of your application in Windows Azure via Performance Counters. The topic came out as a result of working on a health monitoring aspect of an Azure Application where I had to implement means for the system to give feedback to the user. I believe that Azure Diagnostics should be part of every application and value of your app instrumentation cannot be underestimated. In this article I will focus on performance counters and will provide a working code example.

Some Theory
The health of your Azure application is monitored via Azure Diagnostics, an API in Microsoft.WindowsAzure.Diagnostics assembly. Windows Azure Diagnostics provides a variety of tools to check the health of your Azure app. Such tools include:

  • Logs
  • Windows Events
  • Performance Counters
  • Crash Dumps

Using Azure Diagnostics API you can configure your Web Role or Worker Role to collect the health information. The health information is stored on a local file system. From time to time the health information is transferred into a TableStorage by a Monitoring Agent as shown in the Fig.1.

Azure Diagnostics in Action - Image

Enough said let us see some code! First we need to configure the diagnostics API to use the proper storage account, file quota, and frequency of health information transfer:

private void ConfigureDiagnostics()

    var cloudStorageAccount = CloudStorageAccount.Parse(

    var roleInstanceDiagnosticManager =

    var cfg =
        roleInstanceDiagnosticManager.GetCurrentConfiguration() ??
        new DiagnosticMonitorConfiguration();

    new PerformanceCounterConfiguration()
        CounterSpecifier = @"\Processor(_Total)\% Processor Time",
        SampleRate = TimeSpan.FromSeconds(5)
    cfg.PerformanceCounters.ScheduledTransferPeriod = TimeSpan.FromSeconds(1d);
    cfg.PerformanceCounters.BufferQuotaInMB = 512;


With that my worker role is now collecting the CPU utilization every 5 seconds and writing it every minute to the WADPerformanceCountersTable in the Developer Storage.

If we just run the WebRole we will be able to see the perf counters recorded in the WADPerformanceCountersTable:


Next we need define data structures to work with the every WADPerformanceCountersTable:

public class PerformanceCounterEntry :
    public PerformanceCounterEntry()

    public string Timestamp { get; set; }
    public long EventTickCount { get; set; }
    public string DeploymentId { get; set; }
    public string Role { get; set; }
    public string RoleInstance { get; set; }
    public string CounterName { get; set; }
    public string CounterValue { get; set; }

public class PerformanceCounterDataContext : TableServiceContext
    public PerformanceCounterDataContext(string baseAddress, StorageCredentials credentials)
        : base(baseAddress, credentials)
    { }

    public IQueryable PerformanceCounterEntry
            return this.CreateQuery("WADPerformanceCountersTable");

public class PerformanceCounterEntryDataSource
    private static CloudStorageAccount storageAccount;
    private PerformanceCounterDataContext context;

    static PerformanceCounterEntryDataSource()
        storageAccount = CloudStorageAccount.FromConfigurationSetting("DataConnectionString");


    public PerformanceCounterEntryDataSource()
        this.context = new PerformanceCounterDataContext(storageAccount.TableEndpoint.AbsoluteUri, storageAccount.Credentials);
        this.context.RetryPolicy = RetryPolicies.Retry(3, TimeSpan.FromSeconds(1));

    public IEnumerable Select()
        var tempResults = (from pc in this.context.PerformanceCounterEntry
                            where pc.EventTickCount > DateTime.Now.AddMinutes(-10).Ticks
                        select pc).ToList();

	//show last 20 records
        var results = tempResults.OrderByDescending(pc => pc.EventTickCount).Take(20);
        return results;

Now all we need to do is to show TOP 20 counter records in the DataGrid:

<asp:ScriptManager ID="ScriptManager1" runat="server">
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
 DataSourceID="ObjectDataSource1" CellPadding="10" AutoGenerateColumns="false">
 <asp:BoundColumn DataField="TimeStamp" HeaderStyle-Font-Bold="true" HeaderText="TimeStamp" ReadOnly="true"/>
 <asp:BoundColumn DataField="CounterName" HeaderStyle-Font-Bold="true" HeaderText="Counter" ReadOnly="true"/>
 <asp:BoundColumn DataField="CounterValue" HeaderStyle-Font-Bold="true" HeaderText="Value" ReadOnly="true"/>

Now we are ready to run the application. This is the result I got:

Performance Report


One can never undervalue good instrumentation of your apps with diagnostics. In this post I have discussed how to integrate performance counters into your Azure application. The article demonstrates the integration of a CPU utilization performance counter via a code sample.

Introduction to Azure AppFabric

The Azure platform is being rapidly upgraded as new features are rolled in it. In this blog article I would like to review the applications Azure AppFabric to the existing problems of the enterprise software companies. The article includes:

1.       Over view of existing problems of enterprise companies when developing web distributed apps.

2.       Over view of Azure AppFabric

3.       Detailed review of Access Control Service

4.       Detailed review of Azure Service Bus.

Existing problems of enterprise software companies

Software applications are not new in today’s world, there are tons of good quality software solutions created by generations of developers. Today we create distributed applications centered on integration with existing software systems, components over various platforms, protocols, and standards. We see at least following problems:

1.       Connectivity challenges. The software applications have low interoperability because they often exist in different networks, platforms, etc. How can your client connect to a service endpoint if a service is in a different network behind a firewall?

2.       Authentication challenges. Most of the systems of the past came with their own security model. In order to work with such systems the user has to maintain various sets of credentials. Maintaining increasing number of logins presents a threat as it increases the chance of compromising the security credentials.

3.       Authorization challenges. The dispersed security landscape presents a problem as now it is increasingly difficult to administration user authorization.

Azure AppFabric presents possible solutions to the above mentioned problems. The article below will review the AppFabric with concentration on Access Control Service and Service Bus.

Overview of Windows Azure AppFabric

Windows Azure AppFabric presents a set of middleware services. The middleware services target to increase interoperability between the components of your software solution. The services of Azure ApFabric are presented in Fig. 1. The pricing for Azure AppFabric is reasonably cheap and could be looked up at

Services provided by Azure AppFabric

Fig.1 Services provided by Azure AppFabric

Service Bus Service – provides connectivity between the services and its consumers. The Service Bus Service is commercially available.

Access Control Service – allows to de-couple the security management from application logic. The Access Control Service is commercially available.

AppFabric Caching Service – allows centralized caching of your application data. The AppFabric Caching Service is available within the CTP since October 2010.

Integration Services – “AppFabric Connect” – is your BizTalk-like service in the cloud. The service will be available sometimes in 2011.

Composite App and AppFabric Scale Out Infrastructure. Later this year (2011) Azure AppFabric will introduce the ability to define a Composite App and upload the definitions to AppFabric Container. The Composite App represents your distributed applications with cloud- and on premises- based services. The AppFabric  provides an environment to host your Composite App through AppFabric Container’s Multi-tenant host and Composition Runtime . The AppFabric container is responsible for the lifecycle of your Composite App.

AppFabric Access Control Service

Let us see how AppFabric Access Control Service solves the problem of dealing with multiple identity providers. The idea is to de-couple the identity management logic from the application logic. The identity management is not a concern of the new application. To do so AppFabric uses the Claims Based Security Pattern.

Let is take a look at the concept of Claims Based Security Pattern, which you have to use in order to get a beer in Sloppy Joe’s, Key West (inspired by Vittorio Bertocci)

Claims Based Security in Key West

Fig. 2 Claims Based Security in Key West

1.       User submits the request for the Drivers License

2.       User receives the Drivers License authorized by the State of Florida

3.       User presents the Drivers License to the bartender of Sloppy Joe’s

4.       User get’s his beer

Now let us look at what occurs in the AppFabric. First we need to get clear on the terminology:

Relying Party – a service with application logic which expects a security token and relies on the Issuer to generate such token.

Issuer – a service which is responsible for evaluating the user credentials and generate a token which contains a set of Claims.

Claim – an attribute of the user.

Security Token – a set of Claims signed by the Issuer.

Having defined the terminology I would like to proceed with the explanation of the AppFabric’s Access Control Service. The interaction between the Client, the Issuer, and the Relying Party is described in the Fig.3 below.

Interactions in the AppFabric Access Control Service

Fig.3 Interactions in the AppFabric Access Control Service

According to the Fig.3, the Client obtains the Security Token from the Security Token Service (STS) which in turn accesses the Identity Store. Once the client has the token he or she can submit the Security Token as a part of the request to the Relying Party. The identity layer of the Relying Party validates the Token, extracts the claims from it. If your Security Token is valid and your claims set allow the access to the application, the user gets the requested data.

When you create a Service Namespace, the Azure AppFabric provides following build-in service endpoints (Fig.4):

  • STS Endpoint
  • Management Endpoint
  • Management STS Endpoint

Endpoints created with Azure AppFabric Service Namespace

Fig.4 Endpoints created with Azure AppFabric Service Namespace

As seen on Fig.4, besides the STS, the STS Management service and the STS for the Management Service are created. All endpoints expose RESTful services could be accessed by clients of various platforms including JAVA, PHP, etc. The STS is configured through the Management Service and could be configured to use other Identity Providers, for example Active Directory (through ADFS v2). As seen on Fig. 5, the STS can federate over existing Identity Providers, Active Directory, Facebook, Google, etc.

ACS with federated Identity Providers

Fig.5 ACS with federated Identity Providers

As we may see, Access Control Services allows several key benefits:

1)      Security is no longer a concern of the Application

2)      Existing Identity Providers are re-used

3)      Cross-platform interoperability via Restful services

To see how Access Control works:

1)      Download the Windows Azure Training Kit

2)      Go through the IntroAppFabricAccessControl Lab.

Service Bus Service

The Azure Service Bus allows bridging the possible networks and firewalls which may exist between the client and the server. You may imagine a common situation with the client and the service in located in different networks behind the firewalls. Let’s say the firewalls have only port 80 opened for outbound. Let us take a look at how the Azure Service Bus works

As described on the Fig.6, there are following steps:

1)      Service initiates a connection with the Relay Service via port 80 outbound.

2)      The Client initiates a connection with the Relay Service via port 80 outbound.

3)      The Client can send messages to the Relay Service and the Relay Service forwards the messages to the Service.

Interactions within Azure Service Bus

Fig.6 Interactions within Azure Service Bus

Generally speaking I just described the Service Remoting scenario, however there are more scenarios with Azure Service Bus including Eventing and Protocol Tunneling.

Azure Service Bus scenarios

Fig.7 Azure Service Bus scenarios

With Eventing scenario, you may subscribe multiple services to the client events. Such configuration allows multi-casting your messages. For each subscriber AppFabric creates a FIFO message buffer to store the client messages. Once the service connects it will read the messages. The Protocol Tunneling scenario assumes a situation in which you can re-use the opened ports to communicate between the client and a server.

To see how Service Bus works:

1)      Download the Windows Azure Training Kit

2)      Go through the IntroServiceBus2010Part1 and IntroServiceBus2010Part2 Labs


Windows Azure AppFabric is a viable enterprise software solution which encompasses best practices to enforce security, caching, connectivity, and integration. Through AppFabric the enterprise can de-couple the security management. Such approach allows re-using existing Identity Providers and concentrate on developing new application. Through the AppFabric Service Bus the enterprise can re-use existing services. Although some services of Azure AppFabric are commercially available, the middleware services with work-in-progress which presents risks future changes.