Commvault – how to configure MSSQL failover cluster backup
In this article, we will focus on configuring backup for active-passive ms-sql cluster using commvault agent backup
One of the my customer build the active-passive ms-sql cluster based on VMware vSphere 8 (with shared disks), but shared disk not support snapshots operation, so i need configure a agent backup.
Of course you need to have a working firewall, because sql and commvault is in separate networks, don’t forget about DNS – it is usually the culprit of all problems.
First of all i need install the ms-sql agent on both sql nodes in cluster. so i copied the .exe and install same version as my commserve – in my case 11.36.55. I using the authcodes for the registration, in my opinion is the simpliest way to register client.
My collegue created a gMSA account in AD only for our backup purposes (you can use classic domain user of course) So next step is add this account both nodes:
- to the local administrators group on both nodes
- to the mssql management studio as sysadmin
- under services.msc – find the cvd service and change logon as gMSA account
please restart all commvault services – you can do it from the commserve or localy on the nodes.
Now is time to open commcell console and create a new client > windows cluster.
please fill the client name and hostname – host name is fqdn of your failover cluster
on the cluster group configuration tab please add your mssql nodes – in my case V2 and V3
and on the agents tab – add File System and iDA for MSSQL Server
In the control panel > open SQL iDATA Agent and set your user, which have permissions to sql nodes – in my case the gMSA account.
create a Client Computer Group with members – node V2, node V3 and your MS-SQL cluster client.
on the client computer group level, in the tab Advanced Settings set – Override Higher Levels Settings and Use Local System Account
on the Additional Settings add > bDetectClusters
now go to the Client level and choose your ms-sql node, in the properties of SQL Server set Override Higher Levels Settings and Use Local System Account – repeat it for all ms-sql nodes.
We need configure the Job Results Directory. That folder must reside on a shared cluster driver. So i added disk to failover cluster, which is dedicated for the Job Results Directory.
Now go to your cluster in the Client Level > Properties > Advanced Client Properties and to tab Job Configuration. Please dont use „Impersonate User“ the user have to be „empty“.
Now click on the Client Level > Your Cluster > SQL Server > Add Database, please first go to tab Accounts and like before – Override Higher Levels Settings and Use Local System Account.
Go back to General and type your server name (in some case is auto-discovered) but not in my case 🙂
We have created a ms-sql cluster configuration and its time to create a backup plan, after the plan is created please assign it to the instance. like i said in the beginning we have a active-passive cluster, that means:
The SQL instance must run on the node, which is also the active cluster node. For example, my instance BMD is running on the node V2 and node V2 is current host server for the cluster. IF the instance will be running on the node V3 and Current host server is V2, backup fails.