Database deployments with SQL AlwaysOn

Legacy Uncategorised
#1

Hi,

In our current deployment process, we have some database scripts that need to be run only once. What complicates this, is that we have more than one database server. We are using SQL AlwaysOn . Any changes we make to the active node will get replicated across to the passive nodes. What is the best way of using octopus deploy in this situation?

Thanks,
Warwick

#2

Hi Warwick,

Thanks for reaching out. The first idea that came to my mind was “Install a Tentacle on the active node and run the scripts there”, but of course this doesn’t solve the fact that you need this to always be executed on the Active Node.

A possible setup could be:

  • Get a Powershell script that figures out which node is currently active, and make it return the name of that node/database.

  • Create a VS Project for your database scripts using DBUp. Make the DBup console take the database connection string from its App.config file. More info on DBUp (and a video) at the bottom of this doc: http://docs.octopusdeploy.com/display/OD/SQL+Server+databases

  • Not strictly necessary, but it would be handy to have a Tentacle whose sole purpose is to run the steps of the process below. Turning your Octopus Server into a Tentacle and assigning it a role like “DBUpdate” could be a good idea: http://docs.octopusdeploy.com/display/OD/How+to+run+steps+on+the+Octopus+Server

On Octopus:

  1. A Powershell step that runs your script to figure out the Active Node, and that uses that info to create an output variable that could later on be used by further steps using Set-OctopusVariable (read blog post about it). This step could run on the Tentacle on the Octopus server with the role “DBUpdate”.

  2. A Nuget Package Deploy step that deploys your DBUp project (which will contain your scripts and the console that executes them). Before executing the DBUp console, you’ll need to put the right connnection string on the app.config file using the Configuration Variables method and the variable you created on the previous step. This way you’ll make sure that the scripts will always be executed on the node that was active at that moment. This step could also run on the new Tentacle.

Hope that helps!
Dalmiro

#3

I was wondering if a simpler solution is more feasible:

  1. Run a Powershell script that retrieves the name of the primary node.
  2. Disable the deployment targets whose names do not match the primary name.
  3. Run the DbUp migration, which will run only on the enabled targets.

Is it possible to dinamically disable a deployment target?

#4

I see now that it’s possible with OctoPosh.

#5

Hi there,

You can totally disable the targets with Octoposh. The problem is that if Octopus detects that a target has suddendly been disabled during the deployment, It’ll end up failing the deployment the next time it tries to run something on it. You can solve this by adding a “Health Check” step right after the “Disable Targets” step. You can read more about this step type here: http://docs.octopusdeploy.com/display/OD/Deploying+to+transient+targets

Hope that helps,
Dalmiro

#6

For now we’re using a simpler approach:

  1. Migrate the sql using a connection string that points to the virtual node/cluster name. Then the sql server will sync the changes by itself.
  2. Deploy the app that runs on the sql server only on one node and its files will be synchronized by the sync app.
#8