Data Migration Assistant: How to migrate your on-premises SQL Server instance to modern SQL Server platforms


Data Migration Assistant (DMA) provides seamless assessments and migrations to modern on-premises SQL Server and SQL Azure VM data platforms.  The following step-by-step instructions will help you to perform your first SQL server migration using DMA.

Create new migration project

  1. Click on the “New(+)” button on left pane and select “Migration” project type. Set the source and target server type to SQL Server if you intend to upgrade your on-premises SQL Server to a modern on-premises SQL Server.
  2. Click “Create”
    ProjectCreation

Specify source & target

  1. Provide the source SQL Server instance name for “Server name” in the “Source server details” section, use the “Authentication type” supported by source SQL Server instance.
  2. Provide the target SQL Server instance name for “Server name” in the “Target server details” section, use the “Authentication type” supported by source SQL Server instance.
  3. Microsoft recommends you to encrypt the connection by selecting the "Encrypt connection” checkbox provided in the “Connection properties” section.
  4. Click “Next”
    SpecifySource

Add databases

  1. By-default all the user databases on the source SQL Server instance are selected for migration, but you can pick the specific databases that you want to migrate by only selecting those databases, on the left-pane.
  2. Use the migration settings page on the right side to set the migration options that may applied to all databases.
    1. Specify the “Shared location accessible by source and target SQL servers for backup operation”. Make sure that the service account running source SQL Server instance has write privileges on the shared location and target SQL Server service account have read privileges on the shared location.
    2. Specify the location to restore the data and transactional log files on the target server.
    3. These changes could be applied globally to all selected databases by selecting the Server in left pane. However individual databases could have their own settings which could be set by selecting a database in the left pane.

    AddDatabases

  3. If you can’t provide a shared location that both source and target SQL Servers have access to it, you can choose the option, “Copy the database backups to a different location that the target server can read and restore from”. When this option is selected, provide “Location for backups for restore option”. Make sure that the user account running DMA, has the read privileges on backup location and write privileges on location that the target server restores from.
    DifferentLocations
  4. Click Next. DMA performs validations on the backup folders, data and log file locations. If any validation fails, please fix the options and Click Next.

Select logins

  1. By default, all the SQL and Windows logins that qualify for migration will be selected for migration. You can pick the specific logins by selecting only those logins as needed.
  2. Click Start Migration.
    LoginSelection

View results

  1. You can monitor the migration progress in “View results” screen.
    ViewResults

Export Migration results

  1. Click on “Export report” to save the migration results to a csv file.
  2. Please review the saved file for details about Logins migration and verify the changes.

Resources

Comments (0)

Skip to main content