Create a Fail-over SQL Cluster Backup Job with Veeam

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.

domalab.com SQL Cluster Backup Job create

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.

domalab.com SQL Cluster Backup Job failover mode

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.

domalab.com SQL Cluster Backup Job name

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.

domalab.com SQL Cluster Backup Job Protection Group

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.

domalab.com SQL Cluster Backup Job Backup mode

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.

domalab.com SQL Cluster Backup Job storage

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.

domalab.com SQL Cluster Backup Job advanced settings

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.

domalab.com SQL Cluster Backup Job maintenance

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.

domalab.com SQL Cluster Backup Job storage settings

With the throttling option we can reduce the Backup speed at the expense of a longer time to complete.

domalab.com SQL Cluster Backup Job throttling

For each backup job we can use global notification settings or even use custom ones.

domalab.com SQL Cluster Backup Job notifications

And finally on the script tab the option to run scripts before and after the SQL cluster Backup job executes.

domalab.com SQL Cluster Backup Job scripts

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.

domalab.com SQL Cluster Backup Job guest processing

When we click on applications button per object we can define the behaviour.

domalab.com SQL Cluster Backup Job application aware

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.

domalab.com SQL Cluster Backup Job general VSS

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.

domalab.com SQL Cluster Backup Job truncate logs

And lastly we also have the option to specify scripts which will interact directly with the Guest Cluster nodes.

domalab.com SQL Cluster Backup Job geust scripts

At this point we can also specify a schedule depending on the desired RPO and RTO.

domalab.com SQL Cluster Backup Job schedule

Finally a quick summary showing the main options for the SQL Cluster Backup job.

domalab.com SQL Cluster Backup Job summary

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.

domalab.com SQL Cluster Backup Job rescan protection group

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.

domalab.com SQL Cluster Backup Job node properties

If we take a look at installation folder for the Veeam Agent will look something similar to this.

domalab.com SQL Cluster Backup Job agent install

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!

domalab.com SQL Cluster Backup Job view

We can use trial or evaluation licenses and these will look something similar to the one below.

domalab.com SQL Cluster Backup Job license

Now we are ready to run our job successfully!

domalab.com SQL Cluster Backup Job status

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.

Michele Domanico

Passionate about Virtualization, Storage, Data Availability and Software Defined Data Center technologies. The aim of Domalab.com is sharing with the Community the knowledge and experience gained with customers, industry leaders and like minded peers. Always open to constructive feedback and new challenges.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: