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
- SQL Server Migration Assistant for Access
- SQL Server Migration Assistant for DB2
- SQL Server Migration Assistant for MySQL
- SQL Server Migration Assistant for Oracle
- SQL Server Migration Assistant for SAP ASE
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.
- 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.
You can see the query that is going to create your data in the source and in the target but you cannot edit it
You can edit the data type and the precision
You can have a preview of the data in the source and in the target, which is always useful
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
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….
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.
You can export the target project to an SSDT package and do the same in Visual Studio
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.