Microsoft SQL Server database backup and restore requirements: IBM Spectrum Protect Plus V10.1.6

Preventive Service Planning

Abstract

This document details the Microsoft® SQL Server database backup and restore requirements for IBM Spectrum Protect Plus Version 10.1.6.

Content

This document is divided into linked sections for ease of navigation. Use the following links to jump to the section of the document that you require:

General

Beginning with IBM Spectrum Protect Plus V10.1.1, support was added for backing up and restoring Microsoft SQL Server data.

Before you register a Microsoft SQL Server with IBM Spectrum Protect Plus, ensure that the system environment meets the following requirements.

Configuration

Application versions

**Table 1.** Coverage matrix for application levels supported by IBM Spectrum Protect Plus
IBM Spectrum Protect Plus	Microsoft SQL Server 2008 R2 SP3* Standard and Enterprise editions	Microsoft SQL Server 2012* Standard and Enterprise editions	Microsoft SQL Server 2014* Standard and Enterprise editions	Microsoft SQL Server 2016* Standard and Enterprise editions	Microsoft SQL Server 2017* Standard and Enterprise editions	Microsoft SQL Server 2019* Standard and Enterprise editions
V10.1.1					Beginning with V10.1.1 patch 1	--
V10.1.2						--
V10.1.3						--
V10.1.4						--
V10.1.5						Beginning with V10.1.5 patch 1
V10.1.6

* The base release and later cumulative updates and maintenance levels are supported.

Operating systems

**Table 2.** Coverage matrix for supported operating systems on Windows x64
IBM Spectrum Protect Plus	Microsoft Windows Server 2012 R2* Standard and Datacenter editions	Microsoft Windows Server 2016* Standard and Datacenter editions	Microsoft Windows Server 2019* Standard and Datacenter editions
V10.1.1			--
V10.1.2			--
V10.1.3
V10.1.4
V10.1.5
V10.1.6

* The base release and later maintenance levels are supported.

Restrictions

The following restrictions apply:

IBM Spectrum Protect Plus does not support log backup of simple recovery models.
Failover of an SQL cluster instance during backup operations is not supported.
The Volume Shadow Copy Service (VSS) restore file path is limited to 256 or fewer characters. If the original path exceeds this length, consider using a customized restore file path for production restore jobs to reduce the length.

Due to limitations of the VSS framework, leading spaces, trailing spaces, and unprintable characters should not be used in database names. For more information, see Backing up a SQL Server database using a VSS backup application may fail for some databases
You cannot restore data to a New Technology File System (NTFS) or file allocation table (FAT) compressed volume because of SQL Server database restrictions. For more information, see Description of support for SQL Server databases on compressed volumes
Microsoft SQL Server must be configured to use Windows Authentication, sometimes called trusted connections, for it’s Authentication Mode. For more information about SQL Server Authentication Modes and steps to change SQL Server Authentication Mode, see Choose an Authentication Mode
Only one application server or file server per host can assigned.
For example, if a host as a Microsoft Windows file system is already registered, you cannot register the same host as a Microsoft SQL Server or a Microsoft Exchange Server.

Software

Install the most recent Microsoft SQL Server patches and updates in your environment.
Install a supported version of a Windows 64-bit operating system in your environment. Ensure that the most recent patches and updates are installed.

Authentication and privileges

Authentication

Register each Microsoft SQL Server with IBM Spectrum Protect Plus by name or IP address. When you register an SQL Server cluster node, register each node by name or IP address.
Restriction: The IP address must be reachable from the IBM Spectrum Protect Plus server and from the vSnap server. Both servers must have a Windows Remote Management (WinRM) service that is listening on port 5985. The fully qualified domain name must be resolvable and can be routed from the IBM Spectrum Protect Plus server and from the vSnap server.

The user identity must have sufficient rights to install and start the IBM Spectrum Protect Plus Tools Service on the node. These rights include Log on as a service rights. For more information, see the Microsoft article: Add the Log on as a service Right to an Account

If the SQL Server is attached to a domain, the user identity follows the default domain\Name format. If the user is a local administrator, the user identity matches the name of the local administrator.

Kerberos authentication

Kerberos-based authentication can be enabled by specifying a configuration file on the IBM Spectrum Protect Plus virtual appliance. The settings override the default Windows NT LAN Manager (NTLM) protocol.

For Kerberos-based authentication only, the user identity must be specified in the username@FQDN format. The user must be able to authenticate by using the registered password to obtain a ticket-granting ticket (TGT) from the key distribution center (KDC) on the domain that is specified by the fully qualified domain name.

Privileges

To use a Microsoft SQL Server, an IBM Spectrum Protect Plus agent user must have the following permissions:

Microsoft SQL Server public and sysadmin permissions
Windows local administration permissions, which are required by the VSS framework, and volume and disk access
Permissions to access cluster resources in an SQL Server Always On and SQL Server failover clustering instance (FCI) environment

Every Microsoft SQL Server host can use a specific user account to access the resources of that SQL Server instance.

The SQL Server Virtual Device Interface (VDI)-based framework is used to interact with SQL Server databases and to log backup and restore operations. A VDI connection requires Microsoft SQL Server sysadmin permissions. The owner of a restored database is not changed to the original owner. A manual step is required to modify the owner of a restored database. For more information about the VDI framework, see the Microsoft article: SQL Server VDI backup and restore operations require Sysadmin privileges

The target Microsoft SQL Server service account must have permissions to access Microsoft SQL Server restore files. See the Administrative Considerations section in the Microsoft article: Securing Data and Log Files

The Windows Task Scheduler is used to schedule log backups. Depending on the environment, users might receive the following error:
A specified logon session does not exist. It might already have been terminated.
This behavior occurs when a network access group policy setting is enabled. For instructions about disabling the setting, see the Microsoft Support article: Task Scheduler Error “A specified logon session does not exist”

Group Policy Object

For the Network security: LAN Manager authentication level policy setting at Computer Configuration > Windows Settings > Security Settings > Local Policies > Security Options, specifies one of the following options:

Not Defined.
Send NTLMv2 response only.
Send NTLMv2 response only. Refuse LM.
Send NTLMv2 response only. Refuse LM & NTLM.

The Send NTLM response only option is not compatible with the vSnap Common Internet File System (CIFS) and SMB version and can cause CIFS authentication problems.

You can specify the Group Policy Object (GPO) setting by navigating to:

Computer Configuration > Policies > Windows Settings > Security Settings > Local Policies > Security Options > Network security: Restrict NTLM: Incoming NTLM traffic

Computer Configuration > Policies > Windows Settings > Security Settings > Local Policies > Security Options > Network security: Restrict NTLM: Outgoing NTLM traffic

and choose one of the following options:

Allow all
Allow all accounts

Prerequisites and operations

Prerequisites

Ensure that theSoftware, Connectivity, and Authentication and privileges requirements are met.

The following prerequisites must be met before you start protecting your resources:

An Internet Small Computer Interface (iSCSI) route must be enabled between the Microsoft SQL Server system and vSnap server. For more information, see the iSCSI Initiator Step-by-Step Guide
The Windows PowerShell binary path must be set in the %PATH% environment variable.
If you plan to back up databases that were restored in test mode, use the global preference to limit the size of backup target volumes to less than 64 TB. You must set this global preference before you run the first backup for the service level agreement (SLA) that protects the databases. If the size of the backup target volumes is 64 TB or more, the backup job fails.

Operations

Before you start a backup or restore operation:

Register the SQL Servers that you want to back up. When an SQL Server application server is added, an inventory of the instances and databases that are associated with the application server is captured and added to IBM Spectrum Protect Plus. The inventory is required for backup and restore jobs and run reports. For instructions, see Adding an SQL Server application server
Configure service level agreement (SLA) policies. For instructions, see Create backup policies
Before an IBM Spectrum Protect Plus user can implement backup and restore operations, roles and resource groups must be assigned to the user. Grant users access to resources and backup and restore operations by using the Accounts pane. For instructions, see Managing user access
Before you set up and run SQL backup jobs, configure the shadow copy storage settings for the volumes where your SQL databases are located. This setting is configured once for each volume. If new databases are added to the job, the setting must be configured for any new volumes that contain SQL databases. In Windows Explorer, right-click the source volume and click the Shadow Copies tab. Set the Maximum size value to No limit or a reasonable size based on the source volume size and input/output (I/O) activities, and then click OK. The shadow copy storage area must be on the same volume or another available volume during a backup job.
If you plan to back up a large number of databases, you might have to increase the number of maximum worker threads on each associated SQL Server instance to ensure that backup jobs are completed successfully. The default value for maximum worker threads is 0. The server automatically determines the maximum number of worker threads value based on the number of processors available to the server. SQL Server uses the threads from this pool for network connections, database checkpoints, and queries. Additionally, a backup of each database requires one additional thread from this pool. If you have a large number of databases in a backup job, the default value for maximum worker threads might not be sufficient to back up all of the databases and the job fails. For instructions about increasing the maximum worker threads option, see Configure the max worker threads Server Configuration Option
If you are planning to restore data to an alternative location, the SQL Server destination must be running the same version of SQL Server or a later version. For more information, see Compatibility Support

Review the following information about creating backup and restore jobs:

Use a backup job to back up SQL Server environments with snapshots. For instructions, see Backing up SQL Server data
IBM Spectrum Protect Plus supports database backups and transaction log backups. The product name is populated in the msdb.dbo.backupset for records created by backups initiated from IBM Spectrum Protect Plus.
Use a restore job to restore a Microsoft SQL Server environment from snapshots. After you run IBM Spectrum Protect Plus Instant Disk Restore jobs, your SQL Server clones can be used immediately. IBM Spectrum Protect Plus catalogs and tracks all cloned instances, as described in Restoring SQL Server data
If you are planning to run a point-in-time recovery, ensure that both the restore target SQL instance service and the IBM Spectrum Protect Plus SQL Server service use the same user account.
If you are planning to run a production restore operation to an SQL Server failover cluster, the root volume of the alternative file path must be eligible to host database and log files. The volume should belong to the destination SQL Server cluster server resource group, and be a dependency of the SQL Server cluster server.
When you restore data from an IBM Spectrum Protect archive, files are initially migrated from tape storage to a staging storage pool. Depending on the size of the restore, this process might take several hours.
When you are restoring data to a primary instance in an SQL Always On Availability Group environment, the database is added to the target Always On database group. After the primary restore operation, the secondary database is seeded by the SQL Server in environments where automatic seeding is supported (Microsoft SQL Server 2016 and later). The database is then enabled on the destination availability group. The synchronization time depends on the amount of data that is being transferred and the connection between the primary and secondary replicas.
If automatic seeding is not supported or is not enabled, you must initiate a secondary restore job from the restore point with the shortest Log Sequence Number (LSN) gap of the primary instance. Log backups with the latest point-in-time restore point that is created by IBM Spectrum Protect Plus must be restored if the log backup was enabled on the primary instance. The secondary database restore operation is completed in the RESTORING state and you must issue the T-SQL command to add the database to the target group. For more information, see Transact-SQL Reference

In-Memory online transaction processing

In-Memory online transaction processing (OLTP) is a memory-optimized database engine that is used to improve database application performance. This engine is supported in Microsoft SQL Server 2014 and later. Note the following requirements and limitations, which apply to In-Memory OLTP usage:

The restore file path is limited to 256 or fewer characters. If the original path exceeds this length, consider using a customized restore file path to reduce the length.
The metadata that can be restored is subject to VSS and Microsoft SQL Server restore capabilities.

Configuring Always On availability groups

Configure the preferred instance for backup operations by using Microsoft SQL Server Management Studio. Complete the following steps:

Select the Availability Group node.
Select the availability group that you want to configure. Then, select Properties
In the Availability Group Properties dialog box, select Backup Preferences.
In the Where should backups occur pane, select any option.

When a secondary replica is preferred, and more than one secondary replica is available, the IBM Spectrum Protect Plus job executor selects the first secondary replica in the preferred list reported by the IBM Spectrum Protect Plus SQL Server agent.
The Microsoft SQL Server agent sets the VSS backup type to COPY_ONLY.
The No Recovery option does not support production mode restore operations for SQL Always On groups.

Incremental backups

IBM Spectrum Protect Plus uses update sequence number (USN) change journal technology for incremental backups in a Microsoft SQL Server environment. The USN change journal provides write range tracking for a volume when the file size meets the minimum file size threshold requirement. The changed bytes offset and length extent information can be queried against a specific file.

To enable write range tracking, the system environment must meet the following requirements:

Windows Server 2012 R2 or later
New Technology File System (NTFS) Version 3.0 or later

The following technologies are not supported for changed bytes tracking:

Resilient File System (ReFS)
Server Message Block (SMB) 3.0 protocol
SMB Transparent Failover (TFO)
SMB 3.0 with Scale-Out file shares

By default, 512 MB of space is allocated for USN change journaling. In addition, when journal overflow is detected, the allocated space doubles in size to a maximum of 2 GB.

The minimum space required for shadow copy storage is 100 MB, although more space might be required on systems with increased activity. If the free space on the source volume is less than 100 MB, the Microsoft SQL Server agent checks the source volume space and causes a backup operation to fail. A warning message is displayed in the job log when free space is less than 10%, and then the backup proceeds.

A base backup of a file is forced when the following conditions are detected:

Journal discontinuity is reported. This can occur when the log reaches its maximum size, when journaling is disabled, or when the cataloged USN ID is changed.
The file size is less than or equal to the tracking threshold size, which by default is 1 MB.
A file is added after a previous backup.

Log backups

To ensure that SQL log backup works properly, you might have to update the Windows Group Policy Object settings. For more information, see Group Policy Object.

Connectivity

Ensure that the following connectivity requirements are met:

The network adapter used for the connection must be configured as a client for Microsoft Networks.
The Microsoft Windows Remote Management (WinRM) service must be running.
Firewalls must be configured to enable IBM Spectrum Protect Plus to connect to the server by using WinRM.
The IP address of the machine that you register must be reachable from the IBM Spectrum Protect Plus server and from the vSnap server. The SQL server must have a WinRM service that is listening on port 5985.
All servers, proxies, applications, and hypervisors that are added to the IBM Spectrum Protect Plus environment must be registered by using a Domain Name System (DNS) name or Internet Protocol (IP) address.
If DNS names are used, they must be resolvable over the network by the IBM Spectrum Protect Plus virtual appliance server and from the vSnap server. All IBM Spectrum Protect Plus components must also be resolvable by their DNS names.

Ports

The following ports are used by IBM Spectrum Protect Plus agents users.

**Table 3.** Communication ports when the target is an IBM Spectrum Protect Plus agent
Port	Protocol	Initiator	Target	Description
5985	Transmission Control Protocol (TCP)	IBM Spectrum Protect Plus virtual appliance¹	Microsoft SQL Server	Provides access to the Microsoft WinRM service for Windows-based servers
5986	TCP	IBM Spectrum Protect Plus virtual appliance¹	Microsoft SQL Server	Provides access to the Microsoft WinRM service for Windows-based servers

¹ The IBM Spectrum Protect Plus virtual appliance contains the following base components: the IBM Spectrum Protect Plus server, the vSnap server, and a VADP proxy, as described in Product components

**Table 4.** Communication ports when the initiator is an IBM Spectrum Protect Plus agent user
Port	Protocol	Initiator	Target	Description
3260 iSCSI initiator is required on this node.	TCP	Microsoft SQL Server	vSnap server	The Microsoft iSCSI Initiator service vSnap target port that is used for mounting LUNS for backup and recovery operations
443	TCP	Microsoft SQL Server agent	IBM Spectrum Protect Plus virtual appliance¹	Port that allows the agent to communicate with IBM Spectrum Protect Plus for sending alerts in case of log backup failures
445	TCP	Microsoft SQL Server agent	vSnap server	Provides vSnap server SMB or CIFS target port that is used for mounting file system shares for transaction log backup and recovery operations