6

I have a virtualized Windows Server 2019 instance configured as failover cluster node with SQL Server 2016 Availability Group node running on it. I have already set up "traditional" SQL server backups with a regular full / diff / log backup schedule.

Now I am wondering if it would be possible to use Azure Backup to backup the entire Windows Server instance in order to be protected even better from a possible ransomware attack (as documented here.

I do not want to replace my regular backup strategy, but add an additional backup method without breaking the existing setup. Would this work with Azure Backup for Windows Server, or would it interfere with my current backup strategy?

Edit: To clarify why I asked this question in the first place: I want to know if setting up Azure Backup would break my existing full / diff / log backup chain since both backup methods might use VSS.

10
  • 1
    I took a quick look for more info but wasn’t able to find it. I’m not familiar with MARS, but when you use another backup product on top of SQL server backups you have to make sure the other product performs “copy only” backups. A “full” backup in another product will trigger something in VSS that tells SQL server that a new full backup took place which will invalidate your diff/log chain. I found info regarding this on Azure VMs, but nothing specific to your scenario. – Appleoddity Dec 24 '19 at 14:45
  • @Appleoddity: Good point, and this is precisely why I posted my question. – Adrian Grigore Dec 24 '19 at 21:21
  • Have you seen the article Back up SQL Server databases in Azure VMs? – harrymc Jan 11 '20 at 19:51
  • @harrymc: Yes, but it does not seem to apply completely. My SQL Server instance in hosted on an on-premises Hyper-V VM. – Adrian Grigore Jan 12 '20 at 20:07
  • This applies then to your case : Back up on-premises applications and data to cloud. – harrymc Jan 13 '20 at 8:31
5
+75

I wanted to clarify as per that post you linked to in the question and you are asking about "to be protected even better from a possible ransomware attack" specifically as per the. . .

Essentially Azure Backup gives you three levels of backup technology that you want to use. . .

  1. Files and Folders
  2. System State
  3. Full volume point-in-time and image backups
    • Bare Metal (on-premises)
    • VM level backups (OS and other volumes)

There are other types of Azure Backup agents you can add-on to take care of the SQL Server backups for you, but since you want to not affect your existing SQL Server backup strategy nor affect the backup log chain of your existing SQL Server backups, don't use that agent.

  • Simply don't use any SQL Server specific Azure Backup agents or functionality

In terms of a full system recovery and the correlated restore operations in the event any are needed—which is the entire purpose of backups to begin with, adding the Azure Backup level backups to your backup and recovery strategy will help you be better protected and be able to get your system back up and running quicker in the event such an action is needed.

Well if getting your system back up and fully functional in a timely manner is of any importance, then adding the Azure Backup strategy will better protect your system operations/availability/resiliency.

With just the SQL Server backups as you have today, in the event of a full system crash and recovery is needed, you need to get your Windows Server installed, and then get SQL Server installed and configured, and then get your SQL backup files and transactions logs copied over from backup media, and then start restoring the SQL Server database level stuff.

Think about your backup strategy from a perspective of a full system recovery. Think about what data you need to get your system back up and running and go from there as a starting point.


Azure Backup Recovery Understanding

Testing and documenting the restore\recovery process for future reference with the Azure Backup data and technology once you get something in place—that you confirm works without breaking the existing SQL backup jobs, is important so don't overlook this critical step.


Does Azure Backup for Windows Server affect regular SQL Server backups on the same server?

  1. Obviously it can affect the SQL database regular backup jobs if both the Azure Backup job and the SQL Server backup jobs are running at the same time—they will likely need to share CPU, memory, and disk resources from the same Windows Operating System and thus potentially affect each other at this level.

    Concurrently using shared resources from the same OS could equate to the SQL Server database backup jobs taking longer to complete. You won't know the specifics for certain until you just do it. It is important that your current restore strategy have an easy way to get the backup jobs back in sync in the event that it does break and you know the motions to take.

  2. In terms of breaking your SQL Server database backup chain, unless you are running SQL Server database specific job types where you could lose a transaction log or changing SQL Server database recovery models with the Azure backup technology, then it should not have any impact whatsoever on the SQL Server database log backup chain.

    I don't have much experience with SQL Server High Availability but I know when you have databases in standby applying transactions from a primary DB such as with Log Shipping, that the chain is of high importance and if it's broken, the Log Shipping process breaks too since it's not able to commit the transaction from the latest log backup from the primary database.


Safe Options (per your concern and requirements)

If you can ensure Azure Backup will not perform any SQL Server database backup operations, and you do NOT use it for any SQL Server specific operations or select it, then you will be fine.

I think you'll be safe to use Azure Backup for backing up files and folders, entire VMs, system state, or for bare metal recovery and won't have any issues with anything breaking the log chain with your SQL Server database backups.

enter image description here


Supporting Resources

  • Back up system state and restore to bare metal with Azure Backup Server

    System state backup: Backs up operating system files, so you can recover when a computer starts, but system files and the registry are lost. A system state backup includes:

    • Domain member: Boot files, COM+ class registration database, registry
    • Domain controller: Windows Server Active Directory (NTDS), boot files, COM+ class registration database, registry, system volume (SYSVOL)
    • Computer that runs cluster services: Cluster server metadata
    • Computer that runs certificate services: Certificate data

    Bare-metal backup: Backs up operating system files and all data on critical volumes (except user data). By definition, a BMR backup includes a system state backup. It provides protection when a computer won't start and you have to recover everything

0
1

I understand that your main worry is whether two backup jobs, both using Shadow Copy at the same time to generate two different backup sets, will then both conflict in some way, thus causing failure of one or both.

The answer is that no such conflict is possible.

The Microsoft article Shadow Copy Creation for Providers has this quote that answers your question:

There is no limit on the number of shadow copy sets or the number of shadow copy sets of an original volume.

To better understand the term "shadow copy set", from the article Shadow Copies and Shadow Copy Sets:

A shadow copy set is a collection of shadow copies of various volumes all taken at the same time. VSS identifies each shadow copy set by a persistent GUID.

A "shadow copy set" is then a snapshot of one or more disk volumes and other sources that are included in the shadow copy operation. This may include providers that are not only hard disks, as long as they have the capacity to take part in this operation.

From the above, it is clear that each shadow snapshot is stand-alone and will not conflict with other such operations. Each one has its own GUID by which Windows can safely and distinctly handle all of them in parallel, so that all backups are consistent and correct.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.