In this article as a second part from the previous post on how to Backup SQL Failover Cluster we are now ready to create the configuration for a SQL Cluster Backup job in Veeam.
In the previous step in fact we have created a Protection Group automatically listing all the nodes part of the SQL Cluster. To do this we are leveraging the properties from Active Directory where the SQL Cluster actually resides.
In this step we’ll create not only the SQL Cluster Backup Job but also tell to work in “Failover cluster” mode. Essentially there are three modes, Workstation, Server and Failover cluster. The latter will make sure all detected nodes will be automatically included and make sure all storage resources attached to the nodes are included in the backup accordingly. In a nutshell this Backup mode is able to detect the current owner of the Cluster and run the Backup Job accordingly.
Another important aspect is the option to take both application and crash consistent Backups. When the latter works on data blocks, the former is interacting with Microsoft VSS framework and by mean of application VSS writers take a Backup according to the vendor specifications.
Moreover in the case of Microsoft SQL Backup we can decide if and how to truncate the logs choosing to own the SQL Databases Backup chain as well. Last but not least from the very same Backup data set we use the Veeam Explorer for SQL to perform an in place and out of place restores.
Let’s see a SQL Cluster Backup job in action!
SQL Cluster Backup job with Veeam
From the Protection Group we have created let’s do a right click and Add to backup job > windows > new job.
A new wizard is starting and for our scenario we’ll go for the third option “Failover cluster”. This option only allows to be managed by the Backup Server. Which is sensible considering the SQL Cluster in this case will be permanently connected with the Veeam Backup Server. Same applies in Server mode whereas for Workstation we can still choose between the two.
At this point let’s give a name and a description similar to the one in the screenshot below. I like to add the prefix Agent to immediately distinguish this one from the other backup jobs.
In the list of computers we can now add the Protection Group as per previous article. This means all computer nodes in this group will be automatically managed by Veeam Backup Server.
In the Backup mode we can choose between Entire computer and Volume level backup. For our scenario let’s opt for the first one as we want to make all detected drives as part of a SQL Cluster install are included in the Backup job.
At this point we can specify the storage where to save this backup or in other words a Backup Repository. From the same view also the number to set the restore points to keep on disk.
From the Advanced button now we have access to finer details about the SQL Cluster Backup job. In particular from the first tab we can choose when to create Synthetic and Active Full for a Forward Incremental Backup chain.
In the Maintenance we can enable an health check for the Backup files. If required also remove data pertinent to an evicted node in this case from the Cluster. This will help reducing the size of the Backup file. In addition, the option to defrag and compact the full backup file thus increasing the restore performance.
From the Storage we can decide on the compression and encryption of the Backup file. Depending on the storage target we can also operate on data blocks optimization with sizes ranging from 512 KB to 4096 KB should a single Backup file exceed 16 TB in one go.
With the throttling option we can reduce the Backup speed at the expense of a longer time to complete.
For each backup job we can use global notification settings or even use custom ones.
And finally on the script tab the option to run scripts before and after the SQL cluster Backup job executes.
In the Guest processing we have the ability to interact with the Guests in our case the Cluster nodes and take an application consistent Backup leveraging VSS.
When we click on applications button per object we can define the behaviour.
First we need to enable the application-aware processing. Then we can also choose to process the transaction logs or not. Now despite the name not necessarily the transaction logs refer to SQL ones. In this case here they refer to all transaction logs for other applications that run on the computers. A quick command to show the available VSS writers is
“vssadmin list writers”
To get an idea of additional applications leveraging the VSS framework.
Now in the case of SQL we have the option to specify the user Veeam should use to interact with SQL Server and it’s Databases. Ideally a user with at least Databases Owner rights.
Next we can define the interaction by choosing to truncate logs (very useful for Databases in Full Recovery Model), not truncate them or Backup Transaction Logs too on a scheduled basis to reflect the current SQL Cluster settings for its Databases.
And lastly we also have the option to specify scripts which will interact directly with the Guest Cluster nodes.
At this point we can also specify a schedule depending on the desired RPO and RTO.
Finally a quick summary showing the main options for the SQL Cluster Backup job.
As soon as the wizard completes the Veeam Backup Server console will scan the Cluster nodes and install the Management Agent. This agent will act as a bridge between the Veeam Server and Cluster nodes and will help to assist the backup and restore operations of the Veeam Agents.
For each SQL Cluster node we can also see the properties of the Veeam Agent showing amongst other things the version and if the Veeam CBT driver is installed.
If we take a look at installation folder for the Veeam Agent will look something similar to this.
Now that SQL Cluster Backup job is created we are ready to run. Hold on a second, the job has failed!
Reason: let’s make sure the Veeam Agent licenses are installed!
We can use trial or evaluation licenses and these will look something similar to the one below.
Now we are ready to run our job successfully!
This concludes a quick overview on how to run SQL Cluster Backup job with the latest release of Veeam. In separate articles we’ll discuss how to run different restore scenarios.