Skip to main content
NetApp Knowledge Base

How does SCSQL retention really work?

Views:
787
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.

Scan to view the article on your device