IBM Support

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

Preventive Service Planning


Abstract

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

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

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



 


Configuration

Database versions

  • Microsoft SQL Server 2008 R2 SP3
  • Microsoft SQL Server 2012
  • Microsoft SQL Server 2012 SP2
  • Microsoft SQL Server 2014
  • Microsoft SQL Server 2016
  • Microsoft SQL Server 2017 (beginning with V10.1.1 patch1)
  • Microsoft SQL Server 2019 (beginning with V10.1.5 patch1)

Note: Standard and Enterprise editions

Install the latest Microsoft SQL Server patches and updates in your environment.
 

Operating systems

  • Microsoft Windows Server 2012 R2
  • Microsoft Windows Server 2016
  • Microsoft Windows Server 2019 (beginning with V10.1.3)

Note: Standard and Datacenter editions

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.

An iSCSI route must be enabled between the Microsoft SQL Server system and vSnap server. For more information, see: iSCSI Initiator Step-by-Step Guide

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

IBM Spectrum Protect Plus inventory jobs discover system databases and mark the databases that are eligible for protection. Log backups are marked as ineligible for all system databases and for databases running in the simple recovery model.

Microsoft SQL Server backup and restore operations require that the Windows Powershell binary path is set in the %PATH% environment variable.

Microsoft SQL Server backup operations are limited to less than 64 TB volume size. If you plan to back up test mode restored databases, use the global preference to limit the size of backup target volumes to a size less than 64 TB. You must set this global preference before you run the first backup for the SLA that protects the databases. If the size of the backup target volumes is 64 TB or more, the backup fails.



 


In-Memory online transaction processing (OLTP)

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 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 that using a customized restore file path to reduce the length.
  • The metadata that can be restored is subject to Volume Shadow Copy Service (VSS) and Microsoft SQL Server restore capabilities.



 


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 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 when an overflow is detected, 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 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

IBM Spectrum Protect Plus support log backup:

  • With staging area:
    IBM Spectrum Protect Plus uses the backup folder that is configured for the Microsoft SQL Server instance to stage the collection of logs, before copy log files to the vSnap repository. Sufficient free space must be available to store transaction logs in a log backup. The staging area can be modified by changing the backup folder configuration by using SQL Server Management Studio (SSMS).
  • Without staging area:
    This type of log backup requires vSnap Active Directory (AD) integration. To learn how to configure vSnap, see Setting vSnap Active Directory
    The Microsoft SQL service user must be in an  Active Directory (AD) domain. The SLA policy must be configured to use a site that contains a vSnap that is integrated to the same AD domain as the SQL Server service user.
     

To ensure that SQL log backup works properly, a Windows Group Policy change might be required. The Group Policy Object (GPO) setting for the Network security: LAN Manager authentication level policy at Computer Configuration > Windows Settings > Security Settings > Local Policies > Security Options, must be set to 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 Server Message Block (SMB) version and can cause CIFS authentication problems.

The Group Policy Object (GPO) setting for the Computer Configuration > Policies > Windows Settings > Security Settings > Local Policies > Security Options > Network security: Restrict NTLM: Incoming NTLM traffic, must be set to one of the following options:

  • Allow all
  • Allow all accounts

The Group Policy Object (GPO) setting for the Computer Configuration > Policies > Windows Settings > Security Settings > Local Policies > Security Options > Network security: Restrict NTLM: Outgoing NTLM traffic, must be set to one of the following options:

  • Allow all
  • Allow all accounts



 


Configuring always on availability groups

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

  1. Select the Availability Group node.
  2. Select the availability group that you want to configure. Then, select Properties
  3. In the Availability Group Properties dialog box, select Backup Preferences.
  4. 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.



 


Registration and 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 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 virtual machine is attached to a domain, the user identity follows the default domain\Name format. If the user is a local administrator, the format <local administrator> is used.



 


Kerberos

Kerberos-based authentication can be enabled by specifying a configuration file on the IBM Spectrum Protect Plus 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 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 AlwaysOn and SQL Server FCI environment
     

Every Microsoft SQL Server host can use a specific user account to access the resources of that particular 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 Administrative Considerations 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.



 


Ports

The following ports are used by IBM Spectrum Protect Plus agents users. The ports use secure connections (HTTPS or SSL).

Table 1. 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 Windows Remote Management (WinRM) service for Windows-based servers.
5986 TCP IBM Spectrum Protect Plus virtual appliance Microsoft SQL Server Provides access to the Microsoft Windows Remote Management (WinRM) service for Windows-based servers.


 

Table 2: Communication ports when the initiator is an IBM Spectrum Protect Plus agent user.
Port Protocol Initiator Target Description
32601 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
137 User Datagram Protocol (UDP) Microsoft SQL Server vSnap server vSnap Server Message Block (SMB) or Common internet file system (CIFS) target port that is used for mounting file system shares for transaction log backup and recovery operations
138 UDP Microsoft SQL Server vSnap server vSnap SMB or CIFS target port that is used for mounting file system shares for transaction log backup and recovery operations
139 TCP Microsoft SQL Server vSnap server vSnap SMB or CIFS target port that is used for mounting file system shares for transaction log backup and recovery operations
4432 TCP Microsoft SQL Server vSnap server 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 vSnap server vSnap SMB or CIFS target port that is used for mounting file system shares for transaction log backup and recovery operations

1 iSCSI initiator is required on this node.
2For Microsoft SQL Server, this port is available in IBM Spectrum Protect Plus V10.1.4 and later.



 


Hardware

Table 3. 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 for the product to be installed



 

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSNQFQ","label":"IBM Spectrum Protect Plus"},"Component":"Not Applicable","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.1.5","Edition":"","Line of Business":{"code":"LOB26","label":"Storage"}}]

Document Information

Modified date:
02 July 2021

UID

ibm11071980

Page Feedback