Skip to main content

Best Practice for Backup of MS SQL Always on Availability Groups


Forum|alt.badge.img+7

Hi!

 

We are in the process of replacing our old 6-node MSSQL cluster with a new 4-node cluster that will be using Always On Availability Groups. 

The existing cluster and it’s backups was configured many years ago by someone else who is no longer working here, and I am not a Windows Cluster, or MS-SQL person, but more of a storage person :-)

 

Now, i’m trying to wrap my head around what would/should be the “best practice” way of configuring this for backups & restore.

What i have done so far is of course to install the mssql-agent on all 4 physical nodes. 

And from what i can figure out from reading through documentation, it seems i should create a “MSSQL AG Client” pseudo-client for each “ms sql availability group”, which would add up to around 15-20 different pseudo-clients.   

 

However, when looking at the current existing configuration for the old MSSQL-cluster, it is only a single “pseudo-client” that contains all “ms sql availability groups”…. so i guess the question is, how did they make that happen ?

Have they used a different kind of pseudo-client, like the Windows Cluster client, that i have seen mentioned in this post: Mssql always on Failover Cluster Instances configuration | Community (commvault.com)

 

And what would be the recommended and “Best Practise” way of doing this ?

 

 

 

If you have a question or comment, please create a topic

4 replies

Forum|alt.badge.img+7
  • Author
  • Byte
  • 42 replies
  • April 5, 2022

Ah, after fiddling around a bit i noticed that after creating the first pseudo-client, i could just choose “All Tasks” and then “New SQL Server” and then point that to a cluster-node and it’s Availability Groups, to add them all into one pseudo client, instead of one pseudo client for each availability group.

 


Forum|alt.badge.img+14

@Bjorn M 

Correct both methods are options. Depending on the config some customers prefer to have separate clients available for various reasons like management, reporting, grouping different clients/instance etc.

 


Nikos.Kyrm
Byte
Forum|alt.badge.img+13
  • Byte
  • 192 replies
  • April 29, 2022

Hello, in SQL Always on AG (2 nodes) is there any way to force run the backup jobs from the secondary replica server? In order to save some cpu / ram performance from primary replica.

Thank you in advance!


Forum|alt.badge.img+14

@Nikos.Kyrm 

Yes. For full backups you can enable the option for copy only. When this option is enabled, the full backup will run on the replica. For transaction log backups they always run from the preferred replica that is configured on the AG backup preferences.

AG Backup preference:

https://www.mssqltips.com/sqlservertip/4976/sql-server-alwayson-availability-group-backup-preference-setting/#:~:text=SQL%20Server%20AlwaysOn%20Availability%20Group%20%28AG%29%20allows%20backups,Availability%20Group%20Wizard%20and%20use%20the%20default%20configuration.?msclkid=6c64ef26c7b211ec84e974076cce4047

To enable copy only in CommandCenter:

https://documentation.commvault.com/11.24/essential/128397_configuring_copy_only_backups_on_availability_group_instances.html

To enable copy only in Java CommcellConsole: Its under advanced options “copy only” check box

https://documentation.commvault.com/11.24/expert/61510_advanced_backup_options_data.html 


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings