Data Protection Highlight

Backup SQL Cluster in a Fail-over scenario with Veeam

What are the options to fully protect and Backup SQL Cluster?

Microsoft SQL Clusters offer an undisputed level of protection for the Databases hosted in their instances. In fact it is possible to move or decide which node of the SQL Failover Cluster will own the Role and pertinent Resources like Storage and Databases. This provides indeed great flexibility but not necessarily does everything to protect the SQL Cluster nodes themselves from failure and data loss.

And when it is happening it is already too late and in some cases troubleshooting the issues that occurred for example on the operating system level then simply the hardware used (memory, disk, network etc..) or potential viruses or other forms of threats can actually slow down or even impair any of the SQL Cluster nodes.

Hence, the need to protect and Backup SQL Cluster nodes and in particular when offering a Clustered SQL instance because of the relationship between the nodes.

Veeam Backup & Replication as per update 9.5 u3 offers a solution by mean of Protection Groups that successfully covers amongst different scenarios also this one. And in particular this article wants to explore the options with regard to Protection Groups and how to use them to Backup SQL Cluster nodes efficiently. The great news is that the Protection Groups configuration is transparent to both types of workloads: Physical and Virtual. This means the very same configuration applies to both worlds. So hybrid deployments or migrating from physical to virtual now can be executed with confidence knowing all workloads are protected equally.

This article is organised in 2 parts:

  • Backup SQL Cluster nodes using Veeam Protection Groups (Part 1)
  • Create a SQL Failover Cluster Backup Job (Part 2)

This article covers the first part. Let’s get the ball rolling!

 

Backup SQL Cluster with Veeam Protection Groups

One of the first things we’ll notice right after the update to Veeam Backup & Replication 9.5 update 3 is the addition of the “Inventory” tab which contains a view of the entire infrastructure. Both Virtual and Physical. From the Physical and Cloud Infrastructure menu we have the option to view edit and create Veeam Protection Groups. So let’s start creating one for our SQL Failover Cluster.

domalab.com Backup SQL cluster Veeam Protection Group

A new wizard for Veeam Protection Group offers all the options we need to configure. Let’s start by providing a name (which needs to be unique) and an optional description.

domalab.com Backup SQL cluster Create Protection Group

We have 3 options on how to populate this Protection Group. We add computers manually via IP or DNS name or simply provide a CSV list used to import all computer objects we want to protect with this particular Protection Group.

In this case we’ll leverage the 2 option in the GUI which refers to Microsoft Active Directory. In fact by mean of this option we can automatically detect all nodes which belong to the same cluster.

domalab.com Backup SQL cluster Protection Group type

Next is to specify the Active Directory name and if desired also the specific OUs and other containers where to look for these Cluster nodes.

domalab.com Backup SQL cluster select Active Directory

In this screen we can e very specific and point the finder to the desired OU.

domalab.com Backup SQL cluster add object active directory

In the same Veeam Protection Group we can add multiple Clusters. In my case the Clustered SQL Instance is running on top of the WinClu-01 cluster. Should we have more Windows based clusters we can always add them here including the single Computer names from Active Directory. For this purpose the cluster name is sufficient.

domalab.com Backup SQL cluster add cluster name

In this step now we can automatically exclude from the search specific Server types. Since in my home lab all Servers are virtual machines I have unselected the first two options. This means for example that in theory we could expand a cluster with a mix of physical and virtual nodes and we are still able to offer the same level of protection.

Additionally, we can also manually exclude certain objects if required.

domalab.com Backup SQL cluster objects exclusions

At this point we need to provide the credentials to access the SQL Cluster nodes with a user that has local admin credentials. The two main reasons for this are the following:

  • Install the Veeam Agent that will work in Managed Mode
  • interact with Server to take an application and crash consistent Backup

domalab.com Backup SQL cluster credentials

My advice is always to run a quick test for the credentials provided and make sure are working as expected. Additionally, it is possible to specify custom credentials for selected servers.

domalab.com Backup SQL cluster credentials test

In this step we can specify the Discovery and Deployment options. With the former we can dictate how often will rescan the Protection Group to identify new servers to add.

With the Deployment option we can define the Distribution Server and the Agent settings. In particular the Distribution server always offers the latest agent versions including the configurations.

For even more efficient incremental backups we have the option to install a proprietary Veeam CBT driver which greatly enhances the backup speed.

domalab.com Backup SQL cluster protection goup discovery

We can now do a quick review of the components to install. In case of main Veeam Backup server as Distribution point all the components are already present. So for clusters or computers in the same network we can simply use the default Veeam Server.

domalab.com Backup SQL cluster protection group review

At this point we are ready to apply the configuration and complete wizard with the next step.

domalab.com Backup SQL cluster protection group settings

As soon as all components are installed and verified the wizard now shows a successful message for the SQL Cluster Backup Protection Group.

domalab.com Backup SQL cluster portection group summary

In the main view we can now see the newly created Protection Group. We can use the same technique to add different types of servers both physical and virtual. The same applies also for Linux based Servers. We’ll cover this in a separate article.

domalab.com Backup SQL cluster protection group list

This concludes the first part about creating a Protection Group to Backup SQL Cluster with Veeam. Next step is to create a SQL Cluster Backup Job which automatically reads the configuration from this Protection Group and runs the backup. We’ll cover this in the next article.

About the author

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.

Add Comment

Click here to post a comment

Leave a Reply

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

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Browse articles

October 2018
M T W T F S S
« Sep    
1234567
891011121314
15161718192021
22232425262728
293031  

Articles by Category

Archives

%d bloggers like this: