Using Windows Azure SQL Data Sync to synchronise databases in the cloud and on premises

SQL Data Sync is part of Windows Azure, Microsoft’s cloud computing service. It synchronises data between two or more databases so that they contain identical data. When updates are made to one database, the changes are automatically applied to the other databases in the synchronisation group.

The databases can be in the Windows Azure cloud and on computers running on your business’s premises.

There are a lot of potential uses for SQL Data Sync, including:

• Extending an existing business application into the cloud so that it can be accessed anywhere by smartphones and tablets.
• Linking a front-end web application and its database in the cloud with on-premises systems.
• Consolidating data from branch offices into a central database in the cloud which is used for reporting.
• Providing global coverage for an application by synchronising databases in Windows Azure data centres across different geographic regions.

This article is about the first scenario, which is extending a business application into the cloud.

The “Vocab” demo application

I developed a demo application for learning foreign language vocabulary. The system sets tests and reports back with the number of correct answers. It also neatly demonstrates some of the latest Microsoft technologies.

The system consists of a Windows Phone app, a server application, a SQL Server database, and two client applications that run on Windows PC.

Figure 1 - The on-premises system - only useable when attached to the local network

The SQL Server database contains Portuguese words and their meanings in English. It also contains test results—which words were tested in each test and were the answers correct.

Portuguese is the sixth most spoken language in the world, with 204 million native speakers, well ahead of German, French, and Italian. Not many people know that!

The front end is a Windows Phone app which does the language self-tests. When you start a new test, it asks you how many questions you want to answer. The server application then generates a list of Portuguese words and sends them to the phone app. When the test is finished, the phone sends the results back to the server and they get stored in the database.

Figure 2 - A vocabulary test - the user answers with how well they know, or don't know the word

At the back end, there are two administrative programs that run on Windows PC. One is a program for entering new words and their meanings, and the other is a Microsoft Access application for making amendments to the words and producing reports.

Extending into the cloud

Originally the server application was running in an on-premises Windows Server, which meant that it could only be used via the local wi-fi network. It was then extended to the Microsoft Azure cloud so it could be used from anywhere.

Figure 3 - Extending the vocabulary application to Microsoft Azure - it can now be used anywhere there is an Internet connection

Moving the database and server application into Windows Azure was straightforward.

The version of SQL Server in Windows Azure is called Windows Azure SQL Database. It is based on SQL Server 2012, but there are some differences which I may write about in a future blog post. A few small changes were required to the Vocab database to get it to work in Windows Azure SQL Database.

The security of the Vocab server application had to be improved because it would be running over the public Internet. So I changed it to use an encrypted HTTPS connection (the standard for accessing secure websites) and added an authentication mechanism which requires user name and password.

So how well did it work?

The Vocab app on Windows Phone works very well! It can access either a 3G connection or a wi-fi connection, so it can be used more or less anywhere.

The phone app doesn’t require a continuous connection. It connects at the beginning of a test when it downloads the list of questions, and again at the end of the test to upload the list of answers. If a 3G or wi-fi connection isn’t available at the end of test, it will wait to upload.

There is one niggle with the phone app—the server app takes a few moments to get started and the phone app can time out and say it can’t contact the server. On the second attempt it connects successfully. I intend to amend the phone app so that it automatically retries.

However the back end PC applications use a continuous connection to the database and are a little bit sluggish when connected to Windows Azure SQL Database. They are useable, but they are noticeably slower than with the on premises SQL Server. (This happens even with a fast 40Mb/s Internet connection.)

There is also a potential problem with database connections getting dropped. This can happen because the Internet inherently is not as reliable as a local network connection, and because Windows Azure SQL Database is a shared environment providing for many users. The back end apps would need to be modified to cope with this.

How does SQL Data Sync help?

In this situation, a viable solution is to have synchronised databases in Windows Azure and on-premises. Windows Server and SQL Server are already available on-premises. And indeed, the entire Vocab application had been running on premises.

Figure 4 - Using SQL Data Sync to ensure the on-premises and cloud databases contain the same data

SQL Data Sync is part of Windows Azure SQL Database. It is easy to configure through a control panel and doesn’t require any programming.

To conclude

The system now works extremely well, with the proviso that it has only been tested with light loads.

The Windows Phone app connects with the Windows Azure cloud and can be used anywhere there is an Internet connection. The PC apps connect to an on-premises database across a local network. SQL Data Sync takes care of synchronising the data between the two databases.

I hope you found this interesting. Please feel free to comment.

Richard

The Cloud. What it is and why it matters

The cloud is now on every business person’s agenda – understanding it, using it, profiting from it.  But why is it so important?  And what makes it worth investigating? 

Firstly, internet connections are now faster and more prevalent; WiFi, 3G and 4G connections are available in more places to more people.  Secondly, computers are smaller.  Ten years ago, your choice was either the PC sitting under your desk, or the laptop that caused sloping shoulders.  Now your smartphone will fit in your shirt pocket, and a tablet slides easily into a handbag.

So although the idea of accessing a computer resource from a remote location isn’t new, the speed and ease with which we can access it is.  The rise of cloud computing means that businesses can now buy a range of services from companies based miles away, and use their computing as if it were located in their own building.  Perhaps more importantly, though, the maintenance and upkeep of this computer infrastructure is taken care of by full time specialists.  That means a higher quality of service for users, and fewer mistakes. 

Specialisation has long been shown to be at the heart of efficiency, and therefore competitiveness.    So the more any company is able to specialise, the more competitive it will be. 

Cloud computing itself is an umbrella term for a number of different specialist services that can be split into three broad groups: 

1. Software as a Service (SaaS) – this gives users the ability to use a fully functioning hosted application by just connecting to it.  SalesForce.com and Microsoft Office 365 are both SaaS.  All you have to do is set up the users, and use the software.  Oh, and pay the monthly invoice.

2. Platform as a Service (PaaS) – this is where you use a shared computing resource to host an application.  You may need a database, and perhaps a web site.  With PaaS, all the “platform” software is ready to use.  The license fees are included in your monthly fee, and the configuration is all done.  You just upload the software you want to use onto the preconfigured operating system and database, and off you go. 

3. Infrastructure as a Service (IaaS) – this is where a company uses one or more virtual machines, in exactly the way that suits them.  Because you are paying for a virtual machine, rather than a shared resource, you configure it exactly as you want it.  So if you want to use your own operating system, you can upload it and configure it to suit you.  You can use your own copy of SQL Server if you want to, providing you have a license of course.  The reason you have so much flexibility is that you are paying for a dedicated virtual machine, not a shared one.  So it’s much closer to working with your own systems, apart from the fact that someone else is worrying about the air conditioning, the power, and swapping out the hard disk when it fails. 

This range of cloud services enables companies to use “the cloud” in a way that best suits their needs – from connect-and-go to configuring systems exactly as needed.  This type of flexibility, and specialisation, will ensure cloud computing is not a passing phase, but a key component in enabling businesses to focus on their core skills, and creating world-beating customer offerings.