Microsoft SQL Server database backup and restore requirements: IBM Storage Protect Plus 10.1.15

Preventive Service Planning

Abstract

This document details the Microsoft® SQL Server database backup and restore requirements for IBM Storage Protect Plus 10.1.15.

Content

Note: The product now known as IBM Storage Protect Plus was named IBM Spectrum Protect Plus in levels earlier than 10.1.15. To learn more about the brand change, see IBM Spectrum brand change to IBM Storage.

This document is divided into linked sections. Use the following links to go to the section of the document that you require.

General

Beginning with IBM Spectrum Protect Plus 10.1.1, support was added for backing up and restoring Microsoft SQL Server data.
To read about new and changed features in different versions of IBM Storage Protect Plus 10.1, see What's new for IBM Storage Protect Plus.

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

IBM Storage Protect Plus support for third-party operating systems, applications, services, and hardware depend on the respective vendor. If a third-party product or version moves into extended support, self-service support, or end-of-life, IBM Storage Protect Plus supports the product or version at the same level as the vendor. See also IBM Support General Guidelines and Limitations - IBM support for software on unsupported operating systems

Configuration

Application versions

**Table 1.** Coverage matrix for application levels supported by IBM Storage Protect Plus
IBM Storage Protect Plus	Microsoft SQL Server 2008 R2 SP3^*(1)	Microsoft SQL Server 2012^*(1)	Microsoft SQL Server 2014^*(1)	Microsoft SQL Server 2016^*(1)	Microsoft SQL Server 2017^*(1)	Microsoft SQL Server 2019^*(1)	Microsoft SQL Server 2022^*(1)
10.1.1	✔	✔	✔	✔	✔⁽²⁾	--	--
10.1.2	✔	✔	✔	✔	✔	--	--
10.1.3	✔	✔	✔	✔	✔	--	--
10.1.4	✔	✔	✔	✔	✔	--	--
10.1.5	✔	✔	✔	✔	✔	✔⁽³⁾	--
10.1.6	✔	✔	✔	✔	✔	✔	--
10.1.7	✔	✔	✔	✔	✔	✔	--
10.1.8	✔	✔	✔	✔	✔	✔	--
10.1.9	✔	✔	✔	✔	✔	✔	--
10.1.10	✔	✔	✔	✔	✔	✔	--
10.1.11	✔	✔	✔	✔	✔	✔	--
10.1.12	✔	✔	✔	✔	✔	✔	--
10.1.13⁽⁴⁾	✔	✔	✔	✔	✔	✔	--
10.1.14	✔	✔	✔	✔	✔	✔	✔⁽⁵⁾
10.1.15	✔	✔	✔	✔	✔	✔	✔

* The base release and later cumulative updates and maintenance levels are supported.
⁽¹⁾ Microsoft SQL Server supported editions: Standard and Enterprise editions
⁽²⁾Beginning with IBM Spectrum Protect Plus 10.1.1 patch 1, Microsoft SQL Server 2017 is supported.
⁽³⁾Beginning with IBM Spectrum Protect Plus 10.1.5 patch 1, Microsoft SQL Server 2019 is supported.
⁽⁴⁾Beginning with IBM Spectrum Protect Plus 10.1.13, transport encryption feature is supported.
⁽⁵⁾Beginning with IBM Spectrum Protect Plus 10.1.14, Microsoft SQL Server 2022 is supported. See in restriction section about Microsoft SQL Server 2022 Always On availability group contained system databases.

Operating systems

**Table 2.** Coverage matrix for supported Microsoft Windows 64-bit operating systems
IBM Storage Protect Plus	Microsoft Windows Server 2012 R2^*(1)	Microsoft Windows Server 2016^*(1)	Microsoft Windows Server 2019^*(1)	Microsoft Windows Server 2022^*(1)
10.1.1	✔	✔	--	--
10.1.2	✔	✔	--	--
10.1.3	✔	✔	✔	--
10.1.4	✔	✔	✔	--
10.1.5	✔	✔	✔	--
10.1.6	✔	✔	✔	--
10.1.7	✔	✔	✔	--
10.1.8	✔	✔	✔	--
10.1.9	✔	✔	✔	✔
10.1.10	✔	✔	✔	✔
10.1.11	✔	✔	✔	✔
10.1.12	✔	✔	✔	✔
10.1.13	✔	✔	✔	✔
10.1.14	✔	✔	✔	✔
10.1.15	✔	✔	✔	✔

* The base release and later maintenance levels are supported.
⁽¹⁾ Microsoft Windows Server supported editions: Standard and Datacenter editions

Restrictions

IBM Storage Protect Plus does not support log backup operations for simple recovery models.
SQL Server does not support log backup operations for system databases and databases with the simple recovery model. You cannot use IBM Storage Protect Plus to back up the logs of the specified SQL Server databases.
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 256 characters, consider by using a customized file path for production restore jobs to reduce the length.

Due to limitations of the VSS framework, do not use leading spaces, trailing spaces, and unprintable characters in database names. For more information, see Backing up an SQL Server database by using a VSS backup application might 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 more information about SQL Server authentication modes and instructions to change the SQL Server authentication mode, see Choose an Authentication Mode
Only one application server or file server can be assigned per host.
For example, if a host is registered as a Microsoft Windows file system, you cannot register the same host as a Microsoft SQL Server or a Microsoft Exchange Server.
If a nondefault local administrator ID is entered as the Guest OS username when you define a backup job, the file cataloging, backup, application point-in-time restores, and other operations that start the Windows agent fail. A nondefault local administrator is any user ID created in the guest operating system and assigned the administrator role.
This failure occurs if the registry key LocalAccountTokenFilterPolicy in HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System is set to 0 or not set. If the parameter is set to 0 or not set, a local nondefault administrator cannot interact with WinRM. WinRM is the protocol that IBM Storage Protect Plus uses to install the Windows agent for file cataloging, send commands to this agent, and get results from it.
Set the LocalAccountTokenFilterPolicy registry key to 1 on the Windows guest that is being backed up with catalog file metadata enabled. If the key does not exist, go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System and add a DWord Registry key named LocalAccountTokenFilterPolicy with a value of 1.
IBM Storage Protect Plus does not support backup and restore of Microsoft SQL Server 2022 Always On availability group contained system databases.

Software

Install the most recent Microsoft SQL Server fixes and updates in your environment.
A supported version of a Microsoft Windows 64-bit operating system, including the most recent fixes and updates, must be installed in your system environment.
Ensure that the Microsoft Storage Spaces SMP service is enabled and running.
Anti-virus software can interfere with the operations of IBM Storage Protect Plus. Exclude the IBM Storage Protect Plus agent folders C:\ProgramData\SPP and C:\Program Files\IBM from anti-virus scans.
If the SQL Server is configured with Transport Layer Security (TLS) 1.2, a compatible version of the Open Database Connectivity (ODBC) driver must be installed on the SQL Server. For more information about compatibility, see System Requirements, Installation, and Driver Files - SQL Server
If Force Encryption option is enabled against SQL Server Database Engine, a valid certificate must be provisioned, and trusted, see Enable encrypted connections to the Database Engine

Connectivity

Ensure that your system environment meets the following connectivity requirements:

The network adapter that is 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 Storage Protect Plus to connect to the server by using WinRM.
Firewalls must be configured to enable the Microsoft SQL Server to communicate with the IBM Storage Protect Plus server by using the Hypertext Transfer Protocol Secure (HTTPS) protocol through port 443.
The IP address of any client host that you register must be reachable from the IBM Storage Protect Plus server and from the vSnap server.
The SQL Server must have a WinRM service that is listening on the same port as the global preference in IBM Storage Protect Plus for WinRM port. This port must be the port 5985 for WinRM by using the HTTP protocol or the port 5986 for WinRM by using the HTTPS protocol.
All servers, proxies, applications, and hypervisors that are added to the IBM Storage 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 Storage Protect Plus server and from the vSnap server. All IBM Storage Protect Plus components must also be resolvable by their DNS names.
If log backup is enabled, ensure SMBv2 is enabled on the SQL Server.

Authentication and privileges

Authentication

Register each Microsoft SQL Server with IBM Storage Protect Plus by specifying a fully qualified name or IP address. When you register an SQL Server cluster node, register each node by name or IP address. Ensure that the Connectivity requirements are met.

The user identity must have sufficient rights to install and start the IBM Storage 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.

Privileges

To use a Microsoft SQL Server, an IBM Storage 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 back up and restore log files. 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. For more information about requirements and restrictions, see the Administrative Considerations section in the Microsoft article: Securing data and log files

The Windows Task Scheduler is used to schedule log backup operations if log backups are done not by using the LogBackup subpolicy in the SLA. Depending on the environment, users might receive the following error: A specified logon session does not exist. It might already have been terminated.
This message is issued 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”.
Note: For SQL Advanced log backup operation, Windows Task Scheduler related requirements are not applicable.

Microsoft Windows Remote Management (WinRM) service user needs to have PowerShell permission. To check the permission, the following PowerShell command can be issued:

Get-PSSessionConfiguration

Group Policy Object

For SQL log backup and restore operations, the vSnap server must be configured to use the NT LAN Manager V2 (NTLM V2) authentication protocol on the Windows server.
For the Network security: LAN Manager authentication level policy setting, click Computer Configuration > Windows Settings > Security Settings > Local Policies > Security Options and specify one of the following options:

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

Important: For vSnap, you must use one of the previous listed security options listed. Other options are not compatible with the vSnap Common Internet file system (CIFS) and the server message block (SMB) protocol versions, and can cause CIFS authentication problems.

Specify the Group Policy Object (GPO) setting by going to: Computer Configuration > Policies > Windows Settings > Security Settings > Local Policies > Security Options > Network security: Restrict NTLM: Incoming NTLM traffic
And also to: Computer Configuration > Policies > Windows Settings > Security Settings > Local Policies > Security Options > Network security: Restrict NTLM: Outgoing NTLM traffic to remote servers.
For the NTLM traffic, specify one of the following options:

Allow all
Allow all accounts

In addition to the configuration of the SQL Server, you must also enable the NTLM V2 authentication protocol at the enterprise level.

Prerequisites and operations

Prerequisites

Ensure that the Software, Connectivity, and Authentication and privileges requirements are met.

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

An Internet Small Computer System 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.
Ensure that remote PowerShell invocation is enabled for the SQL Server by using Windows Remote Management (WinRM) from the IBM Storage Protect Plus server.
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.
With transport encryption, you can securely transfer data between application host and vSnap during backup and restore. The transport encryption option is not enabled by default. To enable the transport encryption option, see Configuring advanced storage options.

Operations

Before you start a backup or restore operation, take the following actions:

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 Storage Protect Plus. The inventory is required for backup and restore jobs and to run reports.
Configure service level agreement (SLA) policies.
Assign appropriate roles and resource groups to users who are running backup and restore operations. Grant users access to resources and roles by using the Accounts pane.

Review the following information about creating backup and restore jobs:

You can use a backup job to back up SQL Server environments with snapshots. For instructions, see Backing up SQL Server data.
You can use a restore job to restore a Microsoft SQL Server environment from snapshots. After you run IBM Storage Protect Plus Instant Disk Restore jobs, your SQL Server clones can be used immediately. IBM Storage Protect Plus catalogs and tracks all cloned instances, as described in Restoring SQL Server data.

For an overview about protecting SQL Server databases with IBM Storage Protect Plus, see Protecting Databases-SQL Server data.

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 256 characters, consider by 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 and 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 Storage Protect Plus job executor selects a secondary replica that is depending on the backup priority and exclude replica options of the availability group.
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 Storage 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) 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 the backup operation to fail. If free space is less than 10% but more than 100 MB, a warning message is displayed in the job log, and then the backup operation proceeds.

Backup jobs use additional disk space to create a shadow copy of the volume being backed up. If you do not configure the Shadow Copy Storage for a volume, Windows uses the same volume as the Shadow Copy Storage. Shadow copies are deleted when the backup job is completed. The exact amount of required storage space for the shadow copy dependent on the amount of change in the SQL database during the backup operation. For active SQL Server environments with high I/O, establish a dedicated volume for Shadow Copy Storage that has low I/O for SQL volumes and set the shadow copy creation to unlimited. Do not point more than two SQL Servers to any one dedicated volume for Shadow Copy Storage.

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

Journal discontinuity is reported. This behavior 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.

Ports

The following ports are used by IBM Storage Protect Plus agent users.

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

**Table 4.** Communication ports when the initiator is an IBM Storage Protect Plus agent user
Port	Protocol	Initiator	Target	Description
22	TCP	Microsoft SQL Server	vSnap server	Used for Microsoft Internet Small Computer System Interface (iSCSI) data transfer over Secure Shell (SSH) tunnel to and from Logical Unit Numbers (LUNs) mounted from vSnap servers during backup and restore operations when transport encryption is enabled.
3260	TCP	Microsoft SQL Server	vSnap server	Used for Microsoft Internet Small Computer System Interface (iSCSI) data transfer to and from Logical Unit Numbers (LUNs) mounted from vSnap servers during backup and restore operations when transport encryption is not enabled. To enable this function, the Microsoft iSCSI Initiator Service is required on the specified node.
443	TCP	Microsoft SQL Server agent	IBM Storage Protect Plus server	Port that allows the agent to communicate with IBM Storage Protect Plus for SQL log backup, restore, and purge operations, and send alerts if log backup fail.
445	TCP	Microsoft SQL Server agent	vSnap server	Used for SMB or CIFS data transfer to and from file systems mounted from vSnap servers during transaction log backup and restore operations.

Ports update:

For Microsoft SQL Server, port 443 is available in IBM Spectrum Protect Plus 10.1.4 and later.
In earlier versions, ports 137, 138, and 139 on the vSnap server were used by application agents that use SMBv1. Beginning with IBM Spectrum Protect Plus 10.1.6, the SMBv1 protocol is not used. All agents use SMBv2 or later, which does not require ports 137, 138, or 139.
Port 22: Beginning with IBM Spectrum Protect Plus 10.1.13 and later this port with target vSnap server is used when transport encryption is enabled.

Hardware

**Table 5.** Minimum hardware requirements
System	Disk space
Compatible hardware that is supported by the operating system and Microsoft SQL Server	A minimum of 500 MB of disk space on C drive is required for product installation and temporary files at run time.

Related Information

Requirements Documents: IBM Storage Protect Plus

Featured Documents: IBM Storage Protect Plus

[{"Line of Business":{"code":"LOB26","label":"Storage"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSJEPVG","label":"IBM Storage Protect Plus"},"ARM Category":[{"code":"a8m3p000000h9Z4AAI","label":"HW\/SW Requirements"}],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1.15","Type":"MASTER"}]

Product Synonym

IBM Spectrum Protect Plus;

Was this topic helpful?

Document Information

Modified date:
13 June 2023

UID

ibm16991807

Tips