Guest Post: Windows Azure SQL Databases In Action

clip_image002Gareth Hewitt is the Product Director for WhiteSky Studio, a configurable Platform as a Service that allows business users to rapidly and easily create their own applications. Gareth founded WhiteSky Studio in June 2011 and is now focused on growing the product through allowing partners to create, market and sell their own applications using this revolutionary platform. Gareth can be contacted on and @whiteskystudio.


The key to the future of web based services, and applications, is scalability. You need the comfort of knowing as your system grows in users, processing requirements, data size, etc. that you have a platform that supports this scalability. Re-engineering your system for each new scalable requirement is simply not an option. Windows Azure provides the technical infrastructure to support this, but as always you have to design with this in mind from the beginning – you can’t just copy your existing SQL database over to the Cloud and expect the database and services to be instantly scalable.

WSS173WhiteSky Studio is a revolutionary Cloud-based RAD tool that can be used by business analysts to build flexible and scalable systems without technical knowledge – you just need the business knowledge to design the functionality. We provide a cost and time effective alternative to traditional RAD based tools, industry specific packages, and online Cloud solutions. We have successfully configured applications for HR, Timesheets & Expenses, Retail Customer Records & Stock Control, CRM, Portfolio and Property Management, Financial Analysis, and many more. Such a diverse range of users, data sizes, processing requirements, peak demand times and performance requirements, means we needed a solid, proven technology that could scale to meet these challenges. Windows Azure not only provided the infrastructure but enabled us to provide value for money competitive offerings to our Clients demanding performance, scalability, resilience, and security.

Windows Azure

Windows Azure provides services to support your system in a scalable fashion; there are many options to consider, including:

  • Azure Storage Blobs
  • SQL Azure
  • Federated Databases
  • Scaling servers automatically depending on custom metrics or demand
  • Caching
  • Web Roles/Worker Roles
  • VM Roles

WhiteSky initially implemented Web and Worker Roles, caching, a mixture of SQL Azure and Storage Blobs and automatic scaling of the servers depending on work load and demand. We have now implemented federated databases (sharding) which has provided both security and performance benefits and look forward to improved features in the next release.

SQL Azure – Federations

Within four months of starting the Company we had Clients in 7 different sectors, ranging from 5 to 100 users and accessing applications from 4 countries. Windows Azure successfully provided the entire infrastructure we needed, and our next task was to ensure continuing scalability by implementing federated databases. Our first decision was whether to create an in-house implementation or to wait and use SQL Azure Federations. As with any architectural decision there is no single right answer that works for everyone, but with all the pros and cons, there were two features of SQL Azure that made our decision easy to choose SQL Azure Federations:

  • The SQL Azure Gateway Service
  • Splitting (and later merging) with no downtime

The biggest concern in using a custom solution was that no matter what implementation we created it was going to be technically difficult, if not impossible, to provide the ability to split a live database into two smaller ones without significant downtime. This was a major requirement for us, and we couldn’t see an easy way of providing it and meeting our performance and up-time SLA’s. SQL Azure Federations comes with such a guarantee, functionality we felt was at the core of any scalable model. The development roadmap for SQL Azure includes the ability to add database merging which will enable us to auto-scale the merging/splitting of the databases according to performance or demand requirements – much like the auto-scaling of the web and worker roles that we already have in place.

The SQL Server gateway service is a service Microsoft added to ensure you can still use the advantage of connection pooling within your existing code. This provides an incredible performance boost over a custom implementation which would require us to hold connections across several different databases. Whenever we make a call to a federated database the gateway service routes the call to the correct federated database without the need to create a new connection.

Any one of these features was enough to choose SQL Azure Federations over a custom approach, but both made it a no-brainer.

SQL Azure Federations – Implementation

To migrate our existing database to SQL Azure Federations a few structural changes were required. The first, and most likely issues developers come across, is that you cannot use the following table column types:

  • Auto-increment on any column type
  • Timestamp column type

Both of these restrictions arise from the simple fact that SQL Federations has no way of guaranteeing a unique auto-increment field or timestamp without getting all of the federated databases communicating with each other – something that presumably would come with a large performance overhead. The first thing we had to do was add globally unique identifiers in place of auto-increment fields, and to replace our timestamp columns.

The auto-increment columns we were replacing were our primary keys and would be forming part of our new primary key in the federated table. We chose to implement the uniqueidentifiers in SQL with a default value of “CAST(CAST(NEWID() AS BINARY(10)) + CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER)” as described in “The Cost of GUIDs as Primary Keys” article, avoiding much of the fragmentation and performance issues we would get if we adopted a completely new GUID. Versioning wasn’t such an issue for us as we didn’t require the uniqueness that a timestamp guaranteed, so a simple conversion to a date was sufficient.

We had to write our own custom migration application to update all our relationships, but that was an easy process to automate. We then created all the new tables and migrated our data into our new tables without the incompatible column types. This way we had a backup we could refer to in case any of our relationship updates were incorrect.

After updating our server and client code to reflect the database changes, all that remained was the migration of the data into a Federated Database structure. We used the excellent SQL Azure Migration Wizard (Federation Version) to upload the data into five pre-prepared shards in our new database. When this process was complete we were live on a platform that now meets all of our scalability needs.

SQL Azure Scalability

It would have been impossible for us to offer the revolutionary capabilities of WhiteSky Studio without the scalable infrastructure of Azure. The up-front costs in both time and resources would have been too great. The platform-as-a-service offering of Azure allowed us to only pay for what we used as we grew our offering and started scaling the system. The advantage of automatically scaling our servers meant we weren’t paying to maintain the system’s performance during periods of low demands. It is this service based approach that is giving us a competitive and technological edge over competing products. We can offer a fully customizable and scalable enterprise wide business solution based on the custom requirements of any Client at the fraction of a cost for a similar hosted, packaged or bespoke solution.

As with most new technologies it is agile start-up companies that take the innovative steps and create a step change in the way services are delivered. WhiteSky takes advantage of Azure’s ground breaking technical ability to provide a value for money business orientated RAD tool with a global reach and scalable performance.


Useful Links

Skip to main content