mmckechney / sqlbuildmanager Goto Github PK

Experimenting with the --targetdacpac batch run commandline switch, I'm able to confirm that an .sbm file is getting properly generated on our client, and pushed to the batch nodes (alongside the platinum dacpac file and .cfg targets file) .

However, the batch job exits early with exit code -728 - A Target Dacpac file was specified but could not be located at 'C:\scrap2\npr\MyTargetDacPac.dacpac'

The "C:\scrap2..." path was the value supplied to the --targetdacpac file, so I wouldn't expect that exact path to be mirrored on the batch node. However, I'm wondering if this dacpac file is even needed on the batch nodes at all, since the .sbm file was already created from it?

I hacked in this line to BatchManager.CompileCommandLines(), which otherwise allowed it to process successfully (against the same database that the targetdacpac was extracted from, at least):

threadCmdLine.DacPacArgs.TargetDacpac = null;

A logging note -- though the batch job / task failed, the -728 error only appeared in the console log and stdout.txt files -- the Errors.log and stderr.txt were empty.

In the BatchArgs section of our settingsfile, I've togged DeleteBatchPool as follows:

However, the batch pool remains after the run is finished.

I've successfully worked around this by appending --deletebatchpool to the smb run command directly, however there's no mention of the pool deletion in the log file (with logging set to trace and PollBatchPoolStatus enabled).

We're attempting to add 3 tables to our schema:

CREATE TABLE [dbo].[CustomLayout] (
    [CustomLayoutID] INT            IDENTITY (1, 1) NOT NULL,
    [Name]           NVARCHAR (100) CONSTRAINT [DF_CustomLayout_Name] DEFAULT ('') NOT NULL,
    [OwnerUserID]    INT            CONSTRAINT [DF_CustomLayout_UserID] DEFAULT ((0)) NOT NULL,
    [IsShared]       BIT            CONSTRAINT [DF_CustomLayout_IsShared] DEFAULT ((0)) NOT NULL,
    PRIMARY KEY CLUSTERED ([CustomLayoutID] ASC)
);

CREATE TABLE [dbo].[CustomLayoutColumn] (
    [CustomLayoutColumnID] INT IDENTITY (1, 1) NOT NULL,
    [CustomLayoutID]       INT NOT NULL,
    [EntityID]             INT CONSTRAINT [DF_CustomLayoutColumn_EntityID] DEFAULT ((0)) NOT NULL,
    [TypeID]               INT CONSTRAINT [DF_CustomLayoutColumn_TypeID] DEFAULT ((0)) NOT NULL,
    [OrdinalPosition]      INT CONSTRAINT [DF_CustomLayoutColumn_OrdinalPosition] DEFAULT ((0)) NOT NULL,
    PRIMARY KEY CLUSTERED ([CustomLayoutColumnID] ASC),
    CONSTRAINT [FK_CustomLayoutColumn_ToCustomLayout] FOREIGN KEY ([CustomLayoutID]) REFERENCES [dbo].[CustomLayout] ([CustomLayoutID])
);

CREATE TABLE [dbo].[CustomLayoutUser] (
    [UserID]         INT      NOT NULL,
    [CustomLayoutID] INT      NOT NULL,
    [IsDefault]      BIT      CONSTRAINT [DF_CustomLayoutUser_IsDefault] DEFAULT ((0)) NOT NULL,
    [LastUsed]       DATETIME CONSTRAINT [DF_CustomLayoutUser_LastUsed] DEFAULT (((1)/(1))/(1900)) NOT NULL,
    CONSTRAINT [PK_CustomLayoutUser] PRIMARY KEY CLUSTERED ([UserID] ASC, [CustomLayoutID] ASC),
    CONSTRAINT [FK_CustomLayoutUser_ToCustomLayout] FOREIGN KEY ([CustomLayoutID]) REFERENCES [dbo].[CustomLayout] ([CustomLayoutID]),
    CONSTRAINT [FK_CustomLayoutUser_ToUserInfo] FOREIGN KEY ([UserID]) REFERENCES [dbo].[UserInfo] ([UserID])
);

None of the target databases have any of these tables present.
However, when the diff script is generated (at least via sbm batch run), it is only generating a single script with the following ALTER statements -- none of the expected CREATEs precede this.

ALTER TABLE [dbo].[CustomLayoutColumn] WITH CHECK CHECK CONSTRAINT [FK_CustomLayoutColumn_ToCustomLayout];

ALTER TABLE [dbo].[CustomLayoutUser] WITH CHECK CHECK CONSTRAINT [FK_CustomLayoutUser_ToUserInfo];

ALTER TABLE [dbo].[CustomLayoutUser] WITH CHECK CHECK CONSTRAINT [FK_CustomLayoutUser_ToCustomLayout];

This yields the following error, resulting in failed batch jobs.

Cannot find the object "dbo.CustomLayoutColumn" because it does not exist or you do not have permissions.

These are the first foreign keys added to our schema, so we're assuming it's a problem with them.

Running the full dacpacs generated from our SQL project however does create the tables and constraints as expected, so we don't believe there is a syntax issue.

We're testing a managed identity implementation, and have verified that ManagedIdentity is set as the AuthenticationType in our settingsfile, along with the expected values in the IdentityArgs section.

When the nodes attempt to run the batch command though, we see this in the Errors.log:

The --username and --password arguments are required when authentication type is set to Password or AzurePassword.;Returning error code: 1

Upon reviewing the command line on the batch job task, no --authtype arg is present, so it appears to presume SqlAuthentication, and we've purposefully neither of those values in our key vault.

We worked around in our test branch by shoehorning in the following into BatchManager.CompileCommanLines():

                if (cmdLine.AuthenticationArgs.AuthenticationType == AuthenticationType.ManagedIdentity)
                {
                    sb.Append(" --authtype ManagedIdentity ");
                }

We'd a situation in our batch account where we attempted to provision more nodes than our 500 vCPU quota allowed:

However, when prestage was waiting for all of them to idle, it incorrectly reported that all 350 nodes were ready, when only 349 of them were.

The above example was created for illustrative purposes, where it bit us was a separate pool of 1 node that couldn't be created, but passed its prestage logic and moved onto enqueue/run, whereupon the batch job eventually timed out: