Continuous Deployment execute database scripts with TFS

In another post, we’ve learned how we can use a Build definition and Release Management in a CI/CD pipeline. Please go through the articles if you need more information on these topics.

In this post, I’ll explain how we can use Release Definition to execute database scripts as part of the continuous deployment strategy. We will add a task to execute PowerShell script to achieve this.

Create Release definition

The task to be added is PowerShell under Utility. This task should be added before the Application files are deployed in the CD pipeline.

Create a Release definition for your Release pipeline as shown below, the path for the PowerShell script should be a shared path and the database Server is accessible.

Add the below code snippet to the PowerShell script. The script should be modified as per the requirement.

$localScriptRoot = "C:\Scripts"
$Server = "dbserver"
$scripts = Get-ChildItem $localScriptRoot | Where-Object {$_.Extension -eq ".sql"} | Sort-Object -Property Name
$qt = 0
foreach ($s in $scripts)
    {
        Write-Host "Running Script : " $s.Name -BackgroundColor DarkGreen -ForegroundColor White
        $script = $s.FullName	
        Invoke-Sqlcmd -ServerInstance $Server -InputFile $script -username "TFSBuildUser" -password "****" -querytimeout $qt
    }
Write-Verbose "scripts executed successfully" -verbose

The scripts to be executed are present at the location C:\Scripts with extension “.sql”. The password can be also passed as an argument to the PowerShell script and added to the Release Definition secret variable.

Use the Triggers tab to link to the required Build definition of your Project so that the Release definition is integrated with the Build pipeline. The linking can also be done using the Artifacts tab.

You can setup multiple environments using this strategy like Dev, POC and Production.

Change Password for TFS Build Service Account

The TFS Agent for 2015 or 2017 versions is a Windows Server machine that hosts the Service that allows to run Build and Release definitions for your code as part of the DevOps Process. It can run as a Windows Service and can be found by the name VSO Agent in the Services Snap-in. It uses a domain account to run the Windows Service which can be seen under the LogOn properties of the Service.

It runs VSOAgent.exe behind the scenes which is kept in the Agent folder configured for that TFS Agent e.g.

C:\TFSAgent\agent\Agent\VSOAgent.exe

If at any point you require to change the Windows Service account details that was configured earlier due to may be a password change, the following command may help you.

Please note that Password should not be changed from the Services Snap-in as it may cause the Agent Service to break.

  1. Open Command Prompt in Administrator mode.
  2. CD to the directory e.g. as mentioned above C:\TFSAgent\agent\ . Make sure you’re one level above the folder where the VSOAgent.exe is placed else it’ll give error.
  3. Run the following command: .\Agent\VSOAgent.exe /ChangePassword
  4. Fill in the new Credentials as Prompted.
  5. Restart the Service VSO Agent from the Services Snap-in.

The Agent should now be running with the new Service account.

Release Management with TFS

Release Management is a service in VSTS Azure pipelines and TFS on-premise and an essential element of DevOps that helps you continuously deliver software. We can fully automate the testing and delivery of your software in multiple environments all the way to production, or set up semi-automated processes with approvals and on-demand deployments.

CI Build

Release definition defines the end-to-end release process for an application to be deployed across various environments.

You define the release process using environments, and restrict deployments into or out of an environment using approvals. You define the automation in each environment using phases and tasks. You use variables to generalize your automation and triggers to control when the deployments should be kicked off automatically.

You can create a deployment model on the basis of how you manually deliver the builds using build definition for different environments.

The below Release Definition shows different steps using PowerShell scripts that are modeled basis the manual process created for deployment.

Release definition

 

Creating a Release definition

Configure your environment:

  • Give the environment a meaningful name by clicking into the Default Environment name provided and enter one of your own e.g. DEV, QA, PRODUCTION.
  • Click on the ellipsis next to the environment name and select Deployment conditions…

rd1

 

Deployment Conditions

rd2

This is where we set up how we want our deployment to be triggered. It is here that we define what triggers the deployment of our release.

  • No automated deployment– deployment of the build artifacts will need to be undertaken manually with no automation
  • After release creation– this will deploy the build artifacts to the specified environment every time a new release is created i.e. Continuous Delivery.
  • After successful deployment on another environment– this will deploy the build artefacts to an environment after they have been deployed to another environment e.g. after every successful deployment to the STAGING environment deploy the build artefacts immediately to the QA environment

Use the first option if you are deploying to a production environment i.e. make the deployments to your production environment a manual exercise. Use the second option if you are deploying to a development testing environment as part of a Continuous Delivery pipeline. Use the last option if you want to push your deployments between different testing environments.

Approvals

You may setup Approvals before the deployment to a particular environment using the Approvals Tab.

rd3

 

Configure a Queue and set demands

The demands may be System defined like “DotNetFramework” or you can set yourself like selecting Agent name.

rd4

 

General Tab

rd5

 

After configuring your environment, the next step would be to add task(s) which will be invoked when we want to deploy our build artifacts to the specified environment. The deployment task is the action that we want to happen when we invoke a deployment e.g. running a PowerShell script to start/stop App Pool, running database scripts, deploy the build on to a Virtual Machine or Azure instance.

Create Release

Create Release and select the build for which you would like to create a release in Dev environment and click on Create as shown below:

rd6

The successful build selected above will trigger the Automated Deployments based on the selections made for each environment.