Dell SQL VDI User Manual

Dell EMC NetWorker Module for Microsoft for SQL VDI

Version 19.2

User Guide

REV 01

November 2019

Copyright © 2007-2019 Dell Inc. or its subsidiaries. All rights reserved.

Dell believes the information in this publication is accurate as of its publication date. The information is subject to change without notice.

THE INFORMATION IN THIS PUBLICATION IS PROVIDED “AS-IS.” DELL MAKES NO REPRESENTATIONS OR WARRANTIES OF ANY KIND WITH RESPECT TO THE INFORMATION IN THIS PUBLICATION, AND SPECIFICALLY DISCLAIMS IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. USE, COPYING, AND DISTRIBUTION OF ANY DELL SOFTWARE DESCRIBED IN THIS PUBLICATION REQUIRES AN APPLICABLE SOFTWARE LICENSE.

Dell Technologies, Dell, EMC, Dell EMC and other trademarks are trademarks of Dell Inc. or its subsidiaries. Other trademarks may be the property of their respective owners. Published in the USA.

Dell EMC

Hopkinton, Massachusetts 01748-9103 1-508-435-1000 In North America 1-866-464-7381 www.DellEMC.com

2	Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

CONTENTS

Figures	7
Tables	9

Preface		11
Chapter 1	Overview	15
	Using NMM with Virtual Device Interface API...................................................	16
	SQL Server backup and restore workflow.........................................................	18
	Traditional backup workflow (stand-alone backup over a storage node)..
	18
	Federated backup workflow (AlwaysOn Availability Group
	configuration).......................................................................................	19
	Recovery workflow..............................................................................	20
	SQL Server cluster environments.....................................................................	22
	SQL Server backups.........................................................................................	23
	Types of supported backups................................................................	23
	Setting backup levels...........................................................................	24
	Backup promotion................................................................................	27
	SQL Server recovery........................................................................................	29
	Types of supported recovery...............................................................	29
	Recovery modes..................................................................................	30
	Recovery time......................................................................................	31
	Recovery window restrictions..............................................................	32
	The recovery process...........................................................................	32
	SQL Server instance and database names for backup and recovery.................	33
	Named and default instances of SQL Server........................................	33
	Supported special characters in database names for NMM backup and
	recovery ..............................................................................................	34
Chapter 2	Configuration	37
	Configuring NMM in a SQL VDI environment....................................................	38
	Supported Windows Server and SQL Server versions..........................	38
	Migrating from VSS solution to VDI solution for SQL Server data
	protection............................................................................................	38
	Multi-stream Data Domain Boost.........................................................	38
	Microsoft SQL Server Always On Availability Group feature ...............	38
	Availability group listeners....................................................................	39
	Clusterless availability group listeners..................................................	39
	SQL Client Direct to AFTD or DD devices............................................	40
	Microsoft SQL Server named log marks...............................................	40
	Database consistency checks...............................................................	41
	Microsoft hybrid cloud environments...................................................	42
	Transparent data encryption................................................................	42
	Setting the MAXTRANSFERSIZE environment variable.......................	43

Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

3

Contents

	Configuring permissions to perform NMM backup and recovery of Microsoft
	SQL Server.......................................................................................................	44
	Access privileges for backup and recovery...........................................	44
	Assign SQL server roles for backup and recovery operations...............	44
	Assigning Windows user privileges for backup and recovery operations...
	45
Chapter 3	Graphical User Interfaces	49
	User interfaces for backup and restore.............................................................	50
	NMM Microsoft SQL Server Management Studio plug-in GUI..........................	50
	Views of the NMM SQL Server Management Studio plug-in GUI......................	51
Chapter 4	Manual Backups	53
	Manual backup overview...................................................................................	54
	Federated backup preferences for Availability Group databases.......................	54
	Specifying a retention policy for manual backups.............................................	55
	Performing manual backups by using the NMM SSMS plug-in GUI..................	56
	Performing manual backups from the command prompt...................................	61
	Command syntax for nsrsqlsv..............................................................	62
	Command options for nsrsqlsv.............................................................	63
	Backup and recovery command syntax for SQL Server data................	68
	Example backup command syntax........................................................	72
Chapter 5	Scheduled Backups	75
	Overview of scheduled backup..........................................................................	76
	Prerequisites.....................................................................................................	76
	Federated backup preferences for Availability Group databases.......................	76
	Excluding incompatible databases in backups...................................................	78
	Configuring scheduled backups.........................................................................	78
	Setting up backup levels......................................................................	79
	Configuring a client resource...............................................................	80
	Setting data protection policies...........................................................	93
	Monitoring scheduled backups .........................................................................	98
Chapter 6	Data Restore	101
	Overview.........................................................................................................	102
	Prerequisites...................................................................................................	102
	Restoring data by using the NMM SSMS plug-in GUI......................................	103
	Restoring data by using the CLI.......................................................................	110
	Command syntax for nsrsqlrc..............................................................	111
	Command options for nsrsqlrc.............................................................	112
	Backup and recovery command syntax for SQL Server data...............	121
	Example recovery command syntax ...................................................	125
Chapter 7	Granular Level Recovery	127
	Overview.........................................................................................................	128
	Considerations.................................................................................................	128
	Performing Granular Level Recovery...............................................................	133
	Dismounting backups after performing GLR....................................................	136
	Using the Mount system tray icon......................................................	137

4	Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

		Contents
Chapter 8	Striped Backup and Recovery	139
	Overview of striped backups...........................................................................	140
	Performance considerations............................................................................	140
	Performing striped backups.............................................................................	141
	Performing striped recovery............................................................................	142
	Optimal striped recovery operations...................................................	142
	Fail-safe striped recovery operations..................................................	142
Chapter 9	Disaster Recovery	145
	Overview of disaster recovery.........................................................................	146
	Disaster recovery features..............................................................................	146
	Performing disaster recovery...........................................................................	147
	When not to reinstall the SQL Server.................................................	147
	Restoring a damaged primary disk......................................................	148
	Restoring a damaged binary disk........................................................	149
	Restoring SQL Server and NetWorker server.....................................	150
	Restoring the SQL Server without reinstallation.................................	151
	Restoring the SQL Server...................................................................	152
Chapter 10	Bare Metal Recovery	155
	Planning bare-metal recovery..........................................................................	156
	Overview............................................................................................	156
	System requirements..........................................................................	156
	Protecting an environment before a disaster......................................	158
	BMR by using NetWorker and NMM..................................................	159
	SQL Server in a cluster environment...............................................................	159
	Backing up a SQL Server for BMR......................................................	159
	Performing BMR of a SQL Server cluster............................................	161
	SQL Server in a stand-alone environment........................................................	163
	Backing up a SQL Server for BMR......................................................	163
	Performing BMR of a stand-alone SQL Server...................................	165
Chapter 11	Troubleshooting	167
	Error logs for backup and recovery..................................................................	168
	SQL savegroup notifications............................................................................	168
	Troubleshooting general issues........................................................................	169
	Troubleshooting GLR.......................................................................................	169

Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

5

Contents

6	Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

FIGURES

1	VDI backup process between NMM and SQL Server.........................................................	17
2	VDI recovery process between NMM and SQL Server.......................................................	18
3	Traditional backup workflow..............................................................................................	19
4	Federated backup workflow..............................................................................................	20
5	Traditional recovery workflow...........................................................................................	21
6	Message showing DBCC was successful...........................................................................	42
7	Assigning SQL Server privileges........................................................................................	45
8	Adding a user to Windows User Groups............................................................................	47
9	The NetWorker window in the NMM SMSS plug-in GUI ...................................................	51
10	NetWorker Backup General page......................................................................................	57
11	NetWorker Backup Options page......................................................................................	58
12	NetWorker Backup Monitor page......................................................................................	61
13	Starting the Client Configuration wizard...........................................................................	81
14	Specify Client Information page........................................................................................	82
15	Specify Backup Configuration Type page..........................................................................	83
16	Specify the Backup Options page.....................................................................................	85
17	Viewing clients in the Protection tab.................................................................................	86
18	Create Policy window.......................................................................................................	94
19	Creating a workflow for the policy....................................................................................	95
20	Specifying action information in the Policy Action wizard.................................................	96
21	Adding a group to a policy from the Protection pane........................................................	97
22	Specifying workflow and client in the Create Group window.............................................	98
23	Successful backup messages............................................................................................	99
24	Failed backup messages....................................................................................................	99
25	NetWorker dialog box—Database Restore tab General page..........................................	104
26	NetWorker dialog box—Database Restore tab General page for copy restore................	105
27	NetWorker dialog box—Database Restore Files/Filegroups page...................................	106
28	NetWorker dialog box—Database Restore Options page.................................................	107
29	NetWorker dialog box—Database Restore Monitor page.................................................	110
30	NetWorker dialog box—Table Restore General page.......................................................	134
31	NetWorker dialog box—Table Restore Options page......................................................	135
32	NetWorker dialog box—Table Restore Monitor page......................................................	136
33	Mount system tray menu.................................................................................................	137
34	Mount Details window......................................................................................................	137
35	SQL Server Properties dialog box....................................................................................	163
36	Example of SQL savegroup notification...........................................................................	169

Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

7

Figures

8	Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

TABLES

1	Revision history.................................................................................................................	12
2	Style conventions..............................................................................................................	12
3	Backup levels for SQL Server data....................................................................................	25
4	Full backup every 1 to 2 weeks..........................................................................................	25
5	Logs-only backup after a full backup.................................................................................	26
6	Backup level advantages and disadvantages.....................................................................	26
7	Creating additional backup levels with data objects..........................................................	26
8	Backup level promotion process .......................................................................................	27
9	Types of recovery for SQL Server VDI .............................................................................	29
10	Recovery modes ...............................................................................................................	31
11	Supported special characters in database names..............................................................	34
12	Access privileges required for backup and recovery operations........................................	44
13	Where to start backup operations.....................................................................................	50
14	Where to start restore operations.....................................................................................	50
15	Command options for nsrsqlsv .........................................................................................	63
16	Command syntax for SQL Server data..............................................................................	68
17	Command syntax for names containing a period................................................................	71
18	Command syntax for names containing a backslash...........................................................	71
19	Command syntax for names containing a colon.................................................................	71
20	Tasks for configuring SQL Server VDI backups ................................................................	79
21	Backup levels for SQL Server data....................................................................................	80
22	Save sets in a standalone environment..............................................................................	87
23	Save sets in a cluster environment....................................................................................	87
24	Application Information field values...................................................................................	88
25	Save sets...........................................................................................................................	91
26	Application Information field values...................................................................................	91
27	Advanced restore options................................................................................................	108
28	Command options for nsrsqlrc .........................................................................................	112
29	Command syntax for SQL Server data.............................................................................	122
30	Command syntax for names containing a period..............................................................	124
31	Command syntax for names containing a backslash........................................................	124
32	Command syntax for names containing a colon...............................................................	124
33	ItemPoint for SQL Server requirements..........................................................................	128
34	Guidelines for fail-safe striped recovery..........................................................................	142
35	Disaster recovery features...............................................................................................	146
36	Rebuilding SQL Server system databases........................................................................	148
37	Rebuilding SQL Server system databases........................................................................	149
38	Rebuilding SQL Server system databases........................................................................	150
39	Rebuilding SQL Server system databases........................................................................	151
40	Rebuilding SQL Server system databases........................................................................	162
41	Rebuilding SQL Server system databases........................................................................	165
42	Program and log file names without debug logging enabled.............................................	168
43	Program and log file names with debug logging enabled..................................................	168

Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

9

Tables

10 Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

Preface

As part of an effort to improve product lines, periodic revisions of software and hardware are released. Therefore, all versions of the software or hardware currently in use might not support some functions that are described in this document. The product release notes provide the most up-to-date information on product features.

If a product does not function correctly or does not function as described in this document, contact a technical support professional.

Note: This document was accurate at publication time. To ensure that you are using the latest version of this document, go to the Support website https://www.dell.com/support.

Purpose

This guide contains information about using the NetWorker Module for Microsoft (NMM) 19.2 software to back up and recover SQL Server using the Virtual Device Interface (VDI) technology.

Note: The NetWorker Module for Microsoft Administration Guide supplements the backup and recovery procedures described in this guide and must be referred to when performing application-specific tasks. Ensure to download a copy of the NetWorker Module for Microsoft Administration Guide from the Support website at https://support.emc.com before using this guide.

Audience

This guide is part of the NetWorker Module for Microsoft documentation set and is intended for use by system administrators during the setup and maintenance of the product. Readers should be familiar with the following technologies used in backup and recovery:

•NetWorker software

•Microsoft Virtual Device Interface (VDI) technology

Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

11

Preface

Revision history

The following table presents the revision history of this document.

Table 1 Revision history

Revision	Date	Description
01	November, 2019	First release of this document for the NetWorker
		Module for Microsoft 19.2 release.

Related documentation

The NMM documentation set includes the following publications:

•NetWorker Module for Microsoft Release Notes

•NetWorker Module for Microsoft Administration Guide

•NetWorker Module for Microsoft Installation Guide

•NetWorker Module for Microsoft for SQL and SharePoint VSS User Guide

•NetWorker Module for Microsoft for SQL VDI User Guide

•NetWorker Module for Microsoft for Exchange VSS User Guide

•NetWorker Module for Microsoft for Hyper-V User Guide

•ItemPoint for Microsoft SQL Server User Guide

•ItemPoint for Microsoft Exchange Server User Guide

•ItemPoint for Microsoft SharePoint Server User Guide

•NetWorker documentation set

Special notice conventions that are used in this document

The following conventions are used for special notices:

NOTICE Identifies content that warns of potential business or data loss.

Note: Contains information that is incidental, but not essential, to the topic.

Typographical conventions

The following type style conventions are used in this document:

Table 2 Style conventions

Bold	Used for interface elements that a user specifically selects or clicks,
	for example, names of buttons, fields, tab names, and menu paths.
	Also used for the name of a dialog box, page, pane, screen area with
	title, table label, and window.
Italic	Used for full titles of publications that are referenced in text.
Monospace	Used for:
	•	System code
	• System output, such as an error message or script
	• Pathnames, file names, file name extensions, prompts, and
		syntax
	•	Commands and options

12 Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

Preface

Table 2 Style conventions (continued)

Monospace italic	Used for variables.
Monospace bold	Used for user input.
[ ]	Square brackets enclose optional values.
|	Vertical line indicates alternate selections. The vertical line means or
	for the alternate selections.
{ }	Braces enclose content that the user must specify, such as x, y, or z.
...	Ellipses indicate non-essential information that is omitted from the
	example.

You can use the following resources to find more information about this product, obtain support, and provide feedback.

Where to find product documentation

•https://www.dell.com/support

•https://community.emc.com

Where to get support

The Support website https://www.dell.com/support provides access to product licensing, documentation, advisories, downloads, and how-to and troubleshooting information. The information can enable you to resolve a product issue before you contact Support.

To access a product-specific page:

1.Go to https://www.dell.com/support.

2.In the search box, type a product name, and then from the list that appears, select the product.

Knowledgebase

The Knowledgebase contains applicable solutions that you can search for either by solution number (for example, KB000xxxxxx) or by keyword.

To search the Knowledgebase:

1.Go to https://www.dell.com/support.

2.On the Support tab, click Knowledge Base.

3.In the search box, type either the solution number or keywords. Optionally, you can limit the search to specific products by typing a product name in the search box, and then selecting the product from the list that appears.

Live chat

To participate in a live interactive chat with a support agent:

1.Go to https://www.dell.com/support.

2.On the Support tab, click Contact Support.

3.On the Contact Information page, click the relevant support, and then proceed.

Service requests

To obtain in-depth help from Licensing, submit a service request. To submit a service request:

1. Go to https://www.dell.com/support.

Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

13

Preface

2. On the Support tab, click Service Requests.

Note: To create a service request, you must have a valid support agreement. For details about either an account or obtaining a valid support agreement, contact a sales representative. To find the details of a service request, in the Service Request Number field, type the service request number, and then click the right arrow.

To review an open service request:

1.Go to https://www.dell.com/support.

2.On the Support tab, click Service Requests.

3.On the Service Requests page, under Manage Your Service Requests, click View All Dell Service Requests.

Online communities

For peer contacts, conversations, and content on product support and solutions, go to the Community Network https://community.emc.com. Interactively engage with customers, partners, and certified professionals online.

How to provide feedback

Feedback helps to improve the accuracy, organization, and overall quality of publications. You can send feedback to [e-mail].

14 Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

CHAPTER 1

Overview

This chapter includes the following sections:

•

•

•

•

•

•

Using NMM with Virtual Device Interface API........................................................................	16
SQL Server backup and restore workflow..............................................................................	18
SQL Server cluster environments..........................................................................................	22
SQL Server backups..............................................................................................................	23
SQL Server recovery.............................................................................................................	29
SQL Server instance and database names for backup and recovery......................................	33

Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

15

Overview

Using NMM with Virtual Device Interface API

You can use the NetWorker Module for Microsoft (NMM) software to back up and recover Microsoft SQL Server data. NMM uses Virtual Device Interface (VDI), an API that Microsoft SQL Server provides, to integrate with the SQL Server and enable the NetWorker software to back up and recover SQL Server data.

Note: Any references to the Data Domain systems and the Data Domain devices in the product also apply to the PowerProtect Data Domain systems.

When you install NMM, you can run the System Configuration Checker from the Installation wizard. It is recommended that you run the System Configuration Checker to ensure that the setup is correctly configured for backup and recovery. The NetWorker Module for Microsoft Installation Guide provides details.

Note: If you are a NetWorker Module for SQL Server (NMSQL) user and are migrating to NMM VDI, perform a full backup of the SQL Server data after you install NMM VDI. NMM VDI cannot recover SQL snapshot data backed up with NMSQL.

The following figure describes the backup process that takes place between NMM and the SQL Server using VDI:

1.The user starts the backup process with NMM.

2.The backup command is sent to the SQL Server. This interaction is performed through the VDI API.

3.NMM reads the data from SQL Server and stores it on the NetWorker server.

4.NMM sends the backup status to the NetWorker Server and notifies the user when the backup is complete.

16 Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

Overview

Figure 1 VDI backup process between NMM and SQL Server

The following figure describes the recovery process that takes place between NMM and the SQL Server using VDI:

1.The user starts the recovery process with NMM.

2.The restore command is sent to the SQL Server. This interaction is performed through the VDI API.

3.NMM reads the data from the NetWorker server and passes the data to the SQL Server using VDI.

4.NMM notifies the user when the recovery is complete.

Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

17

Overview

Figure 2 VDI recovery process between NMM and SQL Server

SQL Server backup and restore workflow

This section provides information about SQL Server backup and restore workflows.

Traditional backup workflow (stand-alone backup over a storage node)

During a backup in a traditional environment, processes interact between the NetWorker client (that is, NMM), the NetWorker server, and the SQL Server.

During a traditional backup, the backup starts from the nsrsqlsv program, which is started by using one of the following:

•Command prompt

•NMM plug-in for the Microsoft SQL Server Management Studio The following process occurs in a traditional backup:

1.The nsrd program starts the backup nsrworkflow on the NetWorker server.

2.The nsrworkflow starts the savegrp program.

3.The savegrp program runs the NMM backup command (nsrsqlsv) on the client instead of performing a standard NetWorker save.

4.The nsrsqlsv program passes the backup data from SQL Server to the NetWorker server through an X-Open Backup Services application programming interface (XBSA).

The NetWorker server schedules and performs all storage management tasks.

The following figure shows the traditional backup workflow.

18 Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

Overview

Figure 3 Traditional backup workflow

The NetWorker Administration Guide provides information about the NetWorker services and operations.

Federated backup workflow (AlwaysOn Availability Group configuration)

During a federated backup, processes interact between the NetWorker client (that is, NMM), the NetWorker server, and the SQL Server.

Note: NMM supports federated backups of any SQL Server with support for AlwaysOn Availability Groups (SQL Server 2012 and later).

The backup starts from the nsrsqlsv program, which is started by using one of the following:

•Command prompt

•NMM plug-in for the Microsoft SQL Server Management Studio

The following process occurs during a federated backup:

1.The NetWorker server starts the nsrsqlsv program in the active node of the Windows cluster (called the coordinator process).

2.The coordinator process queries the SQL Server and detects the Backup Preference and priority from the Availability group, and starts the worker process on the detected preferred node.

3.The backup is configured with and stored under the Windows cluster name on the Availability Group.

The following figure shows the federated backup workflow.

Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

19

Overview

Figure 4 Federated backup workflow

Note: The coordinator process always goes through the nsrjobd service to start the worker process on the secondary nodes to ensure that the NetWorker server and nsrjobd can communicate with secondary nodes.

Recovery workflow

During a traditional recovery operation that uses a storage node without the Client Direct feature, process interactions occur between the NetWorker client (that is, NMM), the NetWorker server, and the SQL Server.

The following process occurs in a traditional recovery operation:

1.The nsrsqlrc program starts the recovery.

2.The NetWorker XBSA API translates the object names that NMM requests into a format that NetWorker understands and forwards the translated object names to the NetWorker server nsrd service.

3.The nsrmmd media service, contacts the nsrmmdbd service to search the NetWorker server’s media database for the volumes that contain the requested objects.

4.After the media is mounted, the nsrmmd program sends the data through the NetWorker XBSA API to nsrsqlrc, which then sends data to the SQL Server.

The following figure shows the traditional recovery workflow.

20 Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

Overview

Figure 5 Traditional recovery workflow

NMM recovery interactions with the SQL Server

When a SQL instance-level recovery occurs, NMM stops and starts the SQL Server and dependent services.

When you want to recover the SQL Server system database types like master and msdb, the nsrsqlrc program automatically stops and restarts the SQL Server services appropriately, as follows:

1.Before the recovery process begins, NMM stops the SQL Server and other dependent services.

When the SQL Server Analysis Services (SSAS) is running, it might use the only available database connection if the SQL Server is in a single-user mode. Stop the Analysis Services before restoring the master database.

2.NMM starts the SQL Server in single-user mode.

3.NMM performs the recovery.

4.After the recovery process finishes, NMM waits for the SQL Server to shut down.

5.For stand-alone and cluster environments, NMM restarts the SSAS.

When you recover a master database, there can be timing issues related to stopping and starting of services. If you are recovering a master database, it is recommended that before you start the recovery, you manually stop all SQL Server services except for the SQL Server.

Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

21

Overview

SQL Server cluster environments

NMM can back up or restore data from an SQL server that is running as a SQL virtual server in a Windows Server Failover Cluster (WSFC). The SQL Server instance that is running in this configuration is called Failover Cluster Instance (FCI).

NMM requires the SQL virtual server name so it can perform the following tasks:

•Connect to the SQL Server instance.

•Accept data from or deliver data to the SQL Server in the cluster, and to initialize the SQL Server VDI.

•Create entries in the NetWorker client file index.

NMM creates index entries under the virtual server name in the NetWorker client file index.

Note: Backup and restore of SQL Server data in a cluster with NMM requires Cluster Client Connection licenses on the NetWorker server host. A separate Cluster Client Connection license is required for each node in the cluster.

NMM detects SQL Server instances

NMM automatically detects all the SQL Servers in a WSFC, including the SQL virtual servers. NMM detects the SQL Servers only on the active nodes, whenever the following conditions occur:

•You open the NMM plug-in for the Microsoft SQL Server Management Studio.

•You start a backup or restore operation.

Named instances in failover cluster configurations

NMM provides failover cluster support by using the multiple instance features provided in the SQL Server. In a failover configuration, the SQL virtual servers run as either default instances or as named instances. Only one default instance of a SQL virtual server can be installed. Additional SQL virtual servers might be installed as named instances, where each instance name must be unique within the cluster.

Multiple named instances are supported as SQL virtual servers in a cluster configuration. The number of instances that are supported depends on the SQL Server version that is used in the setup. The NetWorker Module for Microsoft Installation Guide and the Microsoft SQL Server documentation provide more information.

Each named SQL virtual server instance has the following qualities:

•A unique IP address, network name, and instance name

•Datafile files that are installed on a clustered drive that belongs to the same cluster group as the associated virtual server for that named instance

Active and passive cluster configurations

When the NMM SSMS plug-in GUI is started on the active node, NMM automatically uses the SQL virtual server as the client name. The client name is used for reading or writing to the NetWorker media database and client file index. The NMM SSMS plug-in GUI can be used only on the active node.

When you use the command line interface, use the nsrsqlsv -A <SQL virtual server> backup command when the target database is a SQL cluster.

The NMM SSMS plug-in GUI shows all active clusters that are running on the host in the SQl Server Instance list.

22 Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

Overview

If each SQL virtual server is running on a different physical node in the cluster, an active or active cluster configuration exists, and no failover occurs, NMM automatically communicates with the SQL virtual server that is running on the same physical node.

Multi-subnet cluster configurations

NMM supports SQL AlwaysOn Availability Groups in a multi-subnet cluster. You can back up and restore the AlwaysOn Availability Group data in a multi-subnet cluster by using the Availability Group Listener client resource. The following sections provide information:

•Availability group listeners on page 39

•Clusterless availability group listeners on page 39

•Configuring a client resource on page 80

SQL Server backups

This section introduces NMM as a tool to back up an SQL Server.

Types of supported backups

NMM supports manual and scheduled backups of SQL Server data.

Manual (traditional) backup

NMM supports traditional backups, which are often referred to as manual backups. A traditional backup of SQL data can be performed at any time and is independent of any scheduled backup.

NMM supports traditional backup of the following items:

•Database

•File

•File group

•Filestream data

•Transaction log

Also, NMM for SQL Server supports file group differential, file differential, and copy-only backups.

A file group differential backup can reduce both media requirements and recovery time because data is stored across more than one disk or disk partition, so recovery time is reduced. A differential backup can substitute for any logs-only backups performed between the full and differential backups. A full backup must be performed first.

Note: NMM can recover a full backup of SQL Server data (including files and file groups) that were created with NMSQL. However, NMM cannot recover snapshot (PowerSnap based) backups that were created with NMSQL.

Scheduled backup

The most reliable way of protecting SQL data is to ensure that backups of the SQL Server are run at regular intervals, that is, scheduled backups. Scheduled backups ensure that all SQL Server data, including the NetWorker server’s client indexes and bootstrap file, is automatically saved. If a disaster occurs, the client indexes and bootstrap file are vital for restoring data to the SQL Server.

Backup limitations

Due to SQL Server behavior, the following limitations apply to SQL Server backups:

•For simple recovery model databases, only full backups (including copy-only full backups) are supported.

Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

23

Overview

•For full backups of a secondary replica, SQL Server supports only copy-only backups.

Backup levels

Learn about the backup levels that NMM supports

Note: NetWorker 8.2.x defines backup levels differently than NMM 19.2. If you are using NetWorker 8.2.3 or 8.2.4 server with the NMM 19.2 client, refer to the NetWorker Module for Microsoft for SQL VDI User Guide version 8.2 SP1 for information about backup levels.

NMM supports the following three levels of backup.

Full backups

Entire database backup, including all file groups or files in the database.

Cumulative incremental backups

A cumulative incremental backup captures all changes since the last full backup.

Logs-only backups

A logs-only backup that corresponds to a SQL Server transaction log backup. A log file backup cannot be used to recover a database.

A log file backup is used after a database recovery to restore the database to the point of the original failure.

The logs only backup option appears in the NetWorker Management Console. When using the command prompt to perform a logs-only backup, use the txnlogs -l command.

Note: When a logs-only backup is taken, NMM records two entries in the media database. One entry is the database save set, which lists the backup level as txnlog. The second entry is a cover set entry, which lists the backup level as incr. Both entries are required for cloning and restore operations.

Copy-only backups

You can take a manual SQL Server copy-only backup at any time without affecting the backup schedule or log chain. You can perform copy-only backups at the full or logs-only backup level.

Copy-only backups are not promoted to a different backup level, which allows other backups to run without disrupting the backup chain.

Copy-only full backups are not considered level full backups during promotion. Having a copy-only full backup does not prevent subsequent backups from being promoted to a full backup.

Setting backup levels

NMM enables you to specify backup levels to logs only, cumulative incremental, and full.

The availability of a backup level depends on the type of data selected for backup and any SQL Server settings on those objects, as listed in the following table.

Note: NetWorker 8.2.x defines backup levels differently than NMM 19.2. If you are using NetWorker 8.2.3 or 8.2.4 server with the NMM 19.2 client, refer to the NetWorker Module for Microsoft for SQL VDI User Guide version 8.2 SP1 for information about backup levels.

24 Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

Overview

Table 3 Backup levels for SQL Server data

SQL Server data objects	Supported SQL Server backup levels
	Full	Cumulative incremental	Logs only
All databases of SQL	Yes	Yes	Yes
default or named instances
Specified databases	Yes	Yes	Yes
All filegroups in specified	Yes	Yes	Not applicable
databases
Filestream data in specified	Yes	Yes	Yes
databases
Specified filegroups in	Yes	Yes	Not applicable
specified database
Specified files in filegroups	Yes	Yes	Not applicable
in specified databases

When you perform a logs-only backup for SQL Server data objects, ensure that the SQL Server database options are correctly configured. The Microsoft SQL Server documentation provides more information. Individual items are subject to promotion.

Example strategies for backing up SQL Server data

This section describes example strategies for backing up SQL Server data.

Example 1 Backup strategy one

If the SQL Server manages a significant amount of data, schedule a backup of the databases every 1 to 2 weeks, as shown in the following table.

Table 4 Full backup every 1 to 2 weeks

Fri	Sat	Sun	Mon	Tues	Wed	Thurs
Full	Logs-only	Logs-only	Logs-only	Logs-only	Cumulative	Logs-only
					incremental
Logs-only	Logs-only	Logs-only	Cumulative	Logs-only	Logs-only	Logs-only
			incremental
Full	Repeat

Example 2 Backup strategy two

Another backup strategy is to schedule logs-only backups on several successive days immediately following a full backup, as shown in the following table. This schedule backs up all data that has changed since the previous logs-only backup.

A level 1 cumulative incremental backup can also be scheduled after several days of logs-only backups, as shown in the following table. This schedule backs up all data since the previous full backup.

Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

25

Overview

Example 2 Backup strategy two (continued)

NOTICE If a database is read-only, perform a full backup of the database. A readonly database cannot be restored from an existing transaction log backup.

Table 5 Logs-only backup after a full backup

Fri	Sat	Sun	Mon	Tues	Wed	Thurs
Full	Logs-only	Logs-only	Logs-only	Cumulative	Logs-only	Logs-only
				incremental
Repeat

Using backup levels

Because it is not practical or efficient to run full backups every day, you can specify different backup levels for scheduled backups. Limiting the frequency of full backups can decrease server load while ensuring complete data protection.

Differences between backup levels

The following table outlines the differences between backup levels.

Table 6 Backup level advantages and disadvantages

	Backup level	Advantages		Disadvantages
	Full	Fastest restore time.		•	Slow backup.
				• Increases load on client, server, and
					network.
				• Uses the most volume space.
	Logs only	•	Faster backup time than a full backup.	•	Slow restore.
		•	Decreases the load on server and uses the	•	Data can spread across multiple volumes.
			least volume of space.	•	Multiple transaction logs can spread
		•	Enables point-in-time restore.		across multiple volumes.
	Cumulative	•	Faster backup time than a full backup.	Generally more time-consuming than a logs-
	incremental			only backup (depending on the backup
		•	Captures all changes since the last full
				schedule strategy).
			backup.

	Combining data objects to create backup levels
	NMM enables the selection of SQL Server data objects in various combinations to create
	scheduled backups of different levels, as shown in the following table.
	Table 7 Creating additional backup levels with data objects
Backup level		Database objects
Full database		To create a level full database backup of the selected databases and their
		transaction log files, select one or more databases.

26 Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

Overview

Table 7 Creating additional backup levels with data objects (continued)

Backup level	Database objects
Full file or file group	To create a level full file or file group backup of the selected files or file
	group, but not their transaction logs, select one or more files or one or
	more filegroups.
Database logs only	To create a database logs only level backup of only the logs only for the
	selected database, select one or more databases.
	The SQL database must be previously configured to enable logs only
	backups.
Database cumulative incremental (level	To create a database level cumulative incremental backup of only the
1)	changes that are made to the selected databases since the last full level
	backup was created, select one or more databases.
File or file group cumulative	To create a file or file group level cumulative incremental backup, select
incremental	one or more files or one or more filegroups. This backup only includes the
	changes that are made to the selected files or filegroups since the last full
	level backup.

Backup promotion

Guidelines for Microsoft SQL Server best practices indicate that a full database backup should be the first step in implementing a restore strategy for a database. In adhering to these guidelines, NMM supports backup level promotion. Backup level promotion is based on data on the NetWorker server and the SQL Server.

Note: NMM does not support backup promotion during copy-only backups.

The following table explains the scenarios which cause backup promotion to occur.

Table 8 Backup level promotion process

Backup item	Requested	Level of	Reason for promotion
	backup level	promotion
Database	Cumulative	Database full	•	A full database backup does not exist.
	incremental
			• A restore was done after the most recent full
				database backup.
			• The last database backup was not performed with
				NMM.
			• The database name is "master" or "msdb."
			• In the case of Always On Availability Groups, the last
				full backup was performed on a different node.
			• Either the mirror partner has a more recent backup
				or its backup status cannot be determined.
	Logs only	Database full	•	A full database backup does not exist.
			• A restore was done after the most recent full
				database backup.

Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

27

Overview

Table 8 Backup level promotion process (continued)

Backup item	Requested	Level of	Reason for promotion
	backup level	promotion
			• The last database backup was not performed with
			NMM.
			• The database name is "master" or "msdb."
			• In the case of Always On Availability Groups, the last
			full backup was performed on a different node.
			• Either the mirror partner has a more recent backup
			or its backup status cannot be determined.
			• The database model changed from simple to full or
			to bulk logged.
			• The database is using the simple restore model.
			• The database is in emergency mode.
File/Filegroup	Full	Database full	• A full database backup does not exist.
			• A restore was done after the most recent full
	Cumulative	Database full
			database backup.
	incremental
			• The last database backup was not performed with
			NMM.
			• The database name is "master" or "msdb."
			• Either the mirror partner has a more recent backup
	Logs only	Database full	or its backup status cannot be determined.
			Logs-only backups of files or filegroups are not
	Logs only	Filegroup/file full	supported.

Toggling backup promotion functionality

You can toggle backup promotion for both scheduled and manual backups using the following tools:

•	Scheduled backup:
	n	In the Client Backup Configuration wizard, select Turn off backup promotion to turn off
		backup promotion.
	n	In the Client Properties dialog box, type the NSR_BACKUP_PROMOTION application
		information variable with a valid value.
•	Manual backup:
	n	At a command prompt, use the BACKUP_PROMOTION flag with a valid value with the
		nsrsqlsv command.
	n	In the NMM SSMS plug-in, on the Options page, select an option from the Backup
		Promotion list.

Note: Consider the following when you disable backup promotion:

•When backing up the 'msbd' and 'master' databases, the backup level is always set to full and the backup promotion setting is ignored.

•The first backup of a database is always set to level full and the backup promotion setting is ignored.

28 Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

Overview

•If you disable backup promotion, data loss may occur if the backup chain is broken. For example, if a transaction logs backup is taken with a third party software or SQL native backup tools in between two NMM logs only backups for the same database, NMM may be unable to restore the database using the logs only backup.

The Manual Backups chapter and the Scheduled Backups chapter provide more information about changing backup promotion settings.

SQL Server recovery

This section introduces NMM as a tool to recover an Microsoft SQL Server.

Types of supported recovery

This section lists the types of supported recovery for SQL Server VDI.

NOTICE NMM supports recovery of a SQL Server 2012 or later database only after the Always On Availability Group replication has been removed for the corresponding database.

The following table lists the types of recovery for SQL Server VDI in NMM.

Table 9 Types of recovery for SQL Server VDI

Type of recovery	When used	Description
Traditional recovery	For data that was backed up by	Data recovery from a traditional
	traditional backup, NMM supports	backup can be performed:
	traditional recovery.	• At any time with NMM.
		• By running NMM recover
		command (nsrsqlrc) from the
		command prompt.
		Traditional recovery operations
		recover files, file groups,
		databases, and transaction log
		backups.
Normal recovery	NMM uses the normal restore type	The normal restore type restores:
	as the default.	• The entire set of data that is
		associated with one or more SQL
		Server backups, including full,
		logs only, and cumulative
		incremental backups.
		• A file, file group, or a database to
		the database originally backed
		up.
		• Level full, level 1 (cumulative
		incremental), and level logs only
		backups in the order required by
		SQL Server.
		NMM can back up and restore
		specified files and file groups.
		Also, a single file group, or
		multiple file groups or files, can

Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide

29

Overview

Table 9 Types of recovery for SQL Server VDI (continued)

Type of recovery	When used	Description
		be restored from a full database
		backup.
Copy recovery	A copy recovery is an operation in	The copy recovery type creates a
	which data is recovered to a SQL	copy of a database by restoring a
	Server host other than the one from	SQL Server database to a new
	which it was backed up. A copy	location, or with a new database
	restore from and to the same SQL	name. The copy recovery type makes
	Server instance can also be done.	it easy to duplicate a database that
		was previously backed up. You can
		only mark a single item for this
		operation. Also, you can copy a
		system database, but you cannot
		overwrite it.
		NMM does not support copy
		recovery of filestream databases.
Flat file recovery	Flat file recovery allows you to	When you perform a flat file
	restore the backup to a file.	recovery, NMM writes the backup to
		files instead of directly to the SQL
		database. This feature allows you to
		restore the recovery files later
		without access to the network by
		using the standard T-SQL file restore
		command.
Granular-level recovery (GLR)	Granular-level recovery allows you to	By using GLR with SQL Server, you
	recover SQL server data at the table	can recover individual tables to the
	level.	production database. This feature
		reduces the space requirements on
		local system storage during a restore
		operation. Depending on the size of
		the content database, GLR may also
		reduce recovery time. Granular-level
		recovery is performed using NMM
		and ItemPoint for Microsoft SQL
		Server.

Recovery modes

To recover a database, you must specify a recovery mode. A recovery mode instructs the SQL Server how to interact with the database after the recovery operation completes. For instance, recovery modes can leave the database in an intermediate state, so additional transaction logs can be applied.

The following table shows how the recovery modes correspond to SQL Server database restore options.

30 Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide