During software product development that involves a database, continuous delivery of changes from the development environment to the production environment is crucial. However, with more people involved in IT processes working remotely, it’s important to adjust workflows to accommodate this changing dynamic.
This article will discuss the primary approach for delivering database changes through migration using Devart tools, with a particular focus on delivering changes while working remotely.
Delivery of changes to the database within the deployment process
An analysis of two approaches to delivering database changes
There are two primary approaches to delivering database changes:
- The state-based approach involves storing database states, but not the scripts needed to transition from one state to another.
- The migration-based approach involves storing database scripts for transitioning from one state to another.
In the following sections, we will compare the advantages and disadvantages of each approach.
|
State-based database development |
Migration-based database development |
|
|
Main benefits |
Modifications can be made directly in the necessary environment, facilitating rapid customization of solutions and reducing the time required for implementing changes such as new features, modifications, and updates to existing functionality. |
● Clear sequencing of scripts for transitioning between states ● Predefined rollback scenarios in case migration changes need to be undone ● Elimination of the need for reverse engineering over time ● Improved performance in different types of testing, reducing the likelihood of significant bugs and risks in the future. |
|
Main pitfalls |
● High likelihood of introducing new bugs and significant risks in the future
● Need to collect and compare database states to templates, followed by generation of migration scripts each time changes are made ● Absence of a rollback scenario in many cases when migration changes are undone ● Necessity of performing reverse engineering as time goes on. |
Since any change must go through a series of steps (such as development, testing, and implementation), customization of solutions and the release of changes (including new features and updates to existing functionality) can be a time-consuming process. |
|
Use |
In rare instances, the cost of releasing changes may be significantly higher than the stability of the entire system. In such cases, changes are typically made directly in the production environment. This approach is more common in immature IT systems and less common for established IT solutions. | In established IT systems, the stability of the current solution often takes priority over introducing new or updated functionality. |
Database states (including schemas and reference data) and change scripts are typically stored and versioned in Version Control systems like GIT, SVN, and Microsoft Azure DevOps. The delivery of changes from a database to a version control system can be achieved using tools such as the dbForge Source Control SSMS Add-in.
To transition from a state-based approach to a migration-based approach, you must first create a baseline schema of the existing database and then make subsequent changes using patches, each of which consists of a migration script for transitioning from one database version to another. To create such a migration script, you need to compare the previous database version with the database where the changes were made. DB comparators, such as SQL Server Schema Synchronization, can be helpful in this process. It is important to follow a migration-based database development approach and avoid making changes directly in the required environments.
Although it may not always be possible to completely eliminate the state-based approach, it is important to strive towards doing so in order to organize the product lifecycle and ensure that the system’s behavior is stable and predictable after changes are made. Therefore, we will further describe migration-based database development.
Continuous delivery of databases through migration
To implement continuous delivery of databases through migration, you can begin by using the DevOps Automation for SQL Server tool. This process requires involvement from all departments, including development, testing (including load testing), updates, and deployment.
Fig. 1 DevOps Automation for SQL Server
It’s important to note that you don’t need to deliver all migrations from one environment to another. Instead, you only need to deliver the difference between two databases. This can easily be defined using tools such as the Devart SQL diff tool, dbForge Schema Compare for SQL Server.
Fig. 2 SQL Server Schema Synchronization
An alternative method for identifying differences between database schemas is to use the Visual Studio IDE tool:
Fig. 3 Schema Comparisons using Visual Studio SQL Data Tools
It’s also highly convenient to store and manage database schema changes using specialized version control tools, such as Source Control for SQL Server:
Fig. 4 Source Control for SQL Server
Regardless of the version control tool you choose, it must meet the requirements of the entire product lifecycle, which include:
- Ability to rollback and rollforward selected changes.
- Ability to view conflicts and resolve them.
- Support for multiple users working asynchronously with the same code.
- Change tracking functionality (including date, time, and source of changes).
All of these features are available in both SQL Tools and dbForge Studio for SQL Server.
Fig. 5 dbForge Studio for SQL Server
The first tool, SQL Tools, is built into SSMS, while the second tool, dbForge Studio for SQL Server, is a separate visual system used for database development, testing, and administration.
This approach to delivering database changes enables a more predictable and transparent software solution lifecycle, making it well-suited for remote work organization.
In the following sections, we will briefly describe the key features of remote work.
Delivery of database changes in remote working conditions
With an increasing number of IT company employees opting for remote work, it has become essential to ensure secure database access in remote working conditions.
To achieve this, the following methods are typically used:
- Locating the database server in a secure network with no direct internet access, and configuring special terminals for server access.
- Establishing dedicated and encrypted channels between the employee’s hardware and corporate network with enhanced security features such as digital signatures and certificates.
- In some cases, installing special software on the employee’s computer and providing a personal USB key to access the system/corporate network.
However, the tool used to work with databases must also provide a wide range of possibilities for accessing data. The dbForge Studio for SQL Server tool, through its Security Manager feature, offers rich capabilities for managing logins.
Fig. 6 Security Manager
It’s also worth mentioning that dbForge SQL Tools now support secure Active Directory authentication with universal MFA authentication.
Fig. 7 SQL Tools support the Active Directory authentication
To provide further clarification, Active Directory with universal MFA authentication is an interactive method that supports Azure multi-factor authentication, among other features. This approach enables secure access to data and applications while also allowing users to choose their preferred authentication method, such as a phone call, text message, smart card with a pin code, or mobile app notification.
For security reasons, it is common practice for access to an employer’s infrastructure to be established through special terminal servers for development, testing, and production environments. Therefore, properly organized access to the company’s IT resources does not typically differ much, whether it’s remote access or access from the office, as any connection is established through special terminal servers.
You can learn more about the Active Directory authentication supported by dbForge SQL Tools on their website.
Conclusion
In order to effectively implement new methods and technologies, database administrators must consider multiple factors, including whether to use a state-based or migration-based approach for delivering changes and updates. We have compared these two approaches and provided useful tools for organizing the continuous delivery of changes in a DevOps environment. By utilizing these tools, it is possible to speed up the delivery process, mitigate risks, and secure the database, which is particularly important in the context of remote work environments.
