Continuous Delivery of Database Changes to SQL Server When Working Remotely

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:

1. 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.
2. 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).	there is a clear order of certain scripts for changes from one state to another; there is a predefined rollback scenario in case migration changes are undone; with time, there is no need to perform reverse engineering;  the solution undergoes different kinds of tests much better, which minimizes the occurrence of serious bugs and risks in the future
Main pitfalls	there is a high probability of new bugs and serious risks in the future;  every time you need  to collect database states and compare them to the template, after which you need to generate migration scripts;  there is often no rollback scenario in case migration changes are undone;  you need to perform reverse engineering with time	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:

1. Development.
2. Testing, including load testing.
3. Update.
4. 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 dbForge Schema Compare for SQL Server tool:

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:

1. Rollback selected changes. 
2. Rollforward selected changes. 
3. View conflicts and resolve them. 
4. Enable multiple users to work asynchronously with the same code snippet. 
5. 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.

 

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:

1. 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).
2. 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).
3. 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.