Create a SQL Backup Job Definition

Back up SQL environments with snapshots using a SQL Backup job definition. During the initial base backup, IBM Spectrum Protect Plus creates a new vSnap volume and creates an NFS share. During incremental backups, the previously created volume is reused. The IBM Spectrum Protect Plus agent mounts the share on the SQL server where the backup is to be performed. Once complete, the IBM Spectrum Protect Plus agent unmounts the share from the SQL server and creates a vSnap snapshot of the backup volume.

The following option is available for SQL Backup jobs:

Log Backup - The log backup feature enables continuous backup of transaction logs to a specified destination. IBM Spectrum Protect Plus leverages transaction logs to enable point-in-time recoveries of databases to facilitate RPOs. To perform log backups, the SQL server agent service user must be a local Windows administrator and must have the sysadmin permission enabled to manage SQL server agent jobs. The agent will use that administrator account to enable/access log backup jobs. The IBM Spectrum Protect Plus SQL agent service user must also be the same as the SQL server service and SQL server agent service account for every SQL instance to be protected.

BEFORE YOU BEGIN:

  • Register the providers to back up. See Add a SQL Server.
  • Configure an SLA Policy. See Configure SLA Policies.
  • Before an IBM Spectrum Protect Plus user can perform backup and restore operations, roles and resource groups must be assigned to the user. Grant users access to resources and backup and restore operations through the Accounts pane. See Access.
  • Microsoft iSCSI Initiator must be enabled and running on the Windows Server. An iSCSI route must be enabled between the SQL system and vSnap server. For more information see https://technet.microsoft.com/en-us/library/ee338476(v=ws.10).aspx.
  • Before setting up and running SQL backup jobs you must configure the Shadow Copy storage settings for the volumes where your SQL databases are located. This setting is configured once per volume. If new databases are added to the job, the setting must configured for any new volumes containing SQL databases.
  • In Windows Explorer, right-click the source volume and select the Shadow Copies tab. Set the Maximum size to No limit or a reasonable size depending on the source volume size and I/O activities, then click OK.
  • The shadow copy storage area must be on the same volume or another available volume during the time of backup.

CONSIDERATIONS:

  • Avoid configuring log backup for a single SQL database through multiple Backup jobs. Logs are truncated during log backup operations. If a single SQL database is added to multiple job definitions with log backup enabled, a log backup from one job will truncate a log before it is backed up by the next job. This might cause point-in-time restore jobs to fail.
  • Note that IBM Spectrum Protect Plus does not support log backup of Simple recovery models.
  • An Always On of a replica of a SQL cluster instance is not supported. Replicas are limited standalone SQL servers and instances.
  • Failover of a SQL cluster instance during backup is not supported.
  • If backing up a large number of databases, you might need to increase the number of maximum worker threads on each associated SQL Server instance to ensure backup jobs complete successfully. The default value for max worker threads is 0 and the server automatically determines the max worker threads value based on the number of processors available to the server. SQL Server uses the threads from this pool to perform network connections, database checkpoints, and queries. Additionally, a backup of each database requires one additional thread from this pool. If you have large number of databases in a backup job, the default max worker threads might not be enough for backing up all of the databases and the job will fail. For more information about increasing the max worker threads option, see https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option?view=sql-server-2017.
  • When a log backup of a secondary SQL Always On database fails with the following error, the backup preference of the availability group must be changed to Primary: "Log backup for database 'DatabaseName' on a secondary replica failed because a synchronization point could not be established on the primary database." Changing the preference to Primary will back up the log from the primary replica. After a successful log backup of the primary replica completes, the backup preference can be changed.

To create a SQL Backup job definition:

  1. From the navigation menu, expand Manage Protection, then expand Applications. Expand SQL, then click Backup.
  2. Select a SQL instance to back up. Use the search function to search for available instances and toggle the displayed instances through the View filter. Available options include Standalone/Failover Cluster and Always On.
  3. Click Select SLA Policy to add an SLA Policy to the job definition that meets your backup data criteria.
  4. To create the job definition using default options, click Save. The job runs as defined by your SLA Policy, or can be run manually from the Job Monitor pane.
  5. To edit options before creating the job definition, click Select Options. Set the job definition options.
  6. Enable Log Backup
  7. If selected, IBM Spectrum Protect Plus backs up transaction logs then protects the underlying disks.
  8. IBM Spectrum Protect Plus automatically truncates post log backups of databases that it backs up. If database logs are not backed up with IBM Spectrum Protect Plus, logs are not truncated by IBM Spectrum Protect Plus and must be managed separately.
  9. When SQL backup job completes with log backups enabled, all transaction logs up to the point of the job completing are purged from the SQL server. Note that log purging will only occur if the SQL Backup job completes successfully. If log backups are disabled during a re-run of the job, log purging will not occur.
  10. If a source database is overwritten, all old transaction logs up to that point are placed in a “condense” directory once the restoration of the original database completes. When the next run of the SQL Backup job completes, the contents of the condense folder is removed.
  11. Note: To perform log backups, the SQL server agent service user must be a local Windows administrator and must have the sysadmin permission enabled to manage SQL server agent jobs. The agent will use that administrator account to enable/access log backup jobs. The IBM Spectrum Protect Plus SQL agent service user must also be the same as the SQL server service and SQL server agent service account for every SQL instance to be protected.
  12. To enable log backup schedule creation for multiple databases on the same SQL instance, ensure all databases are added to the same SLA Policy.
  13. Maximum Parallel Streams per Database
  14. Set the maximum data stream per database to the backup storage. This setting applies to each database in the job definition. Note that multiple databases can still be backed up in parallel if the value of the option is set to 1. Multiple parallel streams may improve backup speed, but high bandwidth consumption may affect overall system performance.
  15. When you are satisfied that the job-specific information is correct, click Save. The job runs as defined by your SLA Policy, or can be run manually from the Job Monitor pane.
  16. To configure additional options, click the Options field associated with the job in the SLA Policy Status section. If no additional options are currently configured for the job, the field will display Not Configured. Set the additional job options.
  17. Pre-scripts and Post-Scripts
  18. In the Pre-Script and/or Post-Script section, select an uploaded script. Select the Use Script Server check box to select a script server where the script will run. To select an Application Server where the script will run, clear the Use Script Server check box. Scripts and script servers are configured through the Jobs and Monitoring > Script pane. See Configure Scripts.
  19. Pre-scripts and post-scripts are scripts that can be run before or after a job runs at the job-level. Batch and PowerShell scripts are supported.
  20. Continue job/task on script error - When enabled, if a Pre-script completes with a non-zero return code, the backup or restore is attempted and the Pre-script task status returns COMPLETED. When disabled, the backup or restore is not attempted, and the Pre-script task status returns FAILED. If a Post-script completes with a non-zero return code, the Post-script task status returns COMPLETED. When disabled, the Post-script task status returns FAILED.
  21. Exclude Resources
  22. Exclude specific resources from the backup job through single or multiple exclusion patterns. Resources can be excluded through an exact match or with wildcard asterisks specified before the pattern (*test) or after the pattern (test*). Multiple asterisk wildcards are also supported in a single pattern. Patterns support standard alphanumeric characters as well as the following special characters: - _ and *. Separate multiple filters with a semicolon.
  23. Force Base Resources
  24. Force base backups for specific virtual machines or databases in the Backup job definition. All previous backup information associated with the resource will be removed. Separate multiple resources with a semicolon.
  25. Once complete, click Save.

 


IBM Spectrum Protect Plus 10.1.2

Licensed Material - Property of IBM Corp. © IBM Corporation and other(s) 2018. IBM is a registered trademark of the IBM Corporation in the United States, other countries, or both. | 8/23/2018