Backup Considerations

• When the SQL virtual cluster object is selected for backup, it discovers and backs up the primary replica database from whichever SQL instance happens to host it. Although the WSFC cluster is aware of all the primary and secondary replica copies, DPX only discovers the location of the primary replica, and use that SQL instance for the backup. The database administrator has the option to use the SQL Server administration console to select a backup preference (primary or secondary) and to assign preference weights to the instances. DPX ignores these flags and backs up the primary replica only. Attempting to force a backup of a secondary replica results in a Microsoft VSS error; the backup fails and will not contain a valid data set. If standalone SQL instances and databases that are not under availability group control display under the SQL cluster virtual object, you can select these instances to back up, and the WSFC cluster directs the backup to connect to the appropriate SQL instances. Multi-site clusters that span subnets are supported. The only prerequisite is that the cluster CMagent process must reside on a node that can directly access every other node in the WSFC cluster. The DPX back up job starts with a discovery process to find where the necessary database components reside and directly connect with the SQL instance hosting the databases of interest. If the primary replica node experiences a failure part way through the backup, the DPX backup job fails. The next execution of the backup job discovers the new primary replica location and backs up the resources from that new location. A WSFC cluster does not operate like a traditional Windows cluster. In a traditional Windows SQL cluster, the Windows cluster manages a set of shared storage disks. When a cluster node fails, the shared storage migrates to other available cluster nodes. Since the same storage is shared between servers, the backup of this storage will follow an expected pattern of base and incremental transfers as physical device availability moves between the cluster physical nodes. A WSFC cluster is different in that each SQL instance is a separate and distinct server with its own local storage pools. Primary and secondary replicas stay synchronized via network transfer of transaction log files. Each time the primary replica moves, it could be hosted on a new server that has its own unique attributes that must be protected. The storage characteristics of WFC clusters have consequences for storage planning. When you run the first base backup of an AlwaysOn database, the base image is taken from the server currently hosting the primary replica. As long as the primary replica does not move to another node, subsequent backups are incremental. When the WSFC must move the primary location (either due to failure, or a planned move for maintenance), the primary replica is hosted on a new server and a new device, and the next backup will be a base backup, followed then by incremental. This movement effectively doubles your secondary storage and license consumption; this increase in secondary storage is expected and by design. A cluster physical node that has already experienced a base backup does not typically need a re-base if the primary replica is migrated back to it; however, depending on the amount of change since the last base/incremental cycle, the new incremental transfer could be large. Keep this mind when architecting AlwaysOn backup strategies. The initial storage & license consumption roughly correlates to the amount of available SQL data; however, since the cluster is composed of separate and distinct nodes, the backup over time (as the primary replica moves) could consume an amount of secondary storage and backup capacity license equal to the sum of storage available across all of the WSFC nodes. Surprises with growth in storage and license can be known and controlled if all of the physical nodes participate in regular backups. See BMR Recovery Considerations for considerations related to BMR backup consistency.

• For SQL Server 2012 with AlwaysOn configured, only a backup of the primary replica is supported.

• When coordinating a restore, the destination SQL 2012 Availability group must have at least one availability database present in the group in order for DPX to browse the availability group as a valid destination.

• A single SQL instance is allowed to host more than one availability group, and each availability group is allowed to use that instance for either primary or secondary replicas. Thus, it is possible for a SQL instance to have a mix of primary and secondary database replicas for different availability groups. If the availability groups are hosted on separate physical devices, then the backup of the primary instance would be expected to behave similarly to the existing standalone SQL server functionality with respect to backup change rates. However, if a single physical device hosts both primary and secondary availability resource groups, it is possible that the backup will experience very large change rates due mainly to the secondary replica instance accepting, storing, and rolling transaction logs into replica databases.

• Since a WSFC cluster is composed of a collection of participating servers, it is possible to scan these nodes into the management console and to include them in backup jobs. However, as noted above, browsing a physical node will not present SQL objects; consequently, backup of these independent nodes will have no SQL server application consistency. This is true even when the node has a standalone SQL instance; backup of that instance must be made through the cluster resource.

• Special considerations apply to BMR object backup and recovery. If you need the ability to use Windows BMR to recover the entire cluster, you must back up the WSFC cluster virtual node and all of its physical nodes in a single backup job. This ensures that the cluster and all dependent components are backed up in one consistent set for recovery.