Monday, October 29, 2012

Migration from SQL Server 2005 to Oracle 11G

Last year we grabbed an opportunity to migrate live CRM database of size 65GB from SQL Server 2005 to Oracle 11G. It is a matter of pride for us that our team has finished the job with zero error. Most importantly, the entire system-CRM Web application and SQL Server database got migrated in one night. So the business downtime was also very minimal.

The preparation of migration night was started 3 month before. Initial 2-3 weeks we spent for taking decisions about proper methodology because it was our first cross platform migration on Oracle. Investigating different available tools leads us to use and throw some of the famous tools available on the net but finally Oracle’s SQL Developer is the one which helped the most.

We practiced the migration process scenario wise and started documenting each and every scenario. Our process was iterative. SQL Developer too was not a full proof migration tool. It covers some of the common scenario but fails for some complex scenario. So iteratively we started solving each complexity and enabling SQL Developer to migrate further. The process took around one month to reach to the end of the database.

Now comes the part of validation of data. Though SQL Developer also does it for you but still manual testing was the one where you can rely upon. Validating the logic written inside the storedprocs, triggers and functions were also important from application point of view. So next 30 days we spent on that.

Once all the process set, it was now time for rehearsal of the migration. We rehearsed the entire process at least 15 times on current database. Our confidence was high but how the application would react for this database was the main challenge now.

For the application to use Oracle as database we needed to change the DAL layer. Oracle’s native driver is the one which could give us the most performance. But the existing system was using Enterprise Library Data application block. We decide to use the same library in customized form so that instead of using the default provider the application can take the leverage of Oracle.DataAccess client provider. We customized it with some intrinsic settings for connection creation used in the application. It was really blissful decision and it gave us a significant performance boost for application.