JEFF TAYLOR

How To Setup
Continuous Integration Continuous Delivery (CICD) Step By STep For your Website & Database

ABOUT ME

Jeff Taylor
Data/Software Architect
SQL Server Since SQL 6.5
22+ years working with Microsoft SQL Server
President of Jacksonville SQL Server Users Group
Contact: Jeff@ReviewMyDB.com
Twitter: @reviewmydb
Blog: https://blog.reviewmydb.com

Questions

What is your current Role?
Does your company currently have CICD?
Does your company have a DevOp's Team?
How long do your current deployments take?
How many team members does it take to perform deployments?

Agenda

What we are not going to cover
Configuring Website
Azure DevOps Pipelines
Database Source Control
Azure DevOps Build Database
Configure Octopus Deploy
Automation Test

Not Covering

Obtaining MSDN Account
Creating VisualStudio.com (Azure DevOps) project site.
Creating Website Solution Project
Installing Red Gate SQL Compare Pro
Installing Red Gate DLM Automation
Installing & Configuring Octopus Deployment Server
Installing Octopus Deploy Tentacles

CICD Flow

Configuring Website

<Target Name="BeforeBuild">
    <TransformXml 
        Source="Web.config" 
        Transform="Web.$(Configuration).config" 
        Destination="Web.config" />
</Target>
<Import 
    Project="..\packages\NETStandard.Library.2.0.1\build\netstandard2.0\NETStandard.Library.targets" 
    Condition="Exists('..\packages\NETStandard.Library.2.0.1\build\netstandard2.0\NETStandard.Library.targets')" />
<Target Name="EnsureNuGetPackageBuildImports" BeforeTargets="PrepareForBuild">
    <PropertyGroup>
      <ErrorText>This project references NuGet package(s) that are missing on this computer. 
        Use NuGet Package Restore to download them.  For more information, see 
        http://go.microsoft.com/fwlink/?LinkID=322105. The missing file is {0}.
      </ErrorText>
    </PropertyGroup>
<Error 
    Condition="!Exists('..\packages\NETStandard.Library.2.0.1\build\netstandard2.0\NETStandard.Library.targets')" 
    Text="$([System.String]::Format('$(ErrorText)', 
    '..\packages\NETStandard.Library.2.0.1\build\netstandard2.0\NETStandard.Library.targets'))" />
</Target>

In the Website Project, if using web.config add the following lines to your web project solution within the <Project></Project> tags.

Configuring Website

<connectionStrings>
    <add 
        name="Admin" 
        connectionString="Data Source=(local);
                          Initial Catalog=Admin;
                          Integrated Security=True;
                          Column Encryption Setting=Disabled;"             
        providerName="System.Data.SqlClient" 
        xdt:Transform="SetAttributes" 
        xdt:Locator="Match(name)"/>
</connectionStrings>

<appSettings>
    <add 
        key="RedisServerSessionState" 
        value="localhost" 
        xdt:Transform="SetAttributes" 
        xdt:Locator="Match(key)"/>
</appSettings>

Add transformations to each config file.

Configuring Website

Creating Azure DevOps Pipeline

PipeLine - WebSite build

https://myproject.visualstudio.com

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE Build

PIPELINE - WEBSITE BUILD

/t:Build

/p:RunOctoPack=true 

/p:OctoPackPublishPackageToHttp=https://mybuildserver.mycompany.com/nuget/packages

/p:OctoPackPublishApiKey=$(OctopusDeployAPIKey) 

/p:OctoPackPackageVersion=$(Build.BuildNumber) 

/p:OctoPackAppendToPackageId=$(BuildConfiguration)

PIPELINE - WEBSITE BUILD

$(BuildDefinitionName)_$(Year:yyyy).$(Month).$(DayOfMonth)$(Rev:.r)

$(date:yyyyMMdd)$(rev:.r)

PIPELINE - WEBSITE BUILD

DATABASE Source Control -

Azure Devops Repo

Database Source Control

Azure Devops Pipeline - BUILD DATABASE

PIPELINE - BUILD DATABASE

USE [master];
GO
CREATE DATABASE [AdminTestDeploy] CONTAINMENT = NONE ON PRIMARY (
               NAME = N'AdminTestDeploy',
               FILENAME = N'D:\Databases\AdminTestDeploy.mdf',
               SIZE = 55296KB, MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB),
    FILEGROUP [AdminInMemFG] CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT (
            NAME = N'AdminInMemFG',
            FILENAME = N'D:\Databases\AdminInMemFG', MAXSIZE = UNLIMITED),
    FILEGROUP [FGBlob] (
            NAME = N'AdminTestDeploy_FGBlob',
            FILENAME = N'D:\Databases\AdminTestDeploy_FGBlob.ndf',
            SIZE = 1024KB, MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB),
    FILEGROUP [FGData] DEFAULT (
            NAME = N'AdminTestDeploy_FGData',
            FILENAME = N'D:\Databases\AdminTestDeploy_FGData.ndf',
            SIZE = 1024KB, MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB),
    FILEGROUP [FGIndexes] (
            NAME = N'AdminTestDeploy_FGIndexes',
            FILENAME = N'D:\Databases\AdminTestDeploy_FGIndexes.ndf',
            SIZE = 1024KB, MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB)
    LOG ON (NAME = N'AdminTestDeploy_log',
            FILENAME = N'L:\DatabaseLogs\AdminTestDeploy_log.ldf',
            SIZE = 128GB, MAXSIZE = 2048GB, FILEGROWTH = 51200KB);
GO

PIPELINE - BUILD DATABASE

Octopus Deploy - DATABASE

Configure Octopus Deploy

Automation Test

AUTOMATION TEST

Resources

Azure DevOps
https://azure.microsoft.com/en-us/services/devops/pipelines/
https://azure.microsoft.com/en-us/services/devops/repos/
Red Gate SQL Compare
https://www.red-gate.com/products/sql-development/sql-compare/
Red Gate DLM
https://www.red-gate.com/products/dlm/dlm-automation/
Octopus Deploy
https://octopus.com/

Questions?

Contact

Email: Jeff@ReviewMyDB.com

Twitter: @ReviewMyDB

How To Setup Continuous Integration Continuous Delivery (CICD) Step By Step For Your Website & Database

By reviewmydb

How To Setup Continuous Integration Continuous Delivery (CICD) Step By Step For Your Website & Database

This session will cover how to setup your database source code in a source code repository, configure your website application, configure continuous integration and continuous deployments of your database alongside your application using Visual Studio Online, Red Gate DLM SQL CI, and Octopus Deploy.

reviewmydb PRO

JEFF TAYLOR

How To Setup Continuous Integration Continuous Delivery (CICD) Step By STep For your Website & Database

ABOUT ME

Questions

Agenda

Not Covering

CICD Flow

Configuring Website

Configuring Website

Configuring Website

Configuring Website

Configuring Website

Configuring Website

Configuring Website

Configuring Website

Configuring Website

Configuring Website

Configuring Website

Creating Azure DevOps Pipeline

PipeLine - WebSite build

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE Build

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

PIPELINE - WEBSITE BUILD

DATABASE Source Control -

Azure Devops Repo

Database Source Control

Database Source Control

Database Source Control

Database Source Control

Database Source Control

Database Source Control

Database Source Control

Database Source Control

Database Source Control

Database Source Control

Database Source Control

Database Source Control

Database Source Control

Azure Devops Pipeline - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

PIPELINE - BUILD DATABASE

How To Setup
Continuous Integration Continuous Delivery (CICD) Step By STep For your Website & Database