Sometimes it is necessary to make manual changes into database environments.

For example, a release went out to PROD environment, however, there was an issue which impacted the PROD database. The issue is immediately root caused and the solution is applied manually to PROD database. How then do you make Datical aware of those manual changes?

Note that any manual changes made to a Datical-managed environment should ultimately be deployed to all environments in the pipeline. This will allow the pipeline to remain in sync with respect to changes deployed over time.

Solutions

There are three different paths to accomplishing this:

1. No manual changes - fix issues like all other changes

This is applicable when you have the sufficient time window to root cause the issue, script it, commit into SCM, package and deploy into your pipeline just like normal changes/ This means you will always leverage your automation framework.

2. Use a "hotfix" process to package changes and deploy directly to STAGE and PROD.

This allows you to bypass deploying into your DEV and QA environments and hotfix changesets can be deployed directly to STAGE and PROD. You will be responsible for eventually deploying your hotfix changes back into DEV and QA environments, typically performed after hotfix changes have released to PROD.

3. An emergency fix is necessary

You only have a small time window to fix the PROD database. In this case, you will root cause your issues, test your fix in STAGE and once validated, deploy to PROD.

Start by capturing the hotfix in a SQL script. Commit the script into SCM so that it can be packaged, converted into a changeset and deployed (i.e., backfilled) into all other environments. Since PROD environment already contains the change, there is no need to deploy the changeset there. Instead, you will use Datical's "Change Log Sync" feature to mark the hotfix changeset as EXECUTED in PROD environment. This will create an entry in PROD's tracking table (DATABASECHANGELOG) and the audit database. This results in Datical becoming aware that the change from hotfix changeset already exists in PROD.

Step-by-step guide

For the purpose of the document, we will assume that the Datical project has the following pipeline (see table below). The emergency fix is captured in the script called "hotfix_aug.sql". Changes from this script have been manually executed in STAGE and PROD environments. We now need to package this script so that it can be deployed to REF1, DEV and QA environments.

	REF1	DEV	QA	STAGE	PROD
hotfix_aug.sql	UNDEPLOYED	UNDEPLOYED	UNDEPLOYED	MANUALLY DEPLOYED	MANUALLY DEPLOYED
label used for release	release/1.0.0
label used to hotfix	hotfix/1.0.0

Package Hotfix Script

Commit your hotfix script in SQL repository. In order for it to obtain the correct hotfix label (e.g., "hotfix/1.0.0"), use the commit message where you can specify the label in square brackets. For example:

git commit -m "Hotfix for release 1.0.0 - [hotfix/1.0.0]"

Deployment Packager will automatically pick up the string inside square brackets and apply it as a label to changesets resulting from scripts that are part of that commit. As a result of running Packager, Datical will generate a new artifact version for you to deploy to your database environments.

Deploy Hotfix Changesets

Resulting changesets can be deployed using the label "hotfix/1.0.0" to DEV and QA environments using your deployment automation framework. You could have a separate process for deploying hotfix changes to STAGE and PROD. Or your deployment automation framework could be designed to automatically detect hotfix changes deploying to STAGE and PROD and choose the "Change Log Sync" method.

In your automation system (Jenkins, Bamboo, UrbanCode, etc.), create a new job or process which will perform following operations:

Download the latest version of the Datical artifact
Run "hammer changelogSync" command. This command takes three arguments:
1. dbDef - This is the name of the environment where changes are to be marked as executed, e.g., "STAGE" or "PROD"
2. pipeline - This is the Datical pipeline which will be associated with the changelogSync for auditing purposes.
3. labels - This is the specific label you want to register as executed

Here is the complete sample script with some additional code for before and after status:

"hammer statusDetails" used to status your environment before and after applying the "changelogSync" command (note: use of REM and environment variables "%<variableName>%" are specific to Windows platform):

Mark Changes as Deployed

echo -------------------------------
set PATH=%PATH%;C:\apps\Datical\repl
hammer statusDetails STAGE
hammer changelogSync STAGE --pipeline="pipelineName" --labels="hotfix_script_name.sql"
hammer statusDetails STAGE

hammer statusDetails PROD
hammer changelogSync PROD --pipeline="pipelineName" --labels="hotfix_script_name.sql"
hammer statusDetails PROD

echo -------------------------------

Here is how your automation job would report the outcome (notice item #12 in statusDetails output which indicates the hotfix entry made to STAGE and PROD) :

get status of STAGE before changeLogSync

Defaulting contexts to STAGE from STAGE
Defaulting labels to release/1.0.0 from STAGE

Database:  STAGE

Deployed Change Sets
    ____________________________________________________________________________________________________________________________________________________________________________
    | Change Set                                           | Contexts               | Labels                                          | DBMS  | Reason| Comments                |
    |===========================================================================================================================================================================|
 1. | Temp_Table                                           |                        | temp_table.sql,release/1.0.0,release/2.0.0      |       | New   | createTable             |
 2. |                                                      |                        |                                                 |       |       | tableName=TEMP_TABL     |
 3. | TAG_q4-2017,createAAA_TESTA1.sql                     |                        | release/1.0.0,createaaa_testa1.sql              |       | New   | tagDatabase             |
 4. | apc1-1 (createTable tableName=AAA_TESTA1)            |                        | release/1.0.0,createaaa_testa1.sql              |       | New   | createTable             |
 5. |                                                      |                        |                                                 |       |       | tableName=AAA_TESTA     |
 6. | TAG_q4-2017,createAAA_TESTA2.sql                     |                        | release/1.0.0,createaaa_testa2.sql              |       | New   | tagDatabase             |
 7. | c5cl-1 (createTable tableName=AAA_TESTA2)            |                        | release/1.0.0,createaaa_testa2.sql              |       | New   | createTable             |
 8. |                                                      |                        |                                                 |       |       | tableName=AAA_TESTA     |
 9. | sxon-1 (createTable tableName=AAA_TESTA3)            |                        | release/1.0.0,createaaa_testa3.sql              |       | New   | createTable             |
10. |                                                      |                        |                                                 |       |       | tableName=AAA_TESTA     |
11. | 20170731104511745_Insert_dml_AAA_TESTA3              |                        | release/1.0.0,insert_dml_aaa_testa3.sql         |       | New   | DDL and DML scripts     |

Undeployed Change Sets
  NONE

Ignored Change Sets
  NONE


Saved status information to AuditDB for project 'PatientPortal'.
Executing changeLogSync for pipeline 'currentRelease'
Defaulting contexts to STAGE from STAGE
Updating AuditDB database for project PatientPortal
Saved status information to AuditDB for project 'PatientPortal'.
changelogSync to 'STAGE' successful. All applicable changesets marked as deployed.
get status of STAGE after changeLogSync
Defaulting contexts to STAGE from STAGE
Defaulting labels to release/1.0.0 from STAGE

Database:  STAGE

Deployed Change Sets
    ____________________________________________________________________________________________________________________________________________________________________________
    | Change Set                                           | Contexts               | Labels                                          | DBMS  | Reason| Comments                |
    |===========================================================================================================================================================================|
 1. | Temp_Table                                           |                        | temp_table.sql,release/1.0.0,release/2.0.0      |       | New   | createTable             |
 2. |                                                      |                        |                                                 |       |       | tableName=TEMP_TABL     |
 3. | TAG_q4-2017,createAAA_TESTA1.sql                     |                        | release/1.0.0,createaaa_testa1.sql              |       | New   | tagDatabase             |
 4. | apc1-1 (createTable tableName=AAA_TESTA1)            |                        | release/1.0.0,createaaa_testa1.sql              |       | New   | createTable             |
 5. |                                                      |                        |                                                 |       |       | tableName=AAA_TESTA     |
 6. | TAG_q4-2017,createAAA_TESTA2.sql                     |                        | release/1.0.0,createaaa_testa2.sql              |       | New   | tagDatabase             |
 7. | c5cl-1 (createTable tableName=AAA_TESTA2)            |                        | release/1.0.0,createaaa_testa2.sql              |       | New   | createTable             |
 8. |                                                      |                        |                                                 |       |       | tableName=AAA_TESTA     |
 9. | sxon-1 (createTable tableName=AAA_TESTA3)            |                        | release/1.0.0,createaaa_testa3.sql              |       | New   | createTable             |
10. |                                                      |                        |                                                 |       |       | tableName=AAA_TESTA     |
11. | 20170731104511745_Insert_dml_AAA_TESTA3              |                        | release/1.0.0,insert_dml_aaa_testa3.sql         |       | New   | DDL and DML scripts     |
12. | hotfix_aug                                           |                        | hotfix/1.0.0                                    |       |       | createTable             |
13. |                                                      |                        |                                                 |       |       | tableName=AAA_hotfix    |

Undeployed Change Sets
  NONE

Ignored Change Sets
  NONE


Saved status information to AuditDB for project 'PatientPortal'.

get status of PROD before changeLogSync
Defaulting contexts to PROD from PROD
Defaulting labels to release/1.0.0 from PROD

Database:  PROD

Deployed Change Sets
    ____________________________________________________________________________________________________________________________________________________________________________
    | Change Set                                           | Contexts               | Labels                                          | DBMS  | Reason| Comments                |
    |===========================================================================================================================================================================|
 1. | Temp_Table                                           |                        | temp_table.sql,release/1.0.0,release/2.0.0      |       | New   | createTable             |
 2. |                                                      |                        |                                                 |       |       | tableName=TEMP_TABL     |
 3. | TAG_q4-2017,createAAA_TESTA1.sql                     |                        | release/1.0.0,createaaa_testa1.sql              |       | New   | tagDatabase             |
 4. | apc1-1 (createTable tableName=AAA_TESTA1)            |                        | release/1.0.0,createaaa_testa1.sql              |       | New   | createTable             |
 5. |                                                      |                        |                                                 |       |       | tableName=AAA_TESTA     |
 6. | TAG_q4-2017,createAAA_TESTA2.sql                     |                        | release/1.0.0,createaaa_testa2.sql              |       | New   | tagDatabase             |
 7. | c5cl-1 (createTable tableName=AAA_TESTA2)            |                        | release/1.0.0,createaaa_testa2.sql              |       | New   | createTable             |
 8. |                                                      |                        |                                                 |       |       | tableName=AAA_TESTA     |
 9. | sxon-1 (createTable tableName=AAA_TESTA3)            |                        | release/1.0.0,createaaa_testa3.sql              |       | New   | createTable             |
10. |                                                      |                        |                                                 |       |       | tableName=AAA_TESTA     |
11. | 20170731104511745_Insert_dml_AAA_TESTA3              |                        | release/1.0.0,insert_dml_aaa_testa3.sql         |       | New   | DDL and DML scripts     |

Undeployed Change Sets
  NONE

Ignored Change Sets
  NONE


Saved status information to AuditDB for project 'PatientPortal'.

Executing changeLogSync for pipeline 'currentRelease'
Defaulting contexts to PROD from PROD
Updating AuditDB database for project PatientPortal
Saved status information to AuditDB for project 'PatientPortal'.
changelogSync to 'PROD' successful. All applicable changesets marked as deployed.

get status of PROD after changeLogSync
Defaulting contexts to PROD from PROD
Defaulting labels to release/1.0.0 from PROD
Database:  PROD

Deployed Change Sets
    ____________________________________________________________________________________________________________________________________________________________________________
    | Change Set                                           | Contexts               | Labels                                          | DBMS  | Reason| Comments                |
    |===========================================================================================================================================================================|
 1. | Temp_Table                                           |                        | temp_table.sql,release/1.0.0,release/2.0.0      |       | New   | createTable             |
 2. |                                                      |                        |                                                 |       |       | tableName=TEMP_TABL     |
 3. | TAG_q4-2017,createAAA_TESTA1.sql                     |                        | release/1.0.0,createaaa_testa1.sql              |       | New   | tagDatabase             |
 4. | apc1-1 (createTable tableName=AAA_TESTA1)            |                        | release/1.0.0,createaaa_testa1.sql              |       | New   | createTable             |
 5. |                                                      |                        |                                                 |       |       | tableName=AAA_TESTA     |
 6. | TAG_q4-2017,createAAA_TESTA2.sql                     |                        | release/1.0.0,createaaa_testa2.sql              |       | New   | tagDatabase             |
 7. | c5cl-1 (createTable tableName=AAA_TESTA2)            |                        | release/1.0.0,createaaa_testa2.sql              |       | New   | createTable             |
 8. |                                                      |                        |                                                 |       |       | tableName=AAA_TESTA     |
 9. | sxon-1 (createTable tableName=AAA_TESTA3)            |                        | release/1.0.0,createaaa_testa3.sql              |       | New   | createTable             |
10. |                                                      |                        |                                                 |       |       | tableName=AAA_TESTA     |
11. | 20170731104511745_Insert_dml_AAA_TESTA3              |                        | release/1.0.0,insert_dml_aaa_testa3.sql         |       | New   | DDL and DML scripts     |
12. | hotfix_aug                                           |                        | hotfix/1.0.0                                    |       |       | createTable             |
13. |                                                      |                        |                                                 |       |       | tableName=AAA_hotfix    |

Undeployed Change Sets
  NONE

Ignored Change Sets
  NONE


Saved status information to AuditDB for project 'PatientPortal'.

Related articles

Browser not supported