IBM Support

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

Preventive Service Planning


Abstract

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

Content

This document is divided into linked sections for ease of navigation. You may use the links below to jump to the section of the document that you require.


General

Before registering each Microsoft SQL Server in IBM Spectrum Protect Plus, ensure it meets the following requirements.
 


Configuration

Database Versions:

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

Note: Standard and Enterprise editions

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

Operating systems:

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

Note: Standard and Datacenter editions

Windows Remote Shell (WinRM) must be enabled.

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

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 databases running in simple recovery model.


In-Memory OLTP

In-Memory OLTP is a memory-optimized database engine that is used to improve database application performance. This engine is supported in SQL Server 2014 and later. Note the following IBM Spectrum Protect Plus requirements and limitations for In-Memory OLTP usage:

  • The maximum restore file path must have fewer than 256 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 Volume Shadow Copy Service (VSS) and SQL Server restore capabilities.


Incremental Backup

IBM Spectrum Protect Plus uses update sequence number (USN) change journal technology to perform incremental backups in an 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.

Requirements to enable write range tracking:

  • Windows Server 2012 R2 or later
  • 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 TFO (Transparent Failover)
  • SMB 3.0 with Scale-Out file shares (SO)
     

By default, 512 MB of space is allocated for USN change journaling. In addition, when journal overflow is detected, 2GB journal size is allocated to deal with the busy file system. 

The minimum space required for shadow copy storage is 100 MB, although more space might be required on systems with increased activity. The SQL Server agent checks the source volume space and will cause a backup to fail if the free space on the source volume is less than 100 MB. 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, due to the log reaching the maximum size, disabling of the journaling, or changing of the catalogued USN ID.
  • 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 Backup

Prior to copying log files to the vSnap repository, IBM Spectrum Protect Plus uses the backup folder that is configured for the SQL server instance to stage the collection of logs. There must be sufficient free space available to store transaction logs between backup jobs. The staging area can be modified by changing the backup folder configuration using SQL Server Management Studio (SSMS).

To ensure that SQL Log Backup works properly, a Windows Group Policy change may be required. 
The Group Policy Object (GPO) setting for Network security: LAN Manager authentication level located at Computer Configuration \ Windows Settings \ Security Settings \ Local Policies \ Security Options, needs to 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 CIFS/SMB version and can cause a 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 through SQL Server Management Studio. Complete the following steps:

  1. Select the Availability Group node
  2. Select the availability group you that you want to configure, and then, select Properties
  3. In the Availability Group Properties dialog box, select Backup Preferences
     


Select any option in the Where should backups occur pane. When secondary replica is preferred, and more than one secondary replica is available, the IBM Spectrum Protect Plus job executor will select the first secondary replica in the preferred list reported by the IBM Spectrum Protect Plus SQL Server agent.

The SQL Server agent sets the VSS backup type to COPY_ONLY.


Registration and Authentication

Register each SQL server with IBM Spectrum Protect Plus by name or IP address. When registering a SQL Server Cluster node, register each node by name or IP address.
Note: The IP address must be reachable from the IBM Spectrum Protect Plus server and from the vSnap server. Both 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. This includes "Log on as a service" rights. For more information see the article, Add the Log on as a service Right to an Account, at the Microsoft website

The user identity follows the default domain\Name format if the virtual machine is attached to a domain. The format <local administrator> is used if the user is a local administrator.


Kerberos

Kerberos-based authentication can be enabled by specifying a configuration file on the IBM Spectrum Protect Plus appliance. The settings will override the default Windows 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

The IBM Spectrum Protect Plus agent user of an SQL Server must have the following permissions:

  • SQL Server public and sysadmin permissions
  • Windows local administration permissions, which are required by the VSS framework, and volume/disk access
  • Permissions to access cluster resources in a 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 VDI-based framework is used to interact with SQL Server databases and to log backup and restore operations. A VDI connection requires 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 following Microsoft article: SQL Server VDI backup and restore operations require Sysadmin privileges

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

The Windows Task Scheduler is used to schedule log backups. Depending on a the environment, users may receive the following error:
A specified logon session does not exist. It may already have been terminated.
This is because of a Network access Group Policy setting that needs to be disabled. 
 


Ports

The following ports are used by IBM Spectrum Protect Plus agent users. Ports that are indicated with "Accept" in the Firewall Rule column use secure connections (HTTPS or SSL).

Incoming IBM Spectrum Protect Plus agent firewall connections
Port Protocol Firewall Rule Service Description
5985 TCP Accept WinRM Windows Remote Management Service
5986 TCP Accept WinRM Secure Windows Remote Management Service


 

Outgoing IBM Spectrum Protect Plus agent firewall connections
Port Protocol Service Description
3260* TCP vSnap iSCSI iSCSI vSnap target port used for mounting LUNS for backup and recovery operations
137 UDP vSnap SMB/CIFS vSnap SMB/CIFS target port used for mounting file system shares for transaction log backup and recovery operations
138 UDP vSnap SMB/CIFS vSnap SMB/CIFS target port used for mounting file system shares for transaction log backup and recovery operations
139 TCP vSnap SMB/CIFS vSnap SMB/CIFS target port used for mounting file system shares for transaction log backup and recovery operations.
443** TCP HTTPS Allows the SQL Server to communicate with IBM Spectrum Protect Plus for sending alerts in case of log backup failures
445 TCP vSnap SMB/CIFS vSnap SMB/CIFS target port used for mounting file system shares for transaction log backup and recovery operations

* iSCSI initiator is required on this node.
** new port in IBM Spectrum Protect Plus V10.1.4

[{"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.4","Edition":"","Line of Business":{"code":"LOB26","label":"Storage"}}]

Document Information

Modified date:
02 July 2021

UID

ibm10878755