Creating experimental database

Question

This question is about databases but I prefer to post it here and not in dba.stackexchange. The reason is that I have concerns with the application as well as database design. Programmers can better assist in this regard.

My four year old .NET application is using MySQL 5.1 database server as back-end. The database is running well. There are no performance issues to make us worry about. But I want to try out advanced database features like Partitioning, Replication and Clusters. I have never used these concepts before and I can definitely give these features a try on existing database, but I have some other plans.

I want to use IBM DB2 express-C as an experimental database. Records from MySQL need to be synchronized into DB2 database and I want to use the DB2 database for reports in my application.

Is there any free tool to synchronize the two databases? Do I need to use two connection strings, the first one involving MySQL for Inserts/Updates/Deletes and the second one involving DB2 for Reporting?

Is this type of foolish approach used in real world in certain scenarios?

Answer 1

Well, you not only need two different connection strings, you also need completely different database connectors. And then the fun starts for real.

Most likely DB2 uses a different SQL syntax, so you would need to check all your select queries whether they actually produce the same output. Plus: each database manufacturer has a few custom features built into their dialect of SQL. Things that work under MySQL are not automatically guaranteed to work with DB2. Which means that just transferring or replicating the data into the DB2 database could be a major job on its own.

I have not come across this approach in any real world scenarios, but then, I don't know every real world scenario that exists out there.

From experience I would advise to steer well clear of this sort of thing unless you have a really strong reason to go down that road. You are most likely in for a world of pain for very little gain.

Answer 2

This is a common scenario in the database world. Nice to see someone who wants to learn it. Companies send data back and forth to different systems all the time (both their systems and their clients systems and their vendors systems). I can't speak to free tools to do this because I live in SQL Server world and SSIS is the tool we use which comes with SQL Server. But what you want to search for is ETL tools. Tools for this are out there, I just don't know which ones are the best. ETL stands for Extract, Transform, Load which are the steps in the process of moving data from one database to another.

Let me describe some of the issues you need to think about when sending data back and forth between two systems. The first and most important is that it is highly unlikely that the two databases will be simliar in structure. Fields that are reuired in one may not exist in the other. Data types will be different. Business rules will be different. Lookup values will be different.

You will have to handle things like the States table which will exist in one db and all addresses must use a valid 2 digit US state code in the required State field, but the data is coming from a table that allows any value (or even no value) to be put in a state field in the address table, therefore it has things like Virginia, Ohoi (instead of Ohio), CA, ON (Canadian state fo Ontario), and Unknown as well as null values. You have to figure out waht to do with records that have data that won't fit in the receiving database.

You need to think about how will you match up existing records to the ones already in your database.

Another thing you have to think about is how to handle data that is merged by one database due to finding and fixing duplicates. Do you need to have way to make sure it gets merged in the other database?

You have to think of performance. This can be an intense process that takes a lot of the resources of both databases. You need to think in terms of how can I minmize the impact to other users of the database.

Most ETL tools will let you send directly to the other database (generally if they are in the same network) and other times you need to create one or more files to send to a clinet in another location. Then you need to think about what the structure of the data sent should be. Should you have one denormalized file or several files that refelect the way your own database is normalized. Should the sender clean the data to the receiovers specs or vice versa or should both be doing some scrubbing. Are there legal requirements (think about sending patient data from a docotprs office to a hospital for instance).

There's a lot more, ETL is a complex specialty, but I think that is enough to get you started on thinking about the planning you have to do once you find your tool.