0

(scroll to the end of the post to see latest development)

There's a necessity for migration of 3 databases from Azure MI to Azure SQL VM (OS 2022, SQL 2019 standard). Each database has 500GB+. Each database also contains partitioning schemes, functions, multiple filegroups, user data types and external assemblies.

The following options have been ruled out due to their limitations:

  • BACPAC files: The databases have external dependencies (i.e. one database pointing to object in other database)
  • Transactional replication: Tens of tables in every database don't have primary keys.

... now I am running "generate scripts" for all the DBs but I doubt that'll provide the complete script to have 1:1 database with all the features like partitioning schemes, functions, multiple filegroups, user data types and external assemblies.

Hint in which direction to look to transfer the complete DB would be appreciated.

EDIT:

Besides BACPAC and TransactionalReplication, I have tried the following routes:

  1. DACPAC extraction through visual studio as described here

    When the database exists, deploy data-tier app wizard in SSMS complains about its existence. When the DB does not exist, wizard complains about not being able to create files based on the original layout in the source database.

  2. Generate Schema+Data scripts

    I tried to generate .sql scripts through SSMS "generate scripts". On the source managed instance, I get multiple timeouts. When generating the scripts on SQL2022rc0, wizard errors out on "the source DB has cyclic dependencies" (foreign keys relationships between tables form a loop).

  3. Data import/export wizard

    I was able to generate schema-only sql scripts on SQL2022rc0 to create the DB on target SQL2019. DB got created there. However, when transferring data through said wizard, I got "failed to open rowset for , check that table exists". It does, schema-only .sql script created it in the target DB.

  4. Transfer SQL server object task in SSIS

  5. The snapshot replication seems promising, however, introduces huge blocking chains when the DB is in production load of the application. Will be tested when the DB is idle.

    The same error (cyclic dependencies found) occured in SSIS as when trying to generate .sql scripts.

At the current stage, for me, the takeaway is, that combination of application (external references, cyclic dependencies) and MI features make it very difficult to leave the MI in favor of SQL VM.

Microsoft is about to get involved.

EDIT2: We did not get much further w/ Microsoft.

However, I seem to have found a way with the following steps (there's still one problem to be solved):

  1. Generate script for every table to export it to BCP
  2. Generate script for every table to import it from BCP to destination server
  3. Through generate scripts, create the script for every DB to be migrated -> and this is where I have hit a wall.

For BCP export/import, I got inspired by this article.

The generate scripts wizard seems to have some sort of timeout somewhere around 10 minutes for particular queries which does not let the wizard finish in databases with complex schema (The schema-only .sql script should have around 60MB), it rather times out. The query that is fired from generate scripts wizard takes 13min 40sec to finish is following (object id list shortened for brevity):

SELECT Cast(Serverproperty(N'Servername') AS SYSNAME) AS [Server_Name],
       Db_name() AS [Database_Name],
       Schema_name(tbl.schema_id) AS [Table_Schema],
       tbl.NAME AS [Table_Name],
       i.NAME AS [Index_Name],
       p.NAME AS [Name],
       p.value AS [Value]
FROM sys.tables AS tbl
    INNER JOIN sys.indexes AS i
        ON (
               i.index_id > 0
               AND i.is_hypothetical = 0
           )
           AND (i.object_id = tbl.object_id)
    LEFT OUTER JOIN sys.key_constraints AS k
        ON k.parent_object_id = i.object_id
           AND k.unique_index_id = i.index_id
    INNER JOIN sys.extended_properties AS p
        ON p.major_id = CASE (i.is_primary_key + 2 * i.is_unique_constraint)
                            WHEN 0 THEN
                                i.object_id
                            ELSE
                                k.object_id
                        END
           AND p.minor_id = CASE (i.is_primary_key + 2 * i.is_unique_constraint)
                                WHEN 0 THEN
                                    Cast(i.index_id AS INT)
                                ELSE
                                    0
                            END
           AND p.class = CASE (i.is_primary_key + 2 * i.is_unique_constraint)
                             WHEN 0 THEN
                                 7
                             ELSE
                                 1
                         END
WHERE (tbl.object_id IN ( 2098106515, 94623380, /*...*/
                          1958831332, 502826145, 614826544, 646826658, 38824492, 150824891, 182825005
                        )
      )
ORDER BY [database_name] ASC,
         [table_schema] ASC,
         [table_name] ASC,
         [index_name] ASC,
         [name] ASC

The error that generate scripts wizard returns is "operation timed out".

Question related to point #3 above: Does anybody know how to increase any timeout on Generate Scripts wizard?

2
  • 3
    FWIW, if you can hold out til SQL Server 2022, Full backup restores from an Azure Managed Instance will likely be available.
    – J.D.
    Aug 24, 2022 at 12:38
  • 1
    Thanks J.D., Tried on SQL 2022 rc 0 and we were able to restore the Azure MI backups there. I'll edit my post to describe the tested paths and why did each path fail.
    – Sranda
    Aug 26, 2022 at 5:51

1 Answer 1

1

If this is a one off migration, where you won't need to keep the SQL Server databases in sync with the MI databases, you could potentially use snapshot replication.

Unlike transactional replication, snapshot replication doesn't require primary keys in articles that are replicated.

There might be some things that don't replicate, like assemblies, but these should be fairly trivial to manually deploy to the SQL Server.

1
  • Hi, thanks for pointing out yet another way how to get the migration done. We'll need to wait for idle period of the source database to test it properly. Unfortunately, while the DB runs under production load, snapshot replication causes huge blocking chains.
    – Sranda
    Aug 30, 2022 at 12:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.