I am a SQL Server DBA with minimal Informix 11 experience.
I'm tasked with extracting an entire Informix database (circa 8GB according to systables in Informix) into a SQL Server database. I plan to do this using linked server use OPENQUERY.
However I am worried about the impact this may have. It's not a large database at all and I know Informix does row level locking (was worried it may do full table locking) and I'm going to run it when the application is not busy.
Does anyone have any advice regarding this?
Best Answer
My main concern, regardless of the migration mechanism used, would be for the consistency of the migrated data. How are you going to ensure that you don't miss changes while the data is transferred.
One of the classic Informix tools could be DB-Export, which (by default) locks the database so it isn't being changed while the export occurs. It gives you the schematic and unloaded (text) data files.
Another option would be to make an archive of the existing system and then restore that into a new (temporary) instance, and you could then run your migration against the temporary instance without affecting the working instance. But you'd still have to worry about later changes.
If your existing machine is not too woefully under-powered, you should be OK running the export as you suggested, but you still face the issue of ensuring consistency of the migrated data if users are changing the source system while you are migrating.