End-to-end: Setup continuous integration/continuous delivery for ASP.NET Core + SQL database with GitHub, AppVeyor and Azure
I have a web project “Chinese Etymology (http://hanziyuan.net)” for searching Chinese character’s etymologies and ancient Chinese characters. It is developed with Microsoft tech stack – ASP.NET Core + SQL database. It is open source on GitHub. Its database is deployed to Azure SQL Database, and the website is deployed to Azure App Service. I will use this project to demonstrate the end-to-end workflow and setup for continuous integration/continuous delivery with AppVeyor. After these steps, when you commit code and push to your repository
- your code will be built automatically.
- tests will run automatically, connecting a staging SQL database with secured connection string info.
- your release build will be deployed to a staging environment on Azure automatically.
AppVeyor
AppVeyor is a CI solution for Windows. It is free for open source projects. I used it quite a while for traditional .NET + SQL Server projects. It is extremely friendly to Microsoft technologies.
To use AppVeyor with code in GitHub, just go to https://appveyor.com, log in with GitHub account. it also supports BitBucket and VSTS (Visual Studio Team Services). Then follow the UI to connect your GitHub repository. Then you need to place a appveyor.yml configuration file at the root of your repository. You can follow the appveyor.yml reference and create the file manually, and validate it with a validator provided by AppVeyor. Or, you can go to your AppVeyor project, click “Settings”, follow te nice UI to fill in the customization, and then export a appveyor.yml file.
AppVeyor has a Build Configuration document, showing its entire pipeline.
- Run
initscripts- Clone repository into clone folder
- Checkout build commit
cdto clone folder- Restore build cache
- Run
installscripts- Patch
AssemblyInfoand.csprojfiles- Modify
hostsfiles- Start services
- Build
- Run
before_buildscripts- Run msbuild
- Run
after_buildscripts- Test
- Run
before_testscripts- Discover and run tests
- Run
after_testscripts- Call
build_successwebhooks- Package artifacts
- Deployment
- Run
before_deployscripts- Run all configured deployments
- Run
after_deployscripts- Finalize successful builds:
- Call
deployment_successwebhooks- Run
on_successscripts- Save build cache
- Finalize failed builds:
- Call
build_failurewebhooks- Optionally save build cache
- Call
deployment_failurewebhooks- Run
on_failurescripts- Finalize both successful and failed builds:
- Call
on_finishscripts
Initialize environment
My project is built with ASP.NET Core 2.0, so I used the build worker image “Visual Studio 2017”, and turned on .NET Core patching:
dotnet_csproj: patch: true file: '**\*.csproj' version: '{version}' package_version: '{version}' assembly_version: '{version}' file_version: '{version}' informational_version: '{version}'
I also used WebPack for the website UI, so I need install Node.js and NPM:
install: - ps: >- Install-Product node 8
By default, AppVeyor runs msbuild to build the solution. So before build, dotnet restore is needed to install all the NuGet packages. npm install is also needed to install all the NPM packages.
before_build: - cmd: >- dotnet restore cd ./src/Etymology.Web npm install cd ../..
Now it is good to build.
Run tests
I am using MSTest framework. After build, the tests will be automatically discovered:
Connect to Azure SQL database with secure variables
My tests needs to connect to Azure SQL database to run. In this case, we can hide the secretes in connection string with secure variables. The following is the settings file containing the connection string:
{ "ConnectionStrings": { "Etymology": "Server=tcp:{server}.database.windows.net,1433;Initial Catalog={database};Persist Security Info=False;User ID={user};Password={password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" } }
This file is committed to repository and exposed to public. Now encrypt the real values in AppVeyor with its encryption tool or UI:
Now these encrypted secretes can go to appveyor.yml and expose to public, along with other environment variables:
environment: Server: secure: TvXoVEeYYNoo2cXBZTeGuQ== Database: secure: VoBmaLf7l0+NV1kGgG+wPg== User: secure: UzHGGTiu63CYQPT9rPKhJA== Password: secure: si1rZXqiINGCaeQU0Oh7hg== APPVEYOR_RDP_PASSWORD: secure: aii6TuA5V1pzlqRiUOXyTQ== ASPNETCORE_ENVIRONMENT: Staging
In AppVeyor, these secure variables can be access just like othernormal environment variables. So before build, just replace the connection string with decrypted values:
install: - ps: >- Install-Product node 8 $file = "$($env:appveyor_build_folder)\src\Etymology.Web\Server\settings.$($env:ASPNETCORE_ENVIRONMENT).json" (Get-Content $file).Replace("{server}", $env:Server).Replace("{database}", $env:Database).Replace("{user}", $env:User).Replace("{password}", $env:Password) | Set-Content $file
Now the settings file has the real connection string info.
On the Azure side, go to the SQL database’s server, in the firewall settings, whitelist all AppVeyor build worker IP addresses:
Connect to on-premise SQL Server database
What if on-premise SQL Server is used? I have an .NET Framework project using .mdf and ldf files with SQL Server LocalDB. In AppVeyor, I just enable SQL Server, attach the .mdf and ldf files, and update the connection string:
services: mssql2016 before_test: - ps: >- $server = "(local)\SQL2016" $database = "AdventureWorks" # Replace the db connection with the local instance. $configPath = "$($env:appveyor_build_folder)\EntityFramework.Functions.Tests\bin\$($env:CONFIGURATION)\EntityFramework.Functions.Tests.dll.config" $config = (gc $configPath) -as [xml] $config.SelectSingleNode('//connectionStrings/add[@name="EntityFramework.Functions.Tests.Properties.Settings.AdventureWorksConnectionString"]').connectionString = "Server=$server; Database=$database; Trusted_connection=true" $config.Save($configPath) # Attach mdf to local instance. $databaseDirectory = "$($env:appveyor_build_folder)\Data" $mdfPath = join-path $databaseDirectory "AdventureWorks_Data.mdf" $ldfPath = join-path $databaseDirectory "AdventureWorks_Log.ldf" sqlcmd -S "$server" -Q "Use [master]; CREATE DATABASE [$database] ON (FILENAME = '$mdfPath'),(FILENAME = '$ldfPath') FOR ATTACH" sqlcmd -S "$server" -Q "Use [$database]; EXEC sys.sp_configure @configname = N'clr enabled', @configvalue = 1;" sqlcmd -S "$server" -Q "Use [$database]; RECONFIGURE;"
Deploy to Azure
First got to Azure, create a App Service on Azure. If you do not have an Azure account/subscription, you can go to Dev Essentials and get a free one. Here I created a App Service of free pricing tier from https://portal.azure.com. Since my code detects the environment, I also used the portal to add environment variable “ASPNETCORE_ENVIRONMENT”, and set it to “Staging”.
Also create a user name/password for deployment:
Now go to AppVeyor.By default, AppVeyor calls msbuild to build the solution, which is good for .NET Core projects. For my ASP.NET Core projects, I run the “dotnet publish” after running tests to export all binaries and resources (HTML, CSS, JavaScript, images, settings) to a Publish/{BuildConfiguration} directory.
Since only the release build needs to be deployed, create an artifact “PublishRelease”, which can be viewed as an alias pointing to the Publish/Release directory.
Then enable packaging for xcopy deployment:
And specify the Azure deployment credential user name and password created just now, as well as the above artifact to deploy:
The YAML will be like this:
build: publish_wap_xcopy: true parallel: true verbosity: detailed after_test: - ps: dotnet publish ./src/Etymology.Web/Etymology.Web.csproj -c $env:CONFIGURATION -o ../../Publish/$env:CONFIGURATION artifacts: - path: ./Publish/Release name: PublishRelease deploy: - provider: AzureAppServiceZipDeploy appservice_environment: false website: etymologystaging username: dixinyan password: secure: aii6TuA5V1pzlqRiUOXyTQ== artifact: PublishRelease
The Debug build will not be deployed:
[00:11:22] Collecting artifacts...
[00:11:23] No artifacts found matching 'Publish\Release' path
[00:11:23] Deploying using AzureAppServiceZipDeploy provider
[00:11:23] No Zip artifacts were found. Only Zip artifacts can be published as Azure App Service Zip Push Deploy. Make sure you have specified correct artifacts filter.
[00:11:23] If you are using AppVeyor Web Application packaging, please ensure that you selected 'Package Web Applications for XCopy deployment' ('publish_wap_xcopy: true' in YAML).
[00:11:23] Build success
And the Release build will be deployed:
[00:10:36] Collecting artifacts...
[00:10:36] Found artifact 'Publish\Release' matching 'Publish\Release' path
[00:10:36] Uploading artifacts...
[00:10:36]
[00:10:40] [1/1] PublishRelease...Zipping to PublishRelease.zip
[00:10:40] [1/1] Publish\PublishRelease.zip (19,221,277 bytes)...1%
[00:10:40] [1/1] Publish\PublishRelease.zip (19,221,277 bytes)...10%
[00:10:41] [1/1] Publish\PublishRelease.zip (19,221,277 bytes)...20%
[00:10:41] [1/1] Publish\PublishRelease.zip (19,221,277 bytes)...30%
[00:10:41] [1/1] Publish\PublishRelease.zip (19,221,277 bytes)...40%
[00:10:41] [1/1] Publish\PublishRelease.zip (19,221,277 bytes)...50%
[00:10:41] [1/1] Publish\PublishRelease.zip (19,221,277 bytes)...60%
[00:10:41] [1/1] Publish\PublishRelease.zip (19,221,277 bytes)...70%
[00:10:41] [1/1] Publish\PublishRelease.zip (19,221,277 bytes)...80%
[00:10:41] [1/1] Publish\PublishRelease.zip (19,221,277 bytes)...90%
[00:10:41] [1/1] Publish\PublishRelease.zip (19,221,277 bytes)...100%
[00:10:41] Deploying using AzureAppServiceZipDeploy provider
[00:10:58] Deploying "PublishRelease.zip" to "*****staging" site...OK
[00:10:59] Build success
Remote desktop debugging
AppVeyor also supports debugging with remote desktop. To enable this, add a secure variable “APPVEYOR_RDP_PASSWORD”, which will be your remote desktop password. Then add the following command:
It will print the connection info at the very beginning of the build console:
[00:00:00] Build started
[00:00:00] iex ((new-object net.webclient).DownloadString('https://raw.githubusercontent.com/appveyor/ci/master/scripts/enable-rdp.ps1'))
[00:00:03] Remote Desktop connection details:
[00:00:03] Server: 67.225.139.254:33923
[00:00:03] Username: appveyor
You may get an authentication error due to CredSSP encryption oracle remediation:
If so, please follow another post of mine.
The full appveyor.yml can be viewed on GitHub: https://github.com/Dixin/Etymology/blob/master/appveyor.yml.