Carbonite Support > Performing Backups of an SQL S...

Performing Backups of an SQL Server Cluster

  • This article is for Windows only

Summary:

Carbonite Safe Server Backup can be used to perform backups of an SQL Server cluster, but since CSSB is not fully cluster-aware, there are certain restrictions to be aware of and certain requirements that must be met.

Note: A cluster-aware application is an application that calls the cluster APIs to determine the context under which it is running (such as the virtual server name etc.) and can failover between nodes for high availability.

SQL Cluster backups are provided as-is and have not been thoroughly tested to work in all environments. We strongly recommend installing Carbonite on your machine and performing a test backup and restore of an SQL Server cluster to ensure it will work in your environment.

Solution:

The sections below are collapsed. Please click the section title to open / close a particular section.

Initial Setup

Carbonite Safe Server Backup must be installed and a matching backup set must be configured on all nodes in the cluster.

  1. Install CSSB on the node which is the preferred (primary) owner for the SQL Cluster resource.
    1. A new registry key named ClusterName (Type = String) must be created in HKLM\Software\Zmanda\ZWC\1.0\Engine\. The key value must be the name of the SQL Cluster.
  2. Configure a SQL Server backup set in CSSB.
    1. Backups on the primary node can be configured as FULL and DIFFERENTIAL backups. Please refer to Differential Backups in an SQL Server Cluster.
  3. Move the SQL instance to another node and repeat the CSSB installation and configuration process.
    1. Deactivate the new SQL backup set. Right click the backup set and select Deactivate.
    2. Only configure FULL backups on all other nodes. Please refer to Differential Backups in an SQL Server Cluster.
  4. Repeat step 3 for all nodes in the cluster.
  5. Move the SQL instance back to the preferred (primary) node.
Moving SQL Instance Between Cluster Nodes After Initial Setup

If you move the SQL instance from the primary cluster node to another node after the initial setup, you must also adjust your CSSB configuration unless you are planning to move resources back before scheduled backup takes place.

  1. First, deactivate the SQL backup set on the current node.
  2. Move the SQL instance to the another node.
  3. Activate the backup set on the new node.
Differential Backups in an SQL Server Cluster

Since CSSB is not fully cluster-aware, you must take great care when using Differential backups in a clustered environment. Imagine the following scenario:

  1. Your first Full backup is performed on Node 1 (the preferred owner) at transaction/record 100. The SQL Server records that a Full backup is performed.
    1. This backup would contain transactions/records 1 - 100. Type = Full.
  2. Your next backup, still on Node 1, is a Differential backup performed at transaction 150. The SQL Server records that a Differential backup is performed.
    1. This backup would contain transactions 101 - 150. Type = Differential. Corresponding Full is 1-100.
  3. Now you move the SQL Server to Node 2 at transaction 200 and perform a backup. CSSB on this node is not aware of the backups on the other node, so you must perform another Full backup.
    1. This backup would contain transactions 1 - 200. The SQL Server records that a Full backup is performed at transaction 200.
  4. At this point, you move the SQL Server back to Node 1 and perform a Differential backup at transaction 250. The SQL Server records that the last Full backup was at transaction 200.
    1. This backup would contain transactions 201 - 250.
    2. However, the last Full backup on this node only contains transactions 1 - 100.
  5. Now there is a problem. Transactions are missing from the backup chain. Only the Full backups can be restored.
    1. A restore of the Full backup on Node 1 would only contain records 1 - 100. The only Differential backup that can be restored is the one containing transactions 101 - 150. Transactions 151 - 250 are lost.
    2. A restore of the Full backup on Node 2 would only contain records 1 - 200. The Differential backup from Node 1 cannot be restored to Node 2. Transactions 201 - 250 are lost.

To avoid such issues, we recommend performing the following actions:

  1. Use Full or Differential backups only on the single primary node; i.e. on the preferred owner of the SQL instance cluster resource.
  2. Only use Full backups on all other nodes.
  3. If SQL data has been backed up while residing on another node, the first backup must be a full backup when you move the SQL instance back to the primary node.

Note: If the SQL instance is only moved to other nodes of the cluster for a short period of time, such as for maintenance, you can simplify your backup settings by only installing and configuring CSSB on a single primary node.

For information about what data you should backup from your SQL Server cluster, refer to this Microsoft article.

Feedback