The SQL Server Migration Assistant (SSMA) is a tool for transitioning from other platforms like Db2 to Microsoft SQL Server. A reference guide for the SSMA can be found in Microsoft’s SQL Docs. This article addresses the issue of failed migrations without warnings or errors while connecting via the standard mode. The pictures herein have had sensitive data redacted to protect the innocent.
The technical environment surrounding this issue:
- Db2 v11.1
- SQL Server 2017
- SQL Server Migration Assistant for Db2 v8.15
The basic process for using SSMA is as follows:
- Create Project
- Connect to each database server
- Convert the schemas
- Synchronize the schemas from SSMA to the target database
- Migrate the data
When using SSMA to migrate a Db2 database to SQL Server, you will be prompted to connect at two separate steps in the process. Both steps 2 and 5 will ask for the connection information to each database. The images below show the typical SSMA connection dialogs for Db2 and SQL Server. Notice that there is no option for displaying the password.
During the initial connection on step 2, using incorrect credentials will result in an error message. However, when starting the data migration in step 5, if the credentials for either Db2 or SQL Server are incorrect, no error message will be displayed. In such cases, SSMA will proceed with the migration as expected, and provide any of the normal prompts regarding schemas and tables. The process will continue on with preparing the tables and such. The only indication of a problem occurs when it is time for SSMA to actually move the data. When data movement is triggered, SSMA will promptly come to a halt and present a data migration report. Though the report will show that the migration failed, the failure will have no useful information to help determine a cause. There is nothing in the report that would indicate a problem with the credentials. Checking that credentials were entered correctly is not an intuitive troubleshooting step as one would expect to see an error for that condition after clicking connect for respective connection–Db2 or SQL Server.
In our investigation of this issue we did not find any log information in SSMA or SQL Server. The Db2 diagnostic log, however, did show a warning with DIA3220W for the failed connection to the Db2 database.
2021-02-05-13.33.19.387505-300 I55683654A447 LEVEL: Warning
PID : 48955900 TID : 26112 PROC : db2sysc 0
INSTANCE: db2inst2 NODE : 000
APPHDL : 0-22590
HOSTNAME: notarealhostname
EDUID : 26112 EDUNAME: db2agent (idle) 0
FUNCTION: DB2 UDB, common communication, sqlcctcplogIPAddress, probe:50
MESSAGE : DIA3220W Client sent data from IP address = "10.10.10.10", port =
"50000".
Restarting the migration and carefully entering the correct password allowed the migration to complete. This seems like it would be a good candidate as a fix in an upcoming release. At the time of writing, version 8.16 was available, however the release notes make no mention of this issue. For instances where your migration comes to a sudden halt with an empty report, it is a good idea to retry the migration, taking extra care when entering any passwords for connecting.