Synchronizing Data with Sage 300
Often there is a need to synchronize data from an external source with Sage 300 data. For instance, with Sage CRM we want to keep the list of customers synchronized between the two systems. This way if you enter a new customer in one, it gets automatically added to the other. Similarly, if you update say an address in one, then it is updated in the other. Along the way there have been various techniques to accomplish this. In this blog post I’ll cover how this has been done in the past and present a new idea on how to do this now.
In the past there have been a number of constraints, such as supporting multiple databases like Pervasive.SQL, SQL Server, IBM DB2 and Oracle. But today Sage 300 only supports SQL Server. Similarly, some suggested approaches would be quite expensive to implement inside Sage 300. Tied closely to synchronization is the desire by some customers for database auditing which we will also touch upon since this is closely related.
Sage CRM Synchronization
The first two-way data synchronization we did was the original Sage 300 to Sage CRM integration. The original integration used sub-classed views to capture changes to the data and to then use the Sage CRM API to make the matching change to Sage CRM. Sage CRM then did something similar and would write the change back to Sage 300 via one of its APIs.
The main problem with this integration technique is that its fairly brittle. You can configure the integration to either fail, warn or ignore errors in the other system. If you select error, then both systems need to be running in order for any one to use either. So if Sage CRM is offline, then so is Sage 300. If you select warn or ignore then the record will be updated in one system and not the other. This will put the databases out of sync and a manual full re-sync will need to be performed.
For the most part this system works pretty well, but isn’t ideal due to the trade off of either requiring both systems always be up, or having to run manual re-syncs every now and then. The integration is now built into the Sage 300 business logic; so sub-classed Views are no longer used.
The Sage Data Cloud
The intent of the Sage data cloud was to synchronize data with the cloud but not to require the on premise accounting system be always online. As a consequence, it couldn’t use the same approach as the original Sage CRM integration. In the meantime, Sage CRM added support for vector clock synchronization via SData. The problem with SData synchronization was that it was too expensive to retrofit that into all the accounting packages that needed to work with the Sage Data Cloud.
The approach the Sage Data Cloud connector took was to keep a table that matched the accounting data in a separate database. This table just had the key and a checksum so it could tell what changed in the database by scanning it and re-computing the checksums and if they didn’t match then the record had been modified and needed synching.
This approach didn’t require manual re-synchs or require both systems be online to work. However, it was expensive to keep scanning the database looking for changes, so they may not be reflected terribly quickly or would add unnecessary load to the database server.
What Does Synchronization Need?
The question is then what does a modern synchronization algorithm like vector clock sync require to operate efficiently? It requires the ability to ask the system what has changed since it last ran. This query has to be efficient and reliable.
You could do a CSQRY call and select based on the audit stamps that are newer than our last clock tick (sync time). However, the audit stamp isn’t and index and this query will be slow on larger tables. Further it doesn’t easily give you inserted or deleted records.
Another suggested approach would be to implement database auditing on the Sage 300 application’s tables. Then you get a database audit feature and if done right, you can use this to query for changed records and then base a synchronization algorithm on it. However, this has never been done since it’s a fairly large job and the ROI was never deemed worthwhile.
Another approach that is specific to SQL Server would be to query the database transaction logs. These will tell you what happened in the database. This has a couple of problems namely the queries on the transaction logs aren’t oriented around since the last sync, and so are either slow or return too much information. Further SQL Server manages these logs fairly aggressively so if your synchronization app was offline for too long, SQL Server would recycle the logs and the data wouldn’t be available anymore. Plus, this would force everyone to manage logs, rather than just have them truncated on checkpoint.
SQL Server 2008 to the Rescue
Fortunately, SQL Sever 2008 added some nice change tracking/auditing functionality that does what we need. And fortunately Sage 300 only supports SQL Server so we can use this functionality. There is a very good article on MSDN about this and how it applies to synchronization here. Basically the SQL Server team recognized that both data synchronization and auditing were important and quite time consuming to add at the application level.
Using this functionality is quite simple, you need to turn on change tracking for the database, then you need to turn on change tracking for each table you want to track changes for.
Then there is a SQL function that you can select from to get the data. For instance, I updated a couple of records in ARCUS and then inserted a new one and the result is shown.
This is giving me the minimal information, which is all I require for synchronization since I really only need to know which records to synchronize and then can get the full information form the main database table.
If you want to use this to audit all the changes in your database, then there are more options you can set to give you more complete information on what happened.
If you are writing an application that needs to synchronize data with Sage 300 (either one way or two way), consider using these features of SQL Server since you can add them externally to Sage 300 without affecting the application.
Similarly, if you are writing a database logging/auditing application you might want to look at what Microsoft has been adding to SQL Server starting with version 2008.