Skip to end of metadata
Go to start of metadata

Information

ZCB can be used to take backups of an SQL Server cluster. However, because ZCB is not fully "cluster-aware", there are certain restrictions to be aware of and requirements that must be met.

Icon

SQL Cluster Backups are provided as-is and have not been thoroughly tested to work in all environments. Proceed at your own risk.

Initial Setup

ZCB must be installed and a matching backup set configured on all nodes in the cluster.

  1. Install ZCB on the node which is preferred (primary) owner for the SQL Cluster resource.
    1. A new registry key ClusterName  (Type = String) must be created in HKLM\Software\Zmanda\ZWC\1.0\Engine\ . Key value must be name of SQL Cluster
  2. Configure a SQL Server backup set in ZCB
    1. Backups on the primary node can be configured as FULL and DIFFERENTIAL backups. Please see Differential Backups in a SQL Server Cluster below.
  3. Move SQL instance to another node and repeat ZCB install and configuration.
    1. Deactivate the new SQL backup set. Right click the backup set and select Deactivate.
    2. Configure only FULL backups on all other nodes. Please see Differential Backups in a SQL Server Cluster below.
  4. Repeat step 3 for all nodes in the cluster.
  5. Move SQL instance back to the preferred (primary) node.

Moving SQL Instance Between Cluster Nodes After Initial Setup

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

  1. First, Deactivate the SQL backup set on the current node.
  2. Move SQL instance to the another node.
  3. Activate the backup set on the new node.

Differential Backups in a SQL Server Cluster

Because ZCB 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 taken on Node 1 (the preferred owner) at transaction/record 100. SQL Server records that Full backup is taken.
    1. This backup would contain transactions/records 1 - 100. Type = Full.
  2. Your next backup, still on Node 1, is a Differential backup taken at transaction 150. SQL Server records that Differential backup is taken.
    1. This backup would contain transactions 101 - 150. Type = Differential. Corresponding Full is 1-100
  3. Now you move SQL Server to Node 2 at transaction 200 and take a backup. ZCB on this node is not aware of the backups on the other node, so you must take another Full backup.
    1. This backup would contain transactions 1 - 200. SQL Server records that Full backup is taken at transaction 200
  4. At this point, you move SQL back to Node 1 and take a Differential backup at transaction 250. SQL Server has record 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 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 issues such as the above, we recommend:

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

If 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 scheme by only installing and configuring ZCB on a single primary node.

Advise

Icon

We encourage you to read following article posted on MSDN blog: What data should you backup from your SQL Server cluster?

Help us improve this article!