Azure SQL DacPacTask takes hours at Update Database step

Question

I was handed over an Azure DevOps Build Pipeline to modify to work with an Azure SQL Managed Instance database. The current pipeline works perfectly with the Azure SQL Server database. (Note that both have exactly the same schema and data, Managed Instance database is basically a copy of the old database in Azure SQL).

The only change I made was updating the Server, db name (same as old), login and password (same as old). When I run the pipeline, the Azure SQL Database deployment Task reached Updating database (Start) step. And after more than an hour and finally gives an error saying We stopped hearing from agent Hosted Agent. Verify the agent machine is running and has a healthy network connection. No other errors were showing in the log.

Then I tried to run the same SqlPackage.exe command to publish dacpac generated locally (Using Command Prompt) and it still took hours to deploy. The same dacpac can be deployed in Azure SQL within seconds. My question is why dacpac publish (to upgrade) Managed Instance db is incredibly slow, whether I'm doing something wrong here and how to make it fast.

Below is a part of my model.sql after unpacking dacpac

CREATE TABLE [dbo].[__MigrationHistory] (
    [MigrationId]    NVARCHAR (150)  NOT NULL,
    [ContextKey]     NVARCHAR (300)  NOT NULL,
    [Model]          VARBINARY (MAX) NOT NULL,
    [ProductVersion] NVARCHAR (32)   NOT NULL,
    CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY CLUSTERED ([MigrationId] ASC, [ContextKey] ASC) ON [PRIMARY]
) ON [PRIMARY];

GO
CREATE TABLE [dbo].[__MigrationLog] (
    [migration_id]    UNIQUEIDENTIFIER NOT NULL,
    [script_checksum] NVARCHAR (64)    NOT NULL,
    [script_filename] NVARCHAR (255)   NOT NULL,
    [complete_dt]     DATETIME2 (7)    NOT NULL,
    [applied_by]      NVARCHAR (100)   NOT NULL,
    [deployed]        TINYINT          NOT NULL,
    [version]         VARCHAR (255)    NULL,
    [package_version] VARCHAR (255)    NULL,
    [release_version] VARCHAR (255)    NULL,
    [sequence_no]     INT              IDENTITY (1, 1) NOT NULL,
    CONSTRAINT [PK___MigrationLog] PRIMARY KEY CLUSTERED ([migration_id] ASC, [complete_dt] ASC, [script_checksum] ASC) ON [PRIMARY]
) ON [PRIMARY];

GO
CREATE TABLE [dbo].[__SchemaSnapshot] (
    [Snapshot]       VARBINARY (MAX) NULL,
    [LastUpdateDate] DATETIME2 (7)   NULL
) ON [PRIMARY];

GO

and this is the SqlPackage command I tried out,

/Action:Publish

/SourceFile:"C:\DWH_ISMCRMMirror.dacpac" /TargetServerName:"SQL MI Host,port" /TargetDatabaseName:"db Name"

/TargetUser:"UName"

/TargetPassword:"*******"

/p:ExcludeObjectTypes=Tables;Logins;Users;SecurityPolicies

/TargetTimeout:120

Answer 1

Hi Amila Ariyasena •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you are trying to modify to work with an Azure SQL Managed Instance database using Azure DevOps.

The issue might be due to timeout which could be handled by the ways as follows with respect to SqlPackage:

For issues related to timeouts, the following properties can be used to tune the connection between SqlPackage and the SQL instance:

• /p:CommandTimeout=: Specifies the command timeout in seconds when a query is executed. Default: 60
• /p:DatabaseLockTimeout=: Specifies the database lock timeout in seconds. -1 can be used to wait indefinitely, default: 60
• /p:LongRunningCommandTimeout=: Specifies the long running command timeout in seconds. The default value, 0, is used to wait indefinitely.

By default, SqlPackage sets the maximum server parallelism to 8. If you note low server resource consumption, increasing the value of the MaxParallelism parameter may improve performance.

Reference doc: https://learn.microsoft.com/en-us/sql/tools/sqlpackage/troubleshooting-issues-and-performance-with-sqlpackage?view=sql-server-ver16

Make sure that the Managed Instance is configured correctly for handling large database deployments. This includes setting the correct timeout values for idle connections, configuring the maximum number of client connections, and setting any necessary security options such as SSL/TLS encryption.

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.

Thanks.