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
Tables
Preface
Chapter 1
7
9
11
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
Chapter 4
Chapter 5
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
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
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
Chapter 7
4 Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide
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
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
Contents
Chapter 8
Chapter 9
Chapter 10
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
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
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
VDI backup process between NMM and SQL Server......................................................... 17
VDI recovery process between NMM and SQL Server.......................................................18
Traditional backup workflow..............................................................................................19
Federated backup workflow..............................................................................................20
Traditional recovery workflow........................................................................................... 21
Message showing DBCC was successful...........................................................................42
Assigning SQL Server privileges........................................................................................45
Adding a user to Windows User Groups............................................................................ 47
The NetWorker window in the NMM SMSS plug-in GUI ...................................................51
NetWorker Backup General page...................................................................................... 57
NetWorker Backup Options page......................................................................................58
NetWorker Backup Monitor page...................................................................................... 61
Starting the Client Configuration wizard........................................................................... 81
Specify Client Information page........................................................................................82
Specify Backup Configuration Type page..........................................................................83
Specify the Backup Options page..................................................................................... 85
Viewing clients in the Protection tab.................................................................................86
Create Policy window....................................................................................................... 94
Creating a workflow for the policy.................................................................................... 95
Specifying action information in the Policy Action wizard.................................................96
Adding a group to a policy from the Protection pane........................................................ 97
Specifying workflow and client in the Create Group window.............................................98
Successful backup messages............................................................................................99
Failed backup messages....................................................................................................99
NetWorker dialog box—Database Restore tab General page.......................................... 104
NetWorker dialog box—Database Restore tab General page for copy restore................ 105
NetWorker dialog box—Database Restore Files/Filegroups page................................... 106
NetWorker dialog box—Database Restore Options page.................................................107
NetWorker dialog box—Database Restore Monitor page.................................................110
NetWorker dialog box—Table Restore General page.......................................................134
NetWorker dialog box—Table Restore Options page...................................................... 135
NetWorker dialog box—Table Restore Monitor page...................................................... 136
Mount system tray menu................................................................................................. 137
Mount Details window......................................................................................................137
SQL Server Properties dialog box....................................................................................163
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
Revision history................................................................................................................. 12
Style conventions.............................................................................................................. 12
Backup levels for SQL Server data....................................................................................25
Full backup every 1 to 2 weeks..........................................................................................25
Logs-only backup after a full backup.................................................................................26
Backup level advantages and disadvantages..................................................................... 26
Creating additional backup levels with data objects.......................................................... 26
Backup level promotion process ....................................................................................... 27
Types of recovery for SQL Server VDI ............................................................................. 29
Recovery modes ............................................................................................................... 31
Supported special characters in database names..............................................................34
Access privileges required for backup and recovery operations........................................ 44
Where to start backup operations.....................................................................................50
Where to start restore operations.....................................................................................50
Command options for nsrsqlsv ......................................................................................... 63
Command syntax for SQL Server data..............................................................................68
Command syntax for names containing a period................................................................ 71
Command syntax for names containing a backslash...........................................................71
Command syntax for names containing a colon................................................................. 71
Tasks for configuring SQL Server VDI backups ................................................................ 79
Backup levels for SQL Server data....................................................................................80
Save sets in a standalone environment..............................................................................87
Save sets in a cluster environment....................................................................................87
Application Information field values...................................................................................88
Save sets...........................................................................................................................91
Application Information field values................................................................................... 91
Advanced restore options................................................................................................108
Command options for nsrsqlrc .........................................................................................112
Command syntax for SQL Server data.............................................................................122
Command syntax for names containing a period..............................................................124
Command syntax for names containing a backslash........................................................ 124
Command syntax for names containing a colon............................................................... 124
ItemPoint for SQL Server requirements.......................................................................... 128
Guidelines for fail-safe striped recovery.......................................................................... 142
Disaster recovery features...............................................................................................146
Rebuilding SQL Server system databases........................................................................148
Rebuilding SQL Server system databases........................................................................149
Rebuilding SQL Server system databases........................................................................150
Rebuilding SQL Server system databases........................................................................ 151
Rebuilding SQL Server system databases........................................................................162
Rebuilding SQL Server system databases........................................................................165
Program and log file names without debug logging enabled.............................................168
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
recovery procedures described in this guide and must be referred to when performing
application-specific tasks. Ensure to download a copy of the
Administration Guide
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:
l
NetWorker software
l
Microsoft Virtual Device Interface (VDI) technology
NetWorker Module for Microsoft Administration Guide
from the Support website at https://support.emc.com before using this
supplements the backup and
NetWorker Module for Microsoft
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:
l
NetWorker Module for Microsoft Release Notes
l
NetWorker Module for Microsoft Administration Guide
l
NetWorker Module for Microsoft Installation Guide
l
NetWorker Module for Microsoft for SQL and SharePoint VSS User Guide
l
NetWorker Module for Microsoft for SQL VDI User Guide
l
NetWorker Module for Microsoft for Exchange VSS User Guide
l
NetWorker Module for Microsoft for Hyper-V User Guide
l
ItemPoint for Microsoft SQL Server User Guide
l
ItemPoint for Microsoft Exchange Server User Guide
l
ItemPoint for Microsoft SharePoint Server User Guide
l
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
Monospace
Used for full titles of publications that are referenced in text.
Used for:
l
System code
l
System output, such as an error message or script
l
Pathnames, file names, file name extensions, prompts, and
syntax
l
Commands and options
12 Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide
Table 2 Style conventions (continued)
Preface
Monospace italic
Monospace bold
[ ] Square brackets enclose optional values.
| Vertical line indicates alternate selections. The vertical line means or
{ } Braces enclose content that the user must specify, such as x, y, or z.
... Ellipses indicate non-essential information that is omitted from the
Used for variables.
Used for user input.
for the alternate selections.
example.
You can use the following resources to find more information about this product, obtain support,
and provide feedback.
Where to find product documentation
l
https://www.dell.com/support
l
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 DPAD.Doc.Feedback@emc.com.
14 Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide
CHAPTER 1
Overview
This chapter includes the following sections:
l
Using NMM with Virtual Device Interface API........................................................................16
l
SQL Server backup and restore workflow..............................................................................18
l
SQL Server cluster environments..........................................................................................22
l
SQL Server backups..............................................................................................................23
l
SQL Server recovery.............................................................................................................29
l
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
Installation Guide
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:
provides details.
NetWorker Module for Microsoft
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
Figure 1 VDI backup process between NMM and SQL Server
Overview
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:
l
Command prompt
l
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
Figure 3 Traditional backup workflow
Overview
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:
l
Command prompt
l
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
Figure 5 Traditional recovery workflow
Overview
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:
l
Connect to the SQL Server instance.
l
Accept data from or deliver data to the SQL Server in the cluster, and to initialize the SQL
Server VDI.
l
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:
l
You open the NMM plug-in for the Microsoft SQL Server Management Studio.
l
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:
l
A unique IP address, network name, and instance name
l
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
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:
l
Availability group listeners on page 39
l
Clusterless availability group listeners on page 39
l
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.
Overview
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:
l
Database
l
File
l
File group
l
Filestream data
l
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:
l
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
Backup levels
l
For full backups of a secondary replica, SQL Server supports only copy-only backups.
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
Microsoft for SQL VDI User Guide
version 8.2 SP1 for information about backup levels.
NetWorker Module for
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:
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.3 or 8.2.4 server with the NMM 19.2 client, refer to the
Microsoft for SQL VDI User Guide
When a logs-only backup is taken, NMM records two entries in the media database. One
NetWorker 8.2.x defines backup levels differently than NMM 19.2. If you are using
NetWorker Module for
version 8.2 SP1 for information about backup levels.
24 Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide
Table 3 Backup levels for SQL Server data
SQL Server data objects Supported SQL Server backup levels
Full Cumulative incremental Logs only
Overview
All databases of SQL
default or named instances
Specified databases Yes Yes Yes
All filegroups in specified
databases
Filestream data in specified
databases
Specified filegroups in
specified database
Specified files in filegroups
in specified databases
Yes Yes Yes
Yes Yes Not applicable
Yes Yes Yes
Yes Yes Not applicable
Yes Yes Not applicable
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
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.
Backup strategy one
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
incremental
Logs-only Logs-only Logs-only Cumulative
incremental
Full Repeat
Example 2 Backup strategy two
Logs-only Logs-only Logs-only
Logs-only
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 read-
only 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
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 Disadvantages
Full Fastest restore time.
Logs only
l
l
l
Backup level advantages and disadvantages
Faster backup time than a full backup.
Decreases the load on server and uses the
least volume of space.
Enables point-in-time restore.
l
Slow backup.
l
Increases load on client, server, and
network.
l
Uses the most volume space.
l
Slow restore.
l
Data can spread across multiple volumes.
l
Multiple transaction logs can spread
across multiple volumes.
Logs-only Logs-only
Cumulative
incremental
l
Faster backup time than a full backup.
l
Captures all changes since the last full
Generally more time-consuming than a logsonly backup (depending on the backup
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
Backup level Database objects
Full database To create a level full database backup of the selected databases and their
26 Dell EMC NetWorker Module for Microsoft for SQL VDI User Guide
Creating additional backup levels with data objects
transaction log files, select one or more databases.
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 (level1)To create a database level cumulative incremental backup of only the
changes that are made to the selected databases since the last full level
backup was created, select one or more databases.
Overview
File or file group cumulative
incremental
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:
The following table explains the scenarios which cause backup promotion to occur.
Table 8
Backup item Requested
backup level
Database Cumulative
incremental
To create a file or file group level cumulative incremental backup, select
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.
NMM does not support backup promotion during copy-only backups.
Backup level promotion process
Level of
promotion
Database full
Reason for promotion
l
A full database backup does not exist.
l
A restore was done after the most recent full
database backup.
l
The last database backup was not performed with
NMM.
l
The database name is "master" or "msdb."
l
In the case of Always On Availability Groups, the last
full backup was performed on a different node.
l
Either the mirror partner has a more recent backup
or its backup status cannot be determined.
Logs only Database full
l
A full database backup does not exist.
l
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
backup level
Level of
promotion
File/Filegroup Full Database full
Cumulative
Database full
incremental
Logs only Database full
Reason for promotion
l
The last database backup was not performed with
NMM.
l
The database name is "master" or "msdb."
l
In the case of Always On Availability Groups, the last
full backup was performed on a different node.
l
Either the mirror partner has a more recent backup
or its backup status cannot be determined.
l
The database model changed from simple to full or
to bulk logged.
l
The database is using the simple restore model.
l
The database is in emergency mode.
l
A full database backup does not exist.
l
A restore was done after the most recent full
database backup.
l
The last database backup was not performed with
NMM.
l
The database name is "master" or "msdb."
l
Either the mirror partner has a more recent backup
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:
l
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.
l
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:
l
When backing up the 'msbd' and 'master' databases, the backup level is always set to full
and the backup promotion setting is ignored.
l
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
l
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
Overview
Type of recovery When used Description
Traditional recovery For data that was backed up by
traditional backup, NMM supports
traditional recovery.
Normal recovery NMM uses the normal restore type
as the default.
Data recovery from a traditional
backup can be performed:
l
At any time with NMM.
l
By running NMM recover
command (nsrsqlrc) from the
command prompt.
Traditional recovery operations
recover files, file groups,
databases, and transaction log
backups.
The normal restore type restores:
l
The entire set of data that is
associated with one or more SQL
Server backups, including full,
logs only, and cumulative
incremental backups.
l
A file, file group, or a database to
the database originally backed
up.
l
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
which data is recovered to a SQL
Server host other than the one from
which it was backed up. A copy
restore from and to the same SQL
Server instance can also be done.
Flat file recovery Flat file recovery allows you to
restore the backup to a file.
Granular-level recovery (GLR) Granular-level recovery allows you to
recover SQL server data at the table
level.
The copy recovery type creates a
copy of a database by restoring a
SQL Server database to a new
location, or with a new database
name. The copy recovery type makes
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.
When you perform a flat 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.
By using GLR with SQL Server, you
can recover individual tables to the
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
Loading...