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