Solved

Best practice for VSPC and Veeam ONE SQL configuration database high availability


Userlevel 7
Badge +10

I have what I hope is a simple question. What’s the best practice for the a high availabilty SQL server for the VSPC or Veeam ONE configuration database. I see in https://www.veeam.com/kb2301 how to deploy SQL AOAG for VBR and VEM. I’m not sure if this applies to VSPC or Veeam ONE.

icon

Best answer by Iams3le 25 May 2023, 21:02

View original

7 comments

Userlevel 7
Badge +6

Hello @HangTen416 

It applies to them too.

Userlevel 7
Badge +9

Hi @HangTen416 Just as @Moustafa_Hindawi have mentioned, the guide you shared is excellent and covers the best practices for deploying SQL AOAG also for VeeamOne etc. 

Same steps are applicable! Do not forget to configure the VeeamOne server to use the Availability Group listener as the configuration string for the database. This will ensure it ALWAYS connects to the primary node. Lastly, do not forget to manually test the failover process…

Userlevel 7
Badge +20

In all honesty, as Veeam ONE is for monitoring only, I wouldn’t HA the database unless you’ve got money to burn.

 

HA databases can be more trouble than benefit so considering VBR is a single instance, I’d avoid it in general unless service provider or enterprise.

 

Setting them up is quite straight forward, and well documented here: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-manually-configure-tutorial-single-subnet?view=azuresql

 

The number 1 thing I see tripping people up with these is that you have to use the full recovery model for database + transaction logs, but then nobody backs up the database and truncates the logs, thereby endlessly growing them. Saw this a few times in VCCs using this because VCC VBRs can’t do any backups themselves. And so they forgot to backup their backup architecture other than config backups and maybe the VBR server itself.

 

Other than that I’ve seen people targeting the incorrect listener such as the cluster or a server FQDN instead of the always on listener, I also see difference in the authentication configuration between the always on nodes.

 

Its simple to setup, a headache to fix when it’s wrong because everyone expects it to never have downtime 🤷‍♂️

Userlevel 7
Badge +9

> In all honesty, as Veeam ONE is for monitoring only, I wouldn’t HA the database unless you’ve got money to burn.

 

Good point! But then he also specified the VSPC. Then I went mute… Here is an interesting link for the later: https://bp.veeam.com/sp/MSP/M_Design/vspc-scale.html 

Userlevel 7
Badge +22

In all honesty, as Veeam ONE is for monitoring only, I wouldn’t HA the database unless you’ve got money to burn.

 

HA databases can be more trouble than benefit so considering VBR is a single instance, I’d avoid it in general unless service provider or enterprise.

 

Setting them up is quite straight forward, and well documented here: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-manually-configure-tutorial-single-subnet?view=azuresql

 

The number 1 thing I see tripping people up with these is that you have to use the full recovery model for database + transaction logs, but then nobody backs up the database and truncates the logs, thereby endlessly growing them. Saw this a few times in VCCs using this because VCC VBRs can’t do any backups themselves. And so they forgot to backup their backup architecture other than config backups and maybe the VBR server itself.

 

Other than that I’ve seen people targeting the incorrect listener such as the cluster or a server FQDN instead of the always on listener, I also see difference in the authentication configuration between the always on nodes.

 

Its simple to setup, a headache to fix when it’s wrong because everyone expects it to never have downtime 🤷‍♂️

Excellent answer @MicoolPaul I was thinking about this today and remembered something blurry, at first thought it was simply not supported but now I remember someone saying that it was overkill and created more problems than was worth! 

Userlevel 7
Badge +10

Thanks, everyone. Great answers as always. And as always, the answer for HA or not is “it depends”...but it’s good to see that there are links to relevant KBs for SQL configuration except for VSPC.

The VBR documentation links to this: https://www.veeam.com/kb2301

The VONE documentation links to this: https://www.veeam.com/kb2312

The BP document is really useful for VSPC SQL configuration. Thanks @Iams3le 

Userlevel 7
Badge +9

Thanks, everyone. Great answers as always. And as always, the answer for HA or not is “it depends”...but it’s good to see that there are links to relevant KBs for SQL configuration except for VSPC.

The VBR documentation links to this: https://www.veeam.com/kb2301

The VONE documentation links to this: https://www.veeam.com/kb2312

The BP document is really useful for VSPC SQL configuration. Thanks @Iams3le 

You are welcome!

Comment