Review the following before implementing your SQL protection strategy:
Unitrends supports protection of the following SQL features:
• | SQL system and user databases - To protect these databases, the only requirements are the ones described in Agent prerequisites for Microsoft SQL and SQL system requirements. (Additional requirements apply if these databases are Always Encrypted, Stretch, or have disk storage on an SMB 3.0 share, as described below). |
• | SQL clusters - Unitrends supports protection of a variety of SQL cluster configurations, including cluster volumes, clustered shared volumes, AlwaysOn clusters, and failover clusters. To protect SQL clusters, see SQL backup requirements and considerations for Windows agent, SQL system, and cluster requirements. |
• | Databases with disk storage on SMB 3.0 shares - To protect these databases, see SQL backup requirements and considerations for Windows agent, SQL system, and SMB 3.0 share requirements. |
• | Always Encrypted databases - To protect these databases, see SQL backup requirements and considerations for Windows agent, SQL system, and Always Encrypted database requirements. |
• | Stretch databases - Unitrends backups capture the data on the local SQL server only (and do not include any data in the Azure database). To protect these databases, see SQL backup requirements and considerations for Windows agent, SQL system, and Stretch database requirements. |
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 and system requirements described below apply to all SQL protection. If you are protecting SQL clusters, data on SMB 3.0 shares, Always Encrypted databases, or Stretch databases, additional requirements apply. See the following topics for details:
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 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. |
• | Additional agent version requirements apply to specific SQL features. For details, see the feature requirements sections. |
In addition to the agent requirements:
• | 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. |
• | The SQL application and server must be set up in a supported Microsoft deployment configuration. |
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 and Windows agent 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 |
|
2016 |
2012, 2012 R2 |
Clustered Shared Volume |
2012 |
2012, 2012 R2 |
|
2014 |
2012, 2012 R2 |
|
2016 |
2012, 2012 R2 |
AlwaysOn Clusters |
2012 |
2012, 2012 R2 |
|
2014 |
2012, 2012 R2 |
|
2016 |
2012, 2012 R2 |
SQL Failover Clusters |
2014 |
2012, 2012 R2 |
|
2016 |
2012, 2012 R2 |
SMB 3.0 |
2012 |
2012, 2012 R2 |
|
2014 |
2012, 2012 R2 |
|
2016 |
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. For details, see |
• | 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 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 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.
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:
|
A Stretch database consists of a database on the local SQL server with a paired database on 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 to the Unitrends appliance, 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 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. |
After ensuring all requirements have been met, do the following to start protecting your SQL environment:
Step 1: | Install the Windows agent on the SQL server as described in Installing the Windows agent. |
Step 2: | Add the SQL server to the Unitrends appliance as described in To add an asset. |
Step 3: | Run backup jobs as described in To create a SQL backup job. |