Making Molehills out of Mountains (Part 1)

  • Article

A couple of days ago I was involved in an Oracle Migration Workshop with a partner and client. It was an interesting experience because the client wanted to go from Oracle on a Unix-based platform to SQL Server on Windows. Their reasons were similar to most other organizations contemplating this move - lower TCO, standardized platform, better use of resources, and so on. Their initial reaction at the prospect of migrating three of their Oracle databases to SQL Server was also similar to others - This could be like climbing Mount Everest.

Migrating databases and their associated applications, scripts, stored procedures. and the like can seem a huge task if you are looking at the prospect of doing it manually. Table structures typically do not present too much of a hurdle. Most databases have similar scalar data types - char, date, int, number, etc. - and most support BLOBs in some fashion. Converting Oracle's data types to SQL Server's really does not present that much of a challenge. In fact SQL Server's Data Transformation Services (DTS) today can easily help get this done and SQL Server Integration Services (SSIS) in SQL Server 2005 provides even more functionality for the task. The real challenge is two-fold:

  • Converting PL/SQL code in packages, procedures, functions etc to similar objects in Transact-SQL (T-SQL) in SQL Server.
  • Dealing with embedded SQL calls within client-side applications.

In reality, client side application code will usually need to be re-written. Unless very standard SQL-92 syntax was used on the client-side application allowing you simply to switch the data source, you will need to look at the application code and re-write it or, better still, take the migration as an opportunity to re-architect the app - maybe a thin app instead of a thick app.

For stored objects (procedures, packages, etc.) a manual re-write is also not a happy prospect and, until recently, Microsoft did not really have a good tool to help alleviate the effort. Today, we do. The SQL Server Migration Assistant (SSMA) for Oracle actually does something that blows me away - it converts PL/SQL code to T-SQL!! Now, it may not be able to convert the code perfectly but it will do a good job at most of it and tell you where it can't.

Before this post gets too long, let me stop here. I will talk more about how SSMA works in my next post and show you how it can make a molehill of mountain of a migration project and save you lots of time and effort!!!