How does SCSQL retention really work?

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

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 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)

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_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.

5. 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

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

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.