Question

Best Practices of MS SQL Database

  • 8 March 2023
  • 7 replies
  • 602 views

Badge

Dear Members,

What will be the best practice to create DB policies and schedules for MS SQL Backups. As per our standard policy Primary Copy 7 days retention period, AUX copy to DR 8 weeks and then tape backups for 1 year and 10 years. 

So how should we create schedules for Daily DB backups, Transaction logs backups every 4 hours. Should we keep separate storage policies for DB and logs backups or they can be on the same.

Any addiditional information will be highly appreciated.

 

Regards,

AQ


7 replies

Userlevel 7
Badge +19

Generally speaking I would say it depends on your requirements. However my advise would be to create a full DB at least once a day. This makes sure you do not have to replay an entire chain of logs for multiple days. It's just improves your RTO.

In regards to the configuration itself and I assume, based on your input, that your are using tradition schedules and storage policies to think of moving to server plans who are configured from Command Center as it is easier to configure. 

 

 

Userlevel 5
Badge +16

Generally speaking I would say it depends on your requirements. However my advise would be to create a full DB at least once a day. This makes sure you do not have to replay an entire chain of logs for multiple days. It's just improves your RTO.

In regards to the configuration itself and I assume, based on your input, that your are using tradition schedules and storage policies to think of moving to server plans who are configured from Command Center as it is easier to configure. 

 

 

Question wouldn’t doing differentials daily achieve the same thing of not having to replay the all the logs in between fulls?

Userlevel 7
Badge +19

Generally speaking I would say it depends on your requirements. However my advise would be to create a full DB at least once a day. This makes sure you do not have to replay an entire chain of logs for multiple days. It's just improves your RTO.

In regards to the configuration itself and I assume, based on your input, that your are using tradition schedules and storage policies to think of moving to server plans who are configured from Command Center as it is easier to configure. 

 

 

Question wouldn’t doing differentials daily achieve the same thing of not having to replay the all the logs in between fulls?

It depends on the moment. Say you create the full on Monday followed by differentials the other days and you have to restore the recovery point of Thursday. This would result in the last full + differential of Wednesday + incrementials/trans logs. 

Userlevel 6
Badge +14

In most cases we do not see differentials that often. Usually a full backup nightly unless its a very large database and a full backup takes a very long time, then differentials make sense.

Tlog backups can greatly vary. In most cases if you have various SQL servers you might need one policy for production critical server with tlog backups every 15min or so then a test, etc servers with less aggressive backup schedule.

But as was noted it really depends on your RPO.

Userlevel 5
Badge +16

Generally speaking I would say it depends on your requirements. However my advise would be to create a full DB at least once a day. This makes sure you do not have to replay an entire chain of logs for multiple days. It's just improves your RTO.

In regards to the configuration itself and I assume, based on your input, that your are using tradition schedules and storage policies to think of moving to server plans who are configured from Command Center as it is easier to configure. 

 

 

Question wouldn’t doing differentials daily achieve the same thing of not having to replay the all the logs in between fulls?

It depends on the moment. Say you create the full on Monday followed by differentials the other days and you have to restore the recovery point of Thursday. This would result in the last full + differential of Wednesday + incrementials/trans logs. 

Right so you would have a slightly longer restore and much faster backups assuming a sufficiently large database?

The reason I ask is because I see this full backup every day thing a lot, I also see logs every day with no differential or full a lot. 

as @Scott Reynolds noted I rarely see differentials used and I don’t understand why since the entire point of differentials is to be able to avoid replaying a ton of logs. 

I truly just don’t know when differentials are appropriate since people seem to just ignore them as an option.

Userlevel 6
Badge +14

@christopherlecky 

I think there are a few reasons for not seeing diffs that often:

Quicker recovery time with from a full (in most cases)

If you have a very active change rate in your database a diff can be almost as large as a full backup

low change rate on a smaller DB in which case after dedup consume very little space anyway

habit

 

However I agree differentials can certainly be a great strategy that could potentially also combine some space savings on the storage side. Lucky Commvault provides the flexibility to adapt specifically to your needs.

 

Userlevel 5
Badge +16

if you have a very active change rate in your database a diff can be almost as large as a full backup

Interesting. Thanks for the elaboration.

Reply