Techniques for automated database deployment

Patent metadata

Field	Value
Publication number	US-9946534-B1
Application number	US-201614996682-A
Country	US
Kind code	B1
Filing date	Jan 15, 2016
Priority date	Jan 15, 2016
Publication date	Apr 17, 2018
Grant date	Apr 17, 2018

Abstract

New techniques for automated database deployment are disclosed. Change SQL files intended for any target database are checked into a designated source code control repository. A pre-validation process is applied to the proposed changes and reports all errors therein, not just the first error found. A tracking mechanism may find and report changes that have been done against a database since the last deployment, including changes applied without following source control procedure. All recent SQL files since previous promotion can be automatically gathered and built into a master run book SQL for execution in a correct sequence. Backout SQL may also be automatically generated and validated. A pre-test or mock deployment may be performed during regular business hours against an automatically generated copy of the target database. With these techniques, deployments and maintenance of databases become more efficient, and failure rates and failover responses could be significantly improved.

First claim

The invention claimed is:

1. A computer-implemented method for automated database deployment, the method comprising automatically performing the steps of:
retrieving, from a first designated repository location, one or more proposed change files for a target database, the proposed change files specifying one or more changes to be applied to data content of the target database;
coordinating the proposed change files into an execution sequence;
validating the proposed change files by executing the proposed change files, against a copy of said target database, according to the execution sequence; and
generating a master execution file by sequencing individual validated change files, wherein the master execution file, when executed, applies validated changes to the data content of said target database.

2. The computer-implemented method of claim 1 , further comprising:
validating the generated master execution file.

3. The computer-implemented method of claim 1 , further comprising:
automatically generating a backout execution file that, if executed against said target database, will undo the changes applied to said target database by the master execution file.

4. The computer-implemented method of claim 3 , further comprising:
validating the generated backout execution file to confirm its execution will undo the changes applied to said target database by the master execution file.

5. The computer-implemented method of claim 1 , further comprising:
generating a report of execution errors of a proposed change file.

6. The computer-implemented method of claim 1 , further comprising:
generating a report of parsing errors of a proposed change file.

7. The computer-implemented method of claim 1 , further comprising:
generating a report of any cross-impact among the proposed change files.

8. The computer-implemented method of claim 1 , further comprising:
automatically creating the copy of said target database for validation of the proposed change files.

9. The computer-implemented method of claim 1 , wherein, after each execution error, the copy of said target database is restored to a previous state before said execution error.

10. The computer-implemented method according to claim 1 , further comprising:
moving the proposed change files that pass the validation to a second designated repository location; and
leaving the proposed change files that fail the validation in the first designated location and generating a notice to a developer associated with the failed files.

11. The computer-implemented method according to claim 10 , further comprising:
automatically retrieving the individual validated change files from the second designated repository location.

12. The computer-implemented method of claim 1 , further comprising:
automatically validating each proposed change file upon checking said each proposed change file into the first designated repository location.

13. The computer-implemented method of claim 1 , wherein the proposed change files are automatically collected and validated in batch on a nightly basis or other periodic basis.

14. The computer-implemented method of claim 1 , wherein the proposed change files are in a format of Structured Query Language (SQL).

15. The computer-implemented method of claim 1 , wherein said target database is in an environment selected from a group consisting of: a development (DEV) environment, a quality assurance (QA) environment, a user acceptance testing (UAT) environment, a production (PROD) environment, and a performance testing (PERF) environment.

16. A computer-implemented system for automated database deployment, the system comprising:
at least one storage device having a first designated repository location;
at least one computer or server communicatively coupled to said at least one storage device and configured to execute one or more codes for:
retrieving, from said first designated repository location, one or more proposed change files for a target database, the proposed change files specifying one or more changes to be applied to data content of the target database;
coordinating the proposed change files into an execution sequence;
validating the proposed change files by executing the proposed change files, against a copy of said target database, according to the execution sequence; and
generating a master execution file by sequencing individual validated change files, wherein the master execution file, when executed, applies validated changes to the data content of said target database.

17. A non-transitory computer readable medium for automated database deployment, the non-transitory computer readable medium having code for causing a computer or server to automatically perform the steps of:
retrieving, from a first designated repository location, one or more proposed change files for a target database, the proposed change files specifying one or more changes to be applied to data content of the target database;
coordinating the proposed change files into an execution sequence;
validating the proposed change files by executing the proposed change files, against a copy of said target database, according to the execution sequence; and
generating a master execution file by sequencing individual validated change files, wherein the master execution file, when executed, applies validated changes to the data content of said target database.

Assignees

• Jpmorgan Chase Bank Na

Inventors

• Thompson Keith
• Busch Russell T

Classifications

• G06F16/2365 Primary

  Ensuring data consistency and integrity · CPC title

• G06F16/214

  Database migration support · CPC title

• G06F8/658 Primary

  Incremental updates; Differential updates · CPC title

• G06F17/30371

  Physics · mapped topic

• G06F8/71 Primary

  Version control (security arrangements therefor G06F21/57); Configuration management · CPC title