SSDT And Pre-Model Scripts Part 2: Execute Pre-Model Scripts Using Octopus Deploy


Part one of this post concerned the notion of SSDT and Pre-Model Scripts. This post relates to an actual implementation of executing a SQL script using Octopus Deploy. My pull request on Github was approved, so my step template can be exported from the Octopus Library.

The only part that I feel requires some explanation is the SQL Scripts and the option to select a previous step. If you’ve read part one of this post you’ll know that you keep the pre-model scripts in a folder in the database solution. These are not compiled (ie not in build) but are included as part of the output and packaged up in the NuGet package. When the NuGet package is unpacked, it’s then a case of locating the scripts and executing them.

First, the order. Extract the NuGet package that includes the dacpac and the post-deploy scripts and then add the execute SQL step:


In the step template for execute SQL, there are Octopus parameters. These we define the step that the scripts were unpackaged, and the final path to the scripts themselves:

stepoptionsAnd these are the values of the parameters:


variablesSo now when we execute the step, we can see which scripts were executed:


The Alternative to Pre-Model Scripts


Two of my most recent posts have concerned pre-model scripts; those scripts that need to be run before the dacpac is compared against the production database. These are sometimes necessary. And this necessity is usually because SSDT may not produce a script that is optimal. One such example is an index creation: no index is created “ONLINE”, and this can be a problem if the database being deployed is online during this operation. And can be even worse if the table is particularly large.

In respect to the SSDT team, I can see why this is the case: some versions of SQL have the online index feature, some don’t. So one solution may be to write a pre-model script that will create an index with the online operation included. And while there’s nothing wrong with this, there is an alternative: deployment contributors. Continue reading “The Alternative to Pre-Model Scripts”

SSDT and Pre-Model Scripts


Earlier this week I posted about the need to create the database as a pre-model tasks: that is creating a database before we run sqlpackage/dacfx against a database/server so that the script itself does not create the database.

Going a step further than this, pre-deploy scripts in SSDT scripts may be executed before the main diff script is executed, but not before the diff script is generated. This is an important fact to understand. If you want scripts to be executed before the diff is generated, then you need to execute pre-model scripts.

How you go about this is up to you, but there needs to be a few rules that must be followed:

Idempotent: big word, and is used in maths. But don’t let that put you off. What it means in this context is that a script can be re-run and the same result happens. So in the context of altering a primary key, the pseudo-code would look something like this:

if database exists then
if table exists then
if primary key name eq “oldname” then
drop primary key then
add primary key with “newname”

and then you add primary key “newname” to the solution. That way the drop and add part of the script will only ever be run once, or again against an older version of the database that has the old primary key.The “newname” of the primary key guarantees that this will be the case.

Stored in the Solution: within database solutions you can add scripts that are part of the solution but are not executed:

checksIf you copy the file to the output, what you’ll be able to do is add the files required to your nuspec file and get them deployed as part of the NuGet package. Neat!


So if you have the scripts in the solution and re-runnable then you can be confident that they will run against older versions of the database. It keeps a level of audit and history of the database.

Now, how to run the scripts. As with the create database script, SMO is probably the easiest way to do this. You could use SQLCMD, or Invoke-SqlCmd, but it’s entirely up to you.

Now if you want to use SMO, and you are deploying using Octopus Deploy, then you’re in (some) luck: there are scripts available in the Octopus Library to do something like this, but you may will have to alter them to execute a folder full of sql scripts. Fortunately, I already have completed this, and I will add my script to the GitHub solution to be available on the Library. When this is available, assuming it meets the quality control, I’ll blog about how to use it.

edit: it is now available on the Octopus Library. There is also a part two on how to use the step template.

Dude, Where’s My Gold Code?

Let me tell you about a conversation I had yesterday with some developers in a team. They needed help with their database CI/CD process, and so I wanted to see the database solutions they were using.

Dev1: We don’t have solutions at the moment.

Me: OK, how come?

Dev1: Because we are making so many changes right now, there seemed to be little point in creating solutions, so we’re just using scripts.

[At this point, I bite my tongue and choose not to reply with ‘If you’re continuously making changes, wouldn’t it make sense to continuously integrate and  continuously deploy the changes. I mean, you’d hardly be taxing the definition of the word “continuously…” now are you…’]

Me: So how are you making changes?

Dev1: Oh well, we’re just using scripts to deploy the changes now.

Me: And where are the scripts?

Dev1 They’re in the wiki.

Dev2: No they’re not, because we put anything in the wiki that is changing regularly.

Dev1. Oh. So where are they?

[Dev2 stares blankly].

Dev1: So why do you want solutions?

Me: To help you build a release pipeline.

Dev2: But we’re making lots of changes regularly.

[Again, me, keeping cool in spite of the blindingly obvious contradiction presented to me.]

Me: But let’s get something built to help you make those changes regularly.

Dev1: OK, I’ll get something together by the end of the week. In the meantime we’re too busy making changes to get this together…

Sound familiar? I’m sure many of you have been in a situation like this before. It’s not the first time I’ve had a conversation like this either.

Now I can go on about how the business doesn’t buy into maintenance, or Continuous Integration or Continuous Delivery. Or I could talk about how CI/CD needs to be pushed by the devs and that associated tasks require items in the sprint to make sure that they are completed and audited. And I can talk about how the “build once, deploy many” process reduces the number of bugs and speeds up the deployment process. And you know, it is great that when wearing my DBA hat I have had to write wacky scripts  because dev teams haven’t been deploying to environments in a controlled manner, but I’d really rather be focusing on infrastructure. All these points are well documented.

But these points are consequences of implementing (or not) Continuous Delivery and Continuous Deployment. The most fundamental point of adopting CI/CD is getting your code into some sort of source control. It all starts with getting the code into source control, writing pre and post deployment scripts that are re-runnable, developers checking in changes, building the code, and taking the resulting artefact (in SSDT terms, a dacpac) and deploying that dacpac to your database. And taking the same dacpac and deploying again and again up to production. This process can be achieved in an hour. Actually, in 55 minutes! And AdventureWorks can be tough to automate! OK, it may take longer than that, but getting a repository of your code that can be audited, changes rolled back, tagged, branched, deployed, merged etc etc is the first step towards achieving Database DevOps.

Deploying SSDT Scripts (With My Good Eye Closed)

(If anyone actually gets the reference: kudos. If not, then it’s just that you’re not a Soundgarden fan)

So, there’s something that has been troubling me about deploying to databases with SSDT for some time. Actually, it’s not a SSDT-exclusive problem per se, it’s more of a database/release management process.

What it pertains to is that most people like to create the deployment script for review prior to deploying to production. This makes sense if you are new to the Continuous Deployment process, and that maybe sometimes SSDT creates scripts that are not quite expected. You may skip this because you feel rather more confident in your deployments, or the database sufficiently simple enough to just deploy without having to review. But generating the script as a deploy artefact is still a good idea, because auditing.

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.
Continue reading “Deploying SSDT Scripts (With My Good Eye Closed)”

Extract DacPacs Using PowerShell Via Sqlpackage Revisited

One of my first posts was a simple PowerShell module that used a bat file to run SqlPackage in cmdline mode to extract dacpacs. I’ve recently made some changes that make it more robust and allow other users to run it. The PowerShell module is just a wrapper around a cmd file that executes sqlpackage.exe to extract the dacpac. Check out my September Six of The Best for another solution provided by Gianluca Sartori. Continue reading “Extract DacPacs Using PowerShell Via Sqlpackage Revisited”

Automate Database Builds Part Three: Create A Database Publish Profile

Automate Database Builds Part One: Extract DacPacs Using PowerShell Via sqlpackage.exe 
Automate Database Builds Part Two: Extract Database Structure for Visual Studio Solution
Automate Database Build Part Three: Create a Database Publish Profile


Continuing with my automated database deployment series, this post touches on the build.publish.xml file that is used to script and publish a SQL Server 2012 project in Visual Studio. This step is necessary as we will need this file in our automated build.

Continue reading “Automate Database Builds Part Three: Create A Database Publish Profile”

Automate Database Builds Part Two: Extract Database Structure for Visual Studio Solution

Automate Database Builds Part One: Extract DacPacs Using PowerShell Via sqlpackage.exe 
Automate Database Builds Part Two: Extract Database Structure for Visual Studio Solution
Automate Database Build Part Three: Create a Database Publish Profile


In Visual Studio 2012, the old Data Dude projects have been replaced by SQL Server Data Tools (SSDT), which encompasses a list of improvements to database development in Visual Studio that was formerly known by the codename “Juneau”. When SSDT was first released there were several issues with compatibility levels, but has improved greatly since the December update.

Continue reading “Automate Database Builds Part Two: Extract Database Structure for Visual Studio Solution”

BIDS for Visual Studio 2012 Released

Microsoft have released SQL Server Data Tools for Analysis Services, Integration Services and Reporting Services projects with Visual Studio 2012. Finally, it looks like the BIDS suite is going to be usable with the most up-to-date version of Visual Studio; after we upgraded to SQL Server 2012, for about 3 months last year we were able to use Visual Studio 2010 as our sole IDE, before the update to Visual Studio 2012 rolled out at our office. This release has been very swift, and most welcoming, particularly when it comes to putting software on new Build servers. apparently it is part of the SQL commitment to deliver BIDS integration with the most up-to-date version of Visual Studio. It can be downloaded directly from Microsoft now:

If you don’t already have VS 2012 installed it will install the IDE for you. One of the real big wins is that there is no upgrade/downgrade if you have created a project in VS 2010 and want to open it in VS 2012, and vice versa!

There’s some useful installing info on this blog here:

Delete Old Tables On a Schedule Part Two: The Stored Proc

Been a massively busy week, what with deploying clustered SQL servers on Sunday, Release this weekend, new project starting next week for the dev team, so that means it starts this week for me, plus the usual drama of month end loading.

Today, I have been up since 5am working with cubes using PowerShell. So am still telecommuting. My own proclivities to the activity are documented here and to further the conversation the article linked below is an article on the Business Insider website about how metrics were used to make the decision. Apparently someone checked the VPN to see if people were logging in enough, and apparently they weren’t. I’m good friends with one of the network guys, so I asked him about the VPN stats we have. Turns out you can se how much someone has downloaded in a session, allowing people to see just how much traffic you’re making. The network guy pulled up the stats and showed me one person that logged on for an hour, but the traffic was barely enough for him to check his emails. Conversely, some sessions were in the GB’s w/r/t network traffic. I asked him how if the management ever ask to see these stats. “Often” was the reply.

I also discovered that my script I wrote in this post was not running as a job, so made a stored proc and I call that another way. I have made this more agnostic so that you can select the schema you want to drop the tables from.

USE DbName

CREATE PROCEDURE [dbo].[DropTables]

@LengthOfTime int,
@schema VARCHAR(20)


SELECT name AS tname
FROM sys.objects
WHERE create_date > DATEADD (hh, -@LengthOfTime,  CAST(GETDATE() AS datetime))
and type = 'U'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tname
SET @sql = 'DROP TABLE ' +@schema + '.' + @tname
EXEC (@sql)
--PRINT @sql
FETCH NEXT FROM db_cursor INTO @tname
CLOSE db_cursor
DEALLOCATE db_cursor