Notes on a Migration from Oracle to SQL Server

I have to admit, when asked to perform a migration from Oracle to SQL Server over the past couple of months, I was a bit apprehensive.  After having passed an all-nighter to finish the project off recently, starting at 3pm, and finally heading home around 6:30am, there was an unsuccessful attempt by the ‘I don’t use a plan for my project’ person on the other end of the line to get me to turn around to fix a single column size because one report did not work (which of course, was not noticed in pre-production/test environment, and later was fixed without my intervention). Luck won at that stage, but it is not always that way as a DBA without doubt.

First of all, to anyone who has just worked two shifts in a row; do not attempt to call them back to fix a minor thing (respect?), especially a column size fix that at least two other people (in this case) can do, not just because I’m a windging, grumpy, even zombie-like DBA at 6am in the morning, but most importantly, the due to the high risk of messing something up in production.  There are physical limitations that we must respect of any employee or contractor performing their duties while manipulating a critical production database instance - sleep is not negotiable. This should be needless to say, but after repetitively being asked to forego this basic need, I am blogging it!  

Secondly, as with all migrations, one should constantly try and adhere to the keep it simple rule (K.I.S.S.) – this migration was no exception, so what we did from the very beginning was create insert scripts of all the data into the tables (not a huge database, in the tens of megabytes only), since the schema export was already done for us by a vendor (to which I only had to do minor tweaks to appreciatively).  Before actually going through each table insert script one by one to adjust the fully qualified table names, add Set Identity_Insert On/off statements, with a quick truncate before the begin tran/inserts/commit batch, I had scripted out, in a previous step, all the drop/create foreign key and constraints statements to bring all the data in quickly without per-table FK/Constraint drop/recreation.

Finally, I created a checklist with all the table row counts printed out for manual check-off as I made progress through the 50+ tables to load into SQL Server 2008.  It took all night, as mentioned before, so running in the background Youtube’s links to three long series on Franco-Norman history, around and just after the Invasion, permitted my non-technical historical enrichment to benefit at the same time J

Now, I can honestly say that I look forward to migrating more databases over from Oracle.