Chat now with support
Chat with Support

NetVault Plug-in for SQL Server 12.2 - User Guide

Introducing NetVault Backup Plug-in for SQL Server Planning your SQL Server deployment Installing and removing the plug-in Configuring the plug-in Backing up data
Defining a backup strategy Understanding snapshot-based backups Reviewing the compression features Performing Online VDI backups Performing VSS backups in SQL Server Example of creating a full VDI backup of an AlwaysOn Availability Group on Windows
Restoring data Using the CLI with the plug-in Troubleshooting

Selecting data for an Online VDI backup

You must use sets — Backup Selection Set, Backup Options Set, Schedule Set, Target Set, and Advanced Options Set — to create a backup job.

Backup Selection Sets are essential for Incremental and Differential Backups. Create the Backup Selection Set during a Full Backup, and use it for Full, Incremental, and Differential Backups. The backup job reports an error if you do not use a Selection Set for the Incremental or Differential Backup. For more information, see the Quest NetVault Backup Administrator’s Guide.

 
* 
TIP: To use an existing set, click Create Backup Job, and select the set from the Selections list.
1
In the Navigation pane, click Create Backup Job.
You can also start the wizard from the Guided Configuration link. In the Navigation pane, click Guided Configuration. On the NetVault Configuration Wizard page, click Create backup jobs.
2
In Job Name, specify a name for the job.
Assign a descriptive name that lets you easily identify the job when monitoring its progress or restoring data. The job name can contain alphanumeric and non-alphanumeric characters, but it cannot contain non-Latin characters. On Linux, the name can have a maximum of 200 characters. On Windows, there is no length restriction; however, a maximum of 40 characters is recommended.
3
Next to the Selections list, click Create New.
4
In the list of plug-ins, open Plug‑in for SQL Server, and then open the All Instances node to view the individual instances.
With the instances visible, you can do one of the following:
•
Select an entire instance for inclusion.
 
* 
IMPORTANT: If you are backing up data that was set up for use in a Virtual Client, select the SQL Server Virtual Server, or the items contained within, for inclusion in the backup. If you are using multiple SQL Server Virtual Servers in the same cluster, select the correct SQL Server Virtual Server whose data you want to back up. The instance name displayed on this page is the SQL Server Virtual Server that was established as the Virtual Client during installation.

Check that you run the process using the Virtual Client and not from one of the nodes. If you open or expand one of the nodes and drill down through the hierarchy, you might see a dimmed SQL Server Instance identified as local. While the system might use this instance in maintaining log information, do not run any processes at this level.
•
Open an instance to display its individual databases, and select the databases to include in the backup.
•
For Windows-based environments, open an individual database to display its files and filegroups, and select the items to include in the backup.
 
* 
IMPORTANT: For the backup types Full File and Filegroup, Differential File and Filegroup, Partial Database, and Differential Partial Database, a read-only filegroup must be explicitly selected in the selection tree to include it in the backup. Though a green check mark appears against the read-only filegroups when you select the parent-level database, these filegroups are not backed up. When including a read-only filegroup in a backup, select the individual items instead of the parent-level database.

If you are using Linux, you cannot select individual files and filegroups for the backup job.
5
Click Save, enter a name in the Create New Set dialog box, and click Save.
The name can contain alphanumeric and non-alphanumeric characters, but it cannot contain non-Latin characters. On Linux, the name can have a maximum of 200 characters. On Windows, there is no length restriction. However, a maximum of 40 characters is recommended on all platforms.

Setting backup options for an Online VDI backup

The next step involves creating the Backup Options Set or selecting an existing one.

 
* 
TIP: To use an existing set, in the Plugin Options list, select the set that you want to use.
•
Setting backup options for Windows
•
Setting backup options for Linux
Setting backup options for Windows
1
Next to the Plugin Options list, click Create New.
2
In the Backup Method section on the SQL Server Backup Options tab, select Use Virtual Device Interface (VDI).
The VDI backup method lets you take advantage of Microsoft’s VDI API, which provides maximum reliability and flexibility when defining a backup strategy.
3
Set the following options, if applicable:
•
Block Size (Bytes): This field allows for the input of a device block size, in bytes. The default value is 64KB, that is, 65536 bytes.
•
Transfer Multiple: The value specified in this field is used as a multiplier for the value specified in the Block Size field. The overall value of the two serves as the maximum allowable data transfer size; that is, the Block Size default of 65536 bytes multiplied by the default Transfer Multiple of 24, results in a maximum allowable transfer size of 1.6MB.
4
In the Backup Type section, select the applicable option:
•
Full Database
•
Differential Database
•
Copy-Only
•
Incremental Transaction Log
•
Tail-Log
•
Full File and Filegroup
•
Differential File and Filegroup
•
Partial Database
•
Differential Partial Database
For information on these backup types, see Defining an Online VDI backup strategy and reviewing types.
5
If you want to exclude read-only databases from the backup job, in the Database Options section, select the Do Not Backup Read-Only Databases option.
This option does not apply to Copy-Only backups. Because of how Copy-Only backups are used, all applicable data, including read-only databases, is backed up.
6
In the Incomplete Backup of ALL Items Selected section, select the applicable option to instruct the plug-in on what it should do if this error condition occurs:
•
Complete with Warnings — Saveset Retained: The job returns a status of “Backup Completed with warnings” and a backup saveset is created that includes the items that were successfully backed up.
•
Complete without Warnings — Saveset Retained: The job completes and returns a status of “Backup Completed.” The errors are logged in the NetVault Backup binary logs and ignored on the Job Status page. A backup saveset is created that includes the items that were backed up.
•
Fail — Saveset Retained: The job returns a status of “Backup Failed.” However, a backup saveset is generated that includes the items that were successfully backed up.
•
Fail — No Saveset Retained: The job returns a status of “Backup Failed” and no saveset of backed-up objects is kept. That is, even if some of the objects were successfully backed up, the saveset is discarded.
If either of the following occurs, the plug-in overrides the Incomplete Backup of ALL Items Selected setting and responds as indicated:
•
If a fatal error occurs, the job returns a “Backup Failed” status.
•
If a Partial Database or Differential Partial Database backup is performed on a database whose backup target contains a read-only filegroup, the job returns a “Backup Completed with warnings” status.
7
In the VDI Backup Options section, select the applicable Backup Compression option; Use NetVault Backup Low Compression is the default:
•
Use NetVault Backup Low Compression
•
Use NetVault Backup Medium Compression
•
Use NetVault Backup High Compression
•
Use SQL Server Compression as Set in the SQL Server Instance
•
Use SQL Server Compression
•
Do Not Use Compression
 
* 
IMPORTANT: As stated previously, if TDE is enabled and you want to use compression, select one of the SQL Server Compression options.
8
If you selected one of the NetVault Backup Compression options, complete the VDI Streams/Compression Threads and Number of Parallel NetVault Backup Streams options, and then skip to Step 11.
The number of parallel backup streams selected must be equal to or less than the number of available media or tape drives.
9
If you selected either of the options related to the SQL Server Backup Compression feature, enter the number of VDI streams in the VDI Streams when NetVault Backup Compression is not used field, and then skip to Step 11.
The number of VDI devices selected must be equal to or less than the number of available media or tape drives.
10
If you selected Do Not Use Compression, enter the number of VDI streams in the VDI Streams when NetVault Backup Compression is not used field.
The number of VDI devices selected must be equal to or less than the number of available media or tape drives.
11
If you selected a Backup Type of Differential Database or Incremental Transaction Log, and the Instance Node is selected, use the New Database Found in Differential/Incremental Backup option to specify what action to take if a new database is found:
•
Do full database backup: This option instructs the plug-in to perform a full backup of all databases added since the last Full Database backup.
•
Ignore: Selecting this option instructs the plug-in to ignore any databases created since the last Full Database backup; that is, any databases that were created after the last backup are omitted from the Differential Database or Incremental Transaction Log backup.
12
If you selected a Backup Type of Incremental Transaction Log or Tail-Log, use the Transaction Log Options option to specify whether the inactive portion of the log must be truncated:
•
Normal: Select this option if you want the plug-in to truncate the inactive portion of the log file and make it available for re-use. This option is the default for Incremental Transaction Log backups.
•
No Truncate: Select this option to avoid truncation of the log during backup. This option must be selected for performing Tail-Log backups.
 
* 
IMPORTANT: When the Normal option is enabled, SQL Server automatically truncates the Transaction Logs after sending the backup Transaction Logs to the plug-in. When the No Truncate option is enabled, the Transaction Logs are not truncated. If the Normal option is used and the backup dies unexpectedly, the Transaction Logs are lost permanently. If the plug-in fails to write the index or dies, the Transaction Logs cannot be restored and those transactions have already been deleted from the SQL Server logs. This issue results in errors when attempting to restore Transaction Log backups that were created after the failed backup job. If this situation occurs, make a full backup of your database to eliminate the need for these logs during restoration. If the backup encounters normal errors, the Transaction Logs are not lost.
13
If you want the plug-in to perform checksum on the backups, select the Enable Backup Checksum option, and then use Error Encountered During Checksum to indicate what the plug-in should do if it encounters an error.
Selecting this option tells SQL Server to verify the page, checksum or torn page, if this information is present on the page before writing a page to the backup media. Regardless of whether page checksums are present, SQL Server generates a separate backup checksum for the backup streams. Restore operations can optionally use the backup checksum to validate that the backup is not corrupt. The backup checksum is stored on the backup media, not on the database pages and can affect workload and backup throughput.
•
Continue after error: With this default value selected, the backup job continues even if it encounters an invalid checksum.
•
Stop on error: With this value selected, the backup job fails.
 
* 
IMPORTANT: To change the default action for this option for all subsequent jobs, see Configuring plug-in options.
14
If you are backing up an AlwaysOn Availability Group, click the AlwaysOn Availability Groups tab, and select the applicable Backup Replica Selection Algorithm option:
 
* 
NOTE: NetVault Backup considers all secondary replicas to have equal priority. If you choose an option that lets NetVault Backup select the replica to use for the backup process, NetVault Backup assigns preference to a replica that is functioning in synchronous-commit mode.
•
Primary Only: To require NetVault Backup to use the primary replica, select this option.
•
Prefer Secondary: To instruct NetVault Backup to give preference to using a secondary replica or the primary replica if a secondary replica is not available, select this option.
•
Any Replica: To let NetVault Backup determine which replica, primary or secondary, to use, select this option.
•
Secondary Only (For Copy-Only Backup): To require NetVault Backup to use a secondary replica, select this option.
 
* 
IMPORTANT: To back up a Secondary Copy, select the Backup Type of Copy-Only. If you select a different Backup Type, the job fails.

If you back up a secondary replica in an AlwaysOn Group, SQL Server only lets you perform VDI Copy-Only backups. Only backups of the primary replica are supported with VSS.

•
As Configured in SQL Server: To use the priority level set in your SQL Server configuration using SQL Server Management Studio, select this option.
15
Click Save, specify a name for the set in the Create New Set dialog box, and click Save.
The name can contain alphanumeric and non-alphanumeric characters, but it cannot contain non-Latin characters. On Windows, there is no length restriction; however, a maximum of 40 characters is recommended.
Setting backup options for Linux
1
Next to the Plugin Options list, click Create New.
2
In the Backup Type section, select the applicable option:
•
Full Database
•
Differential Database
•
Copy-Only
•
Incremental Transaction Log
•
Tail-Log
For information on these backup types, see Defining an Online VDI backup strategy and reviewing types.
3
Set the following options, if applicable:
•
Block Size (Bytes): This field allows for the input of a device block size, in bytes. The default value is 64KB, that is, 65536 bytes.
•
Transfer Multiple: The value specified in this field is used as a multiplier for the value specified in the Block Size field. The overall value of the two serves as the maximum allowable data transfer size; that is, the Block Size default of 65536 bytes multiplied by the default Transfer Multiple of 24, results in a maximum allowable transfer size of 1.6MB.
4
Verify that the Number of VDI Streams field, is set to 1.
 
* 
IMPORTANT: Linux only supports one VDI stream. If you select 2, the backup job generates a VDI OpenDevice fails error.
5
Select the applicable Backup Compression option, Do Not Use Compression or Use SQL Server Compression.
6
Click Save, specify a name for the set in the Create New Set dialog box, and click Save.
The name can contain alphanumeric and non-alphanumeric characters, but it cannot contain non-Latin characters. On Linux, the name can have a maximum of 200 characters. On Windows, there is no length restriction; however, a maximum of 40 characters is recommended.

Finalizing and submitting an Online VDI backup job

The final steps include setting additional options on the Schedule, Target Storage, and Advanced Options pages, submitting the job, and monitoring the progress by using the Job Status and View Logs pages. These pages and options are common to all NetVault Backup Plug-ins. For more information, see the Quest NetVault Backup Administrator’s Guide.

1
Use the Schedule, Target Storage, and Advanced Options lists to configure any additional required options.
2
Click Save or Save & Submit, whichever is applicable.
 
* 
TIP: To run a job that you have already created and saved, select Manage Job Definitions in the Navigation pane, select the applicable job, and click Run Now.
You can monitor progress on the Job Status page and view the logs on the View Logs page. For more information, see the Quest NetVault Backup Administrator’s Guide.

Performing VSS backups in SQL Server

Microsoft supports the ability to create snapshots of SQL Server data using VSS. VSS allows volume backups to be performed while applications on the system continue to write to the volumes.

Microsoft provides a SQL Server VSS Writer that permits backup solutions such as Plug‑in for SQL Server to copy SQL Server data while SQL Server is running. VSS-based backups do not negatively affect SQL Server’s performance or stability.

A VSS backup using Plug‑in for SQL Server includes the steps outlined in the following topics.

•
Selecting data for a VSS backup
•
Setting backup options for a VSS backup
•
Finalizing and submitting a VSS backup job
 
* 
IMPORTANT: Before initiating VSS backups, verify that the SQL Server VSS Writer service is running on the SQL Server. The SQL Server VSS Writer is automatically installed with SQL Server. However, the service is not enabled to start automatically. For VSS backups, start this service from the Windows Services panel — Start > Control Panel > Administrative Tools > Services. Quest recommends that you set the Startup Type for this service to Automatic.

You can define a VSS backup job regardless of the state of this service. However, the job fails if it is run while the SQL Server VSS Writer service is not running. The log for the failed backup job contains a message stating “Please ensure the SQL Server Writer service is started.”
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating