Hello. First time poster. I've always been the admin of minor SQL DBs by default. At my current position, I'm ultimately responsible for a very critical instance. It is a ~1 TB SQL 2012 R2 SP3. 4 node Windows Cluster, with 1 DB in particular that is business critical. It is in Full recovery, with full and log backups done with Commvault. Hardware was just refreshed with 28 CPU cores and 256 GB of RAM. All is well with performance. We have a reports instance that is currently refreshed nightly with a full dump backup from prod and a full restore. I realize this is not best way to do things. The vendor set it up this way, and it has worked for the most part. We have now spread to all US time zones. Our shorter maintenance window and and growing data set will not allow this method to be used anymore. I would like to got to transaction log shipping to keep our reports DB refreshed. I've got TL shipping down well, and I used to migrate to our new hardware recently. Where I run into problems, is trying to combine TL shipping with log backups and truncate. If I allow Commvault to backup and truncate the log, it breaks TL shipping. I believe this is because the log is purged and a portion of it is never shipped to the reports server. I can tell Commvault to not truncate the log, but it will then grow out of control, I think. I read somewhere this if I'm doing TL shipping, then I don't do log backups anymore. I just backup the shipped logs. I could do that, but I still don't know how to truncate the main log on the Prod DB. I need to backup every 3 hours at a minimum, but every hour would be better. I'm okay with the shipped logs being the incremental restore if that is indeed an option.
I believe this must be a common scenario, but I cannot find a write-up explaining what I need to do. I've found a lot of great information here, and I'm guessing this is a easy question for someone.
Don't use comvault to back up the data bases. Use ola hallengrens maintenance solution to create the backups and comvault to only back up the drives where the backups reside.
We have had hugr issues with comvault backups having broken back up sets.
Swoozie
I also forgot. Maybe look at an Availability Group solution.
Thanks. We currently use Commvault for all out backups. This includes the feature to replicate it to an offsite location with deduplication. We have not had any problems at all with the Commvault backups. I'm open to suggestions if there is something better out there. I have see the Ola Hallengren site when searching for an answer to this question. If I were to go that route, I'm still not sure I see how to resolve my issue.
If your main database is OLTP I would suggest that you should never (as in NOT EVER!!) truncate the log. You will break the log backup chain and you will not be able to restore across that break.
I would also suggest that you should backup the logs FAR MORE frequently than every three hours. We used to do 10 minutes, as a good compromise of how much data we could afford to lose, but increasingly users are doing data entry from "ethereal" documents (phone calls, email snippets and so on), whereas in the old days data entry was done from SnailMail Post (and could thus, relatively easily, be repeated in a Disaster Recovery situation). Nowadays repeating data entry, after a disaster, is impractical. Also, hardware etc. has improved, and now backing up the log every minute or two is very achievable (I don't see a problem even with "every minute")
If your log backups are too big then get more hardware / disk space. Period. Truncating the logs because they are too big to handle is not an option, because of breaking the chain.
I have no idea what Commvault does, but anything (bar a few well respected brands) that backs up the database "direct" terrifies me. The only backups we allow are using SQL Server to backup direct to Disk Files and then "something else" to backup those physical files to Cloud, Tape, Off-site etc. Having those files available then fits nicely with Log Shipping (if you go down that route).
Also, having a few days (or more) of backup files "on line" on the server / network means that any Restore is easy - no need to wait for Tape drives to be free, or to pull a Multi-TB Cloud Backup across the cabling (possibly at a time that someone else is hacing their own disaster and trying to do that too).
You have a disaster, you get a new Server, you install SQL on it then you restore with Commvault - can the new tape drives (if you have them) read the backup tapes? is the Version of Commvault compatible with the Patch Release of SQL? - indeed, did Commvault work flawlessly with every patch-release-point that you installed on SQL Server? and so on and on and on ... LOTS of stories of things that fall through the cracks when actually confronted with a real world catastrophe.
So my suggestion would be to backup to files. Do a FULL backup as often as you can - once a day is good, but once a week with DIFFERENTIAL backups daily (or even twice daily if you think you need that) is fine. Make sure you use Native Compression (it reduces the file sizes by 80% or so but, more importantly, it reduces the I/O to disk / across the network significantly too - faster backups, faster restores.
Set up the LOG backups for once a minute, certainly no more than every 5 minutes. Run that 24/7 - don't have a different schedule for "out of hours" - any index rebuilds, or data imports, over night will probably generate more Log traffic than the normal working day! and multiple schedules increase complexity and risk-of-failure.
Ola Hallengrens code is sophisticated. Personally I find the DOCs useless and obtuse, and I prefer Minion backup. Either will do a good, water-tight, job of backing up to files.
Re:reports instance
Usually I find that users prefer to have a "once a day restore" to reporting servers, so that the reporting data is constant. If restores are "continuous" then the data changes under foot, so re-running a report (with slightly different criteria) makes it hard to compare Apples with Oranges, so that would mean being in a position to do the restore during your nighttime maintenance window. Whether that is possible, or not, will depend on how massive the LOG files (for a 24 hours period) are. Assuming they are some small-ish percentage of the size of a FULL backup then restoring just the logs onto report server should be faster than doing a FULL restore. The An alternative is, more complex, to restore to a new, TEMP, database at some suitable time (before the end of the working day in the last timezone), restore all the LOG files SINCE that Full (or Full + Differential) backup was made, and then "at the point of cut-over" restore the remaining LOG backups and DROP the original Report DB and RENAME the TEMP DB. The actual time to restore the FULL plus all LOG files can be as leisurely as you like (just start earlier if you need more time - run it all day long, if you like 
), and the cut-over will be "instant". Of course you need enough disk space on the Report Server for two complete databases, the "live" and the "temp"
Points to note, in case you are not aware of it. With Full Recovery Model you can restore any Full Backup (or a Full + Differential Backup) and then ALL Log Files since that time. So if you discover that last night's FULL Backup is damaged you can use the night before (or last week ...) and ALL Log Files since then. Because of the way that Log Files are created anecdotal evidence is VERY good that a damaged FULL Backup will not mean that the LOG backup is also damaged - particularly if DATA and LOG are on different spindles / disk controllers.
When you have your TEMP database (or the Reporting Database) you could run DBCC CHECKDB tests on it, to check that it (and therefore the LIVE database) are not corrupted. You should do that frequently (once a week? daily is better ) to get early warning if something goes Pop! Also worth turning on CHECKSUM / TORN PAGE etc. for early warning of disk corruption.
Thank you. This is helpful. Commvault is generally respected, and we have had 0 issues with restores. We do not use tape. It goes to disk and replicates to offsite storage. I looked through the Minion site, and it looks great. Definitely more options than Commvault. I think I was off on some of my understanding of some terminology and how a few things worked. Hardware, storage, and performance is not an issue. This is a large company. Large enough to have someone more experienced doing this, but that is a different issue. I think I'm wrong in assuming the log needed to be truncated. I do not have a space issue. I was thinking if you did not truncate the log/LDF file, it literally grew indefinitely. No space would reused. Does a particular type of log/incremental backup free up white/free space within the log file? Again, space is not an issue, but I can't have the log file growing 1 TB per day. Space would be an issue then. On the Minion Backup guide here http://minionware.net/wp-content/uploads/MinionBackup1.0.pdf the default list in the first table looks like it would be a good start for me. I still get stuck on the same thing when I work through this in my head. Does the log backup create white space that can then be reused? My LDF will grow hundreds of Gigs per day at a minimum. I do not want to do anything to break the backup chain.
On the other topic, that is a good point for restoring to reports. I will confirm with the the stakeholders if they would reports be refreshed throughout the day, or once at night. Either way, the complete backup/restore method is pushing outside our current maintenance windows. TL shipping seems like a good method to me if I choose daily or throughout the day. I know how to get this part done. If I go this route, I just need to make sure my backups don't interfere with the TL shipping.
I think if I can understand how LDF/log maintenance is supposed to work in this setup, I will be able to figure out what I need. I am under the impression the LDF file doesn't simply grow forever.
Hmmm ... I've not heard of it, but of course that doesn't mean anything, I only know what I know!, but if it is backing up to Disk then my gut feeling is that I would much prefer to be using native SQL Server backups for that part. The replication off-site is a different issue. We use RoboCopy (that's a Microsoft utility) for that, but its a piece of String and Gum Batch file that we wrote ourselves, so doesn't really qualify as "bullet proof" without decent testing and some knowledge. RoboCopy has an option to MIRROR COPY - it will maintain (on the Destination) identical file structure to Source, so as new backup files are created they are copied, and as old ones are purged they are deleted from the Destination. It can also throttle the copying, so as not to hog bandwidth.
A normal Log Backup achieves that. The log file itself is written to / appended sequentially, Once the log is backed up all the blocks included in the backup are marked as "free" and available for reuse (note that there may be some additional blocks, written at the very end of the log file as the log backup finishes / which were not yet committed, which are not released, but this would only be an issue if you needed to Shrink the log file because they are at the very end (generally its a bad idea to do that as repeated Shrink / Grow of the log fragments the file). Those extra blocks [at the end of the file] will be in the next log backup, and will then be marked for reuse.
Note that the Log Space will not be reused if something else needs that log data - I'm thinking that some types of Replication may also be using the Log File pages, so until "everyone" has finished with a log page it won't get reused. I only mention that for completeness, Log Shipping (for example) should be able to use the same Log File Backups for both the Shipping and for your physical secure backups (not sure how CommVault would fit into that picture; you can build your own log shipping system e.g. using Commvault's Log backups (I've done it using SQL's Log backups, its not very hard), but obviously easier to use the built-in Wizards in SQL Server to set-up and maintain Log Shipping.
Indeed! The key to that will be increasing then frequency of the Log Backups. If you have 1TB a day of log file usage, and your backups are once an hour that's an average log file size (and individual log backup file) of 41GB. Change the backup to every minute and that falls to 700MB [on average].
If you backup every minute you will have lots of log Backup Files [in a 24 hours period], but the same overall total disk space usage as a once-a-day backup (although there is a little overhead on each file). The other difference is the reduction of lost data when recovering after a "total loss" disaster - assuming all the files are off site 
Possible point of interest: After a disaster, if you can still access you SQL server, you may be able to take a "Tail Log backup" (i.e. even if the Data MDF file is damaged beyond use / repair). In that situation you would prevent any further access / connection / etc. to the DB, take the Tail-Log Backup, then restore the last known good FULL backup, and all the LOG backups since, including the Tail Log Backup, and have zero data loss The chances are good (unless server / building has been destroyed 
) that in the event of severe damage to the Data File that the Log File will still be in good shape for tail log backup in this way (worth making sure that your storage guys are indeed segregating all your Data and Log files onto different spindles / controllers / etc. - I'm not a hardware guy, so my terminology may be way-off, but I'm sure you get the idea ...)
if you use Log Shipping direct to the Report Database (rather than to a TEMP that you rename, once a day, to the Report Database) you may find that there is outage when the Log Restores happen. I can't remember what the issue is, if any, but I read something recently along those lines and was surprised to hear it, as it was news to me. Hopefully I have the wrong end of the stick on that point!
Hopefully I've clarified that ... a Transaction Log Backup will mark the pages for reuse, for any transactions that are committed, and assuming nothing else (e.g. replication) is wanting to also use that page. (There is also a COPY_ONLY option to (both Database and Log) Backup, in case you need to make a backup of the Log and NOT purge it. That might be an option if both Commvault and Log Shipping need to, both, independently, make backups of the Log File. I suggest you try to avoid that, its a fairly large job to backup a log file as big as yours, just having one backup for both Secure Backup and Log Shipping would be better than two separate ones IMHO !!
If you aren't already doing so I suggest you stage a Trial Restore periodically (no less than quarterly, monthly would be better, every night, by automated scripts, would be ideal), to prove that you can - and then run DBCC CHECKDB on it to ensure that it is not corrupted (you probably do not want to run CHECKDB on your production DB, it will take forever and slow the server down, but running CHECKDB on a restored database copy is identical to doing it on the original, so no actual need / benefit to run that on the production DB itself).
Thank you! This "Transaction Log Backup will mark the pages for reuse" is the sentence I needed. This is where I was confused. I thought the truncate function was was marked the pages for reuse. I'm not tied to Commvault for the initial backup. It is an Enterprise backup program that can back up about anything. I'm fine using Minion, and then backing up the flat file backup with the Commvault disk agent. Minion has a lot more options. I think the best option for me would be Minion backups with a full every night, and Transaction Log backups every 5 minutes or so. TL shipping for the rperst instance should work fine. There are 2 options there that are related to what you mentioned. You can apply the logs whenever there are no connections, or you can force connections closed. Thank you all. I'm glad I found this place!
Recommend compressed backups, I expect that is an option in Minion, but if you are so minded there is a "default backup type" setting in SQL which will ensure that all backups are Compressed (unless explicitly told otherwise).
If you are interested : there is a thread on Pros/Cons of compressed backup, together with both tests which gave both Disk and I/O savings
I'm loving Minion backup so far, but I'm just now trying it on tis mission critical instance. I have tested Minion Backup on a variety of SQL servers at our office, and everything looks great. I tried to roll out on our most mission critical server, and no luck. It is a 3 node Windows 2012 R2 Cluster with Sql 2012 R2. The instance I'm trying to back up has a sing DB in full recovery. I have it set to to a full daily and logs every 10 minutes. The system DBs are backing up fine, but the user DB never backs up. I looked in the Minion.BackupLog table, and it agrees it was skipped. I can't find any detail as to why it was skipped. Any tips on where I can look to find out why?
Might be worth asking Minion as your first step.
That apart, I would check all the MINION tables. From my quick review of the DOs this might include:
Minion.BackupSettings - BackupType set correctly? Exclude = FALSE? IsActive = TRUE?
Turn on LogProgress perhaps?
Minion.BackupSettingsServer - where DBType = USER there needs to be a record where Include has the name of the database (and it should not be in Exclude !!) and IsActive = TRUE.
Minion.BackupDebug - Review after running Minion.BackupMaster / Minion.BackupDB in Debug Mode.
Ditto Minion.BackupDebugLogDetails
Presumably Minion.BackupFiles and Minion.BackupLog (and Minion.BackupLogDetails too) are "silent" WHERE [DBName] = 'YourDBName' ?
Longshot: YourDB is not READ ONLY ?
Enough disk space for the backups?
Everything looks correct. I've asked Minion as well. Just hoping someone here might have an answer.
Good luck finding an answer, would be good to hear back when you have got it sorted pls.