sqlpackage migration script always wants to rebuild-CodePudding

Can anyone explain when using sqlpackage.exe to produce a migration script always wants to drop the table and recreate when adding a field.

My databases are hosted on Azure PaaS service.

I have a database with a table created with the following sql:

CREATE TABLE [dbo].[test] (
    [Id]             INT            IDENTITY (1, 1) NOT NULL,
    [Field 1]        INT            NULL,
    [Timestamp]      ROWVERSION     NOT NULL,
    [InsertedTime]   DATETIME       DEFAULT (getdate()) NOT NULL,
    [UpdatedTime]    DATETIME       NULL,
    [LastUpdatedBy]  NVARCHAR (100) NULL,
    [AssignedTo]     NVARCHAR (90)  NULL,
    [ActionRequired] NVARCHAR (MAX) NULL,
    [AuditLog]       XML            NULL,
    PRIMARY KEY NONCLUSTERED ([Id] ASC)
);

I then create a new temporary database and run the following sql (adding field 2)

CREATE TABLE [dbo].[test] (
    [Id]             INT            IDENTITY (1, 1) NOT NULL,
    [Field 1]        INT            NULL,
    [Field 2]        INT            NULL,
    [Timestamp]      ROWVERSION     NOT NULL,
    [InsertedTime]   DATETIME       DEFAULT (getdate()) NOT NULL,
    [UpdatedTime]    DATETIME       NULL,
    [LastUpdatedBy]  NVARCHAR (100) NULL,
    [AssignedTo]     NVARCHAR (90)  NULL,
    [ActionRequired] NVARCHAR (MAX) NULL,
    [AuditLog]       XML            NULL,
    PRIMARY KEY NONCLUSTERED ([Id] ASC)
);

Then run the following command to extract the dacpac:

sqlpackage.exe /SourceConnectionString:"<tempdb>" /a:Extract /tf:local.dacpac

Then run this to get a migration report:

sqlpackage.exe /TargetConnectionString:"<orginaldb>" /a:DeployReport /sourcefile:local.dacpac /outputpath:report.xml

But it always wants to do this:

<?xml version="1.0" encoding="utf-8"?>
<DeploymentReport xmlns="http://schemas.microsoft.com/sqlserver/dac/DeployReport/2012/02">
    <Alerts>
        <Alert Name="DataMotion">
            <Issue Value="[dbo].[test]" />
        </Alert>
    </Alerts>
    <Operations>
        <Operation Name="TableRebuild">
            <Item Value="[dbo].[test]" Type="SqlTable" />
        </Operation>
    </Operations>
</DeploymentReport>

CodePudding user response：

The behavior of sqlpackage.exe can be influenced very detailed by many parameters, here are only a few shown:

A detailed list of all parameters you can find here:
https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-deploy-drift-report?view=sql-server-ver15

It takes likely some time and thoughts to adjust it exactly to your needs.

CodePudding user response：

This probably happens because the new column is not the last column in the table (it is before other existing columns). In such a case also the SSMS Table Designer needs to recreate the table (to preserve the specified order of columns).