Microsoft SQL Server

Veeam Backup & Replication provides three options for backing up SQL Server:

• Image-level backup of the virtual machines running SQL Server
• Backup of Windows machines running SQL Server using Veeam Agent
• Backup of selected databases on SQL Server using Veeam Plug-in for Microsoft SQL Server

The options differ in capabilities and the list of supported configurations.

Note: Please read the white paper on benefits of using SQL Always-on Availability Groups for Virtual Environment

Veeam Agent has additional limitations applied to specific configurations:

• Backup of CSV (Cluster Shared Volumes) is not supported. Cluster disks used as CSV are automatically excluded from backup.
• AlwaysOn Availability Groups based on multiple Failover Cluster Instances are not supported.
• AlwaysOn Clusterless Availability Groups are not supported.

Preparation

The following section will provide the best practices to verify SQL Server for the smooth and fast backup.

Check the status of VSS writers and providers:

1. Open a command prompt as administrator.
2. Type vssadmin list providers and press enter.
3. Verify all the writers are in health state.
4. Type vssadmin list writers and press enter
5. Check and confirm writers are Stable with no errors.

As backup is I/O intensive operations check the SQL server performance status before start taking the backup. To verify the SQL Server Health:

1. Open Performance Monitor.
2. Add below objects verify the performance:
3. Under the Memory object, add the counter: Available MB
4. Under the Processor object, add the counter: %Processor Time (Instance: Total)
5. Under the Physical Disk object, add the counters: Avg. Disk Sec/Read and Avg. Disk Sec/Write (All Instances)
6. Under the Paging File object, add the counter: %Usage (Instance: Total)

Check Microsoft article “Monitor Memory Usage” to validate the health status of SQL Server.

Job configuration

General Cluster Backup

Only process one MSSQL cluster within one backup job. This provides better handling for tasks like jobs cancelations or on-demand backups before a cluster maintenance is performed.

Virtual Machine Image Level Backup

Standalone SQL Server

No additional configuration is required to backup the standalone SQL server, you can configure the backup with application aware processing to take the backup of SQL server with the databases.

Please check the Veeam User’s Guide sections to get the more information about SQL backup configuration

SQL Always-on Availability Group

When backing up AlwaysOn availability group make sure all cluster nodes are processed by the same backup job for transaction logs processing and restores to work properly. Consider increasing cluster timeouts in case failover occurs during the backup, similar to Exchange DAG as per KB1744.

You can also follow the steps outlined in KB2110 to exclude certain databases from application aware processing.

Transactions logs backup:

The default location of the temporary folder on Windows systems is <drive with most free space>:\Veeam\Backup\SqlLogBackup.

If you wish to specify the location of the temporary file you can do so by changing the SqlTempLogPath (STRING) registry value:

• Path: HKEY_LOCAL_MACHINE\SOFTWARE\Veeam\Veeam Backup and Replication
• Key: SqlTempLogPath
• Type: REG_SZ
• Default value: undefined

Make sure that the desired path is writeable by the Veeam services.

Veeam Plug-in for Microsoft SQL Server

Database administrators can use Veeam Plug-in for Microsoft SQL Server to create native application-level backups of Microsoft SQL Server data. Compared to image-level backups created by Veeam Backup & Replication, Veeam Plug-in offers more flexible scenarios for database backup. In particular, Veeam Plug-in users can back up and restore individual Microsoft SQL Server databases, as well as configure independent backup schedule for full, differential and log backups using the SQL Agent Job functionality of Microsoft SQL Server.

Please see the Enterprise Plug-Ins page for general plug-in best practices.

---

References

• Veeam Knowledge Base - Change Temporary Folder Path
• Microsoft - Shrink SQL Log File
• Plug-in Requirements

---