So far, Octopus has been awesome with deploying our Visual Studio projects. I add the OctoPack Nuget package to the appropriate projects in the solution. The TeamCity plugin in finds it, and builds the Nuget packages served to our TeamCity Nuget feed. Finally, Octopus finds the packages and deploys them. We are using Visual Studio 2013.
I have one final step remaining: database deployments.
The problem is we don’t do any formal SQL Server source control now. The way we deploy changes is to do a manual schema compare using RedGate SQL Compare right before the deploy, pick the changes that need to go, generate the script, and execute on the target machine. I’d like to essentially mimic that with deployment automation – but first we need to get the schema into our solution and, thus, into source control (we use Git).
I’m thinking I’d like this in the Visual Studio solution itself since our apps own their databases. I’m familiar with the SQL Server Database Project type in Visual Studio. Should we use that? Should we use something else – I seem to remember that RedGate had a Visual Studio project type, but I can’t seem to find that now? What should we use? And do I just add the OctoPack to that project and call it a day? How does the Red Gate Database step feature in Octopus integrate with it?
Thanks for getting in touch! There are a number of solutions out there that all very in effort and cost. Ill suggest the following purely based on the fact that you already use a RedGate tool.
They have some products that directly plug into TC and OD and create your NuGet packages and also release them. But I do not know what licensing you do have and if it will be a valid solution.
Here are some links to read up on how their solutions work with ours:
When it comes to the VS database solution, we aren’t huge fans of it, and would recommend things like DbUp or ReadyRoll instead. DbUp might be a good solution, as it takes scripts and version controls them and only runs them and in the order required, and you could use SQL Compare to generate it.
Feel free to respond back with and extra questions about any of this.
Vanessa
I’ve looked at DbUp before but I’m thinking our team would prefer a more visual experience, rather than writing scripts for schema changes. It’s hard to tell eyeballing scripts what is the “truth.”
What is your apprehension with VS db solutions?
For the most part, all of our databases are single instances for a single application. IOW, we do not “share” our apps with anyone, and we do not have multiple instances of a db to an application (like in your first link). So, we’re looking for something simple.
I don’t know if we really have an apprehension. There is some bias around the OD office from our x-consultants turned devs who have had to deal with them for clients and been burned.
I did a quick search and I can’t find any blog posts written about our customers doing deployments with VS db solutions, but that doesn’t mean you can’t or people aren’t.
It also means you don’t get a head start and can’t learn from their mistakes.
An update on this. We are heavily leaning towards ReadyRoll. I had to be indoctrinated into the pros of using source-controlled migration scripts rather than on-the-fly schema compares, but I get it now.
For anyone starting to traverse down this path of source-controlling your database schema, managing data updates (like to lookup data), and auto-deploying your changes, I’d suggest looking at ReadyRoll. Good support, too.