Managing the different stages of database development can be challenging due to the many objects that are actively being created and altered, the ability to view the change history of multiple changes on a single object or at a schema level, and the need to be able to select a version to deploy or roll back to.
For this, DBmaestro has implemented the Label Entity. A ‘label’ is a point-in-time reference to a state of the schema, compounded of all the checked-in changes at that point in time.
This post will explain the Label Entity, as well as the different setups referring to labels, revisions and live. It will also demonstrate how to best utilize the Label Entities as a baseline.
As far as DBmaestro is concerned, there are several different stages in a database change cycle.
Once a change has been compiled on the database, a new object has been created, or an object has been altered, that change becomes part of the “LIVE” settings. This setting refers to any change that is present on the database and has been compiled. It acts as a distinction for DML commands on tables that have their “Content Management” enabled.
After the change is checked into DBmaestro or the new object is added, that change creates a new “REVISION” for the object. Each revision is saved in the DBmaestro repository and it is possible to view the revision history of an object. You can also select any two revisions and compare the changes between them, or select a single revision and compare it to the live version.
Applying a “LABEL” on a database will create a point-in-time reference to the latest checked-in revision for all the objects under the DBmaestro source control management for that database.
Let’s take the following example:
- Create table Table1 (I number).
- Add Table1 to source control.
- Create table Table2 (I number).
- Apply Label1.
- Add Table2 to source control.
- Apply Label2.
- Check out Table 1.
- Alter Table1 add (J number).
- Check in Table1.
- Check out Table 1.
- Alter Table1 add (K number).
- Apply Label3.
Live includes: Table1 (I number, J number, K number) and Table2 (I number). This is because those are the changes that have been compiled to the database.
Label1 includes: Table1 (I number). This is due to the fact that even though Table2 existed, it wasn’t part of the source control.
Label2 includes: Table1 (I number) and table2 (I number).
Label3 includes: Table1 (I number, J number) and Table2 (I number). Since the new column K wasn’t checked in, it wasn’t included in the label.
The latest revision for the database will include: Table1 (I number, J number) and Table2 (I number). This is the last checked-in revision for each object.
Understanding the differences between the settings allows great flexibility in building the deployment scripts.
The baseline entity came to resolve a very problematic issue with the two-way compare. In most cases, comparing the source and target will highlight differences. However, it will also lack the knowledge of whether the change happened on the source or on the target, which usually results in the source overriding the target. But what happens when something was deliberately changed on the target? For starters, we need to know that this change happened and we also need to be able to preserve that change.
This is where the baseline comes into play, evolving the two-way compare to a three-way compare. This entails ensuring that the change detection not only works between the source and the target, but also to a third schema, which serves as an “anchor” for both the source and target. This 3rd schema is a “label,” depicted as the baseline for the impact analysis.
The baseline allows you to identify not only the differences between the source and the target, but also provides the crucial information of whether the change occurred on the source or on the target. It also is able to identify the cases of a configuration drift and conflict when the same object was changed both on the source and the target.
A baseline can be any label entity, however, usually the label will be chosen from the target. In many cases, a third schema can be set up as a static point to a version upload, and the label on that third schema can be used as well. This is a very common setup when the target isn’t managed.
Let’s take the following example:
- We start with both Prod and Dev on Version 1
- The development team developed version 2 on Dev, while also uploading hotfixes to Prod.
- If we were to deploy version 2.0 from dev to prod without a baseline, the following changes would have been performed:
- However, if we were to do the same with Prod 1.0 as a baseline, the outcome would be different:
- Create Table2. Table2 appears in the source, but not in the target or baseline. This is an indication that it’s a new object that needs to be added to the target.
- Do nothing with Table3. Table3 appears in the target, but not in the source or baseline. This is an indication that the object was added on the target only and the change needs to be preserved.
- Create a conflict alert on ProcA. ProcA was changed, both on the source and on the target. Using the baseline, we know that and display the three versions, allowing the DevOps to decide which version to take. In this case we will merge the changes.
- Thus, Prod would end up as follows:
These steps allow you to perform an intelligent three-way, baseline-aware analysis of your database so you can get your automation under way correctly.