Post Deploy with Test Static Data

chad · October 9, 2019 8:53PM

I have a table with static data and a script that inserts a few more records for non-prod servers. Those extra records are not part of the static data in source control.

I was thinking that these steps would happen during each deployment:
1. SQL Source Control syncs data and deletes the extra test records.
2. Post deploy script re-inserts the test records again.

But instead I am getting a PK violation because the post deploy script is inserting an existing test record. This is happening because the post deploy script is running before the data syncing.

Yes, this is not fully "idempotent" script, but it would be fine if it were only run once per deployment and only after all syncing tasks have finished. Should I assume that post deploy scripts are run multiple times for each deployment or am I missing something?

Sergio R · October 15, 2019 7:32PM

Hi Chad,

In SQL Source Control this will not work because the static data will not be reset: the software sees the TESTROW as something to commit to the repository so it won't get the static data again from the repository.
The solution, in this case, is to write your Post-Deployment with a guard clause that only inserts the rows if they do not exist:

<div>IF(@@SERVERNAME = 'TESTSERVER')
</div><div>BEGIN
<span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">IF NOT EXISTS (SELECT * FROM [dbo].[MyTable]&nbsp;
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">WHERE [MyField1] = 'TESTROW')
</span><span style="background-color: transparent; color: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif; font-size: inherit;">BEGIN
</span><span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">INSERT INTO [dbo].[MyTable] ([MyField1]) VALUES ('TESTROW')</span></div><div>
END
<span style="background-color: transparent; color: inherit; font-size: inherit; font-family: Roboto, "Helvetica Neue", Arial, sans-serif;">END</span></div>

Please note that if these are not development databases then the best practice is to use a deployment tool (SQL Compare or SQL Change Automation Powershell) rather than SQL Source Control (in which case the advice I provided in my first reply would apply).

Sergio R · October 11, 2019 9:00AM

Hi Chad,

Post-deployment scripts are run after static data is deployed and only once per deployment.

What you are trying to do should work, though it would probably be best not to use static data on that table and use SQL Data Compare to sync the data. If you use SQL Change Automation Powershell for the deployment you going to get Post-Deployment Schema check failures with this approach.

What tool are you using for the deployment?

chad · October 11, 2019 8:12PM

I am using SSMS with the integrated SQL Source Control. The error happens when developers are syncing their test databases from source.

The error is happening during the "Applying Schema changes to database" step. This seems to be happening before the data is synced. Is that the case or am I missing something?

Image: https://us.v-cdn.net/6029854/uploads/editor/pb/rjhn8yp0819d.png

If you want, here is the code to create that error:

<div>CREATE TABLE dbo.MyTable (
    MyField1 varchar(10) NOT NULL PRIMARY KEY</div><div>)</div>

Static Data:

<div>INSERT dbo.MyTable VALUES&nbsp;</div><div>    ('ROW1'),&nbsp;</div><div>    ('ROW2');</div>

Post-Deployment Script:

IF(@@SERVERNAME = 'TESTSERVER')
BEGIN
    INSERT INTO [dbo].[MyTable] ([MyField1]) VALUES ('TESTROW')
END

Running the first time is successful. Running the second time results in a primary key violation.

Post Deploy with Test Static Data

Best Answer

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk