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