We are moving from SOAPware (a medical records package) to OpenEMR (another medical records package). SOAPware only runs on windows/MS SQL; OpenEMR runs on just about anything, using MySQL.
We need to make this migration happen fairly soon. I was naive enough to think that moving SQL to SQL shouldn't be that difficult -- but then I forgot we are talking about microsoft...
I figure if I can use open source products to make this happen, that I'd put my solution back out into the open source arena -- I'm sure there are other doctor's offices that will want to make the same migration happen.
What I'm looking for, to make this request as simple as possible, is a reliable tool to xfer the existing windows XP pro/MS SQL database and tables into their equivalent MySQL database and tables. Just give me everything that was in MS SQL, saved in equivalent files in MySQL.
If the migration tool also does nice things like convert dates from MS SQL format to MySQL format, that would be wonderful. But if it does not, then I'll fix that later.
I will write the code (most probably in perl) to do the rest. I'm kind of looking at it in these steps:
1) Raw MS SQL to raw MySQL migration, leaving MS SQL files unchanged. (This is the tool I am asking for help getting and making work). I assume both of these will be on the windows box. That's fine, I can xfer the MySQL files to my linux system for the rest of it.
2) Cleaning up the raw MySQL tables, copying the information into correct MySQL formatting plus changing fields so they are more in line with what OpenEMR will be using. This will leave the raw MySQL tables unchanged, and create a cleaned (2nd) set of tables.
3) Take the information from the cleaned MySQL tables and put them into OpenEMR formatted MySQL tables.
4) Generate a PDF report, one for each patient, containing absolutely everything from every table pertaining to that patient, using the cleaned tables as my source. I will probably add that as a report to the OpenEMR system for the patient.
I have successfully done plenty of data migrations in the past, so I'm fairly confident that I can pull this off. I might not get 100% of the data, but I'll get enough that the doctor's office will be okay with it. The reason for step #4 is so there is a record of everything which can be accessed later on, if needed.
So I'm asking for advice/pointers/help on any of the above steps. Left to my own devices, I could do everything except step #1. I am NOT a windows kind of guy -- I have worked exclusively on linux (and various forms of unix) since early 1990. I don't know windows well enough to feel confident in doing this step.
Also, when I'm done, I'll want to stick this someplace for everyone to access it. I have not packaged anything for public consumption in a VERY long time, so pointers on doing that would also be nice...