Using Unit Test in the DevOps process

Unit Test for SQL Server can be directly involved in the Continuous Integration (CI) process via the PowerShell cmdlets that can help automate to create and run unit tests. Unit Tests allow verifying the changes made by developers and validating that the applied changes do not affect the database after its deployment.

To start using Unit Test in the DevOps process, be sure that:

• dbForge DevOps Automation for SQL Server and dbForge Unit Test for SQL Server are installed on your computer. For more information about the installation of dbForge Unit Test, see Installing.
• PowerShell version is 3.0 or higher.
• The tSQLt framework is installed for the database against which you want to run unit tests. For more information about how to install the framework, see How to install the tSQLt framework.
• Unit tests have been created in the database. For more information about how to create tests, see the Save Unit Tests to Drive section in Database Continuous Integration with Devart Tools.

After the tests were created, you can add them to the database with the Invoke-DevartExecuteScript cmdlet and then run with the Invoke-DevartDatabaseTests cmdlet.

Note

By default, PowerShell is installed on Windows 8, 8.1, 10, and 11. For other versions, download PowerShell from the Microsoft official download center page.

To check the current version of PowerShell, open WindowsPowerShell ISE and execute the $PSVersionTable.PSVersion command. In the Major column, you can see the version currently installed on your computer.

The guide covers the following topics:

• Installing dbForge DevOps Automation PowerShell for SQL Server
• Creating unit tests using the Invoke-DevartExecuteScript cmdlet
• Running unit tests using the Invoke-DevartDatabaseTests cmdlet

Installing dbForge DevOps Automation PowerShell for SQL Server

dbForge DevOps Automation for SQL Server is supplied as part of dbForge SQL Tools (Professional version) and automatically includes dbForge DevOps Automation PowerShell for SQL Server.

To install dbForge DevOps Automation PowerShell for SQL Server

1. Go to the download page and click Get for free.

2. On the Devart Identity Server page, sign in to your account and then click Download. If you don’t have a Devart account, then you need to sign up.

3. After the download is complete, go to the folder with the downloads and run the sqltoolspro.exe file to install dbForge DevOps Automation PowerShell for SQL Server.

4. In the dbForge SQL Tools Professional installer, click Install.

Note

Prior to installing the tool, be sure that SSMS instances are closed.

5. On the Select Products page, make sure that the dbForge DevOps Automation PowerShell for SQL Server check box is selected. Then, click Next.

Note

If you have not installed dbForge Unit Test for SQL Server yet, then in this step select the dbForge Unit Test for SQL Server check box as well.

6. After the installation is complete, click Finish. The Devart.DbForge.DevOpsAutomation.SqlServer module with a set of cmdlets appears in Windows PowerShell ISE.

You can check the full list of requirements to run dbForge DevOps Automation on the Requirements and Compatibility page.

Creating unit tests using the Invoke-DevartExecuteScript cmdlet

The Invoke-DevartExecuteScript cmdlet creates tests in the database from the existing SQL files.

To create unit tests using PowerShell cmdlet

1. Run Windows PowerShell ISE on your computer.

2. In Windows PowerShell ISE, execute the PowerShell script to create unit tests.

# Defining variables
$ServerName = "JORDANS\MSSQLSERVER2019"
$DatabaseName = "AdventureWorks2019"
$Connection = New-DevartSqlDatabaseConnection -Server $ServerName -Database $DatabaseName  -WindowsAuthentication true

#Adding tSQLt tests into a database
Invoke-DevartExecuteScript -Connection $Connection -Input 'D:\Source_TUnit_tests'

Parameters used in the script

Running unit tests using the Invoke-DevartDatabaseTests cmdlet

The Invoke-DevartDatabaseTests cmdlet allows running tSQLt unit tests against a database.

To run unit tests using PowerShell cmdlet

1. Run Windows PowerShell ISE on your computer.

2. In Windows PowerShell ISE, execute the PowerShell script to run unit tests.

# Defining variables
$ServerName = "JORDANS\MSSQLSERVER2019"
$DatabaseName = "AdventureWorks2019"
$Connection = New-DevartSqlDatabaseConnection -Server $ServerName -Database $DatabaseName  -WindowsAuthentication true
$ReportFile = "D:\Reports\TestReport"

# Running tests
Invoke-DevartDatabaseTests -InputObject $Connection -InstalltSQLtFramework -OutReportFileName $ReportFile -ReportFormat JUnit

Parameters used in the script

Video tutorial

Watch the video tutorial to learn how to use dbForge Unit Test in DevOps process:

• Previous
• Next