Internet connectivity is prevalent and the need of the time everywhere. Despite incredible advancements, there are however situations where the internet connectivity is unavailable or undesirable.
I came across a requirement from one of our clients. They had Microsoft Dynamics AX implementation across multiple warehouses and there were issues related to connectivity between the remote warehouse and the headquarter warehouse where AOS was deployed. Also, within each warehouse they wanted to use Mobile applications for different warehouse operations. The connection between the mobile device and the local warehouse server was also sporadic with regular disconnections.
The following diagram explains the scenario in detail:
As you can see there are two disconnections:
- Disconnection between the headquarter warehouse and the remote warehouse
- Disconnection between the remote warehouse and the mobile device within that warehouse
The challenge of the problem was how do we still allow mobile device workers to operate in completely offline mode and sync when the connection is available? The mobile device users should be able to receive Purchase orders, transfer orders, check inventory on-hand and perform a bunch of other warehouse operations.
The table below highlights the connectivity details between different warehouses and also the peak working hours.
As you can see, the connection with the AOS Warehouse (Headquarter warehouse) was only possible between 10PM-12AM for Warehouse 1 and 11PM-1AM for Warehouse 2. Whereas, the working hours were during the day time.
One solution was to push all the data into mobile device databases and have them sync up. But each device is operated by a worker. This would add an additional burden to manually sync up the mobile device during the connectivity hours.
This solution has a few drawbacks:
- Worker would have to manually sync their device with the AOS.
- On-device database has a limited space whereas the AOS database could be huge. Getting all data on individual devices is not recommended.
- Two devices between the same warehouses cannot be synced and cannot communicate. Multiple workers might receive the same purchase orders.
The alternate approach was to introduce a Warehouse server. The mobile devices will have an on-device SQLite database that will communicate with the Warehouse server database running SQL Express.
On daily basis, the Warehouse server database will sync up with the AOS server and work in a bi-directional fashion.
How to Sync SQLite with SQL Express?
A middleware called ‘Warehouse sync server’ needs to be deployed. This will help sync the SQLite data with the SQL Express in the individual warehouse. The Warehouse sync server will be hosted on IIS and will be running a .NET application for carrying out sync operations.
To sync SQLite and SQL Express, we can also use SQLite-sync
To get greater flexibility, the .NET application should be written to consume Microsoft Sync Framework
We opted to Microsoft Sync Framework and custom application approach to allow greater flexibility and control.
How to sync Warehouse SQL Express with SQL Server AOS Database?
A middleware called ‘AOS sync server’ needs to be deployed in the AOS Warehouse. A .NET application needs to be written to allow sync between the 2 databases. The .NET application should consume Microsoft Sync Framework for synchronization.
Also, change tracking needs to be enabled on individual SQL servers to allow sync operations.
To get an understanding of how to write the sync application between SQLite and SQL Server using Microsoft Synch Framework, please go thru this link:
Thank you for reading this blog. In case of queries, feel free to contact me at Bilal@AXPulse.com