Know Your Data

Knowing your data might be the most important quality of the new database administrator (DBA). Just like a marketing person needs to know their customers, a CEO needs to know their market; a DBA needs to truly understand their data. This article is about how knowing your data becomes even more important with cloud databases like SQL Azure Database, and NoSQL style data stores like Cassandra and Windows Azure Storage.

SQL Server

Using a scale up approach for your on-premise SQL Server database, the SQL Server DBA needed:

• A good working understanding behind the relationships of the data, i.e. how tables related to each other. This allowed the DBA to develop foreign key to primary key relationships, one of the mantras of a relational database.
• Understanding how data was being inserted and updated into the table allowed the DBA to optimize their queries for locking, partitions for performance, and transaction logs for growth.
• Understanding how data was queried from the database and how paging worked, allowed the database administrator to tune the database with covered indexes and denormalization of data for better performance.
• How much data was being added over a period of time, how to scale up the database to grow with the data and how to warehouse that data.

SQL Azure

If you are designing your SQL Azure data store for growth, you need to design in a scale out approach. For more about scale out your SQL Azure database read: Scale Out Your SQL Azure Database. Scaling out divides your data across multiple databases, a concept that SQL Azure calls federation – federation is the concept of sharding in SQL Azure. For more about SQL Azure Federation read: Intro to SQL Azure Federations: SQL Azure Federations Overview.

 

Federating your data breaks transaction boundaries and pushes SQL Azure towards a NoSQL database structure. The reason I call it a step toward NoSQL is that SQL Azure doesn’t allow you to have transactions that span multiple databases. The parity of transactions to a single database is caused in part because the databases exist on different hardware. If the transaction needs to travel on the network there would be considerable performance degradation. Because SQL Azure is on commodity hardware, the maximum scalable size is 50 Gigabytes. If you need a database bigger than 50 Gigabytes, you need to federate your data across multiple databases and across transaction boundaries. You lose the ACID principles of the database – closer to NoSQL than to a relational database.

The DBA needs to understand more about data in order to properly partition or federate the data correctly. Specifically the DBA needs to know:

• How to distribute the data into acceptable transaction boundaries.
• How to find the natural dividing lines of the data so that you can partition it across multiple databases.

Transaction Boundaries

Every query on SQL Azure exists within a transaction context – a principle of ACID relational databases. A transaction is treated in a coherent and reliable way, independent of other transactions. Just like SQL Server, SQL Azure is capable of creating transaction contexts for any statement that you might want to create against the database. And it can rollback any transactions where a failure might prevent the whole transaction from completing.

 

Regardless of SQL Azure transaction capabilities, typically very few of the statements executed against the database need to have a transaction context. For example, when selecting data with a forward-only, read-only cursor that only row-locks – the transaction context is restricted to the row that is being read. However, what if that table is only updated when the application is offline? The table doesn’t require any locking for read – since there are never any writes. The database doesn’t know this, only the application – or more specifically the application developer.

 

The pretense of the NoSQL database is that the application developer knows every case where they need a transaction context and every case where they do not. Since true transaction needs are typically very low, the application developer programs all transactions into the client code – and doesn’t require a database that supports transactions. They might even program the rollback code.

Since the NoSQL developer knows the data (some might say better than the relational database developer), they can program their application to use multiple databases to store the data – in other words they know how to scale out using homegrown transactions on the client application.

 

So they can use commodity hardware in the cloud which is significantly more cost effective than on-premise SQL installations managing the same data volume.

Dividing Lines

If the application is highly dependent on transactions – the developer can keep all transaction to a single database by partitioning the data on transaction boundaries. For example, if the application is supporting software as a service (SaaS) that is used by multiple users in such a way that no user modifies the other user’s data – the user is the transaction boundary. All the data for a user is stored in the same database. This way all the data reads or write happen together in a single database and they don’t span databases. Each database holds a set of users that it can support, however no two databases hold data for the same users.

Summary

Knowing your data allows to you “see” the natural transaction boundaries and gives you an edge on scaling out. I foresee the role of the DBA trending away from hardware maintenance and towards database architecture.

Scale Out Your SQL Azure Database

 

Scaling up an on-premise SQL Server is the concept of adding more computing resources to your existing hardware, like memory, processing power or storage. The purpose is to allow your SQL Server to handle more queries, gain performance or store more data. When you do decided to go to the cloud you should realize that SQL Azure doesn’t scale up like SQL Server – it scales out. Scaling out is adding additional machines with the same data and dividing the computation between them. This article will discuss the differences between scaling up and scaling out and how you need to plan for scale out on SQL Azure to gain performance.

Due to hardware and licensing costs of SQL Server, it is much easier to add resources to existing hardware, than it is to deploy an additional replicated SQL Server on new hardware to gain performance.

 

The problem with scaling out (adding more machines) in an on-premise SQL Server installation is that this adds a degree of complexity to the design that deters DBAs from this path. The DBA now has to consider clustering, deploying a replication schema, etc. It is easier to purchase bigger hardware and redeploy your current databases on it, replacing the existing hardware, then it is to deploy as a cluster.

 

In essence, the SQL Server DBA has been trained to scale up, however hardware costs prohibit infinite scale up. If you have to double your server capacity (RAM, DISK, CPU) by purchasing a new server every time you double your workload and you can’t put the old server to use – your server costs get exponentially greater. In contrast, if you can add an additional server in a SQL Server cluster every time your work load doubles (scaling out) then your expenses grow at the same rate as your demand for resources. This is the beauty of scale out – additional resources cost you the same as the current resources – your costs grow linearly with your hardware.

 

However, most DBAs rely on Moore’s law: Hardware speeds, disk storage, and memory capacity doubles every two years. This means that you can double the performance at the same price as the original cost of your two-year old servers. So, if your workload doubles every two years, you end up purchasing a server twice as big (to scale up) at the same cost as the old server. In this case, all you have to do is purchase a server that will meet your current needs for two years – and hope that your capacity needs do not double beyond that.

SQL Azure

Currently, you cannot scale up SQL Azure processing and memory resources – you are allocated a fixed amount of computing and memory resources per database. Through SQL Azure load balancing, you might gain more resources on a particular machine in the database center, but you cannot crack the case and add more RAM to SQL Azure. For more information see: Inside SQL Azure.

You can, however, scale up SQL Azure storage to the limit of 50 Gigabytes. This is the same as increasing the storage system on an on-premise SQL Server. After 50 Gigabytes you need to scale out your storage.

 

The inability to scale up confuses many people and leads them to think that SQL Azure doesn’t scale at all. It does scale – it scales out. Not only does it scale out, it scales out at a fixed cost; double the resource costs you roughly double the price.

This table will help determine your options for scaling:

	Need Better Performance*	Need More Storage
SQL Server	Scale Out or Scale Up	Scale Out or Scale Up
SQL Azure	Scale Out	Scale Up To 50 Gigabytes Then Scale Out

* You have other options for better performance than just scaling, you can also optimizing your queries, see: Improving Your I/O Performance for more information.

Scaling Out With SQL Azure

In order to scale out with SQL Azure, you need to be able to partition your data, dividing the database across multiple databases. Each of these databases represents a fixed amount of computing and memory resources; the more databases that you divide your data across, the more computing and memory resources you have at your disposal.

 

Usually scaling involves work that a DBA is used to doing, for example:

 

• Adding more RAM.
• Upgrading the storage system.
• Clustering two on-premise SQL Servers.
• Optimizing stored procedures.

However, partitioning your data involves working with the developers that wrote the application that queries the database. Currently, you can’t scale out your SQL Azure database without modifying the client access layer to be aware of the partitioning. This is generally is the developers area, not the DBAs.

 

Vertical partitioning divides a table into multiple tables that contain fewer columns. For SQL Azure, we take this one step further, that the tables the data is stored on are in different databases. In other words, scaling out your storage, memory and processing power across multiple databases. For more information see: Vertical Partitioning.

 

Horizontal partitioning divides a table into multiple tables. Each table then contains the same number of columns, but fewer rows. Like vertical partitioning, horizontal partitioning scales out your storage, memory and processing power across multiple databases. For more information see: Horizontal Partioning.

 

Another option is to shard your database, using multiple databases to store the same data; each database contains an identical copy of your data. With sharding, you scale out the processing power and RAM, however the amount of data stored stays the same. The benefit of sharding is that your application doesn’t have to make multiple queries to collect all the data – since all the data can be found in one of the many databases. However, for each additional database you are paying for the redundant data to be stored – paying for data storage to gain performance For more information see: Sharding with SQL Azure.

Summary

In order to efficiently scale, whether with an on-premise SQL Server or SQL Azure in the cloud, you need to be able to scale out. In order to scale out, you need to design databases that scale – which means that the application developers need to be involved in coding the database that can be vertically or horizontally partitioned, or sharded.

WP7 Windows Phone 7 Resources for Developers

This is a catchall for any kind of Windows Phone 7 resource for developers. If you have a favorite resource you would like added to the list, add it to the comments and I will update the list.

MSFT Blogs
Windows Phone Developer Blog
Jesse Liberty, Silverlight
Mike Ormond, UK
David Anson
Chris Koenig
Jaime Rodriguez
Jeff Wilcox
Pete Brown
Scott Guthrie
Shawn Oster
James Conard
John Papa


MSFT Sites

Windows Phone developer resources (main landing page)	http://bit.ly/h6B0MN
Silverlight for Windows Phone	http://bit.ly/hZHv3e
XNA Game Studio and XNA Framework	http://bit.ly/f0vzAI
Windows Phone Developer Tools and Device Unlock	http://bit.ly/fEv2Gd
User Experience and User Interface	http://bit.ly/e9BcQ7
Application and Execution Model	http://bit.ly/dJ1Kg2
Input, Touch and Gestures	http://bit.ly/erTdpa
Launchers and Choosers	http://bit.ly/hgCoRo
Security	http://bit.ly/evDf4S
Frame and Page Navigation	http://bit.ly/hCUMyw
Isolated Storage	http://bit.ly/efzCGE
Performance	http://bit.ly/h8go3y
Advertising Services	http://bit.ly/hEHzm7
Camera and Photos	http://bit.ly/hL9hl9
Media – Audio and Video	http://bit.ly/h8VxZa
Push Notifications and Live Tiles	http://bit.ly/eaK0zt
App Bar and Controls	http://bit.ly/i7f0zQ
Location and Mapping	http://bit.ly/ic2fud
Networking and Web Services	http://bit.ly/hdlkIz
Sensors	http://bit.ly/eVnYNF
Globalization and Localization	http://bit.ly/fU78Xn
Porting Your App or Game to Windows Phone 7	http://bit.ly/fSmCNI
Application Publishing and Marketplace	http://bit.ly/h3RSi4

App Hub with developer tools
3rd Party Sample Code Repository
Free Frameworks, Tools, and Controls

Channel 9 
Online Training
WP7 Tag Cloud
Blog

MSDN
main
code samples
class library

Codeplex
Patterns and Practices
TestFramework
ActionPack
NUnit
Developer Guidance
Touch Enabled Graph Control  (demo video)
Isolated Storage Explorer
Tombstone Helper
Clipboard

Conferences
PDC 2010 Sessions
Mix 2011 Sessions

CESPage
Silverlight phone tutorials

Am I missing something? Comment below and I will add it.

Have we forgotten the sport of development?

There is a long standing pattern with sport teams of all types:

1. The Team Members
2. The Team Captain
3. The Team Coach
4. The Team Manager

This pattern is well established, and it is well accepted that one bad apple in the above can change a champion team into a loser. Development should learn from this pattern. The following appear to be appropriate mappings by functions and roles served:

• Team Captain –> Dev Lead. The best member with leadership ability. The person that the team members have confidence in and respects. Someone good in the sport (development)
• Team Coach –> Architect. A coach plans out and teaches the plays. He makes the call on what should happen on the field.
• Team Manager –> The Development Manager. He is responsible for getting resources (including new people),  dealing with the sponsors etc.

My general observation is that most development teams are crippling themselves by trying to save money by overloading positions. The consequences are the same as for a sports team:  poor execution. A Team Captain on the field in the middle of a game is not able to sit back and observe what is happening and craft the best strategy, his vision is the immediate now. All of the above need to communicate well with those above and below them for an effective team.

 

Perhaps it is time that we start pressing for a sports analogy for staffing and funding.

Running Multiple Threads on Windows Azure

The default worker role created by Visual Studio 2010 generates code that only leverages a single thread in your compute instance. Using that generated code, if you make synchronous network requests to SQL Azure or to a web service (for example via REST), your dedicated core for the instance becomes underutilized while it waits for the response from the network. One technique is to use the asynchronous functions in ADO.NET and the HTTPWebRequest classes to offload the work to the background worker. For more information about asynchronous calls read: Asynchronous Programming Design Patterns . Another technique that I will cover in this blog post is how to start up multiple threads, each for a dedicated task, for this purpose I have coded a multi-threaded framework to use in your worker role.

 

Goals of the framework:

• Remain true to the design of the RoleEntryPoint class, the main class called by the Windows Azure instance, so that you don’t have to redesign your code.
• Gracefully deal with unhandled exceptions in the threads.

RoleEntryPoint

The RoleEntryPoint class includes methods that are called by Windows Azure when it starts, runs, or stops a web or worker role. You can optionally override these methods to manage role initialization, role shutdown sequences, or the execution thread of the role. A worker role must extend the RoleEntryPoint class. For more information about how the Windows Azure fabric calls the RoleEntryPoint class read: Overview of the Role Lifecycle.

 

The included framework provides the the ThreadedRoleEntryPoint class which inherits from RoleEntryPoint and is used as a substitute for RoleEntryPoint in WorkerRole.cs. Here is an example of using the framework:

 

public class WorkerRole : ThreadedRoleEntryPoint
{
    public override void Run()
    {
        // This is a sample worker implementation. Replace with your logic.
        Trace.WriteLine("Worker Role entry point called", "Information");

        base.Run();
    }

    public override bool OnStart()
    {
        List<WorkerEntryPoint> workers = new List<WorkerEntryPoint>();

        workers.Add(new ImageSizer());
        workers.Add(new ImageSizer());
        workers.Add(new ImageSizer());
        workers.Add(new HouseCleaner());
        workers.Add(new TurkHandler());
        workers.Add(new Crawler());
        workers.Add(new Crawler());
        workers.Add(new Crawler());
        workers.Add(new Gardener());
        workers.Add(new Striker());

        return base.OnStart(workers.ToArray());
    }
}

 

Inside the OnStart() method we create a list of class instances that are passed to the ThreadedRoleEntryPoint class, each is given its own thread. Also note that some of the classes are listed more than once. Multiple classes mean that simultaneous identical work is being performed – it also allows us to balance the work. In the example above crawling is three times more important than house cleaning.

 

It would be nice to reuse the RoleEntryPoint class to create subclasses for each thread that we want to start, however Windows Azure requires that there only be one and only one class the subclasses the RoleEntryPoint class in the cloud project. Because of this restriction I have created an abstract class called: WorkerEntryPoint which all the thread class must inherit from. This class as the same lifecycle methods as the RoleEntryPoint class:

• OnStart Method
• OnStop Method
• Run Method

To create a threaded worker you override these methods in just the same way as if you were inheriting from the RoleEntryPoint class. However, the only method you have to override is the Run method. Typically it would look something like this:

internal class Striker : WorkerEntryPoint
{
    public override void Run()
    {
        while (true)
        {
            // Do Some Work

            Thread.Sleep(100);
        }
    }
}

Handling Exceptions

One thing that a Windows Azure worker role does nicely is to try to stay up and running regardless of errors. If an exception occurs within the Run method, the process is terminated, and a new instance is create and is restarted by Windows Azure. When an unhandled exception occurs, the stopping event is not raised, and the OnStop method is not called. For more information about how the Windows Azure fabric handles exceptions read: Overview of the Role Lifecycle. The reason .NET terminates the process is that there are many system exceptions that you can’t recover from like: OutOfMemoryException without terminating the process.

When an exception is thrown in one of the created threads we want to simulate the Windows Azure processes as close as we can. The framework allows Windows Azure to terminate the process on unhandled exceptions; however it tries to gracefully shutdown all threads before it does. Here are the goals for exception handling within the framework:

• Gracefully restart all threads that throw an unhandled non-system exception without terminating the other threads or process space.
• Use the managed thread exception handling to terminate the process on unhandled system exceptions.
• Leverage Windows Azure role recycling to restart threads when the role is restarted.

When there is an unhandled exception on a thread created with the Start method of the Thread class, the unhandled exception causes the application to terminate. For more information about exception handling in threads read: Exceptions in Managed Threads. What this means is that we need to build in some exception handling in our threads to that framework can exit gracefully. The ProtectedRun method accomplishes this:

 

/// <summary>
/// This method prevents unhandled exceptions from being thrown
/// from the worker thread.
/// </summary>
public void ProtectedRun()
{
    try
    {
        // Call the Workers Run() method
        Run();
    }
    catch (SystemException)
    {
        // Exit Quickly on a System Exception
        throw;
    }
    catch (Exception exception)
    {
        // Perform Error Logging or Diagnostic
    }
}

 

The main RoleEntityPoint class which manages all the threads loops across all the threads and restarts any that are terminated (see code below). The threads become terminated when they exit the ProtectedRun method as opposed to unhandled exception which terminates the process space.

while (!EventWaitHandle.WaitOne(0))
{
    // WWB: Restart Dead Threads
    for (Int32 i = 0; i < Threads.Count; i++)
    {
        if (!Threads[i].IsAlive)
        {
            Threads[i] = new Thread(Workers[i].Run);
            Threads[i].Start();
        }
    }

    EventWaitHandle.WaitOne(1000);
}

 

The while loop terminates when a stop is requested from the Windows Azure Fabric, by tripping the EventWaitHandle to a signaled state. The EventWaitHandle provides thread protection, since the stopping request is made on a different thread.

Error Reporting

Windows Azure Diagnostic assembly has diagnostics class for collecting and logging data. These classes can be used inside a worker role to report back information and error about the state of the running role. For more information about diagnostics in Windows Azure see: Collecting Logging Data by Using Windows Azure Diagnostics.

 

When using the threaded role framework you need to call the diagnostic classes from the threaded class (which inherits from WorkerEntryPoint class) went you have something to report. The reason behind this is that the unhandled system exceptions in the threaded class will cause the process space to terminate. Your last chance to catch and log error information is in the threaded class. Generally I use a try/catch in the Run method to catch all exceptions, report them to diagnostics and then re-throw the expectation – which is not different than what I would implement in the default single threaded worker role.

Summary

The included framework enables multi-threaded Windows Azure worker roles within the same context of the Windows Azure Service Runtime. Using multiple threads can increase your processor utilization when your application is waiting for network requests – like calls to SQL Azure to complete.

 

Download the Framework.

 

{6230289B-5BEE-409e-932A-2F01FA407A92}

How to Combine a Worker and Web Role in Windows Azure

Some of us are running really small web sites on Windows Azure, and for every web site it seems that I needed to do a little background processing. Usually, this means creating both a web role to handle the web requests and a worker role to the background processing – typically through separate Visual Studio projects. However for a small web site this can get expensive if you are not fully utilizing the resources of both instances.

 

This article is going to show you how to include your background processing into your web role so you can fully utilize the Windows Azure instance. The same background processing that you would typically do in a worker role.

 

The recommended approach for Windows Azure is always run two instances to maximize uptime – if you were to follow the recommendation you would have the web role plus the extra background work running twice. Which means you need to design your background work to run Idempotency. For more information read: Idempotency for Windows Azure Message Queues.

The tasks that we are moving from the worker role will consume processing power and memory from the Windows Azure instance running the web role. The tasks will not block the web requests, they just share the resources of the instance. If you have a lot of background tasks or a lot of web requests, you should probably have separate worker and web roles in different instances.

 

An example of a background tasks that I want to include with my web site is a cleanup process for Session state that I blogged about here: Using SQL Azure for Session State, or creating a daily back-up of my SQL Azure database (something that I would do with SQL Server Agent if I was using an on-premise SQL Server). These tasks are not resource intensive – for the most part the work is being off loaded onto SQL Azure. However, at the same time I want them to be performed outside of the request/response cycle of the web role.

 

A Windows Azure web role is really a worker role with some IIS trim to handle HTTP requests. Every role in Windows Azure starts by calling the class that subclasses the RoleEntryPoint class. For a web role created by Visual Studio 2010 this look like this:

 

public class WebRole : RoleEntryPoint
{
    public override bool OnStart()
    {
        // For information on handling configuration changes
        // see the MSDN topic at http://go.microsoft.com/fwlink/?LinkId=166357.

        return base.OnStart();
    }
}

You can find this code in the file named Webrole.cs. You will also find that the Worker role has very similar code. The default worker role from Visual Studio also overrides the Run() method of the RoleEntryPoint like this:

 

public override void Run()
{
    // This is a sample worker implementation. Replace with your logic.
    Trace.WriteLine("WorkerRole1 entry point called", "Information");

    while (true)
    {
        Thread.Sleep(10000);
        Trace.WriteLine("Working", "Information");
    }
}

 

The easiest way to create a combined worker and web role is to create the web role first using Visual Studio 2010 and then override the Run() method. Here are the steps to do that:

1. Open Visual Studio 2010
2. Create a New Windows Azure Project

   

3. Choose ASP.NET MVC 2 Web Role or ASP.NET Web Role

   

4. After the files are created, file the WebRole.cs file and open it.
5. Add this code to override the Run() method

    

   public override void Run()
   {
       // This is a sample worker implementation. Replace with your logic.
       Trace.WriteLine("WorkerRole1 entry point called", "Information");
   
       while (true)
       {
           Thread.Sleep(10000);
           Trace.WriteLine("Working", "Information");
       }
   }

    

6. Add a using statement for these two assemblies: System.Diagnostics, System.Threading;
7. Save and compile.

Now you have a combined worker and web roles for the price of one Windows Azure instance.

 

{6230289B-5BEE-409e-932A-2F01FA407A92}