I have to synchronize two databases. Individuals databases stores same semantic objects but physically different across two databases.
I plan to utilize a DTO Pattern to uniformize object representation :
DB ----> DTO ----> MAPPING (Getters / Setters) ----> DTO ----> DB
I believe it is a better idea than physically synchronize using SQL Query on both sides, I personally use hibernate to include abstraction, and synchronize object.
Do you consider, it's wise ?
Doing by using an ORM may be reduced by order of magnitude than the usual well-crafted SQL script. It is dependent on how big the DB.
I'd include that your decision should rely on the quantity of variations between your two schemas, not your expertise with SQL. SQL is really common that designers should have the ability to write simple script inside a clean way.
SQL has additionally the benefit that everyone understand how to run the script, although not everyone will understand how to cost you custom tool (this can be a problem I experienced used if migration is really operated by someone else).
For schemas which only slightly differ (e.g. names, or simple transformation of column values), I'd choose SQL script. This really is most likely smaller sized and simple to make use of and communicate.
For schemas with major variations, with data organized in various tables or complex logic to map some value in one schema towards the other, a devoted tool could make sense. Odds are the the first effort to create the tool is much more important, but it may be an resource once produced.
Opt for non-functional aspects, for example exception handling, logging of errors, splitting operate in more compact transaction (because you will find a lot of data), etc.
SQL script can certainly become "untidy" under such conditions. For those who have such constraints, SQL will need advanced abilities and are usually difficult to use and keep.
The custom tool can evolve right into a small-ETL with capability to chunck the job in small transactions, manage and log errors nicely, etc. This really is more work, and can lead to as being a devoted project.
Your decision is yours.
I've done that before, and I believed it was quite a solid and simple method to map between 2 DBs. The only real bad thing is that whenever either database changes, I needed to update the mapping logic, but it is usually really quite simple to complete.
Nice reference above to Hitchhiker's Guide.
My two cents. You have to think about using the best tool to do the job. Even though it is compelling to create custom code to resolve this issue, you will find numerous tools available that already do that for you personally, map source to focus on, do custom tranformations from attribute to attribute and will probably deliver with faster time for you to market.
Turn to ETL tools. I am not really acquainted with the various tools avaialable on view source community but when you lean for the reason that direction, I am sure you will find some. Other tools you may take a look at are: Informatica, Data Integrator, SQL Server Integration Services and when you are coping with spatial data, there's another known as Alteryx.