Using a translated log file - best practice for triggers?

[justin_willey]

If applying the SQL generated by DBTran.exe to a database (where all the triggers exist), is it better to:

1. have DBTran include the SQL statements generated by triggers and then run the script with the fire_triggers option set to OFF OR
2. just run with defaults excluding trigger generated statements

The situation is practising a plan to revert a database file version update, if it turned out to be necessary. Some triggers refer to system variables that aren't there at the re-run time.

---

Update - Thank you everyone for your input.

Option 1 produced the desired results. John's comment about that essentially being how the recovery process does it being the key point. Since that isn't the default behaviour of dbtran, I wonder if the docs could be clarified?

[VolkerBarth]

Some triggers refer to system variables that aren't there at the re-run time.

Does it mean the triggers will fail (and the according DML statements will automatically fail, as well)?

---

I don't know, however, the docs on DBTRAN seem to imply the second choice:

Trigger actions should be included if the matching trigger does not exist in the database against which the script file is to run.

I would derive from that statement that otherwise, on should rely on the existing triggers in the database... - but that's just my logic:)

[justin_willey]

Does it mean the triggers will fail (and the according DML statements will automatically fail, as well)?

Yes that's what happens. In the examples I've seen - we can work around that - but that won't necessarily always be the case.

docs on DBTRAN seem to imply the second choice:

On re-reading the docs, and with Breck's comments, I think you are right, -t does seem to be the only way to go.

[VolkerBarth]

Since that isn't the default behaviour of dbtran, I wonder if the docs could be clarified?

Given the fact that I've drawn my conclusions from the docs, and they have been not that fitting here, I surely second that suggestion:) - What about a link in DCX to this current FAQ?

[johnsmirnios · Accepted solution]

What do you mean that the later logs would be from a later SQLA version? Are they from a different database? If not, a new SQLA server generally tries to keep new logs compatible with the database that they are for. Therefore, you could use a newer SQLA server to apply those logs to the older database. There is the caveat that there is only so much version mix & match testing that can be done. Also, downgrading the server version isn't often done so there is probably not much field experience with it either.

As for option #1, that is essentially what recovery does: it replays all operations including the ones logged by triggers but does not fire triggers.

[justin_willey · Accepted solution]

I'm not sure if my clarification on Breck's sub-thread helps. The problem is that we are trying to apply a log file which was made for a clean v11 database (ie after the re-load in v11 format), to a v10 format database. The actual server version is v11.0.1 in both cases.

[Breck_Carter]

The statement "Some triggers refer to system variables that aren't there at the re-run time." pretty much closes and locks Door Number 2... the triggers will fail to run properly.

Another problem with alternative 2 would be references to changing special values like CURRENT TIMESTAMP... presumably you want to apply exactly the same changes to the database that were originally applied, just like database recovery does.

Personally, I prefer Door Number 3: Restore from a backup.

[justin_willey]

I suppose my question really should have been "Are there an implications of option 1 I haven't thought of" !

Agree about option 3, - in this case we would be using the translated log to apply later changes to the restored backup. We can't apply the log file directly as it would be from a later SQLA version - hence the need for translation.

We may even have to edit the file if there are SQL constructs the previous version does not understand - but so far that hasn't been an issue - presumably because we deliberately aren't using any new features.

[Breck_Carter]

Please explain exactly what your are trying to accomplish... maybe other ideas will be forthcoming.

[VolkerBarth]

EDIT: This comment is wrong, see John's remark

Well, if the trigger statements will fail anyway, it won't help much if their actions are contained as separate statements UNLESS they are

• turned into comments or are deleted/modified
• or they run in their own transaction.

Otherwise, a failing "fake trigger statement" might also cause the previous original statement to get rolled back...

I can't think that DBTRAN would change transactional grouping as that would surely modify the meaning of these statements.

Just another CAVEAT:)

[justin_willey]

Sure -

The client has a v10 database running on a v11 engine. They want unload & reload the database into a new v11 structure. (They'd like to upgrade to 16 but we have not yet resolved all the performance issues there).

Their internal procedures (required by their auditors) demand that any system changes they make are reversible (for a limited period of time). They therefore have to have a practicable method of reverting the upgrade, if that became necessary.

If they had to invoke the plan, they would first revet to the backup made immediately before the upgrade. Then there would be the issue of how to apply changes made to the database after the upgrade. As the log file(s) would not be part of a continuing sequence, they cannot be applied directly, so they would have to be translated and applied as SQL - unless there's another way of doing this that I'm not aware of.

Any suggestions very welcome!

[johnsmirnios]

I'm not sure I follow Volker's comment (I'm not sure what the 'fake trigger statement' is). The trigger actions as produced by "dbtran -t" should never fail (unless someone edit's the generated SQL or, by user error, some SQL isn't applied). The operations will be very simple DML statements. It's only the original trigger definition that could fail if triggers were enabled and you allowed them to fire (option #2). The trigger might, for example, refer to a connection-level variable. That variable would have been defined by the application & available when the trigger fired on the original database but the variable's setting is never logged and therefore wouldn't be present if option #2 were used. The logged trigger actions, however, would not refer to that variable: they would contain the actual value that was used.

[justin_willey]

Thanks John - the message here is pretty clear - use -t and don't let triggers fire when re-running the SQL 🙂

[VolkerBarth]

OK, then I stand corrected: I had assumed that the statements that fake the triggers's actions (i.e. the additional statements as result of -t) would mimic the triggers's definition and could therefore also refer to - possibly later non-existing - variables.

As you have clearly explained, that is not the case as they will simply contain the variables's values. So one less CAVEAT.

@Justin: Hm, that's an implication of option 1 you need not have to thought of:)

[Breck_Carter]

The requirement "any system changes they make are reversible" is easy: Restore from a backup, and re-enter all subsequent transactions made during that "limited period of time". A slightly more difficult choice is to operate both systems in parallel for the "limited period of time". I have seen both, but never have I seen this roll-forward-the-log-data approach being used. Do the auditors REALLY require automatic application of new-system transactions to the old-system after reverting? What about upgrades that encompass massive changes in the system architecture and/or software... say, changing from a central Oracle database to a cloud HANA database with distributed remote databases? Developing and testing the reversion process could easily be much harder than the upgrade itself.

[justin_willey]

Well, what they say is that the auditors require that they have a tested procedure for reversing out the changes if unexpected problems (not found in prior testing) made that necessary. As we all know, whatever testing you do is not the same as hundreds of users (+ tens of thousands of web users) all pumping in stuff in real time for a few days.

re-enter all subsequent transactions made during that "limited period of time"

I suppose, that's really what we are trying to accomplish (but without the direct involvement of the users and the customers)

Developing and testing the reversion process could easily be much harder than the upgrade itself.

That has certainly been true on a few occasions 😞 This sort of requirement seems to becoming more common, especially in relation to critical systems at publicly quoted organisations.

In this case, testing the roll-forward the translated log method (now you, John & Volker have cleared up the matter of the triggers) isn't too hard. A full week's logs should cover pretty well anything we are likely to come across, and then we can do some statistical comparisons between the databases at the end of it. If that is successful, I think it will qualify as a "tested procedure" .