David Beavon (Software) asked a question.
We have a fairly large, mission critical ERP system that uses the OE database. However, in preproduction environments on HP-UX we have *never* enabled the database transaction logging (aka "after imaging").
I don't know why that is. Ideally we would have at least one preproduction environment that worked in a way that was fairly consistent with production. The reason for disabling "after-imaging" may be something that was done for historical reasons on HP-UX, and they may not apply any more.
Is it common for Progress customers to disable their transaction logs outside of production? Shouldn't AI logging be enabled consistently in all environments, as a best practice?
Any tips would be appreciated. In the SQL Server world, we don't ever disable our transaction logs. (ie. we always use the "full recovery" model for running our SQL Server databases).
It is my experience with customers that the vast majority of them don't enable after-imaging even in production.
The reasons vary wildly but the most common cause is lack of qualified/experienced personnel. Of course I do have many horror stories back from when I was with the support team.
The few times I remember seeing AI in a non-prod environment were customers experimenting with OE Replication, which requires AI.
I like the idea of having a pre-prod or a test environment that is identical to production in every aspect. It is not always feasible due to the costs involved, but I'd say you should get the AI bits if possible. And the reason I like it is you get to interact and learn a lot more about the AI subsystem this way, so you'd be better prepared to handle the production environment properly.
And as you mentioned SQL Server, I find the rationale goes the same way no matter what system you use, even though I run a lot of databases in Simple recovery model on the dev/test SQL Server instances 😀
@Marcos Kirchner
That is really surprising. Running a database without a transaction log seems like a nightmare (one of those where you start going about your day, and realize you forgot to put on any clothing after getting out of bed).
When you mention the "costs involved", I don't think that would have ever applied to us. We have always used enterprise database licenses, even in pre-production environments. I'm assuming you do NOT have to pay extra for AI, so long as you are running the enterprise version of this database.
Unless you are simply referring to the incremental cost of CPU and disk resources, which should be fairly minor, right? I would imagine AI can be archived regularly, in order to limit the growth of disk.
.. or perhaps are you referring to the extra complexity for the OE DBA's themselves? Is enabling AI a burdensome thing in OE? I'm not an OE DBA myself, but I would think it should be a matter of checking a box in OEE, and configuring an automated schedule for AI archiving. Is it more than that?
Yeah, it seems like a nightmare (hence the horror stories) but in reality I do think it is OK to not use "after-imaging"/"full recovery model" for some databases, depending on the recoverability requirements. Assessing those requirements and including the right technologies to meet them is the key to a proper recovery strategy.
Just to be clear, you know we are not "disabling" transaction logging or doing "unlogged" user transactions, right? As far as I know there is not such thing in an OE database (or SQL Server for that matter, disregard tempdb). OE still logs to before-image files and transactions are durable and recoverable, even in the case of a server crash/restart/loss-of-power.
The "costs involved" part was not exactly for AI. You don't pay extra for enabling it. There is some overhead incurred but it's very manageable if done properly. That part of my previous answer became a mess, I was thinking more about having a duplicate of every bit of hardware/software/configuration/operation in a test environment, so you can try things before doing them in prod. Like if you have a cluster in production but a simple server for test, some operational procedures might differ substantially and you don't really know what to expect of production before you actually do the operation and risk breaking stuff.
Enabling AI is quite easy and AI Archiver does a very good job of archiving and rotating AI extents for you, but some stuff change. You can't run idxbuild, for example. You need to protect the archived extents and manage long-term retention, while being able to access them in an emergency. It's doable but you need to be aware of these kind of things and account for them.
>> OE still logs to before-image files and transactions are durable and recoverable, even in the case of a server crash/restart/loss-of-power.
Without the AI transaction logging, I'm assuming that recovery is much more problematic if your database becomes corrupted or the hardware fails. I'm assuming you lose all data since the prior full backup.
Correct, and that's why full backups alone are usually not a good enough recovery strategy in most cases. Incremental/differential backups may help improve the situation, but it's hard to have a near-zero data loss requirement and not use some sort of logging archival.