Why is database version control necessary?

Source: Huang zhaoji CSDN blog: Why database version control is necessary?Preface

Version Control is very common in the development process, but in the database World, Version Control is second-class or even third-class citizens. When many people develop a database management and application system at the same time, it is very important to monitor the changes of objects such as tables, stored procedures, and views in the database in a reasonable way. Every time you change the database, you need to make a detailed record. Several companies I have worked on have implemented version control in the development process, but the database is blank. during the development process, it often involves adding columns, modifying columns, modifying stored procedures and views on tables. After changes, the definition of previous database objects will be lost, causing a lot of trouble for development, the following describes how to solve the problems caused by no database version control mechanism.

 

Back up database

To solve this problem, we first thought we would back up the database every few days, when you need to find historical database objects, restore the backup database to the test machine and then find the corresponding stored procedures or table definitions. In this way, many problems are discovered. First, it is very troublesome to restore the backup every time you need to view the history. Secondly, with the development, it is hard to remember who modified the database objects at what time, and must restore all the backups within a period of time before searching. This process is not only annoying, but also a waste of time and energy. If the backup interval is too long, the object definition changed during this period cannot be retrieved.

 

Export database objects

The solution was to save the definition of the database object to SVN in the form of. SQL every time the database object was modified. This method was very troublesome. Many times developers are lazy and forget to check in SVN, which may cause a big problem. It is difficult for new team members to understand the ins and outs of version modification. In addition, this method is not intuitive. If you want to see the historical version record of a database object, you need to search for it in SVN, and the display is not intuitive.

Another problem is that the development DBA in the team is very reluctant to use SVN.

 

Store version records to the database

I have seen this method on the Internet. It records database change operations through triggers in the database. As a developer, this method is not very popular. Besides, finding version modification records in the History Table is time-consuming and laborious.

 

Use third-party tools

Now some third-party companies have developed database version Control tools, we have tried to RedGate SQLSource Control (http://www.red-gate.com/products/sql-development/sql-source-control/), SQLSource Control is based on SVN, it is not very suitable for the use of the team, and is not easy to use. The training cost for Team promotion is also relatively high.

Another tool recently used by the Team is an easy-to-use Chinese version of database version control software called SourceSafe for SQL Server (http://www.grqsh.com/products.htm? Tab = sourcesafe-for-SQL-server), the principle of Source Safe for SQL Server is to store the change records in the version library, and regularly import the database-defined scripts to SVN, the history of database versions can be viewed as in SVN, and the training cost for Team promotion is also relatively low. It helped us solve many problems in our team.

 

 

Postscript

Database version seems to be a dispensable process, but if it is done well, it can reduce a lot of trouble in development and implementation, or even in the production environment. Our system is a living example. The methods described in this article come from the experience of previous teams. The development of the development team will experience from childish to mature. Learning from the experience and methodology of mature tools can reduce losses and increase productivity by understanding and solving problems in advance.