0

In a SSDT project there is a partition function defined in a file as follows.

CREATE PARTITION FUNCTION [pfSourceID](varchar(50)) AS RANGE RIGHT FOR VALUES (
'', 
'COKE',
'DRPEPPER',
'MOUNTAINDEW',
'PEPSI'
)
GO

There is a scheme to go along defined in another file:

CREATE PARTITION SCHEME psSourceID 
AS PARTITION pfSourceID ALL TO ([PRIMARY]);

Tables are defined in files using the following construct:

CREATE TABLE [dbo].[MyTable] (
    [MyTableID] INT NOT NULL,
    [SourceID] VARCHAR(50) NOT NULL
    CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED ([MyTableID] ASC, [SourceID] ASC) ON psSourceID(SourceID),
    CONSTRAINT [CIX_MyTableByPartition] UNIQUE CLUSTERED ([SourceID] ASC, [MyTableID] ASC) ON psSourceID(SourceID)
) ON psSourceID(SourceID);

All is well and this works out of the gate when no partitions have been defined on the target.

The problem occurs once the target and source partition functions don't match. (there are partitions at the target that do not match the SSDT project definition such as adding a new partition in ssdt). When the partition functions are un-matched, the deployment pipeline just spins and inside of Visual Studio, the "Generate Preview Script" also goes into a spin.

Maybe there is a need to determine data motion, however, all sql traces indicate very little SQL activity by the account running the deployment. While monitoring the deployment, there were a handful of very quick queries by the account running the publish and "DxSomething" app, then silence until the client process was halted non-gracefully and manually after ~2 hours.

There are two ways to get around the issue.

  1. Make sure the PARTITION FUNTION in the SSDT project matches exactly what is at the target.
  2. (and/or) Check "Ignore partition schemes."

From what was researched, when all data is aligned to an existing partition, using a SPLIT to add a new "BRAND" on the scheme should be relatively fast. The hope was to control this via SSDT, however, if 1 or 2 above are utilized then it would not make sense to use the SSDT :/

UPDATE:

Possible solution would be to split in a "PostScript"

IF NOT EXISTS (select * from sys.partition_range_values where value='NEWDRINK') 
BEGIN
   ALTER PARTITION SCHEME psSourceID NEXT USED [PRIMARY];
   ALTER PARTITION FUNCTION pfSourceID() SPLIT RANGE ('NEWDRINK');
END

Then during the next release, the function defined in SSDT would be shored up with the latest. This will be tried tomorrow but still seems backwards or off a bit.

1 Answer 1

0

This was solved by the following steps:

  1. Align the PARTITON FUNCTION checked in to source control with the target databases current state. (If moving properly up the deployment chain, this should not become an issue.)

  2. When a new client comes on board and before loading data, create a release with a PostScript command to SPLIT for the new client and deploy.

  3. Update the PARTITION FUNCTION after deployment to again match the current state.

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.