Integrate Schema Compare into DevOps

dbForge Schema Compare for SQL Server can be directly involved in the Continuous Integration (CI) process via the PowerShell cmdlets that can automate the synchronization of database schema changes during database development.

To start using Schema Compare in the DevOps process, make sure that:

  • dbForge DevOps Automation for SQL Server and dbForge Schema Compare for SQL Server are installed on your computer. For more information about the installation of dbForge Schema Compare, refer to Installation.
  • Your PowerShell version is 3.0 or higher.

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.

During the CI process, dbForge Schema Compare can be used to create databases from state-based scripts either using the state-based or migration scripts approaches.

Workflow

When the CI process starts, a script folder is retrieved from the remote repository to your local machine. After that, you create a database from this script folder locally using the Invoke-DevartDatabaseBuild cmdlet that triggers the launch of Schema Compare.

Each time you make changes in the database, dbForge Schema Compare needs to synchronize the changes with the script folder. For this, you open the tool, run the New Schema Comparison wizard, set up a new schema comparison as a source and a scripts folder as a target. Then, specify all the necessary options and click Compare. In a new SQL document that opens, click Synchronize objects to the target database to start the synchronization process and update the scripts folder with the latest changes. For more information, see the Synchronizing scripts folders section in Select synchronization output.

The guide covers the following topics:

Install 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, you need to sign up.

3. After the download is complete, navigate 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 all SSMS instances are closed.

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

Note

If you have not installed dbForge Schema Compare for SQL Server yet, in this step select the dbForge Schema Compare for SQL Server checkbox 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.

Installing dbForge DevOps Automation PowerShell for SQL Server

To check the full list of requirements to run dbForge DevOps Automation, refer to Requirements and compatibility.

Create a database using the Invoke-DevartDatabaseBuild cmdlet

The Invoke-DevartDatabaseBuild cmdlet creates the database on the SQL Server from the scripts folder.

To create a database using the cmdlet:

1. Run Windows PowerShell ISE on your computer.

2. In Windows PowerShell ISE, execute the following PowerShell script.

# Variables
$serverName = "SQLEXPRESS15"
$databaseName = "AdventureWorks2019"

$scriptFolder = "D:\ScriptsFolder\"

# Create a database connection
Write-Host "Creating database connection..."
$connection = New-DevartSqlDatabaseConnection -Server $serverName
-Database $databaseName -WindowsAuthentication $true

# Test a database connection
Write-Host "Testing database connection..."
Test-DevartDatabaseConnection -Connection $connection;

# Recreating the database
Write-Host "Recreating database..."
Invoke-DevartDatabaseBuild -SourceScriptsFolder $scriptFolder -Connection $connection

Parameters used in the script

Parameter Description
-Connection Connection to the server on which the database will be created.
-SourceScriptsFolder Folder with scripts from which the database will be created.

See also: How to automatically track database schema changes with PowerShell scripts

Video tutorial

Watch the video tutorial to learn how to use dbForge Schema Compare in DevOps process: