The Alternative to Pre-Model Scripts

Hello!

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”

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)”

Deploy Cubes Using Powershell: The Script

Hello!

Recently someone got in touch with me about one of my earliest posts. I realised I had made a mistake on that post and had updated. But I also realised that the script itself is actually part of a PowerShell module. So wanting to make things as easy as possible, and being the helpful guy that I am, I have altered that example into a script which I am posting here.

It’s worth stressing that the errors that output to the console are fairly generic, and that there is a log file that will contain all the information if there are any errors.

Continue reading “Deploy Cubes Using Powershell: The Script”

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

Introduction

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

Introduction

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”

Pre-Release Preperation

Working in an Agile environment means that we have many releases throughout the year. Despite our sprints being of the same length, there generally seems to be releases where there is a lot to of functionality, and some where  not so many changes are going in. Releases tend to be a team effort here, but the overall management of the process falls to me.  But no matter how regularly we deploy, or how big an impact the release will be, it always makes common sense to be diligent when preparing for a release. The last thing I want to be doing on a Saturday is trying to fix an issue that could and should have been caught in regression. So how do we help ourselves?

Continue reading “Pre-Release Preperation”