Exercise - Migrate a database schema by using Data Migration Assistant


In this exercise, you create a migration project by using Data Migration Assistant and move your database into Azure SQL Database. For this exercise, you'll use the offline migration option, accepting downtime. This way, you won't incur the cost associated with the premium database, a requirement of online migrations.

Create a new project

  1. If you're not connected already, connect to the admsdemovm virtual machine through RDP.

  2. Open Data Migration Assistant.

  3. In the left menu pane, select +.

  4. In the New pane that appears, for Project type, select Migration.

  5. For Project name, enter Social Database Migration.

  6. Leave Source server type set to SQL Server, and leave Target server type set to Azure SQL Database.

  7. From the dropdown list, change Migration scope to Schema only, and select Create.

Select the source database

  1. In the Connect to source server section, for Server name, enter admsdemovm. Accept the other settings at their default values, and select Connect.

  2. After you're connected, you'll see a list of databases. In the list, you can select only one database to migrate. In this case, you have only the Social database.

    Source server.

  3. To continue, select Next.

Select the target database

  1. Get the name of your SQL Server instance that was created earlier. Open the Azure portal if you don't already have it open.

  2. Open the admsdemorg resource group, and select the SQL Server instance.

    Items in the resource group, with the SQL Server instance selected.

    The SQL server Overview pane appears.

  3. In the Essentials section, at the end of Server name, select Copy to clipboard.

    Selection for copying the server name.

  4. Return to Data Migration Assistant on your virtual machine. Paste the name of your SQL Server instance into the Server name box.

  5. For Authentication type, select SQL Server Authentication.

  6. In the Username field, enter azuresqladmin, and enter the password that you specified for this account.

  7. Select Connect.

    The pane is updated to show a list of databases on the target server. From here, you can select a database that will be the target of the migration.

  8. You should see the Social database that you created during the setup exercise, and it should be selected by default. To proceed to the next step, select Next.

    Social database selected as a target.

Select objects to migrate and deploy the schema

In this procedure, you can select the database objects that you want to migrate, and clear the ones you don't. In this exercise, you only have one object, the table dbo.Twitters.

  1. Select dbo.Twitters, and you'll see there are no issues found for this object. This is in part because you fixed any issues during the assessment phase of the migration.

    Selected object with no issues.

  2. To proceed, select Generate SQL script. This step generates a T-SQL script that will re-create the selected database objects on the target server. Note the warning provided about SQL logins that might have been selected as part of the migration.

    Generated script for a schema.

  3. To deploy the schema to the target server, select Deploy schema.

    After the target database is updated, it displays the results on the right.

    Deployment results.

You have now completed the schema migration, and can close Data Migration Assistant.