Azure Data Migration Service

Azure has an amazing migration service which is easy to use and is suitable for various data migration needs. The service has the capability to do offline and online migration (i.e. migrate data without any downtime to the source database).

Note: Online migration is available in premium pricing tier

The complete list of source and target database supported for offline and online migration is available in the below link:

https://docs.microsoft.com/en-us/azure/dms/resource-scenario-status

Steps involved in migration

• Create Azure Database Migration Service in Azure portal.
  

• Create a new migration project and enter the source and the target database credentials.
  Note that ADMS requires TLS 1.2. Check the ‘Common Errors’ section on how to resolve this.
  

• Choose the databases that needs to be migrated
  

• Choose the location where the exported data will be placed for the restore process to pick up.
  Note that this location should be accessible by both the source and the target database. Check the ‘Common Errors’ section on how to map the network location.
  

• Once the activity is complete, backup file will be generated in network share location. We must manually delete it if it is not required
  

Common Errors

• Error due to algorithm mismatch (TLS 1.2):
  If we get this error, then TLS1.2 is not present in SQL. Either remove it from ADMS or enable it in SQL server.Enable in SQL using the upgrade appropriate for your version.
  https://support.microsoft.com/en-us/help/3135244/tls-1-2-support-for-microsoft-sql-server
  For my 2012 express edition, I used this
  https://support.microsoft.com/en-us/help/3194719/ms16-136-description-of-the-security-update-for-sql-server-2012-servic

• For the network share path to be accessible by both the source the target, run the xp_cmdshell to map the network path.
  Example: The following commands are used to map the File Storage so that it can be used as the network share location
  — To allow advanced options to be changed.
  EXEC sp_configure ‘show advanced options’, 1;
  GO
  — To update the currently configured value for advanced options.
  RECONFIGURE;
  GO
  EXEC sp_configure ‘xp_cmdshell’, 1;
  GO
  — To update the currently configured value for this feature.
  RECONFIGURE;
  GO
  EXEC xp_cmdshell ‘net use Z: \\nameofstorage.file.core.windows.net\fileshare /u:nameofstorage xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’
  xp_cmdshell ‘dir Z:\’

• When there is not enough space in file storage, clear the unwanted files from the share location and retry the migration process