Share via

Database migration on Azure VM/Azure SQL Database

Zahid 21 Reputation points
2020-09-22T01:04:58.633+00:00

Hi Expert,
Our customers repository resides on SQL Server 2016 (2016 SQL Management Studio). However the Customer’s database to be migrated is on 2017 version. When we researched we realized that we cannot just install the SQL Server 2017 Standard (Licensed) or SQL Server 2017 Express (Free) on an existing VM because the SQL Server will consume a lot of memory on the Server/PC it runs. Also, per our understanding, we think we cannot use SQL Server Express (Free) since the Customer database can expand to more than 10 GB when restored and we need SQL Server Standard (License).

Hence the following are the two options that we can think of:

We can set up a VM dedicated to the Customer running SQL Server 2017 for the data migration on Azure. Since the data migration activity is part of the complete life of the project until Go-Live and also the source database is kept alive for at least 6 months after Go-Live to make sure the data migration is smooth, we are thinking that this must be kept at least for a year. This is going to cost us additional fee for the Azure resources.

  1. An alternate way of restoring the Customer database is to request the Customer’s IT team to export their database as “Data-tier Application” ( Document Attached for the Steps) and Optimum may then restore it as “Azure SQL Database”. We are not sure if the Customer IT team is comfortable exporting their database as “Data-tier Application”, but we can try? Though this will also cost us additional fee, it can be comparatively lower than Option 1. We understand that under this option we may have to request for database backups multiple times.
    We already initiated Option 2, and we are trying to understand the errors they received in this process, see below:

CLIENT ERRORS RETURNING AFTER FIRST ATTEMPT:

“…When I export, I get the following errors…”

TITLE: Microsoft SQL Server Management Studio

One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71627: The element User: [XXX] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71627: The element Login: [XXX] has property IsMappedToWindowsLogin set to a value that is not supported in Microsoft Azure SQL Database v12.
(Microsoft.SqlServer.Dac)

BUTTONS:

OK

TITLE: Microsoft SQL Server Management Studio

One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71627: The element User: [xxx] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71627: The element Login: [xxx] has property IsMappedToWindowsLogin set to a value that is not supported in Microsoft Azure SQL Database v12.
(Microsoft.SqlServer.Dac)

BUTTONS:

OK

TITLE: Microsoft SQL Server Management Studio

One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71627: The element User: [XXX] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71627: The element Login: [XXX] has property IsMappedToWindowsLogin set to a value that is not supported in Microsoft Azure SQL Database v12.
(Microsoft.SqlServer.Dac)

BUTTONS:

OK

My question is can i use DMA tool to migrate the database or generate script including schema and data instead of export .bacpack file?

Thank You,

Zahid

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,362 questions
0 comments
Accepted answer
  1. Cris Zhan-MSFT 6,616 Reputation points
    2020-09-22T04:03:30.55+00:00

    Hi @Zahid ,

    Migrate the SQL Server from higher version to lower version, you can generate the BACPAC file and then import to Azure. The reason for your error may be that you are not using the latest version of SSMS.

    Also consider to use script(Generate Script Wizard)

    Please refer to following articles:

    https://www.mssqltips.com/sqlservertip/2810/how-to-migrate-a-sql-server-database-to-a-lower-version/

    https://www.sqlshack.com/migrate-an-on-premises-sql-server-database-to-the-azure-sql-database/

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Cris Zhan-MSFT 6,616 Reputation points
    2020-09-22T02:44:54.08+00:00

    Hi @Zahid ,

    The Data Migration Assistant(DMA) tool can be used to assess and migrate the databases from on-premises SQL Server to Azure SQL Database or SQL server on Azure Virtual Machines.

    More details pelase read the doc : Overview of Data Migration Assistant

    Download from here: https://www.microsoft.com/en-us/download/details.aspx?id=53595

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  2. Zahid 21 Reputation points
    2020-09-22T02:50:52.927+00:00

    Thanks for replying my answer. There is another tool SQL Database Migration Wizard. Can i use this?
    Basically I tried to use .bacpack it has failed due the following error.

    One or more unsupported elements were found in the schema used as part of a data package.
    Error SQL71627: The element User: [XXX] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
    Error SQL71627: The element Login: [XXX] has property IsMappedToWindowsLogin set to a value that is not supported in Microsoft Azure SQL Database v12.
    (Microsoft.SqlServer.Dac)

    Which one is better to use DMA or SQL Database Migration Wizard or export data-tier application .bacpack? My db size is almost 9 GB.

    Thanks again

  3. Zahid 21 Reputation points
    2020-09-22T03:11:36.743+00:00

    Lastly i am asking would it be any issue if i migrate from 2017 to 2016. Higher to lower version.

    Source: SQL Server 2017 on-premises
    Target: SQL Server 2016 Azure

  4. Zahid 21 Reputation points
    2020-09-22T03:29:38.23+00:00

    Then how do i migrate from higher version to lower version? Because my target SQL Server version is 2016.

    0 comments