Why database versioning is a must

SOURCE : Huang ji csdn Blog: Why database versioning is necessaryPreface

Versioning is common in the development process, but in the world of databases, 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 use a reasonable method to monitor the changes of the tables, stored procedures and views in the database, every time the database is changed, the detailed records need to be made, and the problem is slightly inattentive. I have worked in several companies in the development process version control is done very well, but the database is a blank, in the development process, often involves adding columns on the table, modify columns, modify stored procedures and views, changes, the previous database object definition is lost, the development caused a lot of trouble, Let's talk about my idea of solving the hassle of not having a database version control mechanism.

backing up the database

To solve this problem, our first idea is to back up the database, back up the database every few days, restore the backup library to the test machine when you need to find the historical database object, and then find the corresponding stored procedure or table definition. This way to find a lot of problems in the process, the first time you need to view the history of the time to restore the backup is very troublesome, followed by the development, it is difficult to remember who in what time to change what database objects, must be a period of time to restore the backup and then to find. This process is not only annoying, but also a waste of time and energy, if the backup interval is too long, the changes in the object definition will not be retrieved.

Export Database Objects

The solution to this problem is that every time you modify a database object, the definition of the database object is stored in. sql to SVN, which is a very cumbersome way. Many times developers are lazy and forget to check in SVN, which can cause a lot of problems. It's also hard to see the ins and outs of the new team. In addition, this method is very not intuitive, if you want to see a database object historical version records, but also to find in SVN, the display is very not intuitive

Another problem is that development DBAs in the team are very unaccustomed to using SVN.

storing version records in a database

This is what I have seen on the internet, is that in the database through the trigger for the database change operations to record, as the development of people, the way is not very like. Besides, it is a time-consuming and laborious thing to find a version of the history table to change records.

using third-party tools

Now some third-party companies have developed version control tools for databases, and we have previously tried Redgate's Sqlsource control (http://www.red-gate.com/products/sql-development/ sql-source-control/), the Sqlsource control principle is based on SVN, is not very consistent with the team's usage habits, ease of use is not enough, in the team to promote training costs are relatively high.

Another tool that the team is using recently is an easy-to-use Chinese version of the 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 change records in the repository, periodically import database-defined scripts into SVN, and the database version history can be viewed as if it were in SVN. The training cost of the team promotion is also relatively low. Help us solve a lot of problems in the team.

PostScript

database versioning may seem like an optional process, but it's done to reduce the hassle of developing and implementing even the production environment, and our system is a living example. The approach described in this article is from previous team experience. The development team will experience from naïve to mature, learn from the experience and methodology of mature tools, advance understanding and solve problems can reduce loss and increase productivity.