- SnapCenter Plug-In for SQL 4.5 (SCSQL)
- Microsoft SQL Server
Backup retention evaluation is part of the backup workflow and retention is only evaluated at the time a backup has been completed successfully.
- 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
- Retention is applied differently, depending on the 3 possible types of SCSQL Policies:
- Full Backup
- Full Backup + Log Backup
- Log Backup only
- 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)
- 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
: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.
Get-SmPolicyoutput and when it is applied. The
Get-Sm-Policyoutput 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
- 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.
|Full+Log Policy - Daily|
|LogBackup Only Policy - Hourly|
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.