Migrate a SQL Server database to Azure SQL Database


Azure Database Migration Service partners with DMA to migrate existing on-premises SQL Server, Oracle, and MySQL databases to Azure SQL Database, Azure SQL Database Managed Instance or SQL Server on Azure virtual machines.

 

SQL Migration.png
Azure SQL Migration (Source Microsoft Corp)

 

Moving a SQL Server database to Microsoft Azure SQL Database with Data Migration Assistant is a three-part process:

  1. Prepare a database in a SQL Server for migration to Azure SQL Database using the Data Migration Assistant (DMA).
  2. Export the database to a BACPAC file.
  3. Import the BACPAC file into an Azure SQL Database.

Using Microsoft Data Migration Assistant

Step 1: Prepare for migration

Complete these prerequisites:

  • Install the newest version of Microsoft SQL Server Management Studio (SSMS). Installing SSMS also installs the newest version of SQLPackage, a command-line utility that can be used to automate a range of database development tasks.
  • Download and Install the Microsoft Data Migration Assistant (DMA).
  • Identify and have access to a database to migrate.

Follow these steps to use Data Migration Assistant to assess the readiness of your database for migration to Azure SQL Database:

  1. Open the Microsoft Data Migration Assistant. You can run DMA on any computer with connectivity to the SQL Server instance containing the database that you plan to migrate; you do not need to install it on the computer hosting the SQL Server instance.
  2. In the left-hand menu, click New to create an Assessment project. Fill in the form with a Project name (all other values should be left at their default values), and then click Create.
  3. On the Options page, click Next.
  4. On the Select sources page, enter the name of SQL Server instance containing the server you plan to migrate. Change the other values on this page if necessary, and then click Connect.
  5. In the Add sources portion of the Select sources page, select the checkboxes for the databases to be tested for compatibility, and then click Add.
  6. Click Start Assessment.
  7. When the assessment completes, look for the checkmark in the green circle to see if the database is sufficiently compatible to migrate.
  8. Review the results the SQL Server feature parity results. Specifically review the information about unsupported and partially supported features, and the recommended actions.
  9. Review the Compatibility issues by clicking that option in the upper left. Specifically review the information about migration blockers, behavior changes, and deprecated features for each compatibility level. For the AdventureWorks2008R2 database, review the changes to Full-Text Search since SQL Server 2008, and the changes to SERVERPROPERTY(‘LCID’) since SQL Server 2000. For details about these changes, links for more information are provided. Many search options and settings for Full-Text Search have changed.
  10. Optionally, click Export report to save the report as a JSON file.
  11. Close the Data Migration Assistant.

Step 2: Export to BACPAC file

Follow these steps to use the SQLPackage command-line utility to export the AdventureWorks2008R2 database to local storage.

  1. Open a Windows command prompt and change your directory to a folder in which you have the 130 version of SQLPackage, such as C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin.
  2. Execute the following SQLPackage command at the command prompt to export the AdventureWorks2008R2 database from localhost to AdventureWorks2008R2.bacpac. Change any of these values as appropriate to your environment.

SQLPackageCopy

sqlpackage.exe /Action:Export /ssn:localhost /sdn:AdventureWorks2008R2 /tf:AdventureWorks2008R2.bacpac

Once the execution is complete the generated BACPAC file is stored in the directory where the sqlpackage executable is located. In this example, C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin.

  1. Log in to the Azure portal.
  2. Create a SQL Server logical server

A SQL Server logical server acts as a central administrative point for multiple databases. Follow these steps to create a SQL server logical server to contain the migrated Adventure Works OLTP SQL Server database.

  1. Click the New button found on the upper left-hand corner of the Azure portal.
  2. Type sql server in the search window on the New page, and select SQL server (logical server) from the filtered list.
  3. Click Create, and enter the properties for the new SQL Server (logical server).
  4. Complete the SQL server (logical server) form with the values from the red box in this image.
  5. Click Create to provision the logical server. Provisioning takes a few minutes.

Step 3: Create a server-level firewall rule

  1. Click All resources from the left-hand menu, and click the new server on the All resources page. The overview page for the server opens and provides options for further configuration.
  2. Click Firewall in the left-hand menu under Settings on the overview page.
  3. Click Add client IP on the toolbar to add the IP address of the computer you are currently using, and then click Save. This creates a server-level firewall rule for this IP address.
  4. Click OK.

Step 3: Import a BACPAC file to Azure SQL Database

The SQLPackage command-line utility is the preferred method to import your BACPAC database to Azure SQL Database for most production environments.

SqlPackage.exe /a:import /tcs:”Data Source=<your_server_name>.database.windows.net;Initial Catalog=<your_new_database_name>;User Id=<change_to_your_admin_user_account>;Password=<change_to_your_password>” /sf:AdventureWorks2008R2.bacpac /p:DatabaseEdition=Premium /p:DatabaseServiceObjective=P6

Connect using SQL Server Management Studio (SSMS)

  1. Open SQL Server Management Studio.
  2. In the Connect to Server dialog box, enter this information.
  • Server type: Specify Database engine
  • Server name: Enter your fully qualified server name, such as mynewserver20170403.database.windows.net
  • Authentication: Specify SQL Server Authentication
  • Login: Enter your server admin account
  • Password: Enter the password for your server admin account
  1. Click Connect.
  2. In Object Explorer, expand Databases, and then expand myMigratedDatabase to view the objects in the sample database.

Using Azure Database Migration Service

Azure Database Migration Service (ADMS), now in limited preview, can help you migrate existing on-premises SQL Server, Oracle, and MySQL databases to Azure SQL Database, Azure SQL Database Managed Instance, or SQL Server on an Azure Virtual Machine.

ADMS is designed to simplify the complex workflows you can encounter when migrating various database types to databases in Azure.

  1. In the Azure portal, select Data Migration Service, and then click New Migration Project.
  2. In New Migration Project, enter a unique project name, server source type and a target server type.
  3. Click Start.
  4. Provide all options under Migration target details, and then click Save.
  5. Provide all options under Migration source detail, and then click Save.
  6. In the Select source databases list, select each source database you want to migrate, and then click Save.
  7. Review the details summary, and then click Run Migration to start the migration. The amount of time the migration will run depends on a variety of factors including size and complexity of the database, source disk speed, and network speed.
  8. Once the migration is finished, a Completed status will be displayed in the SQL Migration dashboard.

Author: LM Publications

This is me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.