Migrate databases from Amazon RDS for SQL Server to Azure SQL Database – Part 1

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

Introduction

Azure SQL Database is a fully managed platform as a service (PaaS) database engine that automates backups and other operational tasks and is always running on the latest stable version of SQL Server. For many general-purpose workloads, Azure SQL Database can be a very cost-effective alternative to running the full SQL Server product, so it is a common scenario to move databases from SQL Server to Azure SQL Database.

 

Why move from Amazon RDS for SQL Server to Azure SQL Database?

Amazon RDS for SQL Server is a popular choice for SQL Server workloads in the cloud. It provides a fully managed relational database service that offers SQL Server 2014, 2016, 2017, and 2019 while offloading database administration tasks like managing backups. However, Azure SQL Database can provide additional benefits not available in Amazon RDS for SQL Server:

  • Licensing Cost. When you set up an Amazon RDS DB instance for Microsoft SQL Server, the software license is included; it cannot be purchased separately. In Azure you can take advantage of Azure Hybrid Benefit, which allows you to use existing SQL Server licenses with Software Assurance to pay a reduced rate on Azure SQL Database, usually saving over 35%-50% in licensing costs.
  • Free built-in High Availability and Zone Redundancy. Amazon RDS for SQL Server provides high availability by offering Multi-AZ DB instances, which are implemented using either SQL Server Database Mirroring or SQL Server AlwaysOn Availability Groups to provide zone redundancy. AWS Multi-AZ deployments cost extra, as much as twice the cost of Single-AZ deployments. Azure SQL Database high availability is achieved by using multiple compute nodes which are provided even in the lower price Basic, Standard and General Purpose service tiers. With Azure Availability Zones, SQL Database can place different replicas of the Business Critical database to different availability zones in the same region to provide zone-redundant availability at no extra cost.
  • Always up to date SQL. When you set up an Amazon RDS DB instance for Microsoft SQL Server, you must specify the version of SQL Server to use. When Amazon RDS supports a new version of a database engine, you can upgrade your DB instances to the new version; however, this is a manual process that requires some downtime, not much different as if it were a regular SQL Server VM. In Azure, SQL is always up to date with the latest updates and patches being automatically applied, eliminating end-of-support hassles.
  • Elastic Pools. Azure SQL Database elastic pools are a cost-effective solution for managing a group of databases that have varying and unpredictable usage patterns. This is a common problem for software-as-a-service (SaaS) developers, who usually create one database per customer but can end up with overprovisioned resources because of the unpredictability of customer usage patterns. Elastic pools solve this problem by ensuring that databases get the performance resources they need when they need it. They provide a simple resource allocation mechanism within a predictable budget. Amazon RDS for SQL Server lacks an elastic option for these scenarios.
  • Serverless. Serverless is a compute tier for single databases in Azure SQL Database that automatically scales compute based on workload demand and bills for the amount of compute used per second. The serverless compute tier also automatically pauses databases during inactive periods when only storage is billed and automatically resumes databases when activity returns. Serverless is a cost-effective choice for databases with intermittent, unpredictable usage patterns that can afford some delay in compute warm-up after idle usage periods. Amazon RDS for SQL Server does not offer a serverless option.
  • Large databases. Amazon RDS for SQL Server maximum database size is 16TB. The Hyperscale service tier in Azure SQL Database supports up to 100TB.
  • Security. Amazon RDS for SQL Server lacks some of the advanced security features provided by Azure SQL Database such as threat protection, vulnerability assessment and dynamic data masking.

 

Migration approach

Source SQL Server systems include:

  • SQL Server on-premises.
  • SQL Server on Azure Virtual Machines.
  • SQL Server on Amazon Elastic Compute Cloud (EC2).
  • Amazon RDS for SQL Server.

Moving databases from these systems into Azure SQL Database can be achieved using various mechanisms; the main options are:

 

Migration option

Comments

Import/Export Service (BACPAC)

It is the easiest to use and is integrated with SQL Server Management Studio. However, it requires additional storage and downtime because data is exported at the source, possibly copied to another location and then imported at the destination. It can also take a long time to process databases with many objects.

Data Migration Assistant

Migrates schema and data.

Good for medium-sized deployments (i.e., small number of databases).

Azure Database Migration Service

Ideal for migrations at scale (i.e., large databases or many databases).

 

This article focuses on the Amazon RDS for SQL Server scenario; describes a common problem that you might face when migrating to Azure SQL Database, provides some options to work around it, and a practical guide using three possible migration methods. The topic is divided in two parts: Part 1 covers the use of the Import/Export Service (BACPAC) and the Data Migration Assistant. Part 2 will cover the migration using the Azure Database Migration Service.

 

The problem with admin

When you create a database in Amazon RDS for SQL Server, the AWS console suggests admin as the name for the system administrator account.

 

Specifying the Master username when creating a new RDS SQL databaseSpecifying the Master username when creating a new RDS SQL database

 

This value can be changed, but many people just accept the default when configuring a new database. This would not present a problem if the database were to be later migrated to SQL Server or to Azure SQL Managed Instance, but it turns out that ‘admin’ is not a valid login name in Azure SQL Database. For example, consider the following T-SQL statement:

 

CREATE LOGIN [admin] WITH PASSWORD=N'S0meP@ssword!'

 

This statement would work against SQL Server or Azure SQL Managed Instance, resulting in the creation of a server login called ‘admin’. If the same statement is run against Azure SQL Database, it will result in the following error

 

        Msg 40616, Level 16, State 1, Line 5
       'admin' is not a valid login name in this version of SQL Server.

 

During migration from Amazon RDS for SQL Server to Azure SQL Database, the problem with the ‘admin’ login is that even though the SQL Server logins are defined at the server level and not at the database level, the migration tools will include server logins along with the database. Therefore, they will fail when attempting to create the login in the target server. For example, when using the Import/Export (BACPAC) feature, SQL Server Management Studio will throw the following error during import:

 

BACPAC Import error caused by presence of 'admin' loginBACPAC Import error caused by presence of 'admin' login

 

The specific error is highlighted below:

 

Error SQL72014: .Net SqlClient Data Provider: Msg 40616, Level 16, State 1, Line 1 'admin' is not a valid login name in this version of SQL Server.

 

Data Migration Assistant would also throw a similar error:

 

Data Migration Assistant deploy schema error caused by presence of 'admin' loginData Migration Assistant deploy schema error caused by presence of 'admin' login

 

Solution alternatives

There are a couple of options to work around this problem:

  • Rename the ‘admin’ login in the source RDS server prior to the migration.
  • Skip the ‘admin’ login when generating the scripts to be deployed to the destination Azure SQL Database and create an alternate login in the target database (with a name different to ‘admin’).

 

Rename the ‘admin’ login in the source Amazon RDS for SQL Server

This option sounds obvious, but it is not necessarily a feasible option in all cases. Renaming the login to something other than ‘admin’ prior to exporting from Amazon RDS for SQL Server will ensure that the BACPAC will work when importing it into the target Azure SQL Database.

 

To implement this option, connect to the source Amazon RDS for SQL Server instance using a query tool such as SQL Server Management Studio, select the master database, and run an ALTER LOGIN T-SQL statement. For example:

 

ALTER LOGIN [admin] WITH NAME = [rdsadmin];

 

After renaming the login, SQL Server will re-map the corresponding ‘admin’ database user to the new server login (i.e., the server login will have a new name, but the database user based on that login will continue to be called ‘admin’). Note that if you were connected using the ‘admin’ account in SQL Server Management Studio, the connection to the server will be broken after executing the statement because the ‘admin’ login is no longer valid. You will have to re-connect using the new login name to continue working in SQL Server Management Studio.

 

After the ‘admin’ login has been renamed at the source, you can use any of the three methods mentioned earlier (Import/Export BACPAC, Data Migration Assistant, Azure Database Migration Service) to migrate the database.

 

Note

If an application relies on connecting to the database using the ‘admin’ login specifically, it will have to be modified or reconfigured so it can use a different login. If this is not possible (e.g., the application is “hard-coded” to use the ‘admin’ login), then Azure SQL Database is not a suitable target; in this case, consider migrating to SQL Server on Azure Virtual Machines, or to Azure SQL Managed Instance.

 

In some cases, it might not be possible to rename the ‘admin’ account at the source:

  • If some application still requires access to the source server using the ‘admin’ login.
  • If you are not allowed to make any changes to the source system prior or during the migration.

If these restrictions apply to your scenario, you should use the approach described in the next section.

 

Create an alternate ‘admin’ login in the target Azure SQL Database

If renaming the ‘admin’ login in the source Amazon RDS for SQL Server instance is not possible, an alternative approach could be to avoid creating the ‘admin’ login when migrating the schema; instead, create a login with a different name in the target Azure SQL Database server and then create a database user named ‘admin’ based on that login.

Note

Having a database user named ‘admin’ will ease migration of applications and existing T-SQL code that relies on that specific database username. However, it will still be required for the application to connect using a login different to ‘admin’. If it is not possible to modify or reconfigure the application so it can use a different login. (e.g., the application is “hard-coded” to use the ‘admin’ login), then Azure SQL Database is not a suitable target; in this case, consider migrating to SQL Server on Azure Virtual Machines, or to Azure SQL Managed Instance.

 

This approach can be implemented with a combination of SQL Server Enterprise Manager and the Data Migration Assistant (DMA) tool:

  • Use SQL Server Enterprise Manager to manually create a new login in the target Azure SQL Database server.
  • After the login has been created, use the Data Migration Assistant (DMA) and specify to skip the creation of login/user when migrating the schema.

 

After you determine which approach you can use, you should choose the migration tool for your scenario. The following table provides a guide on how to choose.

 

Solution approach

Migration tool options

Rename the ‘admin’ login at the source Amazon RDS for SQL Server

·       Import/Export Service (BACPAC)

·       Data Migration Assistant

·       Azure Database Migration Service

Create an alternate ‘admin’ login in the target Azure SQL Database

·       Data Migration Assistant

·       Azure Database Migration Service

 

Using the Import/Export Service (BACPAC)

A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from the database. A BACPAC file can be stored in Azure Blob storage or in local storage in an on-premises computer and later imported back into Azure SQL Database.

Note

Only use this option if you have renamed the ‘admin’ user at the source Amazon RDS for SQL Server instance.

 

Using the Import/Export Service (BACPAC)Using the Import/Export Service (BACPAC)

 

The BACPAC file can be generated using SQL Server Management Studio or the SQLPackage.exe command-line utility. The following step-by-step instructions guide you through the Import/Export process using SQL Server Management Studio.

 

Export the source database into a BACPAC file

  1. Open SQL Server Management Studio and connect to the Amazon RDS for SQL Server instance as if connecting to a regular SQL Server Database Engine (in the server name, specify the endpoint of the Amazons RDS for SQL Server instance).
  2. In the Object Explorer pane, expand the Databases node, right-click the desired database, point to Tasks, and then select Export Data-tier Application:

    Starting the Export Data-tier Application wizardStarting the Export Data-tier Application wizard

    The Export Data-tier Application dialog box appears.

    Export Data-tier Application wizardExport Data-tier Application wizard

  3. Select Next, and then, on the Export Settings page, specify a location to store the BACPAC file.

    It can be a local path (Save to local disk), or a container in an Azure Storage account (Save to Microsoft Azure). Note that the Save to Microsoft Azure option also requires that you specify a local directory for the temporary file. The temporary file will be created at the specified location and will remain there after the operation completes.


    Export SettingsExport Settings

  4. Select Next.
  5. On the Summary page, confirm that the settings are correct, and then select Finish.

    Export SummaryExport Summary

    When the process finishes, the Results page appears.

    Export ResultsExport Results

  6. Confirm that all the steps completed successfully, and then select Close.

 

Import the BACPAC file into the target database

  1. In SQL Server Management Studio, connect to the target Azure SQL Database specifying the corresponding server name.
  2. In the Object Explorer pane, right-click on the Databases node, and then select Import Data-tier Application.

    Starting the Import Data-tier Application wizardStarting the Import Data-tier Application wizard

    The Import Data-tier Application dialog box appears.

    Import Data-tier Application wizardImport Data-tier Application wizard

  3. Select Next, and then, on the Import Settings page, specify the location of the BACPAC file.

    The source BACPAC file can be in local disk (Import from local disk), or in a container in an Azure Storage account (Import from Microsoft Azure). Note that the Import from Microsoft Azure option also requires that you specify a local directory for the temporary file. The temporary file will be created at the specified location and will remain there after the operation is complete.
     
    Import SettingsImport Settings

  4. Select Next, and then on the Database Settings page, specify the basic configuration settings for the new database:
    - New database name.
    - Edition: Basic, Standard or Premium for DTU-based purchasing model; General Purpose or Business Critical for vCore-based purchasing model.
    - Maximum database size (GB).
    - Service Objective: DTUs or Compute Hardware for the selected edition; for example, S1 for DTU Standard or GP_Gen5_8 for vCore General Purpose.

    Note
    Take special consideration to the settings for the new database, because it can have significant implications in the time it would take to import the data. A database with a higher Service Objective (for example, S3 for DTU-based purchasing model) will import faster than one with a lower Service Objective (for example, S1 for DTU-based purchasing model). Consult the article Compare vCore and DTU-based purchasing models of Azure SQL Database to understand what’s available in each service tier. Consider using the maximum Service Objective (for example, P15 for DTU-based purchasing model) during the migration process to maximize import speed. You can then scale down after the import is successful.

    Database SettingsDatabase Settings

  5. Select Next, and then on Summary page, confirm that the settings are correct.

    Import SummaryImport Summary

  6. Select Finish.
     
    When the process is complete, the Results page appears.

    Import ResultsImport Results
  7. Select Close, and then refresh and expand the Databases node.

    The new database should appear there.

 

Using the Data Migration Assistant (DMA)

The Data Migration Assistant (DMA) provides two core capabilities:

  • Analyses the source system and detects potential compatibility issues that can impact database functionality in your new version of SQL Server or Azure SQL Database.
  • Migrates schema and data between source and target databases.

 

Using the Data Migration Assistant (DMA)Using the Data Migration Assistant (DMA)

 

Perform an Assessment

The following step-by-step instructions guide through the process of performing an assessment for migrating from Amazon RDS for SQL Server to Azure SQL Database.

  1. Download Data Migration Assistant for free from the Microsoft Download Center., and then install it.
  2. Start DMA, select the New (+) icon, and then select the Assessment project type.
  3. Under Project name, specify a name for the assessment.
  4. Under Assessment type, select Database Engine.
  5. Under Source server type, select AWS RDS for SQL Server.
  6. Under Target server type, select Azure SQL Database, and then select Create.
  7. On the Options page, select Check database compatibility, and then select Next.

    Assessment optionsAssessment options

  8. In the Connect to a server pane, enter the server name and authentication credentials to connect to the source Amazon RDS for SQL Server instance, and then select Connect.

    Connect to serverConnect to server

  9. On the Add sources pane, select the databases to assess, and then select Add.

    Add sourcesAdd sources

    The Select sources page shows the list of selected databases.

    Start AssessmentStart Assessment

  10. Select Start Assessment.

    When the process completes, the Review results page appears, showing the results of the assessment.
     
  11. Verify that the assessment completed successfully (green ticks).

    If the assessment returns any errors due to compatibility issues, you will need to address them before attempting to migrate the database.

    Assessment resultsAssessment results

 

Create the target database

Unlike the BACPAC import, which creates a new database, DMA migrates schema and data to an existing database. Therefore, you must create a new database before running the migration. There are a variety of tools that can be used to create a new database, including the Azure Portal and PowerShell. A quick guide is available here:

 

Quickstart: Create a single database - Azure SQL Database

https://docs.microsoft.com/azure/azure-sql/database/single-database-create-quickstart?view=azuresql

 

Note
Take special consideration to the settings for the new database, because it can have significant implications in the time it would take to import the data. A database with a higher Service Objective (for example, S3 for DTU-based purchasing model) will import faster than one with a lower Service Objective (for example, S1 for DTU-based purchasing model). Consult the article Compare vCore and DTU-based purchasing models of Azure SQL Database to understand what’s available in each service tier. Consider using the maximum Service Objective (for example, P15 for DTU-based purchasing model) during the migration process to maximize import speed. You can then scale down after the import is successful.

 

Create the replacement admin login

  1. Open SQL Server Management Studio, connect to the server hosting the new Azure SQL Database created in the previous step, and run the following statement against the master database to create a new login instead of ‘admin’ (the sample code below uses ‘newadmin’):

    CREATE LOGIN [newadmin] WITH PASSWORD=N'S0meP@ssword!'
    GO

 

Migrate Schema

Once the target Azure SQL Database has been created and it has a new replacement admin login, open Data Migration Assistant to proceed with the migration of the schema from the source Amazon RDS for SQL Server to the target Azure SQL Database.

 

  1. Select the New (+) icon, and then select the Migration project type.
  2. Under Project name, specify a name for the migration.
  3. Under Source server type, select SQL Server.
  4. Under Target server type, select Azure SQL Database.
  5. Under Migration scope, select Schema only., and then select Create.

    New Migration project (Schema)New Migration project (Schema)

  6. On the Select source page, specify the server name and credentials to connect to the source Amazon RDS for SQL Server instance, and the select Connect.

    Connect to source serverConnect to source server

    The right pane shows a list of databases available on the source server.

  7. Select the database that you want to migrate (you can leave the Assess database before migration? option blank, if you completed the assessment previously), and then select Next.

    Select source databaseSelect source database

  8. On the Select target page, specify the server name and credentials to connect to the target Azure SQL Database, and then select Connect.

    Connect to target serverConnect to target server

    The right pane shows a list of databases available on the target server

  9. Select the destination database, and then select Next.

    Select target databaseSelect target database

  10. On the Select objects page, scroll down to the bottom of the list, deselect Users / admin, and then select Generate SQL script.

    Select objectsSelect objects

    The Script & deploy schema page appears, showing the DMA Schema Migration Deployment Script.

    Generated scriptGenerated script

  11. Select the Generated script text box, and the add the following lines to the top of the script:

    /****** Manually add admin database user and link it to new admin login ******/
    IF NOT EXISTS (SELECT * FROM sys.sysusers WHERE name = N'admin')
    CREATE USER [admin] FOR LOGIN [newadmin] WITH DEFAULT_SCHEMA=[dbo]
    GO

    The window should now look like this:

    Add the code to create the 'admin' database userAdd the code to create the 'admin' database user

  12. Select Deploy schema.
  13. When the process finishes, verify that all the statements were completed successfully:

    Deploy schema resultsDeploy schema results

 

Migrate Data

After the target Azure SQL Database has been created and the schema has been deployed, you can use Data Migration Assistant to proceed with the migration of the data from the source Amazon RDS for SQL Server to the target Azure SQL Database.

 

  1. Select the New (+) icon, and then select the Migration project type.
  2. Under Project name, specify a name for the migration.
  3. Under Source server type, select SQL Server.
  4. Under Target server type, select Azure SQL Database.
  5. Under Migration scope, select Data only, and then select Create.

    New Migration project (Data)New Migration project (Data)

  6. On the Select source page, specify the server name and credentials to connect to the source Amazon RDS for SQL Server instance, and then select Connect.

    Connect to source serverConnect to source server

    The right pane will show a list of databases available on the source server.

  7. Select the database that you want to migrate (you can leave the Assess database before migration? option blank, if you completed the assessment previously), and then select Connect.

    Select source databaseSelect source database

  8. Select Next.
  9. On the Select target page, specify the server name and credentials to connect to the target Azure SQL Database, and then select Connect.

    Connect to target serverConnect to target server

    The right pane will show a list of databases available on the target server.

  10. Select the destination database, and then select Next.

    Select destination databaseSelect destination database

    The Select tables page will show the list of tables with row counts.

  11. Select tables as appropriate, and then select Start data migration:

    Select tablesSelect tables

    The Migrate data page shows progress.

  12.  When the process is finished, verify that all tables were migrated successfully.

    Migration resultsMigration results

    The migration is now complete.

Conclusion

We have covered two mechanisms to migrate Amazon RDS for SQL Server databases to Azure SQL Database: using the Import/Export Service (BACPAC) and using the Data Migration Assistant (DMA). These tools are easy to use and work well for small-to-medium deployments. In part 2 we will cover the use of the Azure SQL migration extension for Azure Data Studio in combination with the Azure Database Migration Service, which provides more control over the migration and is better suited for migration at scale.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

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