Stored Procedure Database Deployments

Thanks for all your work on Octopus, really a great product and your hard work is much appreciated. We have our proof of concept deployment of our asp.net app up and running with TeamCity and Octopus and it all looks great. Now we have to figure out the database side. I have read your terrific articles on db deployments and we think DbUp is the way to go for our general Db modification scripts for DML and DDL. The one thing we are scratching our heads with is stored procedures.

I have been using RedGate SQL Compare to generate deployment scripts manually for each deployment and really need to get that process automated. Redgate Automtation is out of our price range and was wondering if you had any suggestions.

We have over 1000 stored procs. Let’s say that during the deployment to staging 40 of those have changed and we want to push them to a staging environment. They are all in SubversionSVN so we know what has changed, but how can I push them? Any ideas on a CI tool that can help? Would you recommend the cost of ReadyRoll, price again is an issue as it would upwards of $3k for the first year although I’m sure I would get that back if the procs solution could be solved. Maybe I answered my own question right there!

Hi Ben,

Thanks for getting in touch! I saw your email come through our Hello tickets but I will respond here as that makes it a searchable response.

We try to be very neutral when it comes to your choice of DB solution combined with Octopus. Obviously all three we recommend have three very different price ranges. We also love them all, and think they all are great solutions. ReadyRoll has a 30 day trial period, so there would be no harm in seeing if it meets your needs, as it appears to be the intermediate between the costs of the others.

Really we would suggest a tool, as removing any human failure from a continuous deployment strategy is the real goal. But worst case, is creating a method to package up the files yourself, push them to your Nuget feed, expand and have another script to put into SQL could also be an entirely messy option.

Let me know what you think or if you have any further questions.
Vanessa

Hi Ben

I’ve used DBUp for this, in fact there’s a whole piece of DBUp I put there for just this reason (although it’s been refactored a few times since).

DBUp (as you probably know) logs all the migration scripts run to a table in your database, but you can also supply it with a NullJournal which doesn’t log. This means that each script will get run every time. Perfect for stored procs if you maintain the script with either Drop and Create or Alter statements in them.

DBUp also has the option of reading scripts from a directory on the filesystem instead of the more common practice of having them embedded in the assembly.

So, a solution to consider is in your DB Migration app, have one pass where you run the migrations, then a second pass where you configure in a Null Journal and read all the stored procs scripts and execute them.

An early pre-predecessor of DBUp I wrote in 2003 ish when working in a web agency did this because I had colleagues who would edit stored procs via the SQL Tools directly on the “dev” server, and often forget to move them (copy and paste) to prod when done. So I automated a process as part of a CI build to re-run all the scripts from source control. After having work wiped out a few times they got on board :slight_smile:

Hope that gives you some ideas

Damian

Thanks Damian and Vanessa

Damian, is there any documentation you know on the option of reading from scripts from directory? that seems like it would be perfect for us.

Thanks again

Hi Ben

I don’t think there is much doco for it no, it’s one of those “discover by autocomplete” things unfortunately.

This should be a good point in the right direction.

Does that help ?

Damian

Thank you all for your help, just wanted to come back here and say that with your help and the amazing library of deployment scripts we just deployed our first production site completely using Octopus, DBUP and team city Thank you for all of your help and I wish you all the best. We have been running in dev, test, and UAT for a month but tonigt was the first full on production deploy. Wow how my attitude towards deployments have changed over the last 6 months. Thanks again.

Hi Ben

Thanks for the kind words! So glad you got everything up and running and congrats on your first automated prod deployment!

We love to hear success stories like this!

Happy Deployments!

Damian