SSMA migration from MySQL to Azure SQL DB repeatedly dropping connection while data migration is in progress.
Error: 'The connection has been disabled'.
Collect relevant logs
In this case the error message is not giving us enough details on the root cause, so further investigation is required. Start initial data collection from SSMA, by following the below steps.
1. On source
- Collect the version details of the source database and the OS info. This is vital as there were numerous cases where we had started troubleshooting and then realized that we are on an unsupported build.
- Collect the version details of the providers / drivers used to make connection to the source database.
2. On destination
- Collect the version details of the SQL Server that we are trying to connect and the OS info.
- Collect the version details of the providers / drivers used to make connection.
3. Check the Migration Engine used (Client side / Server side)
- Navigate to Tools menu and go to Project Settings
- Check the migration engine option from the Migration menu
4. Collect the SSMA logs after reproducing the issue, to get Log file path
- Navigate to Tools menu and go to Global Settings
- Select the Logging page and copy the Log file path under Misc category
You are now ready to check the SSMA logs. Dropping connection with error 'The connection has been disabled' is often encountered with a connection timeout.
Mitigation
When migrating big tables, the workaround is to tune the default migration parameters in Project Settings, to avoid the operation timeout and to make sure the migration succeed.
- Open SSMA for MySql -> Go to Tools -> Project Settings -> Select General from the left menu -> Select Migration -> In the Misc section -> Set data migration timeout to a higher value (i.e. 1000)
- Open SSMA for MySql -> Go to Tools -> Project Settings -> Select General from the left menu -> Select Migration -> In the Parallel data migration section -> Set Parallel data migration mode from Auto to Custom and set Thread Count from 10, which is the default, to a lower value (i.e. 5)
- Open SSMA for MySql -> Go to Tools -> Project Settings -> Select General from the left menu -> Select Migration -> Lower the value of BATCH_SIZE (i.e. 1000)
- Run the data migration.
After the above steps, the migration should complete successfully.
For more details on SSMA tool, check SSMA reference documentation.