Bowel Movements :: How to migrate a DB2 database to SQL Server

Haemorrhoids, this is how I should have called this post.

If you have been dealing with DB2, Oracle or Microsoft Access technologies there is an high chance that you are familiar with this scenario:

✅ The database comes straight from the ’80
✅ The database was already migrated from AS400 few years ago to a more “modern” infrastructure
✅ Nearly total absence of PK or FK “because the constraints were in the code”
✅ We want embrace digital transformation because the developers have retired we are future-oriented
❌ A proficient DBA has already worked on it

To lower expenses and find developers they are probably keen to move to SQL Server and Microsoft has been developing a solution for them…

SQL Server Migration Assistant

Which is a comprehensive, banal, facile tool that just gets shit done. Pun massively intended.
The migration sources allowed are:

And this is a very good tool if you find a spot in the migration niche: if you work in consultancy you are going to see a lot of this in the next few years and SSMA might become one of your best friends.

And yes, here we go again: still in 2022 Microsoft is asking you if you want your production environment to be part of their guinea pigs.
SSMA Here we go again
You can opt out. Who knows if that check box consider your choice…
Once installed… SSMA will not work…
SSMA Microsoft OLE DB Provider for DB2
this because you have to install the latest Microsoft OLE DB Provider.
But there is a positive thing about SSMA, the list of target is definitely modern, hands down:
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016
  • SQL Server 2017 on Windows and Linux
  • SQL Server 2019 on Windows and Linux
  • Azure SQL Database
  • Azure SQL Managed Instance
  • Azure Synapse Analytics (This target is supported only by SSMA for Oracle.)

SSMA “can” and “cannot”

Once you have connected to the source you can select what part of the schema or the list of table you want to involve in the migration. But you cannot really edit the structure of the table, or the column name, scale, or nullable field.

SSMA ups and down

You can see the query that is going to create your data in the source and in the target but you cannot edit it

SSMA SQL

You can edit the data type and the precision

SSMA Type Mapping

You can have a preview of the data in the source and in the target, which is always useful

SSMA Data previw

and you can apparently manage the table index but… no, you cannot really decide on what column put that index so, no you cannot really manage the index

SSMA Storage Options

If you right click on the table folder in the source database you can import a list of tables in bulk with copy/paste or from .txt. This is particularly good for huge, messy databases with around 1000 tables. Which is what happens in 20 – 40 years of database architecture without a goal….

SSMA Advanced Object Selection

You can export your source project to an SSIS package and then import it in Visual Studio and edit. This is a very good feature epecially if there is somethign you want to change in the exportation.

SSMA SSIS Package

You can export the target project to an SSDT package and do the same in Visual Studio

SSMA SSDT

In the end

SSMA is not that bad as a tool and it will help you cross the finish line if you want to migrate a database to SQL Server “as it is”.

Think about this tool as the tape that will help you fix the broken table 1h before the party starts.

But if you want to use nails and screws to give stability to the table Talend and SSIS are the go-to tools.

 

 

Related

Leave a Reply

You have to agree to the comment policy.