Backing up of Always On Availability Groups

[jcofin13]

WE are working to move the majority of our SQL databases (we have 100s) from single SQL servers to Always on Availability Groups.

I guess Im curious about a few things.

1. if you move a DB from a sql server to an always on availablity group, the backup will see it as new data as it will be tied to 2 new servers AND according to the documentation, you need to use an agent/protection group to back this up vs just backing up a stand alone SQL server like we do now. Is this correct?

2. IF that is the case we will need to double our storage size and expect that our public cloud storage usage will double as well. Both of these will be a significant cost we will need to somehow size and plan for.


As far as the documentation for this goes:
https://helpcenter.veeam.com/docs/backu ... ml?ver=120

Step1. Step one says to create a protection group and add to it the failover cluster account. Im sure our SQL team will have this account handy. Veeam itself is not domain joined per best practice. Is is best practice then to have a domain account specifically for connecting to a DC in order to add the AD account for the cluster in the first place? IF so what permissions should that account have? Is read all that's required to be able to be able to browse the AD once the connection is established to do so?

Also, i have to assume it is best practice to have separate backup jobs rather than adding protection groups to existing backup jobs with other vms?
For example, create a separate job for each Fail Over Cluster. I think this makes the most sense but there is no mention of this in the documentation.

Thank you in advance. WE are just planning at this point but i know our DB team is itching to get everything running on Always on Availability Groups and we want to make sure we are getting good backups.

[PetrM]

Hello,

1. Yes, that’s correct. However, you can also protect Always On running on virtual machines. You can find more information on this page.

2. As a workaround, you may reduce the backup block size ("Storage optimization") to achieve a higher deduplication ratio (f.g. 256 KB). Furthermore, our plug-in for Microsoft SQL Server is designed to process data from the preferred node only, please refer to this section of our user guide.

3. Am I correct in understanding that your question is about the account you need to use in the "Specify Domain" window on this wizard step? If so, as far as we can see on this page:

Please select a user account from the Account list that is a member of the DOMAIN\ADMINISTRATORS group.

4. There are no best practices regarding the number of jobs. It is solely up to you to decide how many jobs to use, depending on the required schedule, repository, and other settings such as the transaction log backup interval, etc.

Thanks!

[jcofin13]

Ah thats great. I was reading the Agent based documentation and thought that was the only option on how you had to backup vms using always on avail groups. I didnt realize you could just backup the cluster like a normal VM and it sorts it out for you on which is the primary and secondary. Thats great. For this image based backup is it possible do then restore individual dbs back to an always in avail group or would you need to restore the entire server (image level) to get a single db restored? I imagine this is where the plugin comes into play?

It doesnt look like the plugin is required? But it does look like the plugin will give you some further flexibility? If you dont use the plugin does it backup the DBs twice being that it backs up both servers. What is the benefit of having the plugin vs not having the plugin? Can you still restore individual dbs to an always on availability group without it?

Sorry....im not a SQL guy and our SQL guys are not backup savvy and I'm going to imagine wont want the plugin loaded in the servers. I need to understand it better and give them the option for it.

(For the question about the creds, i think that is void now and not something i need to worry about as i wont need to setup agents like i originally thought)

[jcofin13]

Ok...so i have taken the time to review this again.

Let me see if i have this straight

If you have VMs, you dont need an agent or protection groups for the always on availability group servers.
With always on availability groups, you just turn on application aware processing and it will sort out if it has a always on cluster and backup the node for you as stated in the "microsoft sql log backup support page" (which an odd place to put it) https://helpcenter.veeam.com/docs/backu ... ml?ver=120 and then the logs are set in the same way as you would any other SQL tlog back up by selecting the frequency of which you want those backed up with the default being 15 minutes.

So essentially backing up a always on availability group is no different than backing up a single SQL server vm. Veeam will detect it and just back it up as expected.

What am i missing?

On top of that, is it optional then to load the veeam plugin for MS Sql into my SQL servers and have additional functionality without using the veeam console and writing directly into the repo?

IF you have a availability group, and you dont use the plugin, can you still "backup and restore" databases into it or is this piece required?
The plugin support page seems to mention the following "Veeam Plug-in supports backup and restore of Microsoft SQL Server databases that operate as part of an Always On availability group." No where else is it mentioned if you cant do so without it.

The always on availability group logs page makes no mention of the plugin
The always on availability group agent page makes no mention of the plugin.
Neither of those 2 pages say the plugin is required.
The plugin page makes it sound like it might be required but does not state that it is or what the point of it is other than to possibly run backups and restores directly from the SQL Management Console and write directly into the Veeam repos.
The main application aware processing page makes no mention of always on availability groups or the plugin.

Further this page #5 states that you cant have "encrypt stand alone backups stored on this repository" enabled..via this:
"Veeam Plug-ins cannot send backups or backup copies to a backup repository where encryption is enabled. Thus, make sure that the Encrypt standalone application backups stored in this repository check box is not selected."

Clear as mud.

Does that mean you cant have encryption set on the data in the job as the checkbox implies or that you cant have encryption set on the repo like the statement implies? I guess that would be a requirement that would keep us from using the plugin as we must have everything encrypted at rest.
When the plugin is used are the backups encrypted or are they not encrypted? From the above statement via the helpcenter page it would seem that they cant be.

Apologies for the long ramblings. ITs just very hard to understand how this works having never used it.

[PetrM]

Hello,

No worries, let's try to sort out all of your questions.

1. If you back up VMs, you do not need to have a protection group; just add these VMs to a job. However, all VMs will be backed up anyway, but our logic that analyzes the guest OS application topology will identify the preferred node to process the transaction logs according to the workflow described on this page.

2. There is no requirement to install the plug-in; it is up to you to choose what to use: image-level backup, the plug-in, or both (if using both, one of them must operate in Copy-only mode). Please refer to our FAQ, there is a good summary that will help simplify your decision-making.

3. If you use the image-level backup, you will have the option to restore entire VMs and individual databases using Veeam Explorer for Microsoft SQL Server (including Instant Database Recovery). On this page, you can find more information about restoring to Always On. If you use the plug-in, you can restore individual databases only using the plug-in restore wizard.

4. Encryption is not available for the SQL plug-in, but this feature will be included in our v13 release. Stay tuned.

Thanks!

[jcofin13]

Thank you for the clarification on all these topics!

I think that answers all the concerns i had that were not clear.