Share via

Migrate Azure Managed Instance to SQL VM

Joris CALAMEL 20 Reputation points
2023-04-05T09:20:09.8266667+00:00

Hello, Do you have a solution to migrate an instance from an MI to a SQL VM in Azure? Backups / restores are not possible on version 2022 we have seen that there is a relationship between the two systems, but in this way it is still a limited functionality. We have tried a transactional replication / Snapshot but we always get the error: "String or binary data would be truncated." whatever the table, without any other information. Thanks for your help

Azure SQL Database

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ShaktiSingh-MSFT 14,406 Reputation points Microsoft Employee
    2023-04-05T10:52:28.9+00:00

    Hi
    Joris CALAMEL     •,

    Welcome to Microsoft Q&A forum and thanks for using Azure Services. As I understand, you are trying to Migrate Azure SQL Managed Instance to SQL VM and getting error: String or binary data would be truncated. There are two main SQL technologies that can be used today for moving data from Managed Instance to SQL Server. Those are:

    Export/Import:

    • Using export/import is very simple, either through SSMS or SqlPackage. It’s best to be used for offline migration of small and medium databases. This technology has some limitations: It does not handle databases that reference external objects (schemas with three or four-part names).
    • Its performance degrades when working with large databases or databases with large number of objects (hundreds of GBs or tens of thousands of objects in a database).
    • Produced BACPAC is not transactionally consistent.
    • If you'd like to read more about this option, see how to migrate database from Managed Instance to SQL Server with export/import.

    Transactional Replication:

    • This is a more robust way to migrate databases from Managed Instance to SQL Server. It’s best to be used for online or offline migration of large and complex databases. Some outstanding limitations that apply to transactional replication are: . Tables must have primary keys.
    • Setup is not simple.

    To find out more about this, see how to migrate database from Managed Instance to SQL Server with transactional replication. Regarding Error of "String or binary data would be truncated.", it occurs when a value is trying to be inserted or updated in a table and it is larger than the maximum field size.

    Example it works when: User's image

    And when value exceeds gives error as shown below: User's image

    Hope this helps. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.

    Thank you.