universal replication

Startbeitrag von Arie am 17.11.2015 10:42

I have a requirement to fill a datawarehouse from my application.
We use hfsql in our application. For every project there is an separate database.

I need to upload part of the data to an mssql server, using the same table structure with one difference. It will be used as a datawarehouse for reporting software (not WD).
Every table in mssql gets an extra "project" field and the data of all hfsql-database (projects) is then stored in this one mssql-database.

Could the replication mechanism of pcsoft be usefull?
@ Fabrice: could your replication mechanism be usefull?

It must be rock solid. I also need some mechanism to detect errors (i.e. no database connection, index corrupt or whatever fault may happen during the process) and send an email to certain users.

I even consider using triggers and 'sync' the data on-the-fly. (hfsql and mssql are on the same network) . Any caveats here?


Hi Arie,

I would really NOT use pcsoft replication (any of them), as rock solid is definitely NOT what comes to mind.

As for WXReplication, it can of course be used for that. Because you have an extra field in MSSQL, you would need a few changes (different analysis for the web component, if the web component is the one writing in MSSQL, or in ONE engine, if it's setup that way.

-BUT- your analysis would have to comply with my system (GUIDs as the only unique keys in files).

If you do not want to do that, then you would have to modify a LOT of the code.

Best regards

von Fabrice Harari - am 17.11.2015 13:22

atm I do not have GUID in all tables but 'regular' auto-IDs. To change that it will take considerable time (and risk) too.

Any experience already on performace? In this case it means at least hundreds of thousands of records every night. It is not supposed to take hours.

von Arie - am 17.11.2015 13:42
Hi Arie,
there is no risk in changing autoID by GUID, and for that I have a step by step guid available on my site, in the wxreplication area.

As for performance, you should NOT think in term of one operation a night. A replication system is generally used as much in real time as possible, so your HF DB/machine should send the data to the other one every 5 mn, little by little. Otherwise, it will always be too long. And that is true for ANY replication system.

If you want to make some calculations, there is ALSO an article on the performances I measured here during my testing. Of course, as usual, millage may vary.

Best regards

von Fabrice Harari - am 17.11.2015 14:19
I agree about the replication as kind of real time thing.

Making a dump every night is a different approach I was thinking of. Would be better if I had put that in a separate reply....

von Arie - am 17.11.2015 15:09
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.