Skip to main content
NetApp Knowledge Base

How does SCSQL retention really work?

Views:
1,065
Visibility:
Public
Votes:
0
Category:
snapcenter
Specialty:
snapx
Last Updated:

Applies to

  • SnapCenter Plug-In for SQL 4.5 (SCSQL)
  • Microsoft SQL Server

Answer

Backup retention evaluation is part of the backup workflow and retention is only evaluated at the time a backup has been completed successfully.

  1. SCSQL is designed to delete the following old backup components:
  • Ontap volume snapshots
  • Backups of the .ldf SQL Transaction logs files from the Host Log Directory (HLD), in the form of TRB files
  1. Retention is applied differently, depending on the 3 possible types of SCSQL Policies:
  • Full Backup
  • Full Backup + Log Backup
  • Log Backup only
  1. Here are the Backup components whose deletion can be controlled by the users:
  • Data: Number of Snapshots or amount of days the Snapshots should be kept of the volumes hosting SQL Data and Log files (via UI or CMDLET)
  • LOG: Number or Days the TRB files should be kept in the HLD (via UI or CMDLET, by setting the Up to the Minute UTM value)
  • LOG_SNAPSHOT: Number or Days Snapshots should be kept of volumes hosting TRB files (only via a completely filled CMDLET)
  1. Here is when retention is applied by SCSQL
  • Data: During a Full Backup or Full Backup+Log Backup
  • LOG: During a Full Backup+Log Backup
  • LOG_SNAPSHOT: During Any type of Backup, however the number of Snapshots or days snapshots that are kept is the sum of LOG_SNAPSHOTS values of all Policies associated with the Resource Group (RG) used for the backup. For Example, with a value of 2 LOG_SNAPSHOT for FullBackup+LogBackup Policy and a value of 2 LOG_SNAPSHOT for LogBackup only, SCSQL will keep 4 Snapshots of the HLD volume.
  1. Get-SmPolicy output and when it is applied. The Get-Sm-Policy output for a Full or Log policy contains a section which starts with Schedules, for example Weekly. Under that section, there are key values like:
  • BackupType: Data (there may be two, one for this schedule and one for None, which is the OnDemand backup), LOG and LOG_SNAPSHOT.
  • SchedulerType: Weekly, Daily, Hourly or None
  • RetentionCount: the number to be kept
  • RetentionDays: the number of days to be kept
  1. Which Policy triggers retention for what component:
  • The Full Backup policy triggers all BackupType retentions.
  • The Log Only BackupType triggers only the BackupType LOG_SNAPSHOT.

Additional Information

For example:

Full+Log Policy - Daily
BackupType Data Data LOG_SNAPSHOT LOG  
SchedulerType None Daily None None  
RetentionCount 8 8 2 3  
RetentionDays 0 0 0 0  

 

LogBackup Only Policy - Hourly
BackupType Data Data LOG_SNAPSHOT LOG  
SchedulerType None Hourly None None  
RetentionCount 7 7 2 7  
RetentionDays 0 0 0 0  

A RG with the two policy defined above will result in the following:

Ontap Volume hosting .mdf files: max 8 snapshots per scheduled type, in this case only daily.

Ontap Volume hosting .ldf file: max 8 snapshots per schedule type, as per above.

Volume hosting HDL: TRB files in HDL volume: only at the time a full+log backup occurs, the number of TRB will be reduced to a max of 3

Snapshots in HDL volume: max 4 (2 as per the SC 4.5 default of the Log Policy + the 2 we set for the Full Policy with the Log_snapshot variable via the cmdlet);

NOTE: SCSQL 4.4 and lower had a different retention setting for HLD snapshots ( hard coded to 7 snapshots). Starting from SCSQL 4.5 it is changed to 2 snapshots.

NetApp provides no representations or warranties regarding the accuracy or reliability or serviceability of any information or recommendations provided in this publication or with respect to any results that may be obtained by the use of the information or observance of any recommendations provided herein. The information in this document is distributed AS IS and the use of this information or the implementation of any recommendations or techniques herein is a customer's responsibility and depends on the customer's ability to evaluate and integrate them into the customer's operational environment. This document and the information contained herein may be used solely in connection with the NetApp products discussed in this document.
  • Was this article helpful?