How does SCSQL retention really work?

Applies to

• SnapCenter Plug-in for Microsoft SQL Server (SCSQL) 4.5 and later
• Microsoft SQL Server

Answer

Backup retention evaluation is part of the backup workflow, and retention is only evaluated when 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 log files from the Host Log Directory (HLD), in the form of TRB files
2. Retention is applied differently, depending on the 3 possible types of SCSQL Policies:
   • Full Backup
   • Full Backup + Log Backup
   • Log Backup only
3. Here are the backup components whose deletion can be controlled by the users:
   • DATA: Number of Snapshots or number of days the Snapshots should be kept for 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 for volumes hosting TRB files (only via a completely filled cmdlet)
4. 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_SNAPSHOT values of all Policies associated with the Resource Group (RG) used for the backup.
     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.
5. Get-SmPolicy output and when it is applied. The Get-SmPolicy 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
6. 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

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.