SQL Database Project and Azure DevOps CI CD pipeline

SQL Database Project and Azure DevOps CI CD

0.Azure Key Vault

(1)create a Azure Key Vault and save a Azure SQL Database - server admin login [sqladminuser] password to a secret

My key vault is [devopsdeploy20220629]

create a secret [SctDevSqlAdmPw] and save a Azure SQL Database - server admin login [sqladminuser] password to this secret

Ps. The password for this Azure SQL Database - server admin login account

(2)configure permission for Azure DevOps pipeline access

Add Azure DevOps organization service principal permission on Access policies

Ps. we can find Azure DevOps organization service principal from Azure DevOps project settings

or 

Azure AD - App registrations

1.On Azure DevOps portal, Create a Azure DevOps project (建立DevOps Project會自動建立一個同名的repo(repository)

2.On Azure DevOps portal, rename the repository or create another repo (Optional)

例如: 為dev環境用的repo命名為 dev

3.On VS 2019, connect to the Azure DevOps project and clone the repo into local dev repo

4.On VS 2019, Create a VS SQL Database project on local dev repo

Important:

(1)Verify and change SQLCLR Target Framework from 4.5 to 4.7.2 in SQL Database project property

(2)verify and change Target SQL Server in SQL Database project property

5.On VS 2019, Import Database object from exist object in SQL Server Dev Environment

6.On VS 2019, Commit to local dev repo and pull to remote dev repo(Azure Repos)

7.On Azure DevOps portal, configure the branch policy of the main branch(dev repo) , to enable [Require a minimum number of reviewers]

8.On VS 2019, try to modify code and push to remote dev repo(Azure Repos)

(1)create a new branch for code updating

Team Explorer> Home>Branches> New Branch> NewFeature01

(2)add and modify code

create a new table object (a SQL file)

(3)commit to stage and push to Azure Repos

9.On Azure DevOps portal, create a Pull Request to merge NewFeature01 to main branch

在visual studio，點選 Create a Pull Request超連結，會自動啟動瀏覽器開啟 Azure DevOps portal的Create a Pull Request頁面並且會自動填入一些預設值

10.On Azure DevOps portal, create a build pipeline for dev deployment(deploy to dev Azure SQL Database)

(1)create a pipeline

Pipelines>Pipelines> 

Click create pipeline

On Connect Tab, select [Azure DevOps Git(YAML)]

On Select Tab, select [dev]

On Configure Tab, select [.Net Desktop]

On Review your pipeline YAML

Remove NuGet task

Remove VSTest task

Leave step below only

steps:

- task: VSBuild@1

  inputs:

    solution: '$(solution)'

    platform: '$(buildPlatform)'

    configuration: '$(buildConfiguration)'

On Review your pipeline YAML, click [show assistant], then select [Copy files]

Source Folder

 $(system.defaultworkingdirectory)

 

Contents

 **\bin\$(BuildConfiguration)\**

Tart Folder

 $(build.artifactstagingdirectory)

then click [add]

On Review your pipeline YAML, click [show assistant], then select [Publish build artifacts]

Leave default, click [add]

# .NET Desktop
# Build and run tests for .NET Desktop or Windows classic desktop solutions.
# Add steps that publish symbols, save build artifacts, and more:
# https://docs.microsoft.com/azure/devops/pipelines/apps/windows/dot-net
trigger:
- main
pool:
  vmImage: 'windows-latest'
variables:
  solution: '**/*.sln'
  buildPlatform: 'Any CPU'
  buildConfiguration: 'Release'
steps:
- task: VSBuild@1
  inputs:
    solution: '$(solution)'
    platform: '$(buildPlatform)'
    configuration: '$(buildConfiguration)'
- task: CopyFiles@2
  inputs:
    SourceFolder: '$(system.defaultworkingdirectory)'
    Contents: '**\bin\$(BuildConfiguration)\**'
    TargetFolder: '$(build.artifactstagingdirectory)'
- task: PublishBuildArtifacts@1
  inputs:
    PathtoPublish: '$(Build.ArtifactStagingDirectory)'
    ArtifactName: 'drop'
    publishLocation: 'Container'

Save and run

If build pipeline failed with Error message "error MSB3644: The reference assemblies for framework ".NETFramework,Version=v4.5" were not found", please check step 4.Important (1)

11.On Azure DevOps portal, create a release pipeline for dev deployment(deploy to dev Azure SQL Database)

(1)create a release pipeline

Create Release pipeline

Pipelines>Releases> new pipeline

Click Empty job link

Click Add an artifact on Artifacts

On Project: select [YourProject]

On Source(build pipeline): select yourBuildPipeline

the click [Add]

then, Click [1 job, 0 task] link

Click Add a task to Agent job

select Azure SQL Database deployment, then click [Add]

On Azure Subscription: select Your Azure service principal on Available Azure service connections

On SQL Database

 Authentication Type: SQL Server Authentication (leave default)

 Azure SQL Server: bradtestsql.database.windows.net

 Database: AdventureWorks2019

 Login: sqladminuser

 Password:  $(SctDevSqlAdmPw)

PS. we will create a SctDevSqlAdmPw secred in Azure Key Vault and configure Variable Group in Release pipeline later.

On Deployment Package

 Deploy type: SQL DACPAC file (leave default)

 Action: Publish (leave default)

 DACPAC File: click [...] , then select your dacpac file that created by build pipeline

On Variables tab

 Select Variable groups, Click [Manage variable groups] link to create a variable group

  Variable Group

  On Azure Subscription: select Your Azure DevOps service principal on Available Azure service connections

  On Key vault name: select your key vault

  Variables: select yourSecret

PS. before we create a Variable Group, we have to configure key vault permission for release pipeline access

Click Create release to run the release pipeline

After click Create button, the release pipeline will start to run

If release pipeline failed with Error message below, please check step 4.Important (2)

Error message:

2022-07-04T10:12:53.0144861Z ##[error]*** An error occurred during deployment plan generation. Deployment cannot continue.

2022-07-04T10:12:53.0194924Z ##[error]A project which specifies SQL Server 2016 as the target platform cannot be published to Microsoft Azure SQL Database v12.

2022-07-04T10:12:53.9967076Z 

2022-07-04T10:12:54.0690594Z ##[error]The Azure SQL DACPAC task failed. SqlPackage.exe exited with code 1.Check out how to troubleshoot failures at https://aka.ms/sqlazuredeployreadme#troubleshooting-

2022-07-04T10:12:54.1449122Z ##[section]Finishing: Azure SQL DacpacTask

其他Pipeline問題解決:

(1)No hosted issue

##[error]No hosted parallelism has been purchased or granted. To request a free parallelism grant, please fill out the following form https://aka.ms/azpipelines-parallelism-request

Started: Yesterday at 下午4:52

(2)current operating system is not capable of running this task issue

Starting: SqlAzureDacpacDeployment

=========

Task         : Azure SQL Database deployment

Description  : Deploy an Azure SQL Database using DACPAC or run scripts using SQLCMD

Version      : 1.202.0

Author       : Microsoft Corporation

Help         : https://docs.microsoft.com/azure/devops/pipelines/tasks/deploy/sql-azure-dacpac-deployment

=========

##[error]The current operating system is not capable of running this task. That typically means the task was written for Windows only. For example, written for Windows Desktop PowerShell.

Finishing: SqlAzureDacpacDeployment

將 vmImage改為windows-latest

pool:

  vmImage: 'windows-latest'

Reference:

Get started with Git in Azure Repos