The Challenges of Mobile Database Synchronization

Databases –

Many if not most enterprise mobile applications need to interact with a server side database of some kind. Mobile Apps for Sales, Direct Store Delivery and CRM, for example, usually need to have this functionality in order to work. Since even today it cannot be assumed an Internet connection will always be available to the device an alternative to the always online model needs to be provided. In the real world there are still a lot of places with spotty coverage, slow data service, overloaded cells and expensive metered service plans. So what is the best way to go about syncing SQLite to SQL Server Databases?

One solution is to design the apps to function on devices that are only occasionally connected and use a local database to store data. This of course means that some method of data exchange will be needed to move data back and forth between the device and the server.While this may sound simple enough the reality is very different. Software developers that treat mobile to server data exchange / synchronization as an afterthought often discover just how onerous a task it can be.Until recently the options available to solve this issue have been few, none too good or non-existent.

Build a totally self-developed data synchronization process requiring custom code on both the server and client side of the solution.

Use a commercial or public domain data sync SDK that at least provides the core needed and can be built upon.

Buy a license for a proprietary and non-standard mobile database app that also has a server side and supposedly handles the sync between the two.

Sign up with a hosted sync service that claims to handle the task but bills per record synced and thus can become very costly very quickly.

Purchase a Mobile Enterprise Application Platform which provides tools/features that handle synchronization

Find a solution that handles the entire process automatically, works with industry standard databases and development tools, does not require writing a single line of code, takes no time to learn and costs next to nothing compared to any of the above choices.

So if the occasionally-connected model is the reality, and database sync a requirement, what approach is best and how should the developer proceed?

Home-Grown:

Creating a totally home-grown sync function should be the least desirable of the alternatives listed but sadly it seems to have become the most common. Don’t misunderstand, to build a fully functioning cross platform database synchronization solution, which is compliant with accepted industry standards, would likely require years, a big pile of money and several highly skilled programmers with lots experience.

The truth of the matter is that most home-grown database sync does not really synchronize anything. They’ll do a full download of all data in a server table or they will FTP the entire database file to or from the server. They’ll do remote query to a web service and cache results to a local XML or DB. They’ll create hundreds or even thousands of lines of code on both the server and client side to simulate sync but in the end it won’t be sync.

This approach is highly custom to each application and very inflexible over time. It is also extremely susceptible to errors and therefore database corruption. Any changes to table structures will require making modifications to server side and client side application and the pseudo-sync functions. Application maintenance or enhancement becomes a nightmare and update deployment becomes a massively costly effort.

SDK:

Using some synchronization SDK like the Microsoft Sync Framework^TM is certainly a better choice over any self-built method. However, even this approach has many drawbacks and pitfalls. Most significant of these is the long learning curve that is inherent to any complex SDK but especially the Microsoft Sync Framework^TM. A seasoned developer with no prior experience using MSF could need from a month to a month and a half just to wrap his head around how it works and why it does what it does the way it does. This is before he writes a single line of code that is specific to the application being developed.

Which brings us to the next most significant drawback of using an SDK; you must still write and maintain custom code. Granted, it is nowhere near as much custom code as in the home-grown solution. Nor is it usually any code for doing actual database sync. But, it is still a lot of code that has to be created to define your database structure so that the standard sync functions of the SDK can interact with your data. In the case of the Microsoft Sync Framework a developer has some tools available to help reduce coding at the server side but nothing is available to help or cut effort at the remote device side. The device side will still need to be 100% custom code with database scheme information usually hard-coded. MSF provides no out of the box support for non-Windows client operating systems. So for Android and iOS all aspects of the client side will need to be custom coded by the developer.

The final major drawback of using an SDK (that I want to point out) is inherent to the concept of using such a tool. It is designed to be used by programmers to create new programs, modules or libraries. It is not designed to be user friendly, overly robust in functionality or well documented. Sometimes it’s nothing more than an example of what’s possible when given enough investment of time and money. In other words it’s not going to have everything you need out of the box and you may not find out what’s lacking until you are in real deep.

Proprietary Database:

Ok, if home-grown is bad and SDK’s are better but not great what about proprietary databases?

Using a proprietary database application to enable mobile apps with synchronization may seem like a good alternative at first. Their claimed advantage is that the sync functionality is built in by them and you won’t have to do anything. They will provide a mobile database app to be used on the device and a server side database counterpart. The server side will be presented as open in nature and able to integrate with other industry standard database servers like MS SQL, Oracle, etc.

On the surface all this sounds wonderful but on further review the downside becomes clear. Firstly, every mobile device running Android or iOS already comes from the factory with a built-in open database application called SQLite. Since this is for all intents the native database of current mobile devices developers typically build their programs to use and support it. Using a proprietary database means having to install an extra app on every device that your main program runs on. It also means extra costs as each server and device will need to purchase a software license. And, since the new database app is different it might require re-writes of whole sections of any app that was already built to use SQLite. Finally, if for any reason the maker of the proprietary database goes out of business any apps that use it will now be orphaned.

Many of these same concerns apply to the server side of any proprietary database solution. Other things to consider is how robust and scalable is the database engine on the server. If you are deploying 10 or 20 mobile devices then perhaps it’s not important. But if you are going to have hundreds or thousands of users out there then the performance of the server database is critical. In this day and age it seems like a very big gamble to choose a non-industry standard database platform. Microsoft SQL Server is an industry standard and should be top of the list for anyone needing to build an enterprise solution for mobile.

Hosted SAS:

One of the more recent alternatives for mobile database synchronization is a hosted or SAS model. In this scenario, the server side of the mobile database solution is hosted on some environment like the Amazon Cloud and sync to and from the devices is handled as a service. Users can sync a certain amount of data for free and then pay by the record when they pass the limit. In some cases the free account is so limiting that it’s practically useless so most if not all users would end up paying and no small sum.

How your data gets into the cloud from your ERP, for example, and back again is not clear and likely requires custom programming or at a minimum data export/import. How the mobile application gets its initial database structure, data load or schema changes is also not clear. These two issues are no small thing to address. If programming needs to be done it would be specific to the service selected and not re-usable elsewhere.

The long term viability of this type of approach has yet to be determined. With so many unknowns and such a potentially high cost of usage; we don’t believe this is a good solution. Depending on the total investment it could become unfeasible to cancel or switch later; basically tying you into a costly service in perpetuity.

MEAP:

For many developers a Mobile Enterprise Application Platform presents a very attractive option. MEAPs provide an entire set of tools for developing mobile apps. If the MEAP selected also handles database sync of some kind that might seem like a big bonus. However, the main benefit of the MEAP is also its biggest drawback. As a development environment onto itself the developer has to learn and then master it. Should the developer need some feature not available in the MEAP environment he would either be out of luck or have to find some work around. Just like the investment of time to learn the MEAP; the investment of money to buy the MEAP is significant. Since most MEAPs are licensed per developer a large shop will quickly spend a lot of money for licenses and annual maintenance.

Back in the early days of mobile app development (the late 1990’s and early 2000’s) there were many different development tools available. In those days they were called RAD Tools as in Rapid Application Development. Some of these enjoyed early success and got used by developers and companies to build in house and commercial apps. Unfortunately, within a very short period of time these RAD tool companies began to go out of business and die off. Developers that used these tools to build there apps found themselves orphaned and with no migration options. They either re-wrote their entire app or also went out of business. My concern with MEAPs is that history has a bad habit of repeating itself. So buyers beware.

SyncStudio:

SyncStudio is a patent pending software tool that solves the issue of mobile database synchronization completely in an easy to use and affordable manner. One way to describe SyncStudio is “Freedom from pain and suffering when adding synchronization to your mobile database application”.

Considering all the alternatives listed above, and their corresponding short comings, creating a solution like SyncStudio was no small endeavor. We are mobile developers too, and we understand the needs of our fellow developers because we’ve been there. Before we sat down to create SyncStudio we looked at the options and determined that something better was needed. Too much time and money was being spent on inadequate custom methods. The Microsoft Sync Framework was too difficult to learn and use. Proprietary databases are too limiting and expensive. Hosted sync or a MEAP was going to be costly and still involve custom coding.

We created SyncStudio to solve all these issues and free the developer to focus on building his app. With SyncStudio a completely custom database synchronization solution can be created in minutes without programming a single line. True and robust database sync between SQLite and Microsoft SQL Server can be added to any mobile app by anyone; even a non-programmer.

SyncStudio is built on top of the Microsoft Sync Framework but totally insulates the user from it. With SyncStudio there is no need to learn anything about MSF or how it works. You don’t need to know C#, VB.NET or even install Visual Studio. We are not a MEAP nor do you need to buy one. There is no coding on the server side and all the synchronization coding is handled by our native app or API on the client side. For Android our client side API can be used in Eclipse or Basic 4 Android. If your development tool can use a standard Java .JAR then you can use SyncStudio. For Windows Phone the SyncStudio client gets added to your project like any other DLL. A Nuget package will be available soon for download as well. In the future versions will support iOS, Windows RT, Windows and additional developer environments.

On the server side the SyncStudio Studio presents a wizard like user-friendly interface that guides you though the steps to connect, configure, generate and deploy a fully working synchronization service for your SQL database. On the client side the SyncStudio universal sync client class library is used to communicate with the server and execute the sync process that brings SQL data from the server to a SQLite database on the device and back again. Regardless of what the client side is the server side is the same so a mixed mobile environment is no problem.

Database schema and schema changes are part of the information sent from the server and client side logic automatically builds or updates the local database for you. The server side sync service runs in IIS and is easily scalable to hundreds or thousands of users with the right infrastructure.

SyncStudio is not a home-grown database synchronization solution. Since database synchronization is built into the Microsoft Sync Framework we did not have to create our own method or re-invent the wheel. The core functionality that handles the sync is from Microsoft and very solid. By abstracting the MSF from the user with SyncStudio we have cut weeks of learning time down to minutes. Our interface leads the user step by step and automatically provisions the server database for synchronization, generates and compiles the custom sync server and even configures IIS and deploys the .DLLs.

SyncStudio works with the databases you already have. There is no need to use some company’s proprietary client or server database. There is no need to install a different database app on your device or learn/administer a new database on the server. And there is no need to figure out how you are going to get your data from your real database server into the proprietary one or back again. SyncStudio totally simplifies the task of implementing mobile database synchronization.

Technology advancements have introduced the question of whether to host a database server yourself or have it hosted for you. However, regardless of what you decide you should not have to give up control or be locked into expensive and recuing charges. SyncStudio lets you stay master of your own destiny. Host locally or on a cloud based Virtual Server. There is no transaction or record based costs with SyncStudio.

Robust functionality, simplicity, low cost and freedom; this was our goal when we set out to build SyncStudio. We believe we’ve accomplished this and we hope our fellow developers will agree. There simply is no better way of syncing SQLite to SQL Server Databases; period!