EMC NetWorker Module for Microsoft for SQL VDI

(1)

EMC

®

NetWorker

®

Module for

Microsoft for SQL VDI

Version 9.0

User Guide

302-001-752

(2)

Published September, 2015

EMC 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. EMC Corporation 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 EMC software described in this publication requires an applicable software license.

EMC², EMC, and the EMC logo are registered trademarks or trademarks of EMC Corporation in the United States and other countries. All other trademarks used herein are the property of their respective owners.

For the most up-to-date regulatory document for your product line, go to EMC Online Support (https://support.emc.com).

EMC Corporation

Hopkinton, Massachusetts 01748-9103

(3)

7 9 11

Introduction 15

Using NMM with Virtual Device Interface API...16

NMM environments backup and recovery... 17

Backup environments... 17

Recovery environments (non-client direct or non-direct file access) ... 20

Cluster environments... 21

Using NMM in a SQL VDI environment... 22

Supported Windows Server and SQL Server versions...22

Migrating from VSS solution to VDI solution for SQL Server data protection... 22

AlwaysOn Availability Group feature of Microsoft SQL Server 2012 or later... 23

SQL Client Direct to AFTD or DD devices...23

Microsoft SQL Server named log marks... 23

Named and default instances of SQL Server... 24

Multi-stream Data Domain Boost...25

Database consistency checks... 25

Improved SQL save group notifications... 27

Microsoft Hybrid Cloud environment... 27

Optional SQL Server log gap detection... 28

Supported special characters in database names for NMM backup and recovery ... 29

Transparent data encryption... 30

Access privileges for backup and recovery...31

SQL Server backups... 32

Types of supported backups... 32

Backup levels...33

Setting backup levels...33

SQL Server recovery...36

Types of supported recovery...36

Recovery modes...37

Recovery time... 38

Recovery window restrictions... 38

The recovery process...39

Error logs for backup and recovery...40

Graphical User Interfaces 41 User interfaces for backup and recovery... 42

NetWorker User for SQL Server GUI... 43

Views...43 Figures Tables Preface Chapter 1 Chapter 2

FIGURES

(8)

(9)

Revision history... 12

Style conventions... 12

Supported special characters in database names... 29

Backup levels in NMM for SQL VDI backups... 33

Backup levels for SQL Server data...33

Full backup every one to two weeks... 34

Logs only backup after a full backup...34

Backup level advantages and disadvantages...34

Creating additional backup levels with data objects... 35

Backup level promotion process ... 36

Types of recovery for SQL Server VDI ... 37

Recovery modes ...38

Program and log file names... 40

Where to initiate backup operations... 42

Where to initiate recovery operations...42

SQL Server storage hierarchy display conventions... 44

Tasks for SQL Server VDI backups ... 63

Backup levels for SQL Server data...64

Save sets...68

Save Sets... 74

Guidelines for fail-safe striped recovery... 122

Command options for nsrsqlsv ... 127

Command options for nsrqlrc ...131

Command options for the nwmssql command... 138

Command syntax for SQL Server data... 139

Command syntax for names containing a period...141

Command syntax for names containing a backslash... 141

Command syntax for names containing a colon... 141

Command syntax for names containing periods, back slashes, and colons...142

Disaster recovery features... 144 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

TABLES

(10)

(11)

Preface

As part of an effort to improve its product lines, EMC periodically releases revisions of its software and hardware. Therefore, some functions described in this document might not be supported by all versions of the software or hardware currently in use. The product release notes provide the most up-to-date information on product features.

Contact your EMC technical support professional if a product does not function correctly or does not function as described in this document.

Note

This document was accurate at publication time. Go to EMC Online Support (https:// support.emc.com) to ensure that you are using the latest version of this document. Purpose

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

Note

The EMC 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 EMC NetWorker Module for Microsoft Administration Guide from EMC Online Support (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:

l EMC NetWorker software

l Microsoft Virtual Device Interface (VDI) technology

(12)

Revision history

The following table presents the revision history of this document.

Table 1 Revision history

Revision Date Description

01 September, 2015 First release of this document for EMC NetWorker Module for Microsoft release 9.0.

CHAPTER 1 Introduction

This chapter includes the following sections:

l Using NMM with Virtual Device Interface API...16

l NMM environments backup and recovery... 17

l Using NMM in a SQL VDI environment... 22

l Access privileges for backup and recovery...31

l SQL Server backups... 32

l SQL Server recovery...36

l Error logs for backup and recovery...40

(16)

Using NMM with Virtual Device Interface API

You can use the EMC® NetWorker® Module for Microsoft (NMM) release 9.0 software to backup and recover SQL Server data. NMM utilizes Virtual Device Interface (VDI), an API provided by Microsoft SQL Server, to integrate with the SQL Server and enable the NetWorker software to back up and recover SQL Server data.

When installing NMM, you have the option of running the System Configuration Checker from the installer. It is recommended that you run the System Configuration Checker to ensure that your setup is properly 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 installing NMM VDI because SQL snapshot data backed up by using NMSQL cannot be recovered by NMM VDI.

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

1. The backup process is started using 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.

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:

(17)

1. The recovery process is started using 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 stores the information on the SQL server.

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

Figure 2 VDI recovery process between NMM and SQL Server

NMM environments backup and recovery

This section provides information about backup, recovery, and cluster environments.

Backup environments

This section provides details about the various NMM environments for SQL Server backups using VDI.

Traditional backup environment (non-federated, non-client direct, or non-direct file access)

The figure in this section describes the process interactions between the NetWorker client (that is NMM), the NetWorker server, and the SQL Server during a traditional backup.

Introduction

(18)

Figure 3 Traditional backup command and data flow

The following process occurs in a traditional backup:

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

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

3. 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 performs all scheduling and storage management tasks.

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

Federated backup environment (AlwaysOn Availability Group configuration)

The figure in this section describes an overview of the process interactions between the NetWorker client (that is NMM), the NetWorker server, and the SQL Server during a federated backup.

Note

Federated backups for AlwaysOn Availability Group configuration are only available for SQL Server 2012 or later.

(19)

Figure 4 Federated backup command and data flow

During a federated backup, the savegroup command starts and creates the process in the active node of the Windows cluster. When the backup is started from the NetWorker server, a process called the master/co-ordination process computes and detects the Backup Preference and priority from the Availability group, and starts the slave backup process in detected preferred node.

Introduction

(20)

Recovery environments (non-client direct or non-direct file access)

The figure in this section describes the functional relationship between the NetWorker client (that is NMM), the NetWorker server, and the SQL Server during a traditional recovery operation.

Figure 5 Traditional recovery command and data flow

The following process occurs in a traditional recovery operation: 1. The nsrsqlrc command starts the recovery.

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

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

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

NMM recovery interactions with the SQL Server

NMM stops and starts the SQL Server and dependent services when a SQL instance-level recovery takes place.

(21)

When recovering the SQL Server system database 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 recovering a master database, there can be timing issues related to stopping and starting of services. Manually stop all SQL Server services, except for SQL Server itself, before initiating the recovery.

Cluster environments

NMM can back up or recover data exported by a SQL Server that is running as a SQL virtual server in an WSFC cluster.

NMM uses the SQL virtual server name to perform the following tasks:

l Connect to the appropriate SQL Server instance.

l Initialize the SQL Server VDI to accept data from or deliver data to the appropriate

SQL Server in the cluster.

l Create entries in the NetWorker client file index.

The NetWorker media database or client file index is indexed according to the client that performs a particular backup. NMM creates index entries under the virtual server name in the NetWorker client file index.

Note

Backup and recovery of SQL Server data in a cluster, by using 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.

The NetWorker Power Edition includes two cluster client licenses of the same platform type. When a cluster contains more than two nodes, additional Cluster Client Connection licenses are required. The NetWorker Workgroup Edition and the NetWorker NetWork Edition can back up cluster nodes only if Cluster Client Connection licenses have been added to the NetWorker server.

How NMM detects SQL Server instances

NMM automatically detects all the SQL Servers in a WSFC cluster, including the SQL virtual servers.

NMM detects the SQL Servers only on the active nodes, whenever:

l The NetWorker User for SQL Server program is started. l The Select SQL Instance option is selected.

l A backup or recovery is started.

Named instances in failover cluster configurations

Introduction

(22)

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. One default instance of a SQL virtual server might 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 supported depends on the SQL Server version being used in your setup. The NetWorker Module for Microsoft Installation Guide and 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 Data files installed on a clustered drive that belongs to the same cluster group as the

virtual server.

Active and passive cluster configurations

When the NetWorker User for SQL Server GUI is started on the active node, NMM automatically uses the SQL virtual server as the client name for reading or writing to the NetWorker media database and client file index.

If the SQL Server fails over to the secondary cluster node and the NetWorker User for SQL Server program is started on the secondary node, NMM automatically uses the SQL virtual server name.

If there is one SQL virtual server running on each physical node in the cluster, an active or active cluster configuration exists, and the following occurs:

l NMM automatically communicates with the SQL virtual server running on the same

physical node if no failover has occurred and each SQL virtual server is running on a different physical node.

l The Select SQL Instance option from the Operation menu is disabled.

However, if one of the physical cluster nodes goes offline (for example, if a failover occurs) and both SQL virtual servers are running on the same physical cluster node when the NetWorker User for SQL Server GUI is started, the Select SQL Instance dialog box appears. The Select SQL Instance dialog box lists the SQL Server instances. After

selecting an instance from the Select SQL Instance dialog box, NMM attempts to validate the instance as a NetWorker client.

Using NMM in a SQL VDI environment

This section provides information about using NMM in a SQL VDI environment.

Supported Windows Server and SQL Server versions

The NetWorker Online Software Compatibility Guide on the EMC Online Support website lists the most up-to-date information about supported Windows Server and SQL Server versions.

Migrating from VSS solution to VDI solution for SQL Server data protection

Perform the required steps if you are using an NMM version earlier than NMM 2.4 to recover SQL Server data with VSS technology, and would now like to use NMM 8.2 or later to back up and recover SQL Server data with VDI technology.

l Specify the Backup Command as nsrsqlsv. For SQL virtual server in a cluster

environment, specify nsrsqlsv -A SQL virtual server.

(23)

l Keep the Application Information field blank.

l Change the save set to _MSSQL: for SQL default instance level backup or

MSSQL:dbname for database level backup on a SQL default instance.

AlwaysOn Availability Group feature of Microsoft SQL Server 2012 or later

NMM supports the SQL Server 2012 or later AlwaysOn feature, which allows multiple replicas of a database. Each set of availability database is hosted by an availability replica.

Two types of availability replicas exist:

l A single primary replica, which hosts the primary databases.

l One to four secondary replicas for SQL Server 2012 and one to eight secondary

replicas for SQL Server 2014, each of which hosts a set of secondary databases and serves as a potential failover targets for the availability group.

The secondary replicas can be configured to be in either synchronous or asynchronous mode.

Note

To perform backups of secondary replicas by using NMM, set the Readable Secondary option of SQL Server 2012 or later AlwaysOn configuration to “Yes.” Enable this option for the primary and the secondary replicas, because during failover of an availability group, the secondary replicas become primary and vice versa.

NMM supports the Availability Group functionality that SQL Server 2012 or later has introduced with AlwaysOn. An availability Group is a logical group of databases that have the AlwaysOn capability. The Availability Group is failed over to other nodes as a group, that is, all the databases that are part of the Availability group are failed over together during failure or manual failover.

The Microsoft website describes the AlwaysOn Availability Group functionality in SQL Server 2012 or later, and provides detailed information about how to configure your setup to utilize this support.

When using SQL Server 2012 or later databases that are configured with AlwaysOn, break the replication before recovering the databases. You can use either the Microsoft SQL Server Management Studio GUI or the T-SQL query window for this task.

SQL Client Direct to AFTD or DD devices

The NetWorker client software enables clients with network access to Advanced File Type Device (AFTD) or Data Domain devices to send their backup data directly to the devices, bypassing the NetWorker storage node.

The Client Direct feature is enabled by default, but can be disabled on each client by clearing the Client Direct attribute. When a Client Direct backup is not performed, a traditional storage node backup is performed instead.

The nsrsqlsv.raw backup log displays details about the Client Direct activity for the SQL Server.

Microsoft SQL Server named log marks

Microsoft SQL Server enables enhanced point-in-time restore operations by allowing named log marks to be specified during transaction creation. Database applications create named log marks when transactions are performed. The marks enable access to specific transaction points in a database transaction log backup. NMM restores to the

Introduction

(24)

beginning or end of a named log mark during a database restore. Restoring data by using named log marks is an improvement over point-in-time restore. The time associated with restoring to a specific transaction can be more accurately determined.

When a named log mark is created in the SQL Server database, the log mark time is saved to the millisecond. However, NetWorker’s time format, which is used to specify point-in-time restore, only supports granularity to the second. If named log marks with duplicate names are created within a second of each other, NMM restores to the most recently named log mark.

Managing SQL Server database transaction logs

NMM provides implicit and explicit methods for managing SQL Server database transaction logs:

l Implicit management — Implicit management uses log backups to manage log space.

This management can occur when:

n A backup schedule is implemented that includes logs only level backups. n You run the nsrsqlsv command with the -l txnlog option.

l Explicit management — Explicit management can be performed by using the

following options:

n Specify the nsrsqlsv command on the command line, with or without the -T option

(Truncate Only) for SQL Server 2005 or the -G option (No Log). Both the -T and -G command options are compatible with level full and level 1 (incremental cumulative) backups.

If the -T or -G command options are specified, then the SQL Server truncates the transaction log before the backup completes.

The transaction log truncation after the backup takes place when NMM

determines that the database does not support transaction log backups. The SQL Server databases that use the simple recovery model do not use transaction log backups.

n Select the Truncate content before performing backup option in the Database

Properties page. Prevent log overflow

In Windows, prevent database logs from overflowing available log space by creating an alert in the SQL Server Performance Monitor that forces a logs only backup when the database’s log space reaches a certain capacity (for example, 80% full). An alert is a user-defined response to a SQL Server event. A logs only backup truncates the logs and clears disk space.

Named and default instances of SQL Server

NMM supports backup and recovery of named and default instances.

NMM supports recovery from the default instance or named instances of SQL Server, using a copy restore to any instance of a SQL Server. This includes recovery operations when the destination server is different from the source.

Each named instance has a unique instance name in the following format:

computerName\instanceName

where:

l computerName is the network name of the computer. l instanceNameis the logical name of the named instance.

(25)

Note

When naming a SQL database or an instance, select names that are unique. Examples of poor database name choices are: the name SQL Server uses to identify itself (MSSQL:) and names of installed SQL instances you have installed

The syntax for specifying a SQL standalone named instance of SQL Server at a command prompt is as follows:

MSSQL$Standalone_Named_Instance:[dbName ...][.fgName ...][.fileName ...] An entry of MSSQL: for the Save Set attribute during the Client resource configuration

yields a backup of all databases on the SQL Server host.

When running multiple instances, the nsrsqlsv and nsrsqlrc commands only support specification of one instance at a time. If save sets for more than one instance are specified, the backup or restore operation fails.

Index entries for stand-alone named instances are created by using the local host on which the instance is running. Index entries for clustered named instances are created with the SQL virtual server name. To differentiate backups for the default instance and named instances, the index name has been extended to logically partition the index for a client.

All running named instances are maintained in the client file index directory, excluding clustered instances and the default instance. This named instance directory is created at the end of each traditional backup. Running nsrinfo after backups verifies the existence of this directory, for example:

%SystemDrive% nsrinfo -V -L -n mssql <client name>

Multi-stream Data Domain Boost

NMM supports multi-stream backups for a SQL Server to a Data Domain device. This support leverages the Data Domain Boost feature. SQL Server multi-stream backups over Data Domain Boost enhances the performance by running the backups three times faster.

Database consistency checks

NMM provides the option for database consistency checks (DBCC) before backup is performed.

A DBCC performs a thorough check of the entire database. It examines all tables in the database to find out whether index and data pages are correctly linked and indexes are in proper-sorted order. It also checks that all pointers are consistent and that the data information on each page, and page offsets are reasonable. It enables the early

recognition of problems and thus prevents problem escalation and possible loss of data.

Note

DBCC can be performed for database-level, multiple database-level, and instance-level save sets on cluster and standalone configurations. For federated configurations, DBCC can be performed for database-level save set, but DBCC cannot be performed for Always On Group level backups.

You can run DBCC from either of the following options:

l For manual backups: From the EMC NetWorker page of the Microsoft SQL Server

Management Studio. Performing manual backups from the Microsoft SQL Server Management Studio GUI on page 51 provides more information.

Introduction

(26)

l For manual backups: From the NetWorker User for SQL Server GUI:

n Select the Perform DBCC consistency checks option in the Database Properties

page by right-clicking a database in the Backup page.

n Select the Perform DBCC consistency checks option in the General tab in the

Backup options window.

Performing manual backups from the NetWorker User for SQL Server GUI on page 54 provides more information.

l For scheduled and federated backups: From the NetWorker Management Console (on

the NetWorker Server):

n Type _{NSR_CONSISTENCY_CHECKS=<database1,datbase2>} in the Application

Information field, where with the database names in an instance for which you want to run a consistency check are added along with the variable.

For example: NSR_CONSISTENCY_CHECKS=testdb3,CopyOfDB010,test4

Ensure that there are no spaces between the database names. For example, using

NSR_CONSISTENCY_CHECKS=testdb3, CopyOfDB010,test4 is incorrect.

n Add _{NSR_CONSISTENCY_CHECKS=ALL} in the Application Information field, to

specify all the databases in an instance for which you want to run a consistency check.

l From the Client Backup Configuration wizard, select the Perform DBCC consistency

check option when configuring the client resource. Either select All to perform DBCC for all the databases in an instance, or specify the databases for which you want to run the DBCC checks.

l From the Command Line, in addition to various other backup command options with

the nsrsqlsv command:

n Type the command option -j testdb3,CopyOfDB010,test4 to perform DBCC checks

for databases in an instance.

Where testdb3, CopyOfDB010, and test4 are the names of the databases in the instance. Ensure that there are no spaces between the database names. For example, using testdb3, CopyOfDB010,test4 is incorrect.

n Type the command option -j ALL to perform DBCC checks for all the databases in

an instance.

If the DBCC runs successfully for a database, a message appears in the NMM logs or backup output page.

Figure 6 Message showing DBCC was successful

(27)

If DBCC for a database fails, the failure status appears as part of the policy notification and the backup of the database is skipped. The DBCC failure is reported in the NMM logs or backup output page.

Improved SQL save group notifications

A notification provides information about events that occur in the NetWorker and NMM environments. By default, the NetWorker server sends notifications to log files that are located in the install_dir\applogs directory.

In previous NMM releases, when SQL Server instance-level backups of the MSSQL: and MSSQL$<INSTNACE NAME>: were performed, the complete information of backup success or failure was not displayed in the NetWorker Server save group notifications.

NMM provides support for SQL save group notifications that list the backup success or failure status, with important details about a failure that has occurred.

The following figure shows an example of SQL save group notification.

Figure 7 Example of SQL save group notification

Microsoft Hybrid Cloud environment

NMM supports SQL Server 2014 VDI backups and recoveries on Microsoft Hybrid Cloud environment. Both Private and Hybrid cloud are supported for SQL Server 2014 for standalone and federated environments. The NetWorker server must be deployed only in the On Premise site for this support.

The On-Premise backup workflow is the same as that for SQL Server 2012 or later AlwaysOn Availability Group.

Introduction

(28)

Optional SQL Server log gap detection

NMM 9.0 provides support for SQL Server log gap detection during Logs Only backup to improve the performance of the SQL database backups. This support is not available for Full or Differential backups.

Prior to NMM 9.0, the software by default checked the SQL Server history and the NetWorker Sever backup history for the database selected during backup to find the log gap from a previous logs only backup.

In NMM 8.5, the NSR_SKIP_LOGGAP_DETECTION application information variable is used

to control this functionality. When the NSR_SKIP_LOGGAP_DETECTION variable is set to

"TRUE" the log-gap detection functionality is not performed during the logs only backup

process and this improves the backup performance because the extra overload of detecting the log gap is overridden.

Log-gap detection functionality is skipped for every database in the SQL instance when the NSR_SKIP_LOGGAP_DETECTION = TRUE application information variable is specified along with the SQL instance level save set MSSQL: or MSSQL$<Instance Name>:.

Similarly, the log-gap detection functionality is skipped only for a few specified databases, when the NSR_SKIP_LOGGAP_DETECTION = TRUE application flag is

specified along with the database level save set MSSQL:< database name> or MSSQL

$<instance name>: <database name>.

The log-gap detection functionality is performed if the NSR_SKIP_LOGGAP_DETECTION

application information variable is set to FALSE or if the NSR_SKIP_LOGGAP_DETECTION

application information variable is not specified during a client resource configuration. The log-gap detection setting can be changed at a later point of time.

The backup module log NSRSQLSV.RAW file logs a message if the application flag is configured during client resource configuration.

To skip log-gap detection functionality when using the Client Backup Configuration wizard or from the Backup tab of the SQL Server Management Studio, select the Turn off log gap detection option in the Backup Options page.

The Manual Backups chapter and the Scheduled Backups chapter provide the step for enabling this support.

Note

Perform a full backup before using the NSR_SKIP_LOGGAP_DETECTION attribute with

incremental backup. The SQL Server DBA must perform the full backup.

(29)

Supported special characters in database names for NMM backup and recovery

The following table lists the special characters that may be used in SQL Server database names when performing backup and recovery in standalone, cluster, AlwaysOn

Availability Group, and federated configurations.

Table 3 Supported special characters in database names

Special characters Standalone and cluster configurations

(Database level backup and recovery; Instance level backup and recovery

AlwaysOn Availability Group and federated configurations (AlwaysOn Group level backup and recovery)

~ Tilde Successful Successful

- Hyphen Successful Successful

! Exclamation mark Successful Successful

{ Open curly

bracket

Successful Successful

% Procenttecken Successful Successful

} Close curly bracket

Successful Successful

) Close parenthesis Successful Successful

( Open parenthesis Successful Successful

` Accent grave Successful Successful

@ At the rate Successful Successful

# Hash Successful Fails

Note

Hash is a Availability Group identifier.

_ Underscore Successful Successful

& Ampersand Successful Successful

^ Caret Successful Successful

. Period Successful

Note

Database level backup is successful when performed from the Networker User for SQL GUI. However, back up fails when performed from the EMC NetWorker Backup tab in the SSMS GUI.

Fails

\ Backslash Successful Fails

Introduction

(30)

Table 3 Supported special characters in database names (continued)

Special characters Standalone and cluster configurations

(Database level backup and recovery; Instance level backup and recovery

AlwaysOn Availability Group and federated configurations (AlwaysOn Group level backup and recovery)

Note

Database level backup is successful when performed from the Networker User for SQL GUI. However, back up fails when performed from the EMC NetWorker Backup tab in the SSMS GUI.

' Apostrophe Successful

Note

Backup and recovery are successful but warnings are displayed when performing recovery.

Successful

Note

Backup and recovery are successful but warnings are displayed when performing recovery. $ Dollar Fails Note Dollar is a SQL instance identifier. Fails Note Dollar is a SQL instance identifier. : Colon Fails Note

Colon is a database name identifier.

Fails

Note

Colon is a database name identifier.

Transparent data encryption

Transparent data encryption (TDE) is a feature that performs real-time I/O encryption and decryption of the data and log files. TDE uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. When using this feature, make sure that the certificate and private key are backed up with the encrypted data

Microsoft SQL Server 2008 and later introduces the TDE database-level encryption feature. This feature is designed to provide protection for the entire database at rest, without affecting existing applications. NMM supports the encryption of SQL data at the cell-level, at the full database-level by using TDE, or the file-level encryption options provided by Windows.

(31)

The Microsoft SQL Server product documentation provides more information about TDE, enabling data encryption, and protecting your encryption keys.

Note

When enabling TDE, back up the certificate and the private key associated with the certificate. If the certificate becomes unavailable or if the database is restored on another server, backups of both the certificate and the private key must be available to open the database.

Access privileges for backup and recovery

This section lists the privileges that must be taken care of before performing backup and recovery.

l Grant the Windows logon account that the NMM backup process uses to connect to

SQL Server the following roles:

n SQL Server system administrator (sysadmin) role to issue the T-SQL BACKUP

command query.

n SQL Server sysadmin role to open a shared memory handle when initializing the

VDI.

l Ensure that the NMM administrator is a member of the: n Local backup operators group.

n Local administrators group.

l Windows Server 2008 introduced User Access Control, which causes processes to run

as a standard user even if part of the administrator’s group. NMM processes account for this change.

l Before performing a SQL Server 2012 or later backup, ensure that for Windows Server

2008 R2 the User Account Control is disabled for administrators. Perform these steps:

1. Open the Local Security Policy (secpol.msc) on the Windows 2008 R2 client. 2. Go to Local Policies > Security Options.

3. Change User Account Control: Behavior of the elevation prompt for administrators in Admin Approval Mode to Elevate without prompting.

4. Disable User Account Control: Run all administrators in Admin Approval Mode. 5. Restart the machine.

The esg114125 on the EMC Online Support (http://support.emc.com) provides details.

l (For SQL Server 2012 and later) In the SQL Server Management Studio > Security >

Login > Login Properties page, provide the SQL Server system dbcreator, public, and sysadmin permissions to the Windows logon account. Provide these

permissions to the NTAUTHORITY\SYSTEM user.

Introduction

(32)

Figure 8 Granting permissions to view subcomponents in the SQL Management Studio

l When installing NMM, you are required to log in with Administrator privileges.

However, if you are logged in as a Domain User, provide the Run as Administrator

privilege before using NMM.

SQL Server backups

This section provides an introduction about backups of SQL Server by using NMM.

Types of supported backups

NMM supports manual, scheduled, and federated backups of the SQL Server.

l Manual or traditional backup—NMM provides support for traditional backups.

Traditional backups 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:

n Database n File n Filegroup n Filestream n Transaction log

In addition to creating full backups of file or filegroup, the SQL Server supports the creation of filegroup differential and file differential backups.

A filegroup differential backup can actually reduce both media requirements and recovery time. The data can be stored across more than one disk or disk partition, and recovery time is reduced. A differential can substitute for any log backups performed between the full and differential backups. A full backup must be performed first.

Note

A full backup of SQL Server data (including files and filegroups) created by using NMSQL can be recovered by using NMM. However, NMM cannot recover snapshot (PowerSnap based) backup created by using NMSQL.

l 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, setting up scheduled backups. Scheduled backups ensure that all SQL Server data is automatically saved, including the NetWorker server’s client indexes and bootstrap file. The client indexes

(33)

and bootstrap file are vital for restoring data to the SQL Server in the event of a disaster.

l Federated backup—NMM provides support for SQL Server 2012 or later federated

backup functionality for SQL Server 2012 AlwaysOn databases.

Backup levels

NMM supports three levels of backup.

The following table describes the SQL Server VDI backup levels.

Table 4 Backup levels in NMM for SQL VDI backups

Backup levels Description

Full Entire database backup, including all filegroups or files in the database. Logs only backup A logs only backup, corresponds to a SQL Server transaction log backup.

A log file backup by itself 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.

Note

The logs only backup option appears in NMC. When using the Command Line to perform logs only backup, use the txnlogs command.

Cumulative incremental

Cumulative incremental captures all changes since the last full backup.

Setting backup levels

NMM enables you to specify backup levels to full, cumulative incremental, and logs only. 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.

Table 5 Backup levels for SQL Server data

SQL Server data objects Supported SQL Server versions

Full Cumulative incremental Logs only

All databases of SQL default or named instances yes yes yes

Specified databases yes yes yes

All filegroups in specified databases yes yes N/A

Filestream data in specified databases yes yes yes

Specified filegroups in specified database yes yes N/A Specified files in filegroups in specified databases yes yes N/A

When performing a logs only backup for SQL Server data objects, ensure that the SQL Server database options are properly configured. The Microsoft SQL Server

documentation provides more information. Individual items are subject to promotion.

Introduction

(34)

Example strategies for backing up SQL Server data

This section describes example strategies for backing up SQL Server data. If the SQL Server manages a significant amount of data, schedule a backup of the databases every one to two weeks, as shown in the following table.

Table 6 Full backup every one to two 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 logs only cumulative

incremental

logs only logs only logs only full Repeat

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

Table 7 Logs only backup after a full backup

Fri Sat Sun Mon Tues Wed Thurs

full logs only logs only logs only cumulative incremental logs only logs only Repeat

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

NOTICE

If a database is read-only, perform a full backup of the database. A read-only database cannot be restored from a transaction log backup that already exists.

Using backup levels

Because it is not practical or efficient to run full backups every day, other backup levels can be specified for automatic 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 the backup levels.

Table 8 Backup level advantages and disadvantages

Backup level Advantages Disadvantages

Full l _{Fastest restore time.} l _{Slow backup.}

l Increases load on client, server,

and network.

l _{Uses the most volume space.}

(35)

Table 8 Backup level advantages and disadvantages (continued)

Backup level Advantages Disadvantages

Logs only l _{Faster than a full backup.} l _{Decreases the load on server and}

Uses the least volume space.

l _{Enables point-in-time restore.}

l _{Slow restore.}

l _{Data can spread across multiple}

volumes.

l _{Multiple transaction logs can}

spread across multiple volumes. Cumulative

incremental

l Faster than a full backup. l _{Captures all changes since last}

full.

l Generally more time-consuming

than a logs only backup (depending on the backup schedule strategy).

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 9 Creating additional backup levels with data objects

Backup level Database objects

Full database Select one or more databases to create a level full database backup of the selected databases and their transaction log files.

Full file or filegroup Select one or more files or one or more filegroups to create a level full file or filegroup backup of the selected files or filegroup, but not their transaction logs.

Database logs only Select one or more databases to create a database logs only level backup of only the logs only for the selected databases. The SQL database must be previously configured to enable logs only backups.

Database cumulative incremental (level 1)

Select one or more databases to create a database level cumulative incremental backup of only the changes made to the selected databases since the last full level backup was created.

File or filegroup cumulative incremental

Select one or more files, or one or more filegroups to create a file or filegroup level cumulative incremental backup of only the changes made to the selected files or filegroups since the last full level backup was created.

Promoting backup levels

Guidelines for Microsoft SQL Server best practices indicate that a full database backup should be the first step in implementing a recovery strategy for a database. In adhering to these guidelines, the NMM supports backup level promotion. The following table

explains what prompts a promotion.

Introduction

(36)

Table 10 Backup level promotion process

Item Requested level Level of

promoted Reason

Database Cumulative incremental

Full Database full backup does not exist. Database Logs only Full l _{Database full backup does not}

exist.

l Transaction log backup types

are not supported for simple recovery model databases.

l _{Database is currently in}

emergency mode.(1) on page 36

File/Filegroup Full Database full Full backup of the entire database does not exist.(2) on page 36

File/Filegroup Cumulative incremental

Database full Full backup of the entire database does not exist.

File/Filegroup Logs only Full File or filegroup logs only backups are not supported.

Table 10 Backup level promotion process

(1) Refer to the Microsoft SQL Server Books Online for more information.

(2) Databases consist of files and groups that contain files. The default configuration is a primary filegroup with the main data file. Elaborate database configurations can contain more filegroups; each with more files. If a filegroup or file level backup is specified, and a full database backup is not on record, the filegroup or file backup is promoted to a database full backup.

SQL Server recovery

This section provides an introduction about recovery of SQL Server by using NMM.

Types of supported recovery

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

NOTICE

NMM supports recovery of a SQL Sever 2012 or later database only after the AlwaysOn replication has been removed for the corresponding database.

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

(37)

Table 11 Types of recovery for SQL Server VDI

Type of

recovery When used Description

Traditional recovery

For data that was backed up by traditional backup, NMM supports traditional recovery.

Data recovery from a traditional backup can be performed:

l _{At any time by using NMM.}

l _{By running NMM recover command (}nsrsqlrc_{) from}

the command prompt.

Traditional recovery operations recover files, filegroups, databases, and transaction log backups. Normal

recovery

NMM uses the normal restore type as the default.

The normal restore type restores:

l The entire set of data associated with one or more

SQL Server backups, including full, logs only, and cumulative incremental backups.

l _{A file, filegroup, 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 filegroups. In addition, a single filegroup, or multiple filegroups or files, can 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. Note that copy restore from and to the same SQL Server instance can also be done.

The copy recovery type creates a copy of a database by restoring a SQL Server database to a new location, or to 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. In addition, you can copy a system database, but you cannot overwrite it.

NMM does not support copy recovery of filestream databases.

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 that additional transaction logs can be applied.

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

Introduction

(38)

Table 12 Recovery modes

Types of recovery mode

Description

Normal The normal restore mode instructs SQL Server to leave the database in an operational state after the restore completes. This then enables database reads and writes. The normal restore mode is the default mode NMM uses when restoring a database.

No-recovery The no-recovery restore mode activates the SQL Server NORECOVERY database restore option for the last stage restored. The no-recovery restore mode places the database in a state that cannot be loaded after the restore, but is still able to process additional transaction log restore operations.

Standby The standby restore mode activates the SQL Server STANDBY database restore option for the last stage restored, which forces the database to be in a read-only state between transaction log restore operations. The standby restore mode provides an undo file for SQL Server to use when rolling back the transactions. Online SQL Server provides the ability to perform a restore operation while a SQL Server

database is active. The database is completely offline only while the primary filegroup is being restored. Once the primary filegroup is restored, the database can be brought online while the rest of the filegroups are being restored, and then only the data that is being restored is unavailable. The rest of the database remains available during this type of restore. Earlier versions of SQL Server require that you bring a database offline before you restore the database.

Recovery time

Backups can be recovered to a specific time. The recovery time controls which backup data should be reinstated when a database is recovered. The recovery time also controls the portions of an logs only backup that must be recovered when NMM is instructed to discard transactions performed after a given time.

The default or current recovery time for each database comes from the create time of the marked item. By default, the most recent backup is recovered. If the most recent backup is logs only level or 1, dependent backups are recovered first. User-specified recovery times can restore older backup versions or perform point-in-time recovery operations. For example, a point-in-time recovery is specified by using a recovery time that is earlier than the create time of the transaction log backup, but later than the create time of the previous backup.

NMM provides three methods for recovering to a specific time:

l Database backup versions

l Point-in-time recovery of a transaction log (level logs only) backup l Recovering to a named log mark

Recovery window restrictions

In the Recovery window, the rules for marking an item are based on the selected restore type. The normal restore type does not restrict marking in any way. All restorable objects (file, filegroup, filestream data, database) can be marked. When the copy restore type is chosen, only one database object can be marked. Marking the root SQL Server item is not

(39)

permitted, the filegroups and files of the selected database are automatically marked and restored as part of the full database restore.

The recovery process

This section describes the recovery process. A recovery uses the following process:

1. NMM recovers the most recent full backup, and then recovers the most recent differential (level 1) backup (if any).

If a full database backup is removed from the NetWorker server, and an incremental backup is attempted, the recovery fails. NMM checks the SQL Server instance to determine if a full database backup was performed, but does not verify if a full backup still exists on the NetWorker server.

2. NMM recovers all the transaction log backups that ran after the most recent differential backup (or that ran after the last full backup, if there was no differential backup). To correctly recover uncommitted transactions, the SQL Server NORECOVERY mode is specified for all intermediate transaction logs.

The recovery of the final transaction log specifies the restore mode if STANDBY or NORECOVERY is selected. The default selection is Normal.

For example, if you selected a restore mode of NORECOVERY, that specification appears in the output for a database restore as follows:

C:> nsrsqlrc -s NetWorker_server_name my_database

nsrsqlrc: Restoring database my_database... nsrsqlrc: RESTORE database my_database FROM virtual_device='BSMSQL' WITH norecovery, stats nsrsqlrc: RESTORE database my_database from

virtual_device='BSMSQL' WITH norecovery (differential) nsrsqlrc: RESTORE transaction my_database FROM virtual_device='BSMSQL' WITH norecovery

nsrsqlrc: RESTORE transaction my_database FROM virtual_device='BSMSQL' WITH norecovery

Received 1.0 MB 4 files from NSR server.

NMM imposes the following restrictions on database file relocation:

l Only database backups can be relocated. Individual file and filegroup backups

cannot be relocated without relocating the database that contains those files.

l If the configuration of a database has changed since the most recent, level full

database back up was created, you cannot relocate the database. Configuration changes include the deletion or addition of files, filegroups, or transaction log files.

l A system database might not be the destination database of a relocation.

l The relocation fails if the destination does not have sufficient space to create a new

database.

Introduction

(40)

Error logs for backup and recovery

To help you diagnose problems, certain information is written to an application-specific log file during backup and restore operations.

The following types of information are available:

l Software configuration information l Operation parameters

l Operation status and error messages

Application logs are generated for the nsrsqlsv.exe and nsrsqlrc.exe programs, and are created in the nsr\applogs folder on the SQL Server host. NMM logs are cumulative and are appended each time the nsrsqlsv.exe or nsrsqlrc.exe program runs. A log is truncated when disk space is exhausted. The following table cross-references the program and log file names.

Table 13 Program and log file names

Program Log file

nsrsqlsv.exe nsrsqlsv.raw nsrsqlrc.exe nsrsqlrc.raw XBSA library code xbsa.messages

The logging capabilities of nsrsqlsv and nsrsqlrc are not cluster-aware. For both clustered and nonclustered configurations, the logs are stored on a local disk. The xbsa.messages file collects messages from the X-Open Backup Services application programming interface.

(41)

CHAPTER 2 Graphical User Interfaces

This chapter includes the following sections:

l User interfaces for backup and recovery... 42 l NetWorker User for SQL Server GUI... 43 l EMC NetWorker Backup tab in Microsoft SQL Server Management Studio GUI...46

(42)

User interfaces for backup and recovery

This section describes the graphical user interfaces (GUIs) from where you can perform backup and recovery.

l The NetWorker Administration GUI on the NetWorker server - Start the NetWorker

Management Console on the NetWorker server and go to the NetWorker Administration GUI to perform scheduled backups.

l NetWorker User for SQL Server GUI - Select Start_>Program_>NetWorker Modules_>

NetWorker User for SQL Server on the host where NMM is installed to start the NetWorker User for SQL Server GUI. The NetWorker User for SQL Server GUI has specific views, display conventions, and marking semantics and restrictions that you can use to perform backup (scheduled and manual) and recovery.

l EMC NetWorker Backup tab in the Microsoft SQL Server Management Studio GUI

-Start the Microsoft SQL Server Management Studio (SSMS) GUI to view the EMC NetWorker Backup tab. The EMC NetWorker Backup tab appears in the SSMS GUI after installing the Networker SQL Adhoc Plugin during NMM installation. You can use the EMC NetWorker Backup tab to perform backup (manual) and recovery.

The following table summarizes the various locations from where backup can be initiated and the backup levels that are supported for each interface.

Table 14 Where to initiate backup operations

Backup type Backup initiated from Backup levels available Full txnlog Diff

Scheduled The NetWorker Administration GUI on the NetWorker server

Yes Yes Yes

Manual Command line on the SQL Server, which is the client Yes Yes Yes

NetWorker User for SQL Server GUI Yes No No

EMC NetWorker Backup tab in the Microsoft SQL Server Management Studio GUI (NetWorker SQL Adhoc Plugin)

Yes Yes Yes

The following table summarizes the location from where recovery can be initiated

Table 15 Where to initiate recovery operations

Recovery type Recovery initiated from

Full NetWorker User for SQL Server GUI

EMC NetWorker Backup tab in the Microsoft SQL Server Management Studio GUI (NetWorker SQL Adhoc Plugin)

(43)

NetWorker User for SQL Server GUI

This section provides information on the various views in the NetWorker User for SQL Server GUI, fake objects, display conventions for SQL data types, and marking semantics and restrictions

Views

The NetWorker User for SQL Server GUI has four views.

l Backup view — All backups are performed from the Backup window. Select the

database that needs to be backed up, and either right-click or select Options > Backup Options to view the Backup Options dialog box.

The following figure shows the Backup view.

Figure 9 Backup view

l Restore Operation view — Normal, Copy, or Piecemeal restores are performed from

the Restore Operation view. Select the icon to view the Restore Operation window.

The Restore Operation dialog is shown in the following figure.

Figure 10 Restore Operation dialog

Graphical User Interfaces

(44)

l Change Server view — You can choose the NetWorker server from the choices

available from this view. Select the icon to view the Change Server window. The Change Server dialog is shown in the following figure.

Figure 11 Change Server dialog

l Select SQL Instance view — This view allows you to select SQL instances that you

want to work with. Select the icon to view the Select SQL Instance window. Select SQL Instance dialog is shown in the following figure.

Figure 12 Select SQL Instance dialog

Display conventions for SQL data types

The NetWorker User for SQL Server GUI uses specific data item names, text characteristics, and icons to distinguish the variable qualities of SQL Server data. The following table outlines these conventions.

Table 16 SQL Server storage hierarchy display conventions

Icon Data item Description

SQL Server l _{Root of the storage hierarchy}

l Signifies all SQL Server databases on the

host

EMC NetWorker Module for Microsoft for SQL VDI

EMC

®

NetWorker

®

Module for

Microsoft for SQL VDI

User Guide

CONTENTS

FIGURES

TABLES

Preface

CHAPTER 1

Introduction

Using NMM with Virtual Device Interface API

NMM environments backup and recovery

Backup environments

Recovery environments (non-client direct or non-direct file access)

Cluster environments

Using NMM in a SQL VDI environment

Supported Windows Server and SQL Server versions

Migrating from VSS solution to VDI solution for SQL Server data protection

AlwaysOn Availability Group feature of Microsoft SQL Server 2012 or later

SQL Client Direct to AFTD or DD devices

Microsoft SQL Server named log marks

Named and default instances of SQL Server

Multi-stream Data Domain Boost

Database consistency checks

Improved SQL save group notifications

Microsoft Hybrid Cloud environment

Optional SQL Server log gap detection

Supported special characters in database names for NMM backup and recovery

Transparent data encryption

Access privileges for backup and recovery

SQL Server backups

Types of supported backups

Backup levels

Setting backup levels

Example strategies for backing up SQL Server data

Using backup levels

SQL Server recovery

Types of supported recovery

Recovery modes

Recovery time

Recovery window restrictions

The recovery process

Error logs for backup and recovery

CHAPTER 2

Graphical User Interfaces

User interfaces for backup and recovery

NetWorker User for SQL Server GUI

Views

Display conventions for SQL data types