Recovery Models for GP SQL Database

Question Status

Suggested Answer
MaxB asked a question on 19 May 2017 12:35 PM

I see the standard recovery model for GP SQL database is "SIMPLE" recovery model. I like to know if it is good to consider other recovery models such as "FULL" or "BULK LOGGED" for GP. "FULL" model will allow point-in-time restore of database, but it seems if multiple users are using the system, point-in-time recovery may not be reliable in cases such as where user may be posting at that point-in-time. Any thoughts and experience on this?

Reply
Suggested Answer
Leslie Vail responded on 19 May 2017 1:04 PM

FULL is not messed up by multi-user. I have nearly all of my clients on Full Recovery, just make sure you set up a database maintenance plan that backs up the transaction files.

Kind regards,

Leslie

Reply
Richard Wheeler responded on 19 May 2017 3:39 PM

If a point in time restore is not needed SIMPLE recovery will work fine. FULL would allow you to do a point in  time restore so your schedule might be you do a full backup at night and then a transaction log backup every hour between 8am and 5pm. If you need to restore to 1pm, you would restore the full backup and then each log backup from 8am till 1pm. It all depends on how much data you can afford to lose and how much that lost time is worth. It has nothing to do with the number of users.

Reply
MaxB responded on 19 May 2017 11:28 PM

Thank you for your replies. I assume the log file is recording all the SQL statements in the back-end. For most GP transactions, GP updates multiple tables when user saves or does posting. So if we pick a point in time for restore or backup, it seems there is some possibility of broken records. for example header table is updated but line item table may not be updated. Since the log file is being recorded while users are entering data in GP.  How can we avoid such cases?

Reply
Richard Wheeler responded on 20 May 2017 5:13 AM

You purchase quality computer components and maintain it with quality people to reduce, but not totally eliminate this possibility. To have what you seek would require a massive rewrite of the underlying GP SQL code where a collection of transactions is treated as one so if one portion fails the entire collection fails.

Reply
Suggested Answer
Leslie Vail responded on 19 May 2017 1:04 PM

FULL is not messed up by multi-user. I have nearly all of my clients on Full Recovery, just make sure you set up a database maintenance plan that backs up the transaction files.

Kind regards,

Leslie

Reply