Database Upgrade maintenance strategy in software development stage

Database Upgrade maintenance strategy in software development stage

Why maintain an upgrade

1.1 Status and emerging issues

If you develop a project that does not use the database, then you do not have to maintain the database.

If you develop a project, use the database, but never modify, then the database maintenance is relatively simple.

When you develop a large project, the database has many tables, views, stored procedures, the database is divided into several, the development of the team is very large, the development cycle is very long, due to the business requirements of the database of relatively large changes (no good DBA, this is already the fact, do not work on this aspect), What should be the strategy for maintaining the database at this time?

Let's not say what strategy we are using to see what kind of problems we will encounter:

A. Every time a version is issued, the pace or data of the database is always a key to dragging B. Who has changed the view? C. Who changed the field? Who deleted the table? Who added the fields? D. What fields have been modified since the last version was issued? E. The database running on the customer's official machine is V1.0, but our new version is V3.0, what is the database changed? F. It is now a V2.0 program, but test to test the version of V1.0, then? ...

Why is there such a problem? What should we do about these problems?

The cause of this problem is very simple, we do not do a good job of database version management, there is no unified access to the database to modify the operation, everyone in need, all their own in the database to add, delete or modify some tables, trying, stored procedures, resulting in chaotic effects.

The root of the problem has been found, then there is no problem, after all, there is no solution to the problem, the key is to stop the problem on the basis of dealing with the problem, so the problem has no end.

1. The Library's modification work has a unified entry point, by one person or several people to cooperate, but to ensure that the only one copy of the document (can be backed up, preferably a person to deal with, if several people to work together, it is necessary to ensure that the operation of the same document). 2. Develop a backup and restore strategy for the database, there is no need to back up the database every day, but every time the release or significant changes in the database must be backed up, while the backup strategy, contains the database backup address, backup rules (such as by time or version name). 3. Starting from the initial prototype of the database, establish a tracking database change record of the upgrade program, the program can be read every time the script (if the script, script naming is critical, after all, we have to ensure that each upgrade needs to execute the script), There is also a clumsy but more effective way to write scripts based on time, version, maintenance of people maintenance records and so on to the program, but to constantly maintain the database upgrade procedures, but the small cost can bring good results. 4. The establishment of strict database modification system, if there is no system, all the preparation is useless, everyone still go their own way, modified or not to notify the entrance, with no maintenance.

1.2 Analysis

Starting with the structure of the database, we know that the structure of the database is: two-dimensional tables, views, stored procedures, functions, indexes, and so on, some of them are independent, some interdependent relationship exists, together constitute a complete database.

1.2.12-dimensional table

The simplest database is composed of a number of two-dimensional tables, as long as the maintenance of each table to maintain the entire database, then we maintain the basic unit is a two-dimensional table. The life cycle of a two-dimensional table is roughly divided into prototype period and upgrade period. The so-called prototype period is the table structure we identified when we originally designed the database, which is to create and initialize the table. The upgrade period is the process of adding, deleting, and modifying the structure of the table. As can be seen from these two points, we only need to maintain a complete table to create and initialize the data SQL script, and record each time the table's modification record. For each modification we are going to update our create and initialize data SQL script to maintain his integrity

1.2.2 View

Views depend on one or more two-dimensional tables, which means that the basis of view maintenance is that the two-dimensional table is first complete. So the view is much closer to the maintenance of the two-dimensional table, which in his lifetime also maintains the complete creation of SQL scripts, and does not need to be documented every time the script is modified.

1.2.3 Stored procedures and functions

After DDD, there are not many interfaces for this database feature, but it is also a very important constituent element. The maintenance of stored procedures should be relatively simple, we do not need to record his change process, only need to maintain his full definition of SQL scripts, functions and stored procedures, they are relatively independent on the basis of the table, only need to maintain his SQL integrity.

Second how to maintain the upgrade

With some of the status quo mentioned earlier, the cause of the problem, as well as some analytical ideas, how can we maintain the database upgrade?

The first database upgrade to be resolved here is in the development phase, because the development phase of the database is in a very unstable period, and his changes are frequent. Our idea of maintaining a database:

1) Database changes are necessary, that is, on the basis of the prototype does not meet the needs of the change to upgrade

2) In each change process need to ensure that the entire table, view, stored procedures, functions are complete, and then in order to ensure that our database is complete, where the integrity refers to the creation of a database of the initialization SQL script, he includes the creation of statements and initialization data statements, and need to ensure their sequential relationships and dependencies.

3) The upgrade of the database is divided into initialization and patch upgrade, so-called initialization is to execute a database initialization SQL script, patch upgrade in the initialization process of the database based on some of the column changes.

4) Change, refers to the table, the view of the field of the increase, deletion, modification of records, each change should ensure that his impact is minimal, because the database may already have some data. Also change the bug that the SQL script should be minimized.

Three Database Upgrade maintenance implementation

3.1 SQL Script record

Every table, every view, stored procedure, function has and only one SQL create and initialize script, create a folder with table name, view name, stored procedure name, function name, each change is placed under the respective folder with a date name, only one change file per day. The init script has the same name as the folder.

3.2 Programmatic upgrade

Develop a desktop program that provides initialization of the database and upgrade functionality. Where the initialization database is used to create and initialize the database. The upgrade feature detects whether or not an upgrade script is detected during each startup of the program to prompt the user if they need to upgrade the prompt can be scanned by the program whether the SQL script file is consistent with the last upgraded script file if the inconsistency prompts for an upgrade to achieve a precise change upgrade patch execution.

3.3 SQL Script Source Hosting

Save the SQL script as a text file, but hosted in TFS, each SQL Footstep internally writes the reason for the SQL patch, the time, and who wrote it. And the file is saved in the VS project as a assets static resource, and in vs it modifies his property to not compile, and each time it is regenerated.

3.4 Use by users

When the user first uses the program, it finds the upgrade in the deployed version and opens the execution initialization database. For each subsequent deployment version, only need to open the upgrade program to see if there is an upgrade prompt, if there is an upgrade, if there is a bug in the process of upgrading, you can find the SQL execution exception corresponding SQL writer in the bug log file, and then tell him the bug, let him fix, re-release.

Database Upgrade maintenance strategy in software development stage