After using the SQL Server Migration Assistant for Oracle on a current project to migrate an Oracle database to SQL Server, I have learned a couple of little tricks that I would like to pass on to you.  These are mainly time savers so hopefully they will make you more productive in your project.

The first problem I noticed is that there was no easy way to copy projects nor was there a way to save a project file as another name, which would have allowed me to copy the project.  Why would I want to copy a project?  There are many settings such as column type mappings that will stay the same for my project and are very time-consuming to enter, if you are modifying the defaults.  In my migration, I want to do many iterations in my development and acceptance environments with different SQL Server database names such as Round1, Round2, or Round3 and then use Red Gate Compare and Data Compare to verify that all of the schema and data from the migration are consistent in my migration.  This gives me a warm and fuzzy feeling before I turn over the data to the testers. 

However with each iteration, I must create a new project manually.  Why?  Because SSMA does not refresh to recognize new databases in the existing projects.  That is very annoying.  It only shows what is there  at the time the project is created.  Even if you reconnect to the SQL Server, it does not refresh. 

If you know ahead of time that you will do many iterations, you could however create several databases before starting with the SSMA and then copy the project folder from the first project that you create manually.  You must then rename the folder to the new project name and the corresponding project file (with an o2ssproj file extension) contained within the project folder.  This would allow you to simply switch to the new database.  You must also keep in mind that you need to double-click on the Oracle schema and make sure the target schema is selected as your new database.  This will not happen automagically as you choose a new database name in the SQL Server metadata explorer.

Another option is to create new projects as you create the databases and then copy in the object-container.mappings and/or project-container.mappings files from the first project folder into the new project folder.  This will allow you copy those settings into the new project and save you some time.  I hope you find this useful.  Enjoy.

The Oracle Conversion, day 14, continues and it is not as exciting as I envisioned.  Oh, don’t get me wrong, the end result, Global Domination for SQL Server, will be the ends that justify the means.  But until then, migrate data, check data, clean data, remigrate date, check data, clean data, time migration, repair migration process, check data, clean data, remigrate, ad nauseam.  Rinse, lather, repeat.

Oh, this is not my first rodeo, I’ve converted many projects before but never have I converted Oracle before.  Granted, the SQL Server Migration Assistant for Oracle has made life much easier but at the same time it has made life much more difficult. 

It is a quirky tool that is very temperamental.  We had to restart it before we did a successive migration or it would fail we are guessing because it possibly caches information from the previous migration process and the MSDN blog wasn’t much help.  We also had to hit apply on every setting change or it would revert some of them back to the original value. 

In addition, we would have to convert schema sometimes four or five times before it would synchronize them properly to SQL Server, saying that the SQL execution failed.  That answer gives me a huge amount of troubleshooting possibilities.  When it works, it was a great time saver but had we known the quirkiness we probably could have scripted it all out and saved a lot of headaches. 

Doesn’t sound like a big deal? 

Well when you have some big tables and it takes eight hours to convert the schema and you try synchronizing and it fails and you have to convert the schema again, you watch the progress bar for many hours.  Oh yeah, I left out the fact that the vendor is responsible for the migration contractually, so I guess ultimately they should have scripted it out.  Enjoy!