Integration: Keep two systems in sync

crondata synchronizationintegrationoracleoracle-12c

I have a GIS system with 40 tables, ranging from 1,000 to 60,000 rows per table. The tables are the system of record for assets in a municipality.

The GIS assets in the tables get integrated to a Workorder Management System (WMS) on a weekly basis. The integration is based on web services that serve up the GIS tables to the WMS.

Constraint #1:

The integration to the Workorder Management System is multi-purpose.

There is a single asset table in the WMS that gets updated, via cron tasks, with any edits that have been made to the GIS assets (new assets, changed assets, and decommissioned assets). Only assets that have been edited are updated in the WMS.
The integration is also used to dynamically serve up the assets to a web map in the WMS (all of the GIS assets are used in the map–not just the assets that have been edited). The map in the WMS connects directly to the GIS web services–it does not use the records in the asset table or the cron tasks.

Constraint #2:

The WMS cron tasks are notoriously slow. Given my organization's infrastructure, my vendor says that the WMS cron tasks will only be able to sync 150 records per minute.

Testing is ongoing, but we have been told to only sync the records that actually need to be synced (edits) due to the significant performance concerns. In other words, we can't just integrate or copy all records, all the time.
To give you an idea, this is what the cron task process looks like:
REST GIS web service >> JSON object >> Parse the JSON into individual records >> Generate XML for each record >> Process the XML records with Java classes >> Insert the records into the database.

Constraint #3:

GIS data is notoriously messy.

In constraint #2, I mentioned that the records get processed with Java classes. The Java classes check for errors (parent/child, field rules, etc.) and flag any records that fail.

These records do not get integrated into the WMS.
It is up to the GIS teams to correct the errors in GIS tables, then we'll try again in the next integration instance (next week) to sync the GIS records to the WMS.

Question:

Given the constraints above, I think I need to figure out a way to integrate all the GIS assets to the WMS (constraint 1.2), but also flag the records that need to be synced due to edits (constraint 1.1).

For edited assets that failed to sync–I need to retry them in future syncs until they are successful.
And I need to avoid syncing records unnecessarily–due to the performance concerns.

How can I do this?

Best Answer

Are you running flashback database? If you were you could run a process that would start just after the top of every hour. The process would look at each table as it was at the beginning of every hour and do a Merge command between the source and destination databases for a one way sync. You would then be able to replicate all of the tables every hour. If for some reason, this process takes more than an hour, then you would replicated every other hour using the top of every even or odd hours as the starting point. You would be replicating based on consistent data across all tables everytime.

Goldengate would probably work better if you were licensed for it. It would keep the data more current more of the time. Governments should get a higher discount than corporations. It's worth getting a price for Goldengate even if you find out it costs too much.

Related Solutions

Mysql – Sync two MySQL databases in two different locations

You have a few options:

Set up MySQL replication between the servers. Your internal server can act as the Master, and the web host server as the slave. Any updates performed on the Master will be replicated to the slave immediately (assuming a working connection). This will most likely be the easiest and most effective option to choose. To use replication, your internal database would need to be accessible over the network from the web host.

You can read more about replication here.
Each day, you can perform a mysqldump on the internal server, upload the dump file to the web host, and import the data. Since this is a full dump, if you have a very large database, this might not be feasible. If you like, this procedure could potentially be scripted to avoid having to do it manually.
You can set up binary logging on the internal server. You can then ship the binary logs to the web host and apply them to the database, effectively playing all transactions that occurred that day to the web server. In effect, this is what happens with replication anyway, so you would nearly always go with the replication set up instead of this option.

If there is no connection between the two databases, taking mysqldumps each day will be the easiest path to take.

Mysql – Sync 8 tables of differents databases on same server

There's actually a very simple solution to "syncing" multiple identical tables in MySQL, and that is by not trying to "sync" actual base tables, at all... but instead creating something that looks like another table but is essentially something along the lines of a "reference" or "alias" to the base table, using a simple view like this:

CREATE ALGORITHM=MERGE VIEW this_database.users AS SELECT * FROM that_database.users;

Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table. For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table...

^{— http://dev.mysql.com/doc/refman/5.6/en/view-updatability.html}

The view definition, above, meets these criteria, and would be updatable. Queries referencing a view with a simple definition like this will behave, essentially, no differently than if they were actually written to update (and insert into, and delete from) the actual underlying base table.

So INSERT INTO this_database.users is exactly the same as INSERT INTO that_database.users, and the same is true for UPDATE and DELETE.

If there are foreign keys involved, then the child tables have to have their foreign keys declared against the actual base parent table, but MySQL (InnoDB) has no issue with foreign key constraints that cross database boundaries.

Indexes on the base table will still be used where appropriate, since the MERGE algorithm essentially causes MySQL to "merge" the logic of the view definition into the query you've actually written -- thereby accomplishing the magic of a table that functionally is a reference to another table.

You don't actually have to explicitly declare ALGORITHM=MERGE, since the optimizer will always choose it on such a simple view, but it's still probably a good choice to include that in the definition.

While it is possible that Wordpress might do something internally that prevents this from working, I can't think of anything offhand. It seems like it would be worth testing in your environment.

Best Answer

Related Solutions

Mysql – Sync two MySQL databases in two different locations

Mysql – Sync 8 tables of differents databases on same server

Related Question