Just to elaborate on the NetApp ONAIR Session about SC and the scheduling question. We have a customer where we have 10-12 MSSQL Clusters that we backup using SC 5, and it works great. We do daily full backups with log flush, as well as hourly log backups. The way the current scheduler works is so simple that the hourly log backups have to run every hour all the day. And of cause the database owners have created their own scripts to optimize or whatever that typically run at night. Trouble is that this messes with the log backups, and triggers a warning... and we get a log of warnings because of this... As you know you are able to use either the Windows Scheduler or the SQL Server scheduler, both of which are advanced enough to setup a type of schedule that runs hourly, but skips backups in a set window... But the SnapCenter GUI doesn't allow you to create a lille more complex schedule... which is a bit annoying... We can of cause change the SnapCenter created schedules, but they will end up beeing overwritten eventually... so the way we "fix" this now is to setup another schedule in the Windows scheduler which disables the SnapCenter schedules in a set window... that was the "best" bodge we could come up with, and it has lowered our daily morning SnapCenter mailbox to just a few, and alost no "false positive"... oh, and while we are at it... or DBAs also likes to take databases offline because of "reasons", but not unmount it... which of cause also triggers a warning.. it would be great to have an option to ignore "Offline" databases... but maybe we are just special this way? 🙂 I could go on, because we use SC a lot and have done the last 10 years. In general it's a great product that has matured and hopefully continues to do so...
#SnapsCenter special scheduling...
verbal lynx
signal zinc
Heard. I'll bring it up the next time I meet with the product team. Thank you for the feedback and for watching the show!
upbeat brook
Just to elaborate on the NetApp ONAIR Session about SC and the scheduling questi...
The problem that you are having is that only one system can be responsible for MSSQL log-chain backups, and that needs to be SnapCenter. How MSSQL is architected is that there are two main restore modes: Simple, and Full. You are using Full if you are using SnapCenter, otherwise you can't prune or backup the transaction logs.
Theoretically, one can recreate all the data in the database fully from the transaction logs, this is why MSSQL requires that you do a database backup in order to prune the logs. If you didn’t do a database backup and pruned the logs, then experienced corruption or deletion of data, you would have no starting point to restore the data to as the logs would be incomplete and incomprehensible. Now if you had a backup of the database and all logs created after that backup, you could roll the database back to any point in time from that backup to the current time of the last log entry. These backups of database files and log files are called log chains.
Only one process can be responsible for log chain backup and pruning, because MSSQL will reset the checkpoint in the database on any backup operation, UNLESS the backup is a ‘copy-only’ backup. Copy-only backups do not reset the log-chain and all of your SQL developers MUST click the ‘Copy-only’ checkbox if they are doing database backups, otherwise they are resetting the log-chain and putting the point in time restorability of the database at peril; this is why SnapCenter is giving you the warning.
The NetApp can only have so many snapshots on a volume and if you have a tight RPO, let’s say 10 minutes, you don’t want to do full backups (snapshots) or you will run out of snapshot space in 7 days, you want to do log only backups every ten minutes, and database backups (snapshots), let’s say every 4 hours, so that you can keep over a month of snapshots. So full backups take a snapshot of the database and log volumes. Log-only backups copy the logs to the SnapInfo partition. You can set different retention periods for database backups and for log backups.
As for the offline databases, this is not a problem as the database is not mounted and will be backed up in the Snapshot of the volume in its offline and consistent state, assuming it is on the same volume as all the other databases that are being snapshotted, and the logs are on the same volume as other logs. If you are putting each database or log on a separate volume, you have a problem, which is why SnapCenter is warning you that you might have a problem.
verbal lynx
The problem that you are having is that only one system can be responsible for M...
The issue is not at all the way SnapCenter does the backup. We only use SnapCenter for SQL backups, so logs are of cause handled by SnapCenter. The issue I was trying to describe is the very simple scheduling features avaliable on the SnapCenter policies... Our customers typically wants hourly log-only backups, and a daily full backup with log truncation. This works just fine. But we have some databases where the DBA have setup their own SQL Server schedules (not backups) where they run updates etc.. which in some cases will cause the hourly SnapCenter log backup to fail... So it would be great if the SnapCenter scheduling was a bit more advanced... in this case it would help that we could run hourly log backups from 06:00->23:00 but then skip the other hourly backups... this is not possible right now... (via SnapCenter anyway) now SnapCenter just creates schedules on the SQL Server it self, either the internal SQL Server schedule or the Windows Server Task Scheduler... both of which have more advanced features in teams of scheduling than SnapCenter offers... So you could actually alter the SnapCenter created schedules to your liking... yet they will be overwritten if you change something in your policy from SnapCenter... So either SnapCenter could expand their scheduling featuresets, or they could have an "manual schedule" option so you control your schedules manually... I hope this all makes sense? 🙂