Review the information below before implementing your SQL protection strategy.
Unitrends supports protection of the following SQL features:
To protect databases on a Windows SQL server, the only requirements are the ones described in Agent prerequisites for Microsoft SQL and SQL system requirements.
Unitrends supports protection of a variety of SQL cluster configurations, including cluster volumes, clustered shared volumes, AlwaysOn clusters, and failover clusters. See Requirements and considerations for Windows agent, operating system, and cluster requirements.
Unitrends supports protection of SQL databases with disk storage on SMB 3.0 shares. See Requirements and considerations for Windows agent and operating system, and SMB 3.0 share requirements.
The requirements for protecting your SQL databases vary based on the configuration of your SQL servers and the SQL Server features used in your environment. The agent and system requirements described below apply to all SQL protection. If you are protecting SQL clusters or data on SMB 3.0 shares, additional requirements apply. See the following topics for details:
The Unitrends Windows agent must be installed to protect SQL databases. However, the following services must be installed on all clients for which you intend to protect SQL databases prior to installing the Unitrends Windows agent:
• | The SQL Server VSS Writer, SQL Server Browser, and BP Agent services must be 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 will not 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. |
• | The Volume Shadow Copy service can be set to manual or automatic startup. |
• | SQL backups and restores are performed using the NT AUTHORITY\SYSTEM account. The NT AUTHORITY\SYSTEM account must be configured as sysadmin. Note that SQL Server 2012 does not grant NT AUTHORITY\SYSTEM sysadmin privileges by default. |
• | For SQL Server 2012, NT AUTHORITY\SYSTEM must be manually added as a system administrator. SQL backups and restores are performed using the NT AUTHORITY\SYSTEM account. For more information, go to the Microsoft Knowledgebase. |
• | To protect SQL Server 2014, you must be running agent release 8.0.0-4 or later. |
• | To protect databases hosted on SMB 3.0 fileshares, you must be running agent release 8.1 or later. |
In addition to the agent requirements, SQL must be on one of the following operating systems:
• | Windows Server 2012 and 2012 R2 (Standard, Datacenter, and Essentials versions) |
• | Windows 8.1 |
• | Windows 8 |
• | Windows 7 |
• | Windows Server 2008 and 2008 R2 (32-bit and 64-bit versions) |
• | Windows Vista |
• | Windows Server 2003 and 2003 R2 (32-bit and 64-bit versions) |
• | Windows SBS (64-bit version) |
• | Windows 2008 R2 Core |
• | Windows 2012 Core |
• | Windows 2012 R2 Core |
In addition to the agent and system requirements, the following are required to protect SQL clusters or data residing on SMB 3.0 shares:
• | The Unitrends appliance must be running release 8.1 or higher. |
• | For clusters, all nodes in the SQL cluster must be running Windows agent release 8.1 or higher. |
• | For the applicable clustered or SMB 3.0 setup, SQL and Windows versions must also meet the requirements in the following table: |
Configuration |
SQL |
Windows |
Cluster Volume |
2005 |
2003 SP1, 2008, 2008 R2, 2012, 2012 R2 |
2008 | 2008, 2008 R2, 2012, 2012 R2 | |
2012 | 2012, 2012 R2 | |
2014 | 2012, 2012 R2 | |
Clustered Shared Volume |
2012 |
2012, 2012 R2 |
2014 | 2012, 2012 R2 | |
AlwaysOn Clusters |
2012 |
2012, 2012 R2 |
2014 | 2012, 2012 R2 | |
SMB 3.0 | 2012 | 2012, 2012 R2 |
2014 | 2012, 2012 R2 | |
SQL Failover Clusters | 2014 | 2012, 2012 R2 |
Consider the following before executing backups for databases hosted on servers configured in a cluster:
• | You must add the cluster and each node in the cluster to the backup appliance, each as a separate asset. See Managing protected assets for details. |
• | When adding the cluster to the backup appliance, use the IP address of the clustered SQL server instance. This is the virtual IP address used to connect to the SQL server. |
• | There must only be one cluster IP address configured for each clustered SQL instance. |
• | To protect databases residing on cluster shared volumes (CSVs), you must select the cluster when creating backup jobs. You cannot protect these databases by selecting the owner node. |
• | To protect databases that are hosted on a cluster node but that do not reside on CSVs, you must create a backup schedule for the node that hosts those databases. You cannot protect them in the same schedule as the clustered databases. |
• | When backing up SQL nodes, include all local volumes, and exclude the system state. |
• | Additional considerations apply to SQL AlwaysOn Failover Cluster Instances. See Considerations for SQL AlwaysOn Failover Cluster Instances (FCI) for details. |
SQL Failover Clustering is a High Availability (HA) and Disaster Recovery solution. High Availability means that if one of the nodes in a SQL failover cluster fails, the secondary node is automatically promoted to the primary (active) node. There are some circumstances where a manual restart of the new primary database is required. See the following SQL documentation for details: Failover Policy for Failover Cluster Instances.
Unitrends provides seamless protection of your SQL environments in the event of a failover. Because the job schedule is attached to the cluster and not the individual nodes, the backups can continue as planned, providing uninterrupted protection of your SQL instance. For details about the SQL side of failover, see AlwaysOn Failover Cluster Instances (FCI). If your backups begin failing after a failover, see the following SQL documentation, Failover Cluster Troubleshooting.
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.
For Microsoft SQL recommendations, see the Recommendations section of AlwaysOn Failover Cluster Instances (FCI).
SQL Server 2012 and higher can host SQL instances with disk storage 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 KB 1334. |
• | 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. |
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 KB 1335. |
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.
The recovery model of your SQL databases 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.
Recovery Model |
Backups Supported |
Considerations |
|||||||||
Simple |
|
No SQL logs created. |
|||||||||
Full |
|
Schedule weekly transaction log backups to truncate logs. See Recommendations for full recovery model for details. |
|||||||||
Bulk-Logged |
|
Run a transaction log backup before switching from the full recovery model to the bulk-logged recovery model. See Recommendations for bulk-logged recovery model for details. |
The following table provides descriptions of the SQL system databases and how they can be protected with Unitrends.
Database |
Description |
Compatible recovery model and strategy |
master |
Stores all system-level information, such as logon accounts, configuration settings, and metadata. |
Only uses the simple recovery model and must be protected with full backups. Before restoring this database, all other databases must be stopped. |
msdb |
Used to schedule alerts, jobs, and broker services for database mail. Records backup and restore history. |
Uses the simple recovery model by default, but can be configured to use the full recovery model. (Recommended only if msdb history is used when restoring backups.) |
model |
Acts as a template for any new databases that are created. Content of the model is copied to each new database. |
By default it is configured to use the full recovery model, and new databases inherit this setting. It is only backed up when settings are changed. |
resource |
Contains internal system objects. (Read-only) |
This database cannot be backed up or restored. |
tempdb |
A temporary workspace used by any session connected to the SQL Server instance and is used to hold intermediate or temporary data. For example, temporary tables, cursors, and data for sorting. |
Every time SQL Server starts, this database is re-created. There is no reason to preserve this database by backing up or restoring. |
distribution |
Stores metadata and history data in support of SQL Server replication. |
Present only if replication is configured. |
This section provides example strategies for protecting your SQL databases with Unitrends software.
Database |
Backup Strategy |
System databases |
Weekly full backups |
User databases using the full recovery model |
Weekly full, daily differential, and hourly transaction logs |
User databases using the simple recovery model |
Bi-weekly full backups with daily differentials |
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:
1 | Run a log backup while the database is still in full recovery model. |
2 | Switch to the bulk-logged model. |
3 | Perform the bulk operation. (For example, importing new labels, copying data from one table to another, or creating an index.) |
4 | Switch back to the full recovery model. |
When you run asset-level backups of the Windows server hosting SQL, certain SQL-related files are automatically excluded:
• | The following extensions are excluded from SQL user databases if the SQL VSS component is running on the Windows asset: .mdf, .ldf, and .ndf. |
Note: If the VSS component is not running, these files are included. SQL files for system databases (such as master, model, and msdb) are always included to support the Windows instant recovery feature.
• | Files in SQL database/log directories are excluded. |