Skip to content

Instantly share code, notes, and snippets.

@Alex-Yates
Last active July 3, 2020 10:58
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Alex-Yates/27eb59a38e860f9e7b0c46f0416164b0 to your computer and use it in GitHub Desktop.
Save Alex-Yates/27eb59a38e860f9e7b0c46f0416164b0 to your computer and use it in GitHub Desktop.
Title:
Build your own database deployment pipeline with Octopus Deploy and either SSDT or Redgate SQL Change Automation
Abstract:
Automated deployments enable teams to deliver value faster, more reliably and for less money. If your database deployments are still slow, manual and error prone, and your business wants to remain competitive, building an automated database deployment pipeline should be a high priority.
However, this can be difficult because it requires a working knowledge of various technologies, as well as an understanding about how to glue them all together. For example, folks need to understand database technologies, scripting languages, source control tools and CI/CD architectures, as well as all the necessary networking. To create a truly successful deployment pipeline it often requires collaboration between multiple specialists with a generalist to guide them.
This session is both for the aspiring generalists who wants to understand the big picture and for the specialist who wants to learn to better collaborate with their colleagues. We will focus on the high-level outcomes, without going too deep on any specific technology. Therefore, this session should be accessible to anyone, regardless of speciality or experience.
Attendees will create their own end to end deployment pipelines for SQL Server using GitHub, Octopus Deploy and AWS. While attendees tool choices and hosting providers might vary, this session will focus on universal concepts and should be valuable to folks even if they use different tools, such as Jenkins, Azure DevOps or Azure. Folks will be using their own software and licences with 100% free / trial software/services and will be able to host their infrastucture using their own AWS account, on free tier eligible VMs. The cost of running the infrastructure will be no more than a few cents per hour.
By the end of this class, all attendees will understand how to create an end to end database deployment pipeline, and they will take away their own hosted pipeline which they'll be able to spin up and tear down whenever they like.
Modules:
AM
1. Cloning a Git repo in GitHub
2. Introducing Octopus Deploy, and configuring the hand-off from GitHub Actions
3. AWS 101: Building a basic set of infrastucture and SQL instances from code, using Octopus Deploy Runbooks
PM
1. Exploring database source code and understanding how it gets deployed
2. Configuring Octopus Projects to deploy database updates
3. Further extensions (various options, depending on time available.)
Prerequisites:
(Required)
Create a free account in advance for the following services:
1. AWS
2. GitHub
3. Octopus Deploy (Cloud edition)
(Optional)
If you would like to run the code locally (optional) install the following on your dev machine:
1. SQL Server Developer Edition (Either a full instance or using Docker is fine)
2. (If using SSDT) Visual Studio (Community edition is fine)
3. (If using Redgate) Redgate SQL Change Automation PowerShell cmdlets
4. PowerShell
5. Your prefered IDE for working with SQL Server (SSMS, Azure Data Studio etc)
6. Git
Notes:
This session can either be delivered to support both SSDT and Redgate or to focus on just one deployment tool.
If the event organiser believes that their attendees are split fairly evenly between SSDT and Redgate I recommend we do the general Redgate AND SSDT version to appeal to more people. If the event organiser feels a significant majority of their attendees will prefer one tool or the other, I recommend we simplify the session and focus on the more popular tooling choice to provide a simpler class for the majority of attendees.
I respectfully leave this choice to the event organiser since they will probably understand their attendees better than me. However, I'm happy to discuss it with them if they are unsure.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment