Microsoft SQL Server database backup and restore requirements

Review the Microsoft SQL Server database backup and restore requirements for IBM Spectrum® Protect Plus.

To help ensure that backup and restore operations can be run successfully, your system must meet the hardware and software requirements. Use the following requirements as a starting point. For the most current requirements, which might include updates, see technote 304861.

Configuration

Application versions

Table 1. Coverage matrix for application levels supported by IBM Spectrum Protect Plus
IBM Spectrum Protect Plus Microsoft SQL Server 2008 R2 SP3*
Standard and Enterprise editions		 Microsoft SQL Server 2012*
Standard and Enterprise editions		 Microsoft SQL Server 2014*
Standard and Enterprise editions		 Microsoft SQL Server 2016*
Standard and Enterprise editions		 Microsoft SQL Server 2017*
Standard and Enterprise editions		 Microsoft SQL Server 2019*
Standard and Enterprise editions
V10.1.1 Checkmark Checkmark Checkmark Checkmark Checkmark
Beginning with V10.1.1 patch 1		 --
V10.1.2 Checkmark Checkmark Checkmark Checkmark Checkmark --
V10.1.3 Checkmark Checkmark Checkmark Checkmark Checkmark --
V10.1.4 Checkmark Checkmark Checkmark Checkmark Checkmark --
V10.1.5 Checkmark Checkmark Checkmark Checkmark Checkmark Checkmark
Beginning with V10.1.5 patch 1
V10.1.6 Checkmark Checkmark Checkmark Checkmark Checkmark Checkmark
V10.1.7 Checkmark Checkmark Checkmark Checkmark Checkmark Checkmark
* The base release and later cumulative updates and maintenance levels are supported.

Operating systems

Table 2. Coverage matrix for supported operating systems on Microsoft Windows 64-bit operating systems
IBM Spectrum Protect Plus Microsoft Windows Server 2012 R2*
Standard and Datacenter editions		 Microsoft Windows Server 2016*
Standard and Datacenter editions		 Microsoft Windows Server 2019*
Standard and Datacenter editions
V10.1.1 Checkmark Checkmark --
V10.1.2 Checkmark Checkmark --
V10.1.3 Checkmark Checkmark Checkmark
V10.1.4 Checkmark Checkmark Checkmark
V10.1.5 Checkmark Checkmark Checkmark
V10.1.6 Checkmark Checkmark Checkmark
V10.1.7 Checkmark Checkmark Checkmark
* The base release and later maintenance levels are supported.

Restrictions

  • IBM Spectrum 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 Spectrum 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 using a customized file path for production restore jobs to reduce the length.
  • Due to limitations of the VSS framework, leading spaces, trailing spaces, and unprintable characters should not be used in database names. For more information, see Backing up a SQL Server database using a VSS backup application may 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.

Software

  • Install the most recent Microsoft SQL Server patches and updates in your environment.
  • A supported version of a Microsoft Windows 64-bit operating system, including the most recent patches and updates, must be installed in your system environment.
  • 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 information about compatibility, see System Requirements, Installation, and Driver Files - SQL Server.

Connectivity

Ensure that your system environment meets the following connectivity requirements:

  • 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 the SQL Server to communicate with the IBM Spectrum Protect Plus server by using the Hypertext Transfer Protocol Secure (HTTPS) protocol via port 443.
  • Firewalls must be configured to enable IBM Spectrum Protect Plus to connect to the server by using WinRM.
  • The IP address of the machine that you register must be reachable from the IBM Spectrum Protect Plus server and from the vSnap server. The SQL Server must have a WinRM service that is listening on port 5985.
  • All servers, proxies, applications, and hypervisors that are added to the IBM Spectrum 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 Spectrum Protect Plus server and from the vSnap server. All IBM Spectrum Protect Plus components must also be resolvable by their DNS names.

Authentication and privileges

Authentication

Register each Microsoft SQL Server with IBM Spectrum 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 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 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.

Authentication modes

Microsoft SQL Server must be configured to use Windows Authentication, sometimes called trusted connections, for its authentication mode. For more information about SQL Server authentication modes and steps on changing the SQL Server authentication mode, see Change server authentication mode.

Kerberos authentication

Kerberos-based authentication can be enabled by specifying a configuration file on the IBM Spectrum Protect Plus virtual 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 that is 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 virtual appliance is less than 5 minutes. The default Windows NTLM protocol is not time-dependent.

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 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. See the Administrative Considerations section 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 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”

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. You must specify the network security and Group Policy Object (GPO) settings that are described in this section.

To specify the Network security: LAN Manager authentication level policy setting, click Computer Configuration > Windows Settings > Security Settings > Local Policies > Security Options. 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: If you are using vSnap as the backup destination, you must select Send NTLMv2 response only, Refuse LM or Send NTLMv2 response only, Refuse LM & NTLM. 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 navigating to Computer Configuration > Windows Settings > Security Settings > Local Policies > Security Options > Network security: Restrict NTLM: Incoming NTLM traffic and 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 Interface (iSCSI) route must be enabled between the Microsoft SQL Server system and vSnap server. For more information, see Microsoft 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 Spectrum 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.

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 Spectrum 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 will be 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 Spectrum Protect Plus Instant Disk Restore jobs, your SQL Server clones can be used immediately. IBM Spectrum 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 Spectrum Protect Plus, see Backing up and restoring SQL Server data.

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, 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 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:

  1. Select the Availability Group node.
  2. Select the availability group you that you want to configure and 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 AlwaysOn availability groups.

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

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

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.

Point-in-time (PIT) restore operations

During a PIT restore operation IBM Spectrum Protect Plus uses the backup folder that is configured for the Microsoft SQL Server instance to stage the transaction log file before it is applied to the database. Sufficient free space must be available to store the transaction log file during a PIT restore. You can use SQL Server Management Studio (SSMS) to change the backup folder configuration for the staging area.

Ports

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

Table 3. 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 server Microsoft SQL Server Provides access to the Microsoft WinRm service for Windows-based servers
5986 TCP IBM Spectrum 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 Spectrum Protect Plus agent user
Port Protocol Initiator Target Description
3260 TCP Microsoft SQL Server vSnap server Used for Microsoft Internet Small Computer System Interface (iSCSI) data transfer to and from LUNs mounted from vSnap servers during backup and restore operations.

To enable this functionality, the Microsoft iSCSI Initiator Service is required on the specified node.
443 TCP Microsoft SQL Server agent IBM Spectrum Protect Plus server Port that allows the agent to communicate with IBM Spectrum Protect Plus for SQL log backup, restore, and purge operations, and for sending alerts in case of log backup failures
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 V10.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 V10.1.6, the SMBv1 protocol is not used. All agents use SMBv2 or later, which does not require ports 137, 138, or 139.

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 is required for product installation