Jump to content

Cloud Backup Strategy


Recommended Posts

I am trying to figure out how to backup a Window's network file share to cloud. The network file share is a target for native SQL Server compressed backups handled outside of Retrospect. Example file share path is: \\fileserverX\SQLBackup. The file share contains around 30 TB of backups which represents 3 months of compressed SQL Server backups. The subfolder structure in the file share is: \\fileserverX\SQLBackup\SQLInstanceX. Folders are created inside of SQLBackup for every SQL instance that is being backed up. The backups are automatically pruned after 3 months. 

I need to archive these native SQL Server backups to cloud and retain them for a minimum of one year, but potentially two. The maximum cloud backup set size is 100 TB and Retrospect supports strongly discourages using a backup set size that large. All I really need to do is encrypt the backups and copy them to cloud storage, they won't ever change once created. I can't really use multiple cloud backup sets because I can't easily subdivide the network share into smaller subfolders.

Any ideas on a strategy for archiving this data to cloud?

Link to comment
Share on other sites

(Disclaimer: Anything I may say about the intentions of Retrospect "Inc." in this or any other post is merely the result of "reading the tea leaves", the "tea leaves" being documentation and public announcements supplemented by an occasional morsel from Retrospect Sales.  I have never been paid a cent by Retrospect "Inc." or its predecessors, and I pay for my upgrades. Any judgements expressed are—obviously—mine alone. The same is true of Retrospect's history, especially with references to here.)

dvenardos,

I assume (you didn't say) you intend to use or are using Retrospect Windows, and that it will be the latest version (you didn't say)—rather than some ancient version you inherited from your predecessor.  However the page references I may give you are to the Retrospect Windows 16 and Mac 16 User's Guides, because an employee I call the StorCentric Slasher has robotically deleted screenshots and explanatory paragraphs from the version 17 UGs.  Don't worry, the features I may talk about in this thread haven't changed in years, and Retrospect Inc.'s Product Management refused to update anything besides the "What's New" chapters of the UGs from 2015–2019.

I'll further assume that your objectives in "archiving"—in your sense of the term rather than Retrospect's— this data offsite is either [1] to guard against a disaster—such as flooding—happening to your server room or [2] to enable not-so-rapid recovery of old transactions you must legally retain without tying up disk space in your server room.  I agree with Retrospect Tech Support that both these reasons argue against your using Cloud backup sets.

As to objective [1], IIRC most cloud providers effectively limit a customer's downloads to about 20TB per day.  So it would take 1.5 days to do disaster recovery from even one of your proposed 30TB Cloud backup sets.  I don't think your upper management would be happy about that kind of delay.🙄  Amazon Infrequent Access will cost your organization US$1250/month for storing 100TB, although you may be able to cut that price to US$400/month using Archive Access Tier or with cheaper suppliers such as Backblaze B2.  IMHO you'd be better off doing your "archiving" to LTO tapes, at an initial cost of around US$3700 for a tape drive plus 8 LTO-8 tapes (assuming you can't do further compression).  LTO tape cartridges measure 102.0 × 105.4 × 21.5 mm; if your organization can't find an off-site location to securely store a dozen of those for physical access within 3 days, it has other problems.🤣

As to objective [2], if I understand you correctly, your Retrospect Cloud backup sets would only contain one file for each 3 months of backups of a SQL Instance.  So you'd have to say "the transactions we want must have been backed up from this SQL Instance sometime in that 3-month period", download and restore with Retrospect the entire SQL Instance, and then use SQL Server to to pick out  in the SQL Instance the desired transactions.  That doesn't sound like a speedy process. 🤣  However if you were backing up at the transaction level—which I understand is possible with some non-free SQL Server backup software—then Retrospect backup sets could be used in a more-precise manner. 

P.S.: Based on hurried first reading of your OP, I thought there'd be a question about Retrospect doing backups at 3-month intervals.  So—before reading the OP carefully—I'd already looked it up on pages 232–233 of the Retrospect Windows 16 UG and pages 133–134 of the Retrospect Mac 16 UG (ver. 16 because—as I expected—the StorCentric Slasher has deleted the associated screenshots in the ver. 17 UGs).  I also did an experiment on my Mac 16 "backup server"; the dialog label line starting with "every" does indeed change to "month(s)" when the "repeat" line above has "monthly" in the pop-up.

Edited by DavidHertzberg
P.S.: Based on hurried first reading of your OP, I _thought_ there'd be a question about Retrospect doing backups at 3-month interval; here's the answer..
Link to comment
Share on other sites

Thanks for the response David!

I am using the latest version of Retrospect for Windows. To clarify what is happening (for any readers of this post), native compressed SQL Server backups are going to a network file share via the Ola Hallengren SQL Server Maintenance Solution: https://ola.hallengren.com/sql-server-backup.html. The backups are retained for 3 months. Previously we have been doing tape backups via an old version of Symantec Backup Exec and System Center DPM, we are retiring those systems in favor of Retrospect due to numerous issues. However, we are moving to a colocation facility and tape backups are no longer viable. Our current monthly Iron Mountain costs are in the $500 - $700 month range. 

Looking at the options, Retrospect is not really viable as a cloud tape replacement at this point. It doesn't support AWS tape gateway or StarWind VTL. For this use case I am going to use RClone with the copy command (https://rclone.org/) on an encrypted remote site. This will achieve the objective which is to retain the SQL backups for at least one year. We are going to BackBlaze B2 and I can setup a lifecycle policy to delete old backups. The monthly cost is comparable to Iron Mountain and retrieval fees are also inline with Iron Mountain tape requests.

 

Link to comment
Share on other sites

dvenardos,

My re-reading of your OP and reading of your second post made me realize that you started this thread for the sole purpose of showing your boss: "Look, I told you Retrospect couldn't do the off-site backup the way we want to do it—and here's proof that Retrospect users (not only their Tech Support)  admit that."  That makes me angry enough that I'm going to spend a few more minutes casting further doubt on the way you want to do off-site backup.😡

First, here's a 2018 thread from this very Forum that discusses blm14's desire to use an Amazon VTL.  Starting with the post below blm14's OP, I and Nigel Smith pretty definitively shot that down as a bad idea.  The only thing I have to add to that is Retrospect as of 2016 supports 28 Virtual Tape Libraries with either iSCSI or Fibre Channel interfaces.  You insist on using the only two VTLs that interface over Ethernet; I find it difficult to believe that in your new colocation facility you can't connect a VTL except over Ethernet.  Besides, as the second paragraph in my first post in that linked-to thread says, "what blm14 would be getting for that cost is 'low-latency access to data through transparent local caching'."  You don't need local caching; you're already doing that with the Ola Hallengren SQL Server Maintenance Solution.  That's probably why Retrospect doesn't support Ethernet VTLs; one can do local caching using Retrospect scripts.  IMHO, for you VTLs are just another gimmick for giving non-cloud-destination backup applications cloud destinations.

Second, you admit that the cloud solution you want isn't going to have a monthly cost appreciably less than sending tapes to Iron Mountain.  Pardon me for boring everyone else on these Forums with a repeat of how I do off-site backup in my itty-bitty home installation:  Every Saturday morning I do a Recycle backup of all my drives to a rotated-in portable storage device.  Then, early every other morning of the week, I do an incremental backup to that same portable storage device.  Every Friday mid-morning I carry the current-week portable storage device off-site to my bank branch two short blocks away and put it in my US$90/year safety deposit box, after removing the portable storage device I put there last Friday.  Since my bank branch isn't open 24/7/365 for immediate retrieval, I actually don't immediately re-use the portable storage device I retrieved—but instead store it just inside my apartment door for a week before re-using it.  Thus my 3 (could be just 2  given 24/7/365 retrieval) weekly-rotated backup sets are designated Red, White, and Blue.

And here's tape news; I now actually use pairs of portable storage devices; the device for my new computers is now a portable HDD, but the device for my late ex-wife's 2001 G4 Mac is a DAT tape cartridge (because two years ago Retrospect Inc. eliminated the ability to backup Macs with really old versions of macOS, so I run an old version of Retrospect on that old machine to backup using the DAT tape drive I used for years).  Last week I temporarily took home everything else stored in my bank safety deposit box, so I can tell you immediately (after measuring a folded document that was also stored in my box) that my US$90/year safety deposit box is just wide enough to hold an LTO cartridge—and long enough and high enough to hold a dozen stacked. 

Do you really mean to tell us that, if you sent a pogue to your colocation facility every three months carrying an LTO-8 tape drive and several LTO-8 cartridges, the other people at that facility would not permit him/her to plug in the tape drive and sit there changing cartridges until your every-three-month "archiving" job is finished—after which he/she would carry the newly-used cartridges to a bank safety deposit box? 🙄  I decided years ago that if Kim Jong Un H-bombed New York City my little retirement business as well as my personal affairs would be ended, so I don't store my portable storage backup devices at Iron Mountain.  If your organization needs to do off-site backup that can survive a nuclear attack, may The Auditor bless it. 🤣

P.S.: Mellowing slightly to consider how to do what you want with Retrospect: If each of your SQL Server backups won't exceed 25TB, you should back them up to the cloud using four 3-months-rotated destination backup sets.  You'd create a separate schedule for each destination backup set by clicking the Schedule button, scheduling it at 3-month intervals per pages 232–234 of the Retrospect Windows 16 UG.  For each of these 4 schedules you'd designate the destination backup set per the To:  pop-up shown at the right bottom of the second screenshot on page 233; of course you'd have to have pre-defined all 4 destination backup sets per page 177.  You'd also specify Recycle Backup in the Action: pop-up to the left of the To: pop-up, so that each backup set would be reused once a year.  So long as you use cloud destination backup sets this isn't going to decrease your cost—but it would avoid the danger Retrospect Tech Support warned you about.  You could greatly reduce costs by instead using 4 tape destination backup sets, per the third paragraph of this post, which wouldn't limit the size of each destination backup set to 25TB—while still allowing 1.5-day retrieval.  AFAICT your RClone solution would have remote site 100TB total and retrieve-speed limitations, unless your RClone remote site isn't subject to cloud providers' limitations.

Edited by DavidHertzberg
P.S.: If each of your SQL Server backups won't exceed 25TB, you should back them up to the cloud using 3-months-rotated destination backup sets. This'd avoid the danger Retrospect Tech Support told you about; tape destinations would be cheaper+unlimited.
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...