Ibrahimdemir suggested I should describe how we migrated from SQL Server to Oracle. Here are few notes on the subject. Obviously, there are lots of details here – the move took anything from 2 years to 6 month, depends on where you start counting, so I’m only mentioning the highlights.
1) Motivation: The official reason for moving was that Oracle released version 10.2. It became clear that many companies are successfully deploying RAC systems and that it represents a real solution for database high availability. High availability was such an issue with SQL Server that management agreed to the project with surprising speed.
Less officially, we were five Oracle DBAs and we found ourselves managing a production environment that is based almost entirely on SQL Server. We hated that and wanted a way out. RAC was simply a bait for upper management.
2) The applications: Our largest application was supposed to support Oracle out of the box, but we needed to change JDBC drivers to one that supports RAC. During tests we found other errors related to Oracle being different than SQL Server (Lots of issues around case sensitivity, which completely slipped my mind when I wrote the previous post), but mostly the change on the application was rather painless.
Other applications needed to be changed. Anything from small modifications to the connection string, to complete and total rewrite – depending on how the application used different database features.
We had to convert around a dozen applications, and the most difficult part was hunting them all down. Just yesterday, five days before we shut down the SQL Servers, we found two more applications that were somehow forgotten.
3) Deciding to migrate: At first we didn’t plan to migrate at all. We planned to put new customers on Oracle and keep existing customers on SQL Server, with the understanding that at some point the SQL Server environment will die naturally.
We lived with both environments for about a year and around that time two interesting things happened – we discovered that a two node RAC can carry the same load as six SQL Server machines and give significantly better performance, and the data center started pressuring us to use less electricity. The decision was made – move everything to Oracle and shut down the SQL Servers.
4) How to migrate: By the time we decided to migrate, we already knew exactly how the applications should work on Oracle. The correct schema were already in place. The only question was how to move the data. Our development team took a very controversial approach (I’m still not convinced it was the right one), and they wrote two utilities for the move.
The first utility could dump an SQL Server schema to a bunch of CSV files, and then load the CSV files into a ready schema on Oracle side. The utility moved only data, not definitions (table structure, indexes, constraints, etc were not included), but it was OK because we had the right definitions on Oracle already.
The second utility could “listen” to new data coming in to the SQL Server, and copy that data to Oracle. We used sequence (and identity) numbers to get this done.
Using both utilities we could copy a customer over to Oracle, run both systems in parallel for few days until the customer was happy with Oracle and then cut off the data duplication and remove the SQL Server schema.
We moved over 200 customers this way. One by one over the course of 6 month.
5) Summary: We thought we got it all nailed and done. We were ready for the big shut down on Wednesday. However, this morning we discovered two applications that were forgotten. So, shutdown was delayed by two weeks (which are rather costly, because the data center really wants us to cut down on electricity), during this time we have to make the necessary changes in the application, test them and make the move. We hope that this time it will really be it.
Recently I had to move an application from SQL Server to Oracle. I knew the databases are different, but I had no idea how different. Here are few issues I ran into:
1. Dates. Thats the most obvious difference. SQL Server dates are cast into strings implicitly, developers don’t need to do anything. Date arithmetics is done differently, and date functions are completely different. If you do anything interesting with dates in your code, you are better off writing it from scratch for each DB. Trying to translate the different functions can get you into trouble.
2. Partitions. SQL Server 2005 supports partitions, but before that SQL Server developers were stuck with creating lots of identical tables and putting them in a view to simulate partitions. When you translate an application to Oracle, you probably want to use real partitions instead.
3. Views. In SQL Server, if a view has a set operation like “Union All”, you can still apply DDL commands (like update) to the view itself instead of the underlying tables. The tables will update automatically. Unfortunately, by version 10.2, Oracle still doesn’t support this functionality.
4. Functions. In SQL Server a stored procedure returns the result of the last query used in it. So basically procedures return a table, just like a query. It was quite useful. In Oracle you will need to use temporary tables for a similar functionality.
5. Wait events. SQL Server doesn’t allow you to trace event 10046. Actually it doesn’t even give the normal SQL Trace functionality that Oracle has. No TKProf either. It does have a profiler – but it is not nearly as detailed as what Oracle provides. After working in Oracle, you can no longer understand how anyone managed to do any performance tuning with SQL Server.