Deploying SSDT Scripts

Richie Lee has concerns with database deployments:

At any rate, the script is generated and maybe reviewed….. so then what? In SSDT there is no way to create and deploy script in one step; they are two distinct steps. And even if they were one step, this would still not resolve the issue that troubles me. So what is this issue?

The issue is that by creating a script, and then running the deploy, you cannot be sure that the database is in the exact same state that it was when the initial script was generated. If you don’t already know, SSDT runs a deploy entirely in memory, so as mentioned there is no script created. You have to explicitly create the script as part of the process. Or, if you have already created one, you have to re-create the script.

I’m on the fence here.  In simpler environments, I think Richie has a good point.  But in a complex environment, I wouldn’t even use auto-generated deployment scripts; when you’re changing hundreds of database objects (including adding and modifying columns, backfilling data, modifying indexes, etc.), that automated deployment script is almost guaranteed to fail.  And if it does fail, it could leave you in a state of irreparable harm.

Related Posts

An Incompatible SQL Server Version Was Detected

Hamish Watson troubleshoots an issue with Visual Studio 2017 connecting to SQL Server 2017: This blog post details the error you may get when using Visual Studio 2017 and you get errors that you cannot connect to SQL Server 2017 using Test Explorer or SQL Server Object Explorer. TL;DR – upgrade Visual Studio from base […]

Read More

Mutli-Branch Pipelines In Jenkins

Chris Adkin continues his SQL Server continuous integration series: Whatever you elect to do there will always be a master branch, where you go from here depends on whether you favor branching or feature toggles. Wikipedia provides a nice definition of what a feature toggle is, thus: A feature toggle[1] (also feature switch, feature flag, feature flipper, conditional feature, etc.) is […]

Read More

Categories

September 2016
MTWTFSS
« Aug Oct »
 1234
567891011
12131415161718
19202122232425
2627282930