SQL backup requirements and considerations

The following table describes the SQL features that Unitrends protects.

The requirements for protecting your SQL databases vary based on the configuration of your SQL servers and the SQL features used in your environment. The Agent requirements for Microsoft SQL and SQL system requirements apply to all SQL protection. If you are protecting SQL clusters, availability groups, data on SMB 3.0 shares, Always Encrypted databases, or Stretch databases, additional requirements apply. See the following topics for details:

Agent requirements for Microsoft SQL

The Unitrends Windows agent is needed to protect hosted SQL databases. Before you install the agent, the following must be installed on the SQL server:

•

The SQL Server VSS Writer, SQL Server Browser, and BP Agent services must be installed and running to perform backup and restore operations. If the SQL Server VSS Writer or SQL Server Browser services are not started when you install the Windows agent, the agent cannot detect the SQL instance.

–

The SQL Server VSS Writer must be started and set to automatic startup.

–

The SQL Server Browser must be started and set to automatic startup.

–

The BP Agent service is installed when the Windows agent is installed on the SQL server.

•

The Volume Shadow Copy service must be installed and can be set to manual or automatic startup.

•

The NT AUTHORITY\SYSTEM account must be configured as sysadmin. This account is used to perform SQL backup and recovery jobs.

Note:  Beginning in SQL Server 2012, SQL does not grant NT AUTHORITY\SYSTEM sysadmin privileges by default. For SQL Server 2012 and later versions, you must manually add NT AUTHORITY\SYSTEM as a system administrator. For details, see the Microsoft Knowledge Base.

It is best practice to run the latest Unitrends appliance and agent software versions to protect your SQL environment. Older versions do not support all current Unitrends features:

•

To protect SQL Server 2022 on Windows, the SQL server must be running Windows 2022 and agent version 10.7.8 or later. The appliance must be running release 10.7.8 or later. (SQL Server 2022 on Linux is not supported.)

•

To protect SQL Server 2019 on Windows, the appliance and Windows agent must be running release 10.4.4 or later. (SQL Server 2019 on Linux is not supported.)

•

To protect SQL Server 2017 on Windows, the appliance and Windows agent must be running release 10.1 or later. (SQL Server 2017 on Linux is not supported.)

•

To protect Always On availability groups, the appliance and Windows agent must be running release 10.1 or later.

•

To protect SQL Server 2016, the appliance and Windows agent must be running release 9.0.0-13 or later.

•

To protect SQL Server 2014, the appliance and Windows agent must be running release 8.0.0-4 or later.

Detecting newer SQL versions upon upgrading the agent

After upgrading the Windows agent, you must re-save the SQL server asset and sync inventory to enable the appliance to detect any newly supported SQL versions. Follow these steps to detect new SQL versions:

1

Select Configure > Protected Assets. Select the SQL host asset and click Edit:

2

Click Save:

3

Click the Gear icon (Options menu) and select Inventory Sync:

 

After the inventory sync completes, any newly supported SQL versions hosted on the selected asset display in the UI, and you can begin protecting the new applications.

SQL system requirements

In addition to the agent requirements above, the following requirements must be met for Unitrends protection of all SQL environments:

•

The SQL application must be a supported version listed in the Unitrends Compatibility and Interoperability Matrix.

•

The SQL server must be running a supported Windows operating system listed in the Unitrends Compatibility and Interoperability Matrix. Windows 2012 or later is required if the configuration uses cluster shared volumes (CSV).

•

The SQL application and server must be set up in a supported Microsoft deployment configuration.

•

TLS version 1.0 or 1.2 must be installed and enabled on the SQL server.

•

SQL database names must not contain single or double quotes (' or ").

Note:  Databases that contain quotes in the name cannot be protected due to a Microsoft VSS limitation. These databases do not display in the Unitrends UI.

Requirements for SQL clusters and availability groups

In a SQL cluster, one or more SQL Server failover cluster instances are installed into a Windows Server Failover Cluster (WSFC). Unitrends protects SQL cluster configurations that meet the requirements in the following table.

Requirement	Description
Cluster configuration	The SQL cluster must be set up in a supported Microsoft configuration.
SQL application	The SQL applications must be supported versions listed in the Unitrends Compatibility and Interoperability Matrix.
SQL server	The SQL servers must be running supported Windows operating systems listed in the Unitrends Compatibility and Interoperability Matrix. Windows 2012 or later is required if the configuration uses cluster shared volumes (CSV). Note:  On Windows 2008 R2, there is a Microsoft VSS limitation that allows only one snapshot at a time for CSV volumes. Because SQL jobs typically run in parallel, Unitrends requires support for multiple VSS snapshots.
Windows agent	The Unitrends Windows agent must be installed on every SQL server node in the WSFC. (This applies even if your SQL servers are virtual machines.) These agent version requirements apply: • Each node in the WSFC must be running agent version 10.6.5 or earlier. (The secure pairing feature introduced in agent release 10.6.6 does not yet support WSFC configurations.) • Every node in the WSFC must be running the same agent version. It is best practice to upgrade agents to release 10.6.5 to take advantage of performance enhancements and fixes. • Agent version 10.0 or later is required to protect Always On availability groups. (The appliance must also be running Unitrends version 10.0 or later. Upgrade the appliance before installing the agent.) Note:  It is important that the appliance version is the same or higher than the agent versions of its protected assets. For SQL, API changes introduced in release 10.0 require that the appliance is upgraded first.
Clustered SQL instance	Because a clustered SQL instance can move between SQL server nodes in the cluster, you must add each cluster node and the SQL cluster itself to the backup appliance, each as a separate asset. You then create backup jobs by selecting the SQL cluster asset. On the backup appliance, there must be only one SQL cluster asset for each clustered SQL instance. To add a SQL cluster asset to the appliance, you enter its SQL cluster IP address. (For details, see Protecting SQL clusters and availability groups.) If a clustered SQL instance is configured with multiple IP addresses, add a single SQL cluster asset to the appliance by using one static IP address. Do not add multiple assets for a given SQL cluster by using its other IP addresses. If backups start failing after the SQL clustered instance has failed over, follow Microsoft's failover cluster troubleshooting steps. Examples of common post- failover issues for Always On Failover Cluster Instances (FCI) are given below, along with links to applicable Microsoft SQL documents. (If your cluster is not an Always On FCI, see the applicable Microsoft SQL documents for your environment.) • For general troubleshooting information, see these Microsoft documents: Failover Cluster Troubleshooting and Always On Failover Cluster Instances (FCI). • There are some circumstances where a manual restart of the new primary database is required. See the following Microsoft document for details: Failover Policy for Failover Cluster Instances. • When utilizing SQL failover clusters, the databases are protected at the SQL instance level, where one set of database files is saved on a shared storage device. The failover process takes as long as necessary to write all dirty pages in the cache to disk. For information on cutting down your SQL failover time, see the following SQL documentation: Indirect Checkpoints.
Always On availability groups	With this feature, one or more user databases are configured in an Always On availability group, where mirrored copies of each database reside on the secondary server nodes in the cluster. Availability group databases on the primary (active) cluster node reside in a primary replica. Mirrored copies reside in secondary replicas. Unitrends backs up databases in the primary replica only. Because any secondary replica can become the primary in a failover, you must add each cluster node and the availability group itself to the backup appliance, each as a separate asset. You then create backup jobs by selecting the availability group asset. The following are required to protect Always On availability groups: • The availability group must be set up in a supported Microsoft configuration. The following configurations are not supported: – Clusterless Availability Groups (introduced in SQL 2017) – Always On Basic Availability Groups – Availability groups with secondary replicas in Microsoft Azure • An availability group listener is required. • The availability group listener must be configured to use a static IP address and cannot be configured to use DHCP. • The cluster nodes and availability groups must be added to the Unitrends appliance as described in Protecting SQL clusters and availability groups. To add an availability group, you must supply its listener IP address. • On the backup appliance, there must be only one asset for each availability group. To add an availability group asset to the appliance, you enter its listener IP address. (For details, see Protecting SQL clusters and availability groups.) If an availability group is configured with multiple listener IP addresses, add a single asset to the appliance by using one static listener IP address. Do not add multiple assets for a given availability group by using its other listener IP addresses. • After a failover, a new full backup is required. If the next job is a differential or transaction log backup, it is automatically promoted to a full. Once this full completes, subsequent differential and transaction log backups run as scheduled. Note:  If you cannot allow a full backup to run at certain times in your environment (such as during peak business hours), you can opt to have the appliance fail any scheduled differential and transaction log backups until a full runs. To implement this option, set the SQL_AutoPromote flag to FALSE in the C:\PCBP\MASTER.INI file on each SQL server node. (If you installed the Windows agent in a custom location, check for the file under that location instead.)

Requirements for SQL databases located on SMB 3.0 shares

SQL Server 2012 and higher can host SQL instances with disk storage located on SMB 3.0 shares.

Prerequisites and considerations for protecting SQL databases located on SMB 3.0 shares

The following prerequisites must be met to protect SQL databases located on SMB 3.0 shares:

•

The File Server and the File Server VSS Agent Service roles must be installed on the server hosting the shares. For instructions on installing these roles, see How do I install the File Server and File Server VSS Agent Service roles on a server hosting SMB shares?.

•

The Windows agent installed on the SQL server must be granted read/write access to remote SMB 3.0 shares. For instructions on granting this access, see Granting the Windows agent read/write access to remote SMB 3.0 shares.

•

The SQL server hosting the databases and the server hosting the SMB shares must belong to the same Windows domain.

•

The database can contain one or more files located on SMB 3.0 shares. All files can reside on the same SMB 3.0 share or on different shares hosted by one or more servers in the same domain. All servers participating in the database backup must belong to the same domain.

•

For files located on remote SMB 3.0 shares, the Windows agent creates a VSS snapshot on the remote server and then exposes it to the SQL server through the SMB share pathing. The agent then backs up the database files from the remote snapshot location. When the backup completes, all VSS snapshots created for the backup are removed from the server hosting the SMB share.

Granting the Windows agent read/write access to remote SMB 3.0 shares

The Windows agent installed on the SQL server must be granted read/write access to remote SMB 3.0 shares. Grant this access using one of the following methods:

•

On the SQL server, change the login account for the Unitrends Windows agent service "bpagent" to the domain administrator account. Using these credentials provides all necessary access to the SMB shares. This is the most secure option for SMB access. Note, however, that backups of the SQL server may encounter files whose permissions do not allow domain administrator access. If this is the case for your SQL server and SMB share security is less of an issue, then the method below is recommended.

•

Run the agent as local system account on the SQL server and grant it read/write permission for the SMB shares. For instructions, see Running the Windows agent as local system account on Hyper-V server and granting account read/write permissions for SMB shares.

Once you have satisfied the SMB 3.0 prerequisites and have granted the Windows agent access to the SMB 3.0 shares, run backups as described in Backup Administration and Procedures.

Requirements for SQL Always Encrypted databases

With the SQL Always Encrypted feature, data is encrypted at rest and in motion. Plaintext is exposed only within the SQL application itself. In addition to the agent and system requirements, the following apply to protecting SQL Always Encrypted databases:

Item	Always Encrypted database requirement or consideration
Unitrends appliance version	Must be running release 9.0.0-13 or higher.
Unitrends agent version	The SQL server must be running Windows agent release 9.0.0-13 or higher.
Encrypted data	Data is encrypted at the client level and not at the database level. Encrypted databases cannot be viewed in SQL Management Studio.
SQL Column Encryption Keys	These keys are included in SQL backups and are restored when a SQL backup is recovered.
SQL Column Master Keys (CMKs)	Each Always Encrypted database has CMKs that are stored in a trusted key store located on the local SQL server. The CMKs are not included in SQL backups. After recovering backups of Always Encrypted databases, the CMKs must be available on the recovery target so you can access the recovered data. If these keys are not available, you must install them after you recover the backup. In most environments: • You will not need to install CMKs if recovering to the original database or to another database on the original instance. • You will need to install CMKs if recovering to a different SQL server. • You may need to install CMKs if recovering to a different instance on the original server. • See Microsoft's documentation for instructions on installing the CMKs.

Requirements for SQL Stretch databases

A Stretch database consists of a database that resides on the local SQL server and a paired database that resides in Microsoft Azure. For each table being stretched, an identical table exists in both the Azure and SQL databases. SQL Server moves data from the local tables to the Azure tables based on a user-defined function that acts as a filter.

In addition to the agent and system requirements, the following apply to protecting SQL Stretch databases:

Item	Stretch database requirement or consideration
Unitrends appliance version	Must be running release 9.0.0-13 or higher.
Unitrends agent version	The SQL server must be running Windows agent release 9.0.0-13 or higher.
Data protected	Unitrends backups capture the data on the local SQL server only. Data that was migrated to Azure before the backup runs is not included in the SQL backup.
Data recovered	Recovering a Stretch database backup recovers the part of the database that was backed up on the local SQL server only. You must recover the Unitrends backup to the original database on the original instance. Recovering to an alternate database, instance, or SQL server is not supported. After you recover to the original database, you must reconcile the local data with data that has been migrated to Azure. For instructions, follow Microsoft's Stretch database recovery recommendations in the article Backup and restore Stretch-enabled databases. This requires reconnecting the local recovered database to the remote Azure database using the SQL Master Key and the original credentials that were created when the database was stretched.
SQL Master Key	Each Stretch database has a SQL Master Key that is stored in a certificate located on the local SQL server. This key is not included in SQL backups. After recovering a Stretch database backup, you need to use this key to connect to the Azure database and reconcile the local recovered data with data that has been migrated to Azure.

The recovery model of your SQL database determines what type of Unitrends backups are supported. See the table below for descriptions of the SQL recovery models that are supported by Unitrends. See the Microsoft article Recovery Models (SQL) for additional information on recovery models and how to choose the best recovery model for your environment.

The following table provides descriptions of the SQL system databases and how they can be protected with Unitrends.

This section provides example strategies for protecting your SQL databases with Unitrends software.

When using the SQL full recovery model, transaction log backups must be performed to truncate log files. If not truncated, log files continue to grow until the space on your disk is full, resulting in system failure. To prevent runaway transaction log files, make sure that you create a schedule with frequent transaction log backups.

The SQL bulk-logged recovery model is used as a temporary recovery model to enhance performance when running bulk jobs. Unitrends does not support log backups while a database is in the bulk-logged recovery model because they are unnecessarily large. For compliance with Unitrends best practices, perform the following steps:

When you run file-level backups of the Windows server hosting SQL, certain SQL-related files are automatically excluded:

SQL Failover Clustering is a high availability and disaster recovery solution where one or more SQL Server failover cluster instances are installed into a Windows Server Failover Cluster (WSFC). Optionally, SQL failover clusters may include Always On failover cluster instances and Always On availability groups.

The WSFC has one active Windows server node. Its other servers are inactive members of the server cluster. If the active node fails, a secondary node is automatically promoted to the primary (active) node.

Unitrends backs up clustered instances and availability groups on the active server node. To provide seamless protection, job schedules must be attached to the clustered instance or availability group, and not to the individual nodes. This enables backups to continue after a failover, providing uninterrupted protection of your clustered SQL environment.

Note:  After a failover, a new full backup is required. If the next job is a differential or transaction log backup, it is automatically promoted to a full. Once this full completes, subsequent differential and transaction log backups run as scheduled.

In the Unitrends UI, each SQL server, clustered SQL instance, and availability group is treated as a separate asset. After you add each SQL server node to the appliance, you use the same Add Asset dialog to add each clustered SQL instance and availability group. You add a clustered SQL instance by entering its virtual IP address (note that this is the SQL cluster IP address, not the general cluster IP address). You add an availability group by entering its listener IP address.

In the Unitrends UI, each SQL instance or availability group displays under its host asset, and each database displays under its instance or availability group:

Note:  Filtering the display of instances and availability groups in the UI by SQL asset type was introduced in Unitrends release 10.0.0-6. If your schedules were backing up clustered instances or availability groups from a SQL server node asset, those instances and availability groups are no longer present in existing schedules upon upgrading to release 10.0.0-6 or higher. Follow the instructions in Start protecting SQL clusters and availability groups to add your clustered instance and availability group assets and modify your job schedules.

Start protecting SQL clusters and availability groups

Do the following to start protecting your clustered SQL environment:

Step 1:

Ensure all applicable requirements have been met. For details, see SQL backup requirements and considerations.

Step 2:

Install the Windows agent on each server node in the WSFC. For details, see Installing the Windows agent.

Note:   For most Windows servers, the appliance can push-install the agent when you add the asset. If you will be push-installing the agent, skip to Step 3:. For push-install requirements, see Windows agent requirements.

Step 3:

Add each WSFC server node to the Unitrends appliance. For details, see To add a SQL server asset.

Step 4:

Add each clustered SQL instance to the Unitrends appliance. For details, see To add a SQL cluster asset.

Step 5:

Add each availability group to the Unitrends appliance. For details, see To add a SQL availability group asset.

Step 6:

Create backup jobs as described in To create a SQL backup job.

To add a SQL server asset

You must add each server node to the appliance before adding any clustered instance or availability group assets.

1

Select Configure > Protected Assets, then Add > Asset:

2

Enter the asset's hostname.

3

Enter the asset's IP address. This is optional in some cases, as described here:

•

For Windows assets, you can use DNS rather than entering a static IP address.

•

DNS registration should be used for assets that obtain their network settings through DHCP. It is optional for assets with static IP addresses.

•

If you do not enter a static IP address, make sure that both the asset and the appliance have DNS entries and that reverse lookup is configured.

•

If you enter a static IP address, the appliance attempts to connect using this address. If the attempt fails, it tries again using DNS.

4

Enter or select optional settings.

•

To push-install the Windows agent, you must supply administrative credentials and check the Install Agent box.

•

If you have already installed the Windows agent, uncheck the Install Agent box.

5

Click Save.

Repeat this procedure until you have added every server node in the WSFC.

To add a SQL cluster asset

1

Select Configure > Protected Assets, then Add > Asset:

2

Enter a hostname for the clustered instance.

Because assets display by hostname in the Unitrends UI, it is recommended to use a naming convention that identifies the instance and its cluster.

3

Enter the IP address of the clustered SQL instance. (This is the virtual IP address used to connect to the SQL server.)

4

Uncheck the Install Agent box. (The agent is installed on SQL servers only).

5

Click Save.

To add a SQL availability group asset

1

Select Configure > Protected Assets, then Add > Asset:

2

Enter a hostname for the availability group asset.

Because assets display by hostname in the Unitrends UI, it is recommended to use a naming convention that identifies the availability group and its cluster.

3

Enter the IP address of availability group listener.

4

Uncheck the Install Agent box. (The agent is installed on SQL servers only).

5

Click Save.

Do the following to start protecting your SQL environment: