Friday, 24 January 2014

SSMA - SQL Server Migration Assistant

At a DBA reunion dinner just before Christmas I was talking to a friend and mentioned that I was using SSMA to migrate from Oracle to SQL Server 2012.  "Poor you," he said "That program's a real dog."

I was surprised at his attitude.  I had by that time migrated a couple of databases without a great deal of difficulty and since Christmas I have migrated about 20.  Small things, admittedly, nothing larger than a dozen tables and a couple of million rows of data,  but still, "dog" seemed unfair.  Turned out he had last used the Migration Assistant that came with SQL Server 2000 - and like so much else, Microsoft has been doing some work on it.

Somewhere in Redmond, WA - early this century...
 "OK, guys, we wanna make it easy for people to move to us from Oracle - any ideas?"
 "Er, Mr Gates, how about a migration assistant that actually works?"
"Smart thinking Wilbur, here's a few million dollars, go away and spend it wisely".

 Here's what Wilbur came up with:

I log onto my development server and click on the icon you see here.  The SQL Server Migration Assistant for Oracle opens.  (Incidentally, I believe that there are at least four SSMAs - one for Access, this one for Oracle, one for Sybase and one for MySQL.  Plus versions.)  Here's the team blog.

 Up comes the splash screen:

and then the home screen:

Two menu bars at the top.
Top left quadrant is the Oracle Metadata Explorer.
Bottom Left Quadrant is the SQL Server Metadata Explorer. 
The other two quadrants are for showing you information, and they aren't terribly important - I'll touch on them in passing. 

Getting Started

Click on File, then on New Project.  Up pops the New Project Dialog box - enter a name e.g. Oracle2SQL - Databasename, and click on OK.  If you have lots of different databases to convert, it's worth making a note of which project is which - maybe start an outlook task and put the project name in the Title. 

Connect to Oracle

      Click on Connect to Oracle on the menu at the top of the screen – it will come up and ask for the connection details, and especially the password.  It's a good idea to get the Oracle DBAs to make a copy for you, so that you are never extracting data from the live system.  

      Enter the password.  After you click on Connect, check the bottom of the screen for progress messages (it takes a couple of minutes - loading this, loading that).  Wait for the magic word "Done".

      The top right quadrant now fills up with a list of the various schemas - the Oracle Source Schema, and the SQL Server Target Schema.  There is also a Type Mapping tab and a Properties tab.  You can make changes here if you want to.  I haven't done - generally it does a pretty good job without me tinkering.  And while I'm not totally certain about some of its decisions, I prefer to change them in SQL Server, where I can see the context.  More on this later. 

         Select the Oracle Schema (equivalent of the SQL database) to be converted:


Connect to SQL Server

        Click on the menu option that says "Connect to SQL Server".  

If it’s a new database, enter the name and SSMA will create it.   Do not change the name - Do NOT change from CAPS to Title Case, do not even fix a typo.  Be especially careful if you are doing one job after another – it assumes that you want to do the previous one again.  If you miss this and accept the existing name, you are probably better off abandoning this one and starting it again, being more careful next time. 

      As with Connecting to Oracle, this takes a little while - not as long-winded as Oracle though.  Look out for the word "Done".  When it appears, select the destination database in the SQL Server Metadata Explorer box.  The bottom right hand quadrant fills up with SQL Server information.  Honestly, it's so dull, there's no point in looking at it - I certainly don't intend to show you a picture.

In the Oracle metadata explorer box, highlight the schema to be converted - the easiest way is to right click on it - then Click on Convert Schema.  (For some reason Microsoft show Ctrl R as a shortcut, even though they underline the n of Convert.  Looks a bit sloppy to me, but I’ve never bothered with these shortcuts as in this case it’s easiest to do it with the mouse). 

Check for Warning messages in the dialog box at the bottom of the screen – it may be helpful to copy them to a Notepad box, or an Outlook task, if you created one earlier.

Right click on the Database name in SQL Server metadata explorer and select Synchronise with the Database.  It spins its wheels for a few moments and then pops up a box that is entitled "Synchronise with the Database". 

You are going to find this difficult to believe, but what you do now is press OK.  And off it jolly well goes, whistling a quiet little tune to itself.  It synchs the Oracle structure with the SQL Structure.  No data as yet, but we are getting there.  

      Leave the SSMA program for a moment, go into SQL Server, and take a full backup of the database. You could do this later if you prefer.  Select each table in turn, Script as Drop & Create to New Window,    
      Review the possible errors noted above in the Outlook task.  

      A Few Words about Data Types

      The data types in Oracle do not exactly match those in T-SQL, but the defaults do the job pretty well.  Despite this, I can fairly safely guarantee  that the commonest warning message you see will be:
Warnings: O2SS0356: Conversion from NUMBER datatype can cause data loss.
      This takes the Oracle NUMBER datatype and guesses that it’s a SQL Server FLOAT.  And of course, it depends.  Consider whether float should be INT e.g. for an ID, or FLOAT e.g. for a height. On most of the databases I’ve converted, INT seems to be a better choice.  

      Strings get turned to Varchar – fair enough – you might want to think about Nvarchar if you have any Unicode records.   One exception - varchar(1) should be char(1).  (What earthly use is a varchar(1), eh?)

      Then Execute to change the table.            
Save Script in Data Management Program documentation e.g. Q:\Data Management Programme\Oracle2SQL Migration\SQL\Be_The_Key

      Run exec sp_helpdb [BE_THE_KEY_ENG_NIR]  and note the logical and physical file names in your Outlook Task.  DO NOT CHANGE THE NAMES AT THIS STAGE!   Even if they are horribly wrong.  Do that and SSMA will decline to migrate the data, and you have to start all over again. 
      See notes on untangling the database.

      Migrating the Data 

      OK, back to SSMA now.  In Oracle Metadata Explorer, right click on the Schema and select Migrate Data.  Off it goes - you can watch the numbers clicking upwards if you like.  Be patient - it isn't hugely quick, especially of you have something complicated,  But generally it will think for a while and then come up with a Finished message:

      Look out for that success rate column  - it should be straight 100%.  If it isn't, you will need to do something about it, but usually it comes out fine.  Save the project, save the schema, and Exit.  Congratulations - you have just migrated an Oracle database to SQL Server.