Tracking Changes in Accpac tables

[vbdbcoder]

Microsoft SQL server has built in functionality to track changes within the database. Change tracking can be enabled on a table per table basis.

Is this something we can enable on the needed tables within the Sage database?
Will it affect system performance?
Will it affect Sage upgrades?

We can't use the AUDIT fields in the tables, since the values need to retain. DB load will flush them and overwrite them, which we don't want to happen.

 

[Jay Converse]

http://www.pacifictechsol.com/?q=pts-solutions/sage-accpac-erp-products/audit-logger

 

[vbdbcoder]

The client already looked at the Audit Logger option. With strong IT on staff, they wanted to explore the native SQL offering from change tracking.

 

[Jay Converse]

Sage ignores SQL change tracking, that's down at the database layer. It might slow it down, it might not, it depends on how much you turn on.

 

[ramam1]

While not without operational consequences, you can use the MSSQL Full Backup model to enable point in time recovery; a secondary benefit of this is that you can view the values of the Accpac Audit columns before / after any change you like. At my company we keep a few months worth of transaction log backups to help us pinpoint causes whether user error or macro bug etc.

 

[Jay Converse]

I NEVER use the full backup model, it's too much maintenance for too little benefit.

 

[ramam1]

I've been using it for six years without issue. It sucks up a tiny bit of CPU and a good amount of storage. As we do a lot of customization and macro development in house it is nice to be able to do analyses of previous behavior as well as do point in time recovery if something goes badly (ie roll back to 4:15pm yesterday afternoon).

The costs are mostly know-how and storage. I agree that it is certainly not for a typical shop.

 

[johnny9864]

Your customer might want to consider another option that we use and it works very well for us :

Since you don't usually want to track everything you might consider the option of using triggers on some tables in the ACCPAC database and create an "Audit" database where you keep what the customer is interested in. It's a bit of work up front but very light on performances for the SQL server. Most of the time you just want to track some critical info. Make sure you write and test your triggers correctly, the last thing you want is the trigger to kill an ACCPAC update in a particular table, this would be murder as ACCPAC is not transactional.