SQL Server protection

DPM enables protection of SQL Server in various configurations. Typical SQL server configurations include standalone SQL Server, SQL Server deployed in a clustered environment with Windows Server Failover Clustering as well as SQL Server deployed as an AlwaysOn availability group. With the latest update rollup, DPM 2012 R2 also supports SQL Server 2014 along with all other major versions of SQL Server, such as SQL Server 2012 and SQL Server 2008, on all major Windows Server versions. In earlier versions of SQL Server, such as SQL Server 2008 where SQL AlwaysOn technology is not available, DPM enables protection of SQL Server in a database mirroring configuration. In a mirroring configuration, protection of the principal database is available, although not on the mirrored database. DPM doesn’t support SQL Server backup when it has database files stored on a remote SMB file share or Windows scale-out file server. DPM also doesn’t support databases whose data is stored on Windows Azure blob storage.

SQL Server can be deployed in a physical machine or inside a VM. The DPM agent must be installed on the SQL Server machine. If SQL Server is configured in a clustered mode or as a SQL AlwaysOn cluster, the DPM agent needs to be installed on all the nodes that are failover targets for the SQL Server instance. If cluster members are added, DPM needs to be installed on the newly added cluster nodes as well.

In SQL Server AlwaysOn deployments, DPM honors Preferred Replica, Replica Only, Any Replica, and Primary preferences set by the SQL Server administrator (see Figure 3-3). However, for Preferred Replica, DPM always backs up only from the replica node. When Availability Group is selected for protection, all databases that are added to the availability group are automatically backed up.

In a SQL Server deployment, a user can enable auto protection of all databases within the SQL Server instance. This enables managing backup in a dynamic environment where databases are added or deleted from a SQL Server instance without requiring backup administrator intervention. In an auto protection mode, there is no mechanism to turn off backup of subset of databases, for example, a master or model database. Also, with auto protection, it is important to manage the size of the replica storage pool so that it doesn’t run out of space.

During the restore process, an administrator can take one of the following actions:
 Recover the entire database to the original SQL Server instance
 Recover the database and rename it
 Recover to an alternate database instance
 Copy the database backup files to a restore folder in a network share

Source of Information : Microsoft System Center