SQL DB CI/CD

Download and install Azure DevOps Server 2022 Express
Go to the project in azure devops
Go to project settings → Agent Pools → Default
Click New agent button on the top right corner of the screen

Download the agent and follow the instructions to set it up in Azure DevOps Server 2022

CI

Navigate to the Pipelines
Click New folder

Click a new folder dev/qa/prod
Go inside the respective folder, i.e. dev
Click New pipeline

Select Other Git

Select Azuer Repos Git, Team project and the respective branch. Click Continue

Select the templateASP.NET Core. Click Apply

Provide build pipeline settings
Click Manage on Agent pool settings

Click Add pool

Select Pool to link: Existing and select AC Agent Pool from the dropdown list

Select AC Agent Pool as the Agent pool

Select Get sources paremeters

Select Agent job 1 parameters

Restore task details

Select Restore and add a new command line task

Rename the new command line task as Checkout dependencies and use the below command line script

IF EXIST "..\bk" (
  ECHO "Directory exists. Removing ..."
  RMDIR /S /Q "..\bk"
)
git clone -b dev https://pm.aspireclan.com/FinPlan-Org/Baaki-2024/_git/BK ..\bk

Select Build paremeters

Select Test paremeters

Select Publish paremeters

Select Publish Artifact paremeters

Save the Pipeline
Go to Tirggers tab and check Enable CI

CD

ALWAYS REUSE AC_DEPLOYMENT GROUPS (Baaki-2024 project) FROM DIFFERENT SERVERS WHENEVER POSSIBLE. If reuse is not possible, follow the below steps
Go to Deployment groups. Click Add a deployment group

Give appropriate name to the deployment group, copy the script and run it in theTARGET SERVER. (This step should not be skipped. If the below script is directly run in the target server without creaing the deployment group in the project pipe line, the below script will throw error mentioning "deployment group not found." Make sure the deployment group name created in the project pipeline deployment group matches the deployment group name in the below script, before running it in the target server)

Please use the below script. Rename the deployment group name, agent location, agent name, project org name and server name as appropriate

$ErrorActionPreference="Stop";If(-NOT ([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent() ).IsInRole( [Security.Principal.WindowsBuiltInRole] "Administrator")){ throw "Run command in an administrator PowerShell prompt"};If($PSVersionTable.PSVersion -lt (New-Object System.Version("3.0"))){ throw "The minimum version of Windows PowerShell that is required by the script (3.0) does not match the currently running version of Windows PowerShell." };If(-NOT (Test-Path $env:SystemDrive\'ac_deploymentgroups'\'azagent01')){mkdir $env:SystemDrive\'ac_deploymentgroups'\'azagent01'}; cd $env:SystemDrive\'ac_deploymentgroups'\'azagent01'; for($i=1; $i -lt 100; $i++){$destFolder="A"+$i.ToString();if(-NOT (Test-Path ($destFolder))){mkdir $destFolder;cd $destFolder;break;}}; $agentZip="$PWD\agent.zip";$DefaultProxy=[System.Net.WebRequest]::DefaultWebProxy;$securityProtocol=@();$securityProtocol+=[Net.ServicePointManager]::SecurityProtocol;$securityProtocol+=[Net.SecurityProtocolType]::Tls12;[Net.ServicePointManager]::SecurityProtocol=$securityProtocol;$WebClient=New-Object Net.WebClient; $Uri='https://vstsagentpackage.azureedge.net/agent/3.225.2/vsts-agent-win-x64-3.225.2.zip';if($DefaultProxy -and (-not $DefaultProxy.IsBypassed($Uri))){$WebClient.Proxy= New-Object Net.WebProxy($DefaultProxy.GetProxy($Uri).OriginalString, $True);}; $WebClient.DownloadFile($Uri, $agentZip);Add-Type -AssemblyName System.IO.Compression.FileSystem;[System.IO.Compression.ZipFile]::ExtractToDirectory( $agentZip, "$PWD");.\config.cmd --deploymentgroup --deploymentgroupname "prod-ex-prxy-01 deployment group" --agent "prod-ex-prxy-01_azagent01" --runasservice --work '_work' --url 'https://pm.aspireclan.com/' --collectionname 'FinPlan-Org' --projectname 'Baaki-2024'; Remove-Item $agentZip;

Execute the above step in the respective servers below: (if the deployment groups are already existing in the server through other project(s), use those as a shared deployment group on in this project)

Deployment Groups
Category	Development	QA	Production	Comments
App	dev-app-01	qa-app-01	prod-app-01	One agent each for all services (gw, auth, data, file, script, scheduler [windows service], etc.) per server
Web	dev-web-01	qa-web-01	prod-web-01	Only one agent for web application per server
Load Balancer (LB)	dev-lb-01	qa-lb-01	prod-lb-01	One agent for the API gateway and another one for the Web Application per server
Proxy	dev-proxy-01	qa-proxy-01	prod-in-prxy-01 prod-ex-prxy-01	One agent for the API gateway and another one for the Web Application per server
File	dev-file-01	qa-file-01	prod-file-01	Only one agent for the file service per server
Swagger	NA	NA	prod-docs-01	One agent each for all services (gw, auth, data, file, script, etc.) per server. Currently only PROD server is being used

Go to Releases. Click New pipeline

Select the template IIS website deployment

Click Add an artifact

Click ReleasesContinuous deployment trigger

Add a pre and post powershell task. Pre is the first item in the release pipeline and post is the last item

Pre powershell script

# Define the path to the before-deployment web.config
$beforeDeployConfigPath = "C:\inetpub\wwwroot\maintenance\beforedeploy\web.config"
# Define the path to your application's root directory
$siteRootPath = "C:\inetpub\wwwroot"

# Copy the before-deployment web.config to the root directory
Copy-Item -Path $beforeDeployConfigPath -Destination "$siteRootPath\web.config" -Force

Write-Host "Pre-deployment configuration applied."

Pre Web.config

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
  <system.webServer>
      <rewrite>
          <rules>
              <rule name="Rewrite to maintenance page" stopProcessing="true">
                  <match url=".*" />
                  <!-- Rewrite URL to maintenance page -->
                  <action type="Rewrite" url="/maintenance/maintenance.html" />
              </rule>
          </rules>
      </rewrite>
  </system.webServer>
</configuration>
<!--ProjectGuid: A171D597-7DBB-45FA-BEE4-B88E8E2D33EB-->

Post powershell script

# Define the path to the after-deployment web.config
$afterDeployConfigPath = "C:\inetpub\wwwroot\maintenance\afterdeploy\web.config"
# Define the path to your application's root directory
$siteRootPath = "C:\inetpub\wwwroot"

# Copy the after-deployment web.config to the root directory
Copy-Item -Path $afterDeployConfigPath -Destination "$siteRootPath\web.config" -Force

Write-Host "Post-deployment configuration applied."

Post Web.config [Sample]. (Copy and reuse the web.config file created after the first deployment)

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<location path="." inheritInChildApplications="false">
  <system.webServer>
    <handlers>
      <add name="aspNetCore" path="*" verb="*" modules="AspNetCoreModuleV2" resourceType="Unspecified" />
    </handlers>
    <aspNetCore processPath="dotnet" arguments=".Auth.Srvc.dll" stdoutLogEnabled="false" stdoutLogFile=".logsstdout" hostingModel="inprocess" />
  </system.webServer>
</location>
</configuration>
<!--ProjectGuid: A171D597-7DBB-45FA-BEE4-B88E8E2D33EB-->

Maintenance page folder structure

Simple Maintenance page

<!DOCTYPE html>
<html>
<head>
  <title>Maintenance in Progress</title>
  <style>
      body, html {
          margin: 0;
          padding: 0;
          height: 100%;
          font-family: "Helvetica Neue", Helvetica, Arial, sans-serif;
          overflow: hidden;
      }

      .background-image {
          position: relative;
          height: 100%;
          //background-image: url('./Image-Maintenance.jpg');
          background-size: cover;
          background-position: center;
      }

      .background-image::after {
          content: '';
          position: absolute;
          top: 0;
          left: 0;
          right: 0;
          bottom: 0;
          background: rgba(255, 255, 255, 0.8);
      }

      .content {
          position: absolute;
          top: 50%;
          left: 50%;
          transform: translate(-50%, -50%);
          text-align: center;
          z-index: 1;
      }

      .maintenance-image {
          max-width: 300px;
          margin-bottom: 20px;
      }

      h1 {
          margin-bottom: 10px;
          color: #000000; /* Black color for h1 */
      }

      p {
          font-size: 20px;
          color: #000000; /* Black color for paragraph */
      }

      /* Responsive design adjustments */
      @media (max-width: 600px) {
          .content {
              width: 90%;
          }

          p {
              font-size: 18px;
          }
      }
  </style>
</head>
<body>
  <div class="background-image">
      <div class="content">
          <!-- <img src="./MAINLOGO.png" alt="Maintenance Image" class="maintenance-image"> -->
          <h1>ASPIRE CLAN Site Maintenance in Progress</h1>
          <p>We are currently performing scheduled maintenance. We should be back shortly. Thank you for your patience.</p>
      </div>
  </div>
</body>
</html>

SQL CI

Download and install Visual Studio Build Tools 2022 (VSBT) in prod-build-01 (build server)

Download VSBT from local server...

Download VSBT from Microsoft Website...

Update the SqlPackage.exe location in the "path" system environment variable, in build server

Copy paste the SqlPackage.exe path from below. MUST VERIFY THE BELOW LOCATION FOR THE .EXE AVAILABILITY

C:\Program Files (x86)\Microsoft Visual Studio\2022\BuildTools\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\

Verify the path correctiness and click "OK"

Open windows powershell and type"sql"

Keep tapping the "tab" button in the keyboard till "SqlPackage.exe" is picked up. This test confirms that the Path variable updated in the build server is correct

This test confirms that the Path variable update in the build server is correct
Navigate to the Pipelines
Click New folder

Click a new folder dev/qa/prod
Go inside the respective folder, i.e. dev
Click New pipeline
Select Other Git

"Click" on the Agent job 1

Next step refer the below image and copy paste from the code blocks below the image

Display name:

Copy Files to: drop folder

Source Folder:

$(Build.SourcesDirectory)\bin\Release

Contents:

*.dacpac

Target Folder:

$(Build.ArtifactStagingDirectory) 

Nothing to change in the below step. Keep all default settings and values as is

SQL CD

Click a new folder dev/qa/prod
Go inside the respective folder, i.e. dev
Go to Releases. Click New release pipeline

Next step refer the below image and copy paste from the code blocks below the image

Display name:

Deploy Database

Script:

& SqlPackage.exe `
   /Action:Publish `
   /SourceFile:"$(System.DefaultWorkingDirectory)\_dev-bk-db-ci\drop\BK-DB.dacpac" `
   /TargetConnectionString:"Server=63.98.29.63;Database=BK-DB-DEV;Integrated Security=True;Encrypt=True;TrustServerCertificate=True" `
   /p:BlockOnPossibleDataLoss=false `
   /p:CreateNewDatabase=true