DACPAC and SQL Sequence

Question

I have a Visual Studio database project (DACPAC) which includes a number of SQL Sequences. However when I deploy the DACPAC it always resets the sequence value to the default value included in the Create script (in this case 1). e.g.

CREATE SEQUENCE [dbo].[MySequence]
AS INT
START WITH 1
INCREMENT BY 1;

Can anyone suggest a way of either instructing the DACPAC to ignore the sequence start value, or some way of getting the DACPAC to restore the correct value as a post deployment step perhaps?

Thanks in advance

Answer 1

This is a known problem with sequences when using the SSDT tools. There are a couple of solutions.

1. Ignore sequence objects when publishing.
2. Use a custom deployment filter to ignore the starting value.
3. Use sp_sequence_get_range instead of RESTART WITH to increment the counter after deploying to live.

1. Ignore sequence objects when publishing

This is the simplest option but the most awkward as it means you have to manually deploy sequences. Add the following to your publish profile

<ExcludeSequences>True</ExcludeSequences>

Or, from the command line

/p:ExcludeObjectType=Sequences

---

2. Use a custom deployment filter

First download AgileSqlClub's deployment filter. Then add the following to your deployment profile:

<AdditionalDeploymentContributors>AgileSqlClub.DeploymentFilterContributor</AdditionalDeploymentContributors>
<AdditionalDeploymentContributorArguments>SqlPackageFilter=IgnoreName(Order_No_Seq)</AdditionalDeploymentContributorArguments>

Or, from the command line:

/p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor
/p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreName(Order_No_Seq)"

---

3. Use sp_sequence_get_range

For this instead of using RESTART WITH on the production server to change the start value, use:

DECLARE @range_first_value SQL_VARIANT;
EXEC sp_sequence_get_range
    @sequence_name = 'MySequence',
    @range_size = 1000,
    @range_first_value = @range_first_value OUTPUT;

This way the start value will always match the expected value from the deployment script.

---

Resources

• Connect issue - this link is dead
• Forum post discussing the issue
• MSDN for sp_sequence_get_range
• User Voice/Azure Feedback issue - replaces Connect issue - marked as Planned 2018-03-16

Answer 2

Somewhat belated but I too have had this issue. Causes a lot of headaches.

I've raised a ticket with Microsoft: https://developercommunity.visualstudio.com/content/problem/732407/dacpac-resets-sequences.html

Our current workaround is to use pre and post deployment scripts to capture all the sequence values before the dacpac is applied and to then reset them back afterwards.

This is far from ideal.