IBM Support

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

Preventive Service Planning


Abstract

This document details the Microsoft SQL database backup and restore requirements for IBM® Spectrum Protect™ Plus V10.1.3.

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 you need.


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

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

Operating Systems:

  • Windows Server 2012 R2

  • Windows Server 2016

  • Windows Server 2019
     

Windows Remote Shell (WinRM) must be enabled.

Cluster drive configurations utilizing dynamic disks is not supported. Standalone dynamic disks configurations are supported.

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

IBM Spectrum Protect Plus Inventory jobs discover system databases and mark the databases that are eligible for protection. Log backup is 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 used to improve database application performance, supported in SQL 2014 and later. Note the following IBM Spectrum Protect Plus requirements and limitations for In-Memory OLTP usage:

  • The maximum restore file path must be less than 256 characters, which is an SQL requirement. 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 SQL restore capabilities.
     


SQL Incremental Backup

IBM Spectrum Protect Plus utilizes update sequence number (USN) change journal technology to perform incremental backups in a SQL 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 2012 R2 or later

  • NTFS version 3.0 or later

The following technologies are not supported for changed bytes tracking:

  • 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.

The minimum space required for shadow copy storage is 100 MB, though more space may be required on systems with increased activity. The SQL agent checks the source volume space, and will fail a backup if the free space on the source volume is lower than 100 MB. A warning message displays in the job log when free space is below 10%, then the backup proceeds.

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

  • Journal discontinuity is detected, 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 the tracked threshold size, which by default is 1 MB.

  • The file size is smaller after a previous backup.

  • A file is added after a previous backup.
     


SQL Always On Availability Groups Considerations

Configure the preferred instance for backup through SQL Server Management Studio. Select the Availability Group node. Select the availability group you wish to configure, then select Properties. In the Availability Group Properties dialog box, select Backup Preferences.

On the Where should backups occur? pane, any option can be selected. 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 agent.

The SQL 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 about the "Log on as a service" rights, see  https://technet.microsoft.com/en-us/library/cc794944.aspx

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 through a configuration file on the IBM Spectrum Protect Plus appliance. This will override the default Windows NTLM protocol.

For Kerberos-based authentication only, the user identity must be specified in the username@FQDN format. The username must be able to authenticate 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 need to have
- SQL public and sysadmin permissions,
- Windows local administration permission, which is required by VSS framework, and volume/disk access,
- permission to access cluster resources in a SQL AlwaysOn and SQL FCI environment.

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

The SQL VDI-based framework is used to interact with SQL databases and log backup/restore operations. A VDI connection requires SQL 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 restore files. See Administrative Considerations in the following Microsoft article:  Securing Data and Log Files
 


Ports

The following ports are used by IBM Spectrum Protect Plus Agents. Note that ports marked as Accept use a secure connection (https/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
iSCSI initiator is required on this node.
TCP vSnap iSCSI iSCSI vSnap target port used for mounting LUNS for backup and recovery.
137 UDP vSnap SMB/CIFS vSnap SMB/CIFS target port used for mounting filesystem shares for transaction log backup and recovery.
138 UDP vSnap SMB/CIFS vSnap SMB/CIFS target port used for mounting filesystem shares for transaction log backup and recovery.
139 TCP vSnap SMB/CIFS vSnap SMB/CIFS target port used for mounting filesystem shares for transaction log backup and recovery.
445 TCP vSnap SMB/CIFS vSnap SMB/CIFS target port used for mounting filesystem shares for transaction log backup and recovery.



 

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

Document Information

Modified date:
03 July 2019

UID

ibm10791575