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.
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.
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:
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