Backup and DR Service lets you back up Microsoft SQL Server:

• Instances

• Primary database of an Always On availability group

• Databases in VMs

• System databases

• User databases

• Databases and support files in a consistency group

• Individual members of a consistency group

Before you begin

Before you protect Microsoft SQL Server databases:

• Add the hosts and discover their databases using the management console SQLServer wizard as detailed inAdd a SQL Server database host and discover databases

• CreateBackup plan policy templates andResource profiles thatdefine how to protect the databases.

Best practices for protecting Microsoft SQL Server databases

For Microsoft SQL Server databases that use the full recovery model, takeadvantage of the backup/recovery appliance's ability to back up both the databaseand its logs with a single policy. When both the database and its logs arebacked up, the appliance can recover the database to a point in time by rollingits logs forward using the appliance's user interface. Backing up both thedatabase and its logs is enabled using the policy template's advanced settings.

Back up databases in an instance versus a consistency group

When a database is quiesced to create a backup, a snapshot of its disks iscreated and then released. For consistency groups and database instances,members are quiesced and released together for a consistent point-in-time ofdata.

When backing up aSQL Instance, as databases are added to the instance,they are automatically included in the Backup and DR backup operation.Backing up databases in a SQL instance lends itself to environments wheredatabases are regularly added and removed. Databases mounted to a SQL instanceas virtual applications are not protected with the other members of theinstance. Virtually mounted databases must be protected separately.

Membership to aconsistency group is done manually. Backing up databasesin a consistency group lends itself to environments where databases are notoften added or removed.

Database versus VM management

Microsoft SQL Servers are protected differently whether they are protected as anapplication (database, instance, or availability group) or as part of an entireVM.

Use the following instructions to apply a backup plan to protect Microsoft SQL server database.

1. From the management console, go toApp Manager >Applications.TheApplications page opens.

2. Select the Microsoft SQL server database, instance, AG, orconsistency group that you want to back up and in the lower right cornerof the page selectManage Backup Plan.

3. From theManage Backup Plan window, choose aTemplate andProfilefrom the drop-down lists:

   • Template. An existing backup template that includes policies to definethe snapshot and replication of the application data.

   • Profile. An existing resource profile that defines the resourcesused to store the data of the application as snapshot andreplicated images.

4. From theManage Backup Plan Template window make the following changesprior to applying a backup plan:

   • Application Settings. Settings specific to Microsoft SQL such asapplication type, hostname, host IP address, path, operating system,backup/recovery appliance, and appliance IP address.

   • Policy Overrides. Override specific policy settings previouslyconfigured in the selected backup template. Policy overrides can be usefulor required in certain circumstances. You can only override policysettings if the policy's template has been configured to allowpolicy settings overrides.

5. To select databases, underDatabase Inclusion Rule, clickEdit.TheManage Membership dialog opens.

   • From theManage Membership dialog, select the databases to back upby assigning an inclusion rule (All,System Databases,User Databases), and then select whether the rule shouldInclude Selected orExclude Selected.

   • ClickSave and theManage Membership dialog closes.

6. ClickApply to apply the backup template and resource profile andthe success message box appears.

   The first time the selected database is discovered, an on-demand job runs assoon as possible to protect the data. Afterward, new data is backed up when thescheduled job runs according to the hours of operations defined in the backuptemplate. For example, if at 10:00 (UTC) you assign a template that has hours ofoperation from 02:00 to 05:00 (UTC), then the first job won't start until theappliance has an available job slot after 02:00 (UTC).

   If you back up a workload to a backup/recovery appliance that would exceed itsrecommended storage capacity or snapshot limit, you will see a notificationwith a recommendation to back up the data to a different backup/recovery appliance.

   Caution: Exceeding the capacity of a backup/recovery appliance can lead tofailed jobs and unprotected data and failed restore operations.

Database log protection in a Backup plan policy

When creating a snapshot policy for a database you can also back up its logfiles. The frequency at which database logs are backed up is defined separatelyfrom that of the database. For example, a database can be backed up every dayand its logs backed up every hour. The frequency of database log backup is setin minutes, and the frequency at which logs are backed up must not exceed thefrequency at which its associated database is backed up. For example, if adatabase is backed up every 24 hours, the log file back up frequency must beless than every 24 hours.

Frequency and retention are defined in theDetails & Settings of thedatabase snapshot policy. Log back up is done without regard to when itsassociated database is backed up.

You enable theLog Protection through theEnable Database Log Backup advanced settings in a backup plan snapshotpolicy. Frequency and retention are defined in theDetails & Settings for abackup plan policy.

The space required to accommodate a database's logs is automatically managed bymanagement console. Management console evaluates typical log sizes and theirretention period and adds space as needed. To manage the storage requirementsfor a database's logs, Snapshot policies provide the following advancedsettings:

• Log Backup Retention Period. Log retention is defined separately from the retention of the snapshot policy. Having a separate retention period lets you use logs in conjunction with copies of the database stored in the Snapshot pool and optionally in an OnVault pool. The log retention period is mandatory when log backups are enabled.

• Replicate Logs. You can replicate database logs to a remote backup/recovery appliance or to an OnVault pool, and use the remote logs for any database image within the retention range of the replicated logs. Log replication uses StreamSnap technology between the local and remote appliances, going directly from the local snapshot pool to the snapshot pool on the remote appliance. This requires a StreamSnap replication policy in the template, and at least one successful replication of the database must first be completed.

• Log Staging Disk Size Growth Size. Defines the percent at which to automatically grow the staging disk on which the logs reside. This setting is a percentage and valid values are from 5 to 100.

• Estimated Change Rate. Defines the daily change (in percent), which allows the backup/recovery appliance to better calculate the size of the staging disk needed to hold logs. This setting is a percentage and valid values are 0 to 100.

• Compress Database Log Backup. Instructs the source database to compress its logs before back up. The database server performs log compression during log backup.

Configure advanced settings for policy settings overrides

ClickPolicy Overrides in theManage Backup Plan window to show thePolicy Settings Override dialog. From here you can override specific policysettings associated with the selected backup template. After you are done,clickSave Changes.

To reset a policy override setting to its default state, click the checkbox tothe left of the selection; clickSelect options that will revert back to default to reset all policyoverride settings back to their default state.

• Do Not Unmap. Keep staging disks mapped between jobs: Select this if youwant temporary staging disks mapped to the host, and used during datamovement to remain mapped to the host. LUNs are mapped during the first joband all subsequent jobs reuse the same mapped LUN. By default, this optionis selected. Unmap staging disks after each job: This option both unmountsthe staging disk from the operating system at the conclusion of every job(removing mount points or drive letters), and also unmaps it from the hostaltogether. This option requires the host to perform a scan for SCSI LUNs atthe start of the next job, as the re-mapped staging disks must berediscovered before they can be remounted.

  Note: For applications protected using the Backup and DR agentwhere the application is on an OS running inside a VMware VM, this behavesdifferently. The staging disk is unmapped from the VM after every job, but,LUNs are not unmapped from the ESX hosts.

• Truncate Log After Backup. Specify whether to truncate the logs afterevery backup. When enabled, application-related logs are truncated until therecent or current backup. If you truncate logs, you must also back up thetransaction log to enable a roll forward recovery.

• Skip Offline Applications in the Consistency Group. (For consistencygroup management only) Specify whether to ignore unavailable databases thatare part of a consistency group. You create a consistency group to back upmultiple databases together to preserve consistency of data across thedatabases. Consistency groups are collections of databases from the sameinstance or availability group.

  Note: Backups at the instance or Availability Group also provide consistencyacross all included databases.

  The options are:

  • Fail backup when offline applications are found
  • Skip offline applications during backup

• Map staging disks to all ESX Hosts in a Cluster. (This option is notrelevant when using NFS datastores.) Map staging disk to ESX host for VMonly. Map staging disk to all ESX hosts in the cluster. Map staging disk totwo ESX hosts in the cluster.

  Note: VMs can only be moved to a different ESX host during a backup if thestaging disk is mapped to both the source and destination ESX host. Choosingtwo hosts reduces job durations and minimizes iSCSI HBA scanning on the ESXhosts.

• Backup SQL Server User Logins. Backs up the SQL Server instance loginrecords for accounts granted access to databases being backed up. When thedatabase is mounted as a virtual application (application aware mount) thebacked up user logins can be optionally restored into the target SQL Serverinstance, ensuring the virtual database will be accessible by the same userswith access to the original source database. The options areYes orNo.

• Enable Database Log Backup. TheEnable Database Log Backup optionallows the backup plan policy to backup an Oracle or Microsoft SQL Serverdatabase and all associated transaction log files. The logs are backed upwhen the log snapshot job runs. The options areYes orNo. When settoYes, the related options are enabled.

  Note: For details onLog Protection, seeDatabase Log Protection ina backup plan policy.

• RPO. WhenEnable Database Log Backup is set toYes, RPO definesthe frequency for database log backup. Frequency is set in minutes and mustnot exceed the database backup interval. The smallest value that can be set(in minutes) is 15.

• Log Backup Retention Period. WhenEnable Database Log Backup is settoYes, log retention is defined separately from the retention of thesnapshot policy. Having a separate retention period lets you to use logs inconjunction with copies of the database stored in the snapshot pool.The log retention period is a mandatory setting.

• Replicate Logs. (Uses StreamSnap Technology) WhenEnable Database Log Backup is set toEnable, theReplicate Logsadvanced setting allows Microsoft SQL Server database transaction logs to bereplicated to a remote backup/recovery appliance. For a log replication job torun, there must be a StreamSnap replication policy in the template alongwith a resource profile that specifies a remote backup/recovery appliance, andat least one successful replication of the database must first be completed.You can then use the logs at the remote site for any database image withinthe retention range of the replicated logs. This function is enabled bydefault.

  Log replication uses StreamSnap technology to perform the replicationbetween the local and remote backup/recovery appliances; log replication goesdirectly from the local snapshot pool to the snapshot pool on the remoteappliance.

  Note: Log replication does not occur until a SQL Server database has beenprotected and the database has been replicated to the remote appliance.

• Send Logs to OnVault Pool. WhenEnable Database Log Backup is set toEnable, this setting allows Microsoft SQL Server database transactionlogs to be replicated to an OnVault pool. For a log replication job to run,there must be an OnVault policy included in the template along with aresource profile that specifies an OnVault pool, and at least one databasemust first be sent to the pool. You can then use the logs at the remote sitefor any database image within the retention range. This function is enabledby default.

• Log Staging Disk Growth Size. WhenEnable Database Log Backup is settoYes,Log Staging Disk Growth Size defines the growth to use whenautomatically growing the staging disk on which the logs reside. Thissetting is from 5 to 100 percent.

• Estimated Change Rate. WhenEnable Database Log Backup is set toYes, this setting defines the daily change (in percent), which allowsthe backup/recovery appliance to better calculate the size of the staging diskneeded to hold logs. This setting is from 0 to 100.

• Compress Database Log Backup. WhenEnable Database Log Backup is settoYes, this setting instructs the source database to compress its logsbefore they are backed up by management console. The database serverperforms log compression during log backup. The options areYes orNo. When set toYes, theCompress Database Log Backup option isenabled.

• Script Timeout. The Backup and DR agent lets you create host-sidescripts that run on an application's host before or after a policy is run.The four timeouts provided in a policy template map directly into the fourstages of a host-side script.

  • Script Init Timeout. Defines how long a policy should wait beforeassuming host-side scripts on a managed host have been initialized.120 seconds is the default value, allowed range is from 1 to 86400 seconds(24 hours).
  • Script Freeze Timeout. Defines how long a policy should wait beforeassuming the application is frozen and ready for data back up. 60 seconds isthe default value, allowed range is from 1 to 86400 seconds.
  • Script Unfreeze Timeout. Defines how long a policy should wait beforeassuming the application is unfrozen. 60 seconds is the default value,allowed range is from 1 to 86400 seconds.
  • Script Finish Timeout. Defines how long a policy should wait beforedata backup is complete. 60 seconds is the default value, allowed range isfrom 1 to 86400 seconds.
  • Script Post Replication Timeout. Defines how long a policy should waitbefore replication is complete. 60 seconds is the default value, allowedrange is from 1 to 86400 seconds.

The Backup and DR Microsoft SQL Server DBA guide

This page is one in a series of pages specific to protecting and recoveringMicrosoft SQL Server databases with Backup and DR.You can find additional information at:

• Backup and DR for Microsoft SQL Server Databases
• Prepare SQL Server databases for Backup and DR Service
• Add a SQL Server database host and discover databases
• Configure backup plans for Microsoft SQL Server instances and databases
• Application details and settings for Microsoft SQL Server instances and databases
• Mount a SQL Server database
• Mount databases into SQL Always On Availability Groups
• Manage an active mount
• Migrate a SQL Server database
• Clone SQL Server databases
• Recover SQL Server backups