Applications on laptops that travel outside the office, such the order application used by the company sales representative, need to store data and historically the solution has been to have a local database server on the laptop. When the sales representative returns to the office, at the end of the week, the laptop is plugged into the network and is synchronised with the main database, typically using SQL Server Merge Replication.
The main issue with this approach is that changes made to the head office database and to the order on the laptop database are only synchronised once per week. This means price and stock changes could be missed and new orders are received late.
So why not have the order application on the laptop connect directly to the database server in head office? The reason is usually connectivity and security. The head office firewall would need to allow external connections, which in some organisations is not desirable.
There are a couple of cloud based solutions to this issue:
1. Store the database in the cloud and access it from the laptop and head office. That way the laptop can access it directly and so can head office. This will work for some organisations but others will still want the main database to be on the premises in the head office
2. Store a copy of the part of the database that the order application needs in the cloud and keep it synchronised with the main database in the head office
SQL Azure, Microsoft’s SQL in the Cloud offering, can support both of these options, but option 2 is most the likely solution.
In addition SQL Azure offers a fully managed service, including backups, maintenance and automatic failover to an alternate server in the event of failure. Meaning that there is no need for a buy a new server or to have to install and manage it.
From a development perspective the order application connects to the database on SQL Azure in exactly the same way as it does with an on premise database and the programmer uses exactly the same SQL. Azure uses a login to gain access to the server and also has support for firewall rules to restrict where connections can come from.
The synchronisation requires the installation of the “Microsoft SQL Azure Data Sync Agent” on the laptop and the head office server. The agent connects to Azure to carry out the synchronisation and can be configured to do this at a designated frequency (such as every 30 minutes).
All will remain reasonably up to date and no need to allow connection in through the head office firewall.
