Notes on a Migration from Oracle to SQL Server

Hugo-Shebbeare, 2009-10-26


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.

  You can't seem them easily, but believe it or not, this is a Turtle Farm in the Galapagos.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads