How dbForge Schema Compare
is involved in the DevOps process

The dbForge Schema Compare tool plays an important role in the process of creation a database from state-based scripts and can be used for both state-based and migration-based database deployment. The article describes how Schema Compare can be used in the process of database development as well as in automatically adjusted CI processes.

Database Development Approaches

When you decide to include the database you are developing in the CI process, you need to choose how you are going to store the database model and accordingly how you are going to restore it from scripts for testing and other operations.

There are two main methods of database development and deployment: state-based method and migration scripts method. The choice of the method will affect the database change management.

State-based approach

If you are developing a project from scratch and the database has not yet been delivered to the customer, then, of course, the state-based approach is the most preferable in this case. Indeed, one of the important advantages of this approach is that you can see the final set of entities at any stage of development and you do not need to make a lot of separate changes (scripts) for the same entity. Each table, stored procedure, function, view, or trigger is stored in a separate file in its final form.

If you choose a state-based approach, during the CI process the database is deployed from the script folder with the help of Schema Compare. In the scripts folder, database objects are saved as state-based scripts.

Following this approach, when developers need to make changes to the database schema, they deploy the database locally on the server and make the necessary changes to the database schema. Then they use the Schema Compare tool to synchronize the changes with the script folder and finally using version control they upload the changes to the server.

Migration-based approach

In cases, when a database has already been delivered to the customer, subsequent improvements to it will most probably be implemented using migration scripts. Migration scripts allow you to flexibly change not only the database schema objects but also the data itself. And this is the best choice because synchronization tools do not allow to simultaneously change the database schema objects and migrate data between table columns.

In the migration scripts development approach, during the daily CI process, the following basic operations are most likely to be performed:

database testing
creating a database from state-based scripts (in the form it has been delivered to the customer)
populating a database with the help of a data generator (emulation of adding customer's data on their side)
updating a database using migration scripts (your new features and improvements)

Which approach to choose?

Depending on the project development phase, its requirements or other preconditions, one or another database development method is chosen. Thereof, the corresponding means of the database deployment from scripts during the CI process are selected. And only you decide which method is preferable for you.

Using dbForge Schema Compare in the CI process

As mentioned above, the Schema Compare tool can be actively used in both state-based and migration scripts development approaches. Let’s have a look at how Schema Compare is used to create a database during the CI process.

Creating a database from state-based scripts is the primary purpose of the Schema Compare tool in the CI process. In this case, a database is restored from scripts and each script comprises a separate file with a creation script for a particular database object (a table, a stored procedure, a function, a trigger, or other objects).

The figure below shows a database deployed using the state-based method and saved as a script folder with the help of Schema Compare.

This folder is under version control. Every time a new CI process starts, this folder is pulled from the remote repository to the local test machine and a database is created from it on the test SQL server using Schema Compare.

The following is the cmdldet script for creating a database on an SQL server from a script folder. This cmdldet script is a part of a general script in the CI process:

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

$scriptFolder = "D:\ScriptsFolder\"

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

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

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

Running cmdlet Invoke-DevartDatabaseBuild encapsulates the launch of the dbForge Schema Compare tool.

How can developers benefit from dbForge Schema Compare

Every time developers make changes to the database schema on the SQL server, they need to make changes to the script folder. In fact, they need to synchronize the database with the script folder, which is under the source control system.

01

To synchronize the required database with the script folder, right-click it and proceed to Schema Compare on the shortcut menu.

02

Next, fill in the required fields in the wizard that opens.

03

In the comparison results window, the developer can see all the changes made to the database objects.

04

Then press F8, select Update the scripts folder and start synchronization.

05

After that, you can see all the necessary changes in the folders.

Conclusion

As you can see from the description, dbForge Schema Compare plays a key role in the DevOps process, specifically in database development and the CI process during the database deployment.

Learn about other dbForge tools involved in collaborative
database development and deployment