In the process of software product development that involves a database, one of the key points is the possibility of continuous delivery of changes from the development environment to the production environment.
Equally important is the fact that more and more people involved in IT-processes start working remotely. In view of this, it is important to adjust the workflow to the fast-changing realities.
In this article, we will talk briefly about the main approach to delivering database changes by means of migration using Devart tools, including the delivery of changes when working remotely.
Delivery of database changes in the deployment pipeline
Comparing two methods of database changes delivery
To start with, there are two methods of database changes delivery:
- The state-based method suggests that database states are stored, but the scripts for a transition from one state to another one are not stored.
- Migration-based database development suggests that the database scripts for a transition from one state to another are stored.
Now, let’s compare the benefits and pitfalls of these two approaches:
| State-based database development | Migration-based database development | |
| Main benefits | Changes can be made right in the required environment, which enables fast customization of any solution and minimal time for the release of changes (new functionality, edits, and updates of the current functionality). |
|
| Main pitfalls |
| As any change has to undergo a chain of actions (development, testing, implementation), customizing the solution and releasing changes (introducing new functionality and updating the current functionality) can take up much time. |
| Use | In rare cases, when the release time of changes is much more expensive than the stability of the entire system (changes are usually introduced directly in the production environment. This is common for immature IT-systems and rare for developed IT-solutions). | When the stability of the current solution is more important than its new or updated functionality(it is more common in developed IT-systems). |
Database states (schemas and reference data) and the scripts of changes are usually kept and versioned in Version Control systems like GIT, SVN, Microsoft Azure DevOps. While the delivery of changes from a database directly to a version control can be implemented through dbForge Source Control SSMS Add-in.
In order to transit from a state-based approach to a migration-based one, you first need to create a baseline schema of the existing database and make further changes in the schema with patches; each of them consists of a migration script 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. In this case, DB comparators are good helpers (for instance SQL Server Schema Synchronization). And then, follow migration-based database development, not allowing to make changes directly in the required environments.
Oftentimes, it is impossible to rid off the state-based approach completely, but we should strive to do this, so that the lifecycle of a product is organized, and the behavior of the system is more stable and foreseeable after making changes. Hence, further on, we will describe migration-based database development.
Continuous database delivery through migration
Initially, to implement a database delivery through migration, you can use the DevOps Automation for SQL Server tool:
Fig. 1 DevOps Automation for SQL Server
Notably, to implement this approach, one needs to activate the DevOps process as all departments need to be involved:
- Development.
- Testing, including load testing.
- Update.
- Deployment.
It is important to note that you do not have to deliver all migrations from one environment to another. That is, you only need to deliver the difference between two databases, which is very easy to define with the help of the Devart SQL diff tool called dbForge Schema Compare for SQL Server.
Fig. 2 SQL Server Schema Synchronization
Another way to determine the difference between database schemas is by using the Visual Studio IDE tool:
Fig. 3 Schema Comparisons using Visual Studio SQL Data Tools
Additionally, it is very convenient to store and manage database schema changes by the special instrumentality of version control such as Source Control for SQL Server:
Fig. 4 Source Control for SQL Server
Still, whatever tool you select to control version changes, it has to meet the requirements of the entire product lifecycle, namely:
- Rollback selected changes.
- Rollforward selected changes.
- View conflicts and resolve them.
- Enable multiple users to work asynchronously with the same code snippet.
- Track changes (date, time, source ( who introduced changes and where).
All the above-mentioned functionalities are available in both SQL Tools and in dbForge Studio for SQL Server:
Fig. 5 dbForge Studio for SQL Server
The first tool is built-in SSMS, and the second one is delivered as a separate visual system used for database development, testing, and administration.
As this approach to the delivery of database changes allows setting up a more predictable and transparent software solution lifecycle, the same approach is better suited for remote work organization.
Next, we will briefly describe the main features of remote work.
Additionally, watch these videos to discover how dbForge products can boost database development.
- How dbForge SQL Complete is involved in the Database DevOps process
- How to import data to SQL Server database with dbForge Data Pump during the DevOps process
- Creating database documentation during the Continuous Integration workflow
- How to automate database schema changes for the CI process during database deployment
- dbForge Source Control in the DevOps pipeline
- Test data generation in the Continuous Integration and Deployment processes
- Unit Testing for SQL Server Database in DevOps process
Delivering changes to databases in remote working conditions
As more and more IT company employees opt for remote work, it became essential to guarantee secure work in databases in remote working conditions.
For that reason, let us consider the scenarios of securing the delivery of changes on the database level.
The following methods are normally used to secure databases in remote working conditions:
- Locating a database server in a secure network so that it has no direct internet access, neither inbound nor outbound (special terminals are configured to access the server).
- Establishing dedicated and encrypted channels between the employee’s hardware and corporate network (often with enhanced security in the form of digital signature and/or certificates).
- In certain instances of point 2, installing special software on the employee’s computer and making a personal USB key to access the system/ corporate network.
But what about the tool for working with databases?
Yes, this tool also has to provide a wide range of possibilities to access data. The dbForge Studio for SQL Server tool through Security Manager offers rich capabilities of managing logins:
Fig. 6 Security Manager
In addition, it stands to mention that dbForge SQL Tools now support the new secure Active Directory authentication (with universal MFA authentication):
Fig. 7 SQL Tools support the Active Directory authentication (with universal MFA authentication)
To clarify, Active Directory (with universal MFA authentication) is an interactive method that supports Azure multi-factor authentication, among other things. Azure MFA both helps secure access to data and applications and satisfies the simple users’ need to sign in. It provides reliable authentication with a number of simple parameters, such as a phone call, a text message, smart cards with a pin code, or a mobile app notification, enabling users to choose a method preferable for them.
You can read more about Active Directory authentication supported by dbForge SQL Tools.
For security reasons, it is common practice that access to an employer’s infrastructure is established through special terminal servers for development, test, and production environments.
Thus, properly organized access to the company’s IT resources does not usually differ much whether it is remote access or access from the office, as any connection is established through special terminal servers.
Conclusion
To properly apply new methods and technologies, database administrators have to consider multiple factors, one of which is whether to use a state-based or a migration-based approach to delivering changes and updates. We have compared the two methods and provided handy tools to organize the continuous delivery of changes in a DevOps environment. By means of these tools, one can speed up the process, eliminate the risks, and secure the database, which is especially crucial for remote working conditions.
