IBM Support

Database (Microsoft SQL or Oracle server) backup and restore requirements: IBM Spectrum Protect™ Plus V10.1.1

Preventive Service Planning


Abstract

This document details the Database (Microsoft SQL or Oracle server) backup and restore requirements for IBM® Spectrum Protect™ Plus V10.1.1

Content

This document details the Database (Microsoft SQL or Oracle server) backup and restore requirements for IBM® Spectrum Protect™ Plus V10.1.1

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.
Microsoft SQL Server Requirements
Oracle Requirements

 

Microsoft SQL Server Requirements

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

Microsoft SQL Server Configuration Requirements

  • SQL 2008 R2 SP3 on Windows Server 2012 R2

  • SQL 2012 on Windows Server 2012 R2

  • SQL 2012 SP2 on Windows Server 2016

  • SQL 2014 on Windows Server 2012 R2

  • SQL 2014 on Windows Server 2016

  • SQL 2016 on Windows Server 2012 R2

  • SQL 2016 on Windows Server 2016

  • SQL 2017 on Windows Server 2012 R2 (beginning with 10.1.1 patch 1)

  • SQL 2017 on Windows Server 2016 (beginning with 10.1.1 patch 1)

Note: It is highly recommended that you install the latest SQL Server patches and updates in your environment.

The following configurations are supported with:

Standalone SQL Server Failover Clustering AlwaysOn configurations
SQL 2008 R2 SP3 yes yes yes
SQL 2012 yes yes yes
SQL 2014 yes yes yes
SQL 2016 yes yes yes
SQL 2017 yes
(beginning with v10.1.1 patch1)		 no no


Windows Remote Shell (WinRM) must be enabled.
Cluster drive configurations utilizing dynamic disks is not supported. Standalone dynamic disks configurations are supported. Microsoft iSCSI Initiator must be enabled and running on the Windows Server. 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 system databases.
 

In-Memory OLTP Requirements and Limitations
In-Memory OLTP is a memory-optimized database engine used to improve database application performance, supported in SQL 2014 and 2016. 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 Requirements and Limitations
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 is increased to a specific threshold. 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

02/05/2019 As per APAR IT27984: it indicated that Cluster shared volumes (CSVFS) are supported.
However, the support Cluster shared volumes (CSVFS) are not supported in V 10.1.1 IBM Spectrum Protect Plus Microsoft SQL agent.


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 is forced when the following conditions are detected:

  • Journal discontinuity is detected, due to the log reaching the maximum size, disabling of the log, or changing of the catalogued USN ID.

  • The file size is less than the tracked threshold size, which by default is 102 KB.

  • 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, select Secondary only. If more than one secondary replica is available, the SQL 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 in IBM Spectrum Protect Plus by name or IP address. When registering a SQL Cluster (AlwaysOn) node, register each node by name or IP address. Note that the IP addresses must be public-facing and listening on port 5985. The fully qualified domain name must be resolvable and route-able from the IBM Spectrum Protect Plus appliance.
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 Requirements
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.
Kerberos authentication also requires that the clock skew between the Domain Controller and the IBM Spectrum Protect Plus appliance is less than 5 minutes. Note that the default Windows NTLM protocol is not time dependent.

 

SQL Log Truncation
To truncate SQL application logs during hypervisor Backup jobs, enable the Truncate SQL logs option when creating a job definition. The following SQL environments support SQL log truncation:

  • SQL 2008 R2 SP3 on Windows Server 2012 R2

  • SQL 2012 SP4 on Windows Server 2012 R2

  • SQL 2014 SP2 on Windows Server 2012 R2

  • SQL 2016 SP1 on Windows Server 2016
     

Privileges
On the SQL server, the system login credential must have public and sysadmin permissions enabled, plus permission to access cluster resources in a SQL AlwaysOn environment. If one user account is used for all SQL functions, a Windows login must be enabled for the SQL server, with public and sysadmin permissions enabled.
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: https://support.microsoft.com/en-us/help/2926557/sql-server-vdi-backup-and-restore-operations-require-sysadmin-privileg.
The target SQL server service account must have permissions to access SQL restore files. See Administrative Considerations in the following Microsoft article: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189128(v=sql.105).
To perform log backups, the SQL server agent service user must be a local Windows administrator and must have the sysadmin permission enabled to manage SQL server agent jobs. The agent will use that administrator account to enable/access log backup jobs. The IBM Spectrum Protect Plus SQL agent service user must also be the same as the SQL server service and SQL server agent service account for every SQL instance to be protected.
 

SQL Server Ports
The following ports are used by SQL servers. Note that ports marked as Accept use a secure connection (https/ssl).

Incoming SQL Server 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 SQL Server 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.



 


Oracle Requirements


Before registering each Oracle server in IBM Spectrum Protect Plus, ensure it meets the following requirements.
 

Oracle Configuration Requirements

  • Database Versions

    • Oracle 11g R2

    • Oracle 12c R1

    • Oracle 12c R2

    Note: For Oracle 12c multitenant databases, IBM Spectrum Protect Plus supports protection and recovery of the container database, including all pluggable databases under it. Granular recovery of specific PDBs can be performed via Instant Disk Restore recovery combined with RMAN.
     

  • Operating Systems

    • AIX 6.1 TL9+

    • AIX 7.1+

    • Red Hat Enterprise Linux / Centos 6.5+

    • Red Hat Enterprise Linux / Centos 7.0+

    • SUSE Linux Enterprise Server 11.0 SP4+

    • SUSE Linux Enterprise Server 12.0 SP1+
        

  • Additional Notes

    • Oracle DataGuard is not supported.

    • Databases must be in ARCHIVELOG mode. IBM Spectrum Protect Plus cannot protect databases running in NOARCHIVELOG mode.

    • RAC database recoveries are not server pool-aware. IBM Spectrum Protect Plus can recover databases to a RAC, but not to specific server pools.
       

Software

  • The bash and sudo packages must be installed. Sudo must be version 1.7.6p2 or above. Run sudo -V to check the version.

  • Python version 2.6.x or 2.7.x must be installed.

  • RHEL/CentOS 6.x only:
    Ensure the util-linux-ng package is up-to-date by running: yum update util-linux-ng.
    Depending on your version or distribution, the package may be named util-linux.
     

Connectivity

  • The SSH service must be running on port 22 on the server and any firewalls must be configured to allow IBM Spectrum Protect Plus to connect to the server using SSH. The SFTP subsystem for SSH must also be enabled.

  • The server can be registered using a DNS name or IP address. DNS names must be resolvable by IBM Spectrum Protect Plus.

  • When registering Oracle RAC nodes, register each node using its physical IP or name. Do not use a virtual name or Single Client Access Name (SCAN).
     

Authentication and Privileges

  • The Oracle server must be registered in IBM Spectrum Protect Plus using an operating system user that exists on the Oracle server (referred to as "IBM Spectrum Protect Plus agent user" for the rest of this topic).

  • Ensure the password is correctly configured and that the user can log in without facing any other prompts, such as prompts to reset the password.

The IBM Spectrum Protect Plus agent user must have the following privileges:

  • Privileges to run commands as root and as Oracle software owner users (e.g., oracle, grid) using sudo. IBM Spectrum Protect Plus requires this for various tasks such as discovering storage layouts, mounting and unmounting disks, and managing databases and ASM.

    • The sudoers configuration must allow the IBM Spectrum Protect Plus agent user to run commands without a password.

    • The !requiretty setting must be set.

    • The ENV_KEEP setting must allow the ORACLE_HOME and ORACLE_SID environment variables to be retained.

  • Privileges to read the Oracle inventory. IBM Spectrum Protect Plus requires this to discover and collect information about Oracle homes and databases.
    To achieve this, the IBM Spectrum Protect Plus agent user must belong to the Oracle inventory group, typically named oinstall.
     

For information about creating a new user with necessary privileges, see Sample Configuration of an IBM Spectrum Protect Plus Agent User.
 

NFS Requirements
The Oracle server must have the native Linux/AIX NFS client installed. IBM Spectrum Protect Plus uses NFS to mount storage volumes for backup and restore operations.
During database restore, the Oracle Direct NFS feature is required. IBM Spectrum Protect Plus automatically enables Direct NFS if it is not already enabled.
In order for Direct NFS to work correctly, the executable <ORACLE_HOME>/bin/oradism under each Oracle Home must be owned by root and have setuid privileges. This is typically pre-configured by the Oracle installer, but on certain systems, the binary may not have the necessary privileges. For more information, see the following Oracle Support document: https://support.oracle.com/knowledge/Oracle%20Database%20Products/1430654_1.html.
Run the following commands to set the correct privileges:
chown root:oinstall <ORACLE_HOME>/bin/oradism
chmod 750 <ORACLE_HOME>/bin/oradism
Note: Replace “oinstall” with the appropriate group that owns the installation.
 

Database Discovery
IBM Spectrum Protect Plus discovers Oracle installations and databases by looking through the files /etc/oraInst.loc and /etc/oratab, as well as the list of running Oracle processes. If the files are not present in their default location, the "locate" utility must be installed on the system so that IBM Spectrum Protect Plus can search for alternate locations of these files.
IBM Spectrum Protect Plus discovers databases and their storage layouts by connecting to running instances and querying the locations of their datafiles, log files, etc. In order for IBM Spectrum Protect Plus to correctly discover databases during cataloging and copy operations, databases must be in "MOUNTED," "READ ONLY," or "READ WRITE" mode. IBM Spectrum Protect Plus cannot discover or protect database instances that are shut down.
 

Block Change Tracking
IBM Spectrum Protect Plus requires Oracle Block Change Tracking to be enabled on protected databases in order to efficiently perform incremental backups. If Block Change Tracking is not already enabled, IBM Spectrum Protect Plus enables it automatically during the backup job.
To customize the placement of the Block Change Tracking file, you must manually enable the Block Change Tracking feature before running an associated backup job. If enabled automatically by IBM Spectrum Protect Plus, the following rules are used to determine the placement of the Block Change Tracking file:

  • If the db_create_file_dest parameter is set, the Block Change Tracking file is created in the location specified by this parameter.

  • If the db_create_file_dest parameter is not set, the Block Change Tracking file is created in the same directory as the SYSTEM tablespace.


Log Backup (beginning with 10.1.1 patch 1)

  • The cron daemon must be enabled on the application server.

  • The IBM Spectrum Protect Plus Agent user must have the necessary privileges to use the crontab command and create cron jobs of its own. Privileges can be granted through the cron.allow configuration file.
     

Sample Configuration of an IBM Spectrum Protect Plus Agent User
The commands below are examples for creating and configuring an operating system user that IBM Spectrum Protect Plus will use to log in to the Oracle server. The command syntax may vary depending on your operating system type and version.

  • Create the user that will be designated as the IBM Spectrum Protect Plus agent user: useradd -m sppagent

  • Set a password: passwd sppagent

  • If using key-based authentication, place the public key in /home/sppagent/.ssh/authorized_keys, or the appropriate file depending on your sshd configuration, and ensure the correct ownership and permissions are set, such as:
    chown -R sppagent:sppagent /home/sppagent/.ssh
    chmod 700 /home/sppagent/.ssh
    chmod 600 /home/sppagent/.ssh/authorized_keys

  • Add the user to the Oracle installation and OSDBA group: usermod -a -G oinstall,dba sppagent

  • If ASM is in use, also add the user to the OSASM group: usermod -a -G asmadmin sppagent

  • Place the following lines at the end of your sudoers configuration file, typically /etc/sudoers. If your existing sudoers file is configured to import configuration from another directory (for example, /etc/sudoers.d), you can also place the lines in a new file in that directory:
    Defaults:sppagent !requiretty
    Defaults:sppagent env_keep+="ORACLE_HOME"
    Defaults:sppagent env_keep+="ORACLE_SID"
    sppagent ALL=(ALL) NOPASSWD:ALL
     

Oracle Ports
The following ports are used by Oracle servers. Note that ports marked as Accept use a secure connection (https/ssl).

Incoming Oracle Server Firewall Connections
Port Protocol Firewall Rule Service Description
22 TCP Accept SSH Used for SSH data transfer to and from the internal vSnap server.
Outgoing Oracle Server Firewall Connections
Port Protocol Service Description
111 TCP vSnap RPC Port Bind Allows clients to discover ports that Open Network Connectivity (ONC) clients require to communicate with ONC servers.
2049 TCP vSnap NFS Used for NFS file sharing via vSnap.
20048 TCP vSnap NFS Mount Mounts vSnap file systems on clients such as the VADP Proxy, application servers, and virtualization data stores.

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

Document Information

Modified date:
11 March 2019

UID

swg22013789

Page Feedback