Contents

Dell PowerProtect 19.4 Data Manager SQL Server User Guide PDF

1 of 44
1 of 44

Summary of Content for Dell PowerProtect 19.4 Data Manager SQL Server User Guide PDF

PowerProtect Data Manager for Microsoft Application Agent SQL Server Version 19.4

User Guide REV 02

April 2020

Copyright 2020 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 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

Preface 5

PowerProtect Data Manager for Microsoft Application Agent Overview 9 PowerProtect Data Manager overview..............................................................10 Prerequisites..................................................................................................... 10 Firewall and port considerations.........................................................................11 PowerProtect Data Manager new deployment overview....................................11 PowerProtect Data Manager existing deployment overview..............................12

Enabling the Microsoft Application Agent for SQL 15 Introducing the Microsoft application agent for SQL.........................................16 Microsoft SQL Server data protection and replication requirements................. 16 Protecting a stand-alone SQL Server................................................................ 17 Protecting SQL Server clustered environments.................................................17 Install and configure the application agent.........................................................18

Prerequisites ........................................................................................18 Install the Microsoft application agent..................................................18 Upgrade the Microsoft application agent............................................. 20 Uninstall the Microsoft application agent with the setup file................20 Required privileges for Application Direct backup and recovery of a stand-alone server................................................................................ 21 Required privileges for Application Direct backup and recovery of an Always On availability group.................................................................22 Required privileges for Application Direct backup and recovery of a Failover Cluster Instance or Always On Failover Cluster Instance........ 23 Stagger SQL discovery jobs in host scale-out environments................ 23

Manage the Microsoft application agent for SQL..............................................23 Support for existing Microsoft application agent backups with PowerProtect Data Manager................................................................................................... 25

Supporting existing Microsoft application agent backups with PowerProtect Data Manager............................................................... 25 Using the backup discovery tool for PowerProtect Data Manager management of existing backups......................................................... 26

Microsoft application agent for SQL Server application-aware protection........ 27 Troubleshooting self-service T-SQL backups with an IO error message........... 29

Managing Storage, Assets, and Protection 31 Add protection storage .................................................................................... 32

Troubleshooting protection policy for DD storage unit......................... 33 Viewing the DD Boost storage unit password.......................................33

Setting the sysadmin privilege for Microsoft SQL hosts................................... 33 Discover a SQL application host........................................................................34 Add a protection policy for SQL database protection........................................35 Manage the PowerProtect agent service..........................................................38

About the PowerProtect agent service................................................ 38 Start, stop, or obtain the status of the PowerProtect agent service....39 Troubleshoot the PowerProtect agent service installation...................40

Chapter 1

Chapter 2

Chapter 3

CONTENTS

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 3

Troubleshoot the PowerProtect agent service operations................... 40 Register the PowerProtect agent service to a different server address... 41 Recovering the PowerProtect agent service from a disaster................ 41

Performing Self-Service Backups and Restores of Microsoft SQL Databases 43 Performing self-service backups of Microsoft SQL databases..........................44 Restore a SQL application host.........................................................................44

Chapter 4

Contents

4 PowerProtect Data Manager for Microsoft Application Agent SQL Server 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.

Note: References to Data Domain systems in this documentation, in the UI, and elsewhere in the product include PowerProtect DD systems and older Data Domain systems.

Purpose

This document describes how to configure and use the PowerProtect Data Manager with the Microsoft application agent to back up and restore Microsoft SQL Server.

Audience

This document is intended for the host system administrator who configures and uses the PowerProtect Data Manager with the Microsoft application agent to back up and restore Microsoft SQL Server.

Revision history

The following table presents the revision history of this document.

Table 1 Revision history

Revision Date Description

02 April 3, 2020 Added topic "Setting the sysadmin privilege for Microsoft SQL hosts."

01 March 31, 2020 Initial release of this document for PowerProtect Data Manager 19.4.

Compatibility information

Software compatibility information for the PowerProtect Data Manager software is provided in the eLab Navigator, available at https://elabnavigator.emc.com/eln/modernHomeDataProtection.

Related documentation

The following publications are available on Dell EMC Online Support and provide additional information:

l PowerProtect Data Manager Administration and User Guide Describes how to configure the software.

l PowerProtect Data Manager Release Notes Contains information on new features, known limitations, environment, and system requirements for the software.

l PowerProtect Data Manager Security Configuration Guide Contains security information.

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 5

l PowerProtect Data Manager Cloud Disaster Recovery Administration and User Guide Describes how to deploy Cloud DR, protect VMs in the AWS or Azure cloud, and run recovery operations.

l PowerProtect Data Manager for Cyber Recovery User Guide Describes how to install, upgrade, patch, and uninstall the Dell EMC PowerProtect Cyber Recovery software.

l PowerProtect Data Manager API documentation: https://developer.dellemc.com Contains the PowerProtect Data Manager APIs and includes tutorials to guide to you in their use.

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

Special notice conventions that are used in this document

The following conventions are used for special notices:

CAUTION A CAUTION indicates either potential damage to hardware or loss of data and tells you how to avoid the problem.

Note: A Note indicates important information that helps you make better use of your product.

Typographical conventions

The following type style conventions are used in this document:

Table 2 Style conventions

Bold Used for interface elements that a user specifically selects or clicks, for example, names of buttons, fields, tab names, and menu paths. Also used for the name of a dialog box, page, pane, screen area with title, table label, and window.

Italic Used for full titles of publications that are referenced in text.

Monospace Used for:

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

Monospace italic Used for variables.

Monospace bold Used for user input.

[ ] Square brackets enclose optional values.

| Vertical line indicates alternate selections. The vertical line means or for the alternate selections.

{ } Braces enclose content that the user must specify, such as x, y, or z.

... Ellipses indicate non-essential information that is omitted from the example.

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

Preface

6 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

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.

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.

Preface

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 7

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.

Preface

8 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

CHAPTER 1

PowerProtect Data Manager for Microsoft Application Agent Overview

This chapter contains the following topics:

l PowerProtect Data Manager overview.................................................................................. 10 l Prerequisites..........................................................................................................................10 l Firewall and port considerations............................................................................................. 11 l PowerProtect Data Manager new deployment overview........................................................ 11 l PowerProtect Data Manager existing deployment overview.................................................. 12

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 9

PowerProtect Data Manager overview Use PowerProtect Data Manager with the application agent to perform the following operations:

l Automate the configuration of the application agent backup policy and protection storage settings.

l Create a catalog of backups that are produced by the application agent, and then monitor that catalog data to determine if retention policies are being adhered to.

l Manage the life cycle of backups that are created by the application agentthat is, ensure that the backups are marked for garbage collection based on the rules of the retention policy.

PowerProtect Data Manager does not change the way that the application agent works. DBAs create the backups and perform the restores.

Prerequisites Ensure that your environment meets the requirements for a new deployment or upgrade of PowerProtect Data Manager.

Requirements:

l A list of hosts that write backups to DD systems is available.

l DD OS version 6.1 or later and the DD Management Console (DDMC). All models of DD systems are supported.

l Application agent 19.4 or earlier.

l License: A trial license is provided with the PowerProtect Data Manager software. DPS Applications, Backup, and Enterprise customers can contact Dell EMC Licensing Support for assistance with a permanent PowerProtect Data Manager license.

l Large environments require multiple PowerProtect Data Manager instances. Contact Champions.eCDM@emc.com for assistance with sizing requests.

l The PowerProtect Data Manager 19.4 download file requires the following:

n ESXi version 6.0, 6.5, 6.7, or 7.0.

n 8 vCPUs, 18 GB RAM, one 100 GB disk, and one 500 GB disk.

n The latest version of the Google Chrome browser to access the PowerProtect Data Manager UI.

n TCP port 7000 is open between PowerProtect Data Manager and the application agent hosts.

l VMware ESXi server that hosts PowerProtect Data Manager meets the following minimum system requirements:

n 10 CPU cores

n 18 GB of RAM for PowerProtect Data Manager

n Five disks with the following capacities:

Disk 1100 GB

Disk 2500 GB

Disk 310 GB

PowerProtect Data Manager for Microsoft Application Agent Overview

10 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

Disk 410 GB

Disk 55 GB

n One 1-GB NIC

Firewall and port considerations

Table 3 PowerProtect Data Manager port requirements

Description Communication Port

SSH communications Bidirectional communication between the SSH client and the PowerProtect Data Manager appliance

22 TCP/UDP

RestServer Bidirectional communication between the HTTP client and the PowerProtect Data Manager appliance

8443 TCP

UI redirect Inbound only 80 TCP

443

LDAP Outbound only 389 TCP/UDP

636 TCP

Discovery (devices) Outbound between the PowerProtect Data Manager appliance and the device

3009 TCPStorage Direct and DD system

5989 TCPSMI-S

443 TCPXtremIO

7225 TCPRecoverPoint

PowerProtect Data Manager agent

Bidirectional communication between the database hosts and the PowerProtect Data Manager appliance

7000 TCP

Embedded VM Direct service

Outbound 9090 TCP

PowerProtect Data Manager new deployment overview Familiarize yourself with the high-level steps required to install PowerProtect Data Manager with the application agent.

Procedure

1. Design how to group the backups based on the storage requirements and retention policies.

The account team can help with backup storage design.

2. Install DD Management Center (DDMC).

PowerProtect Data Manager uses DDMC to connect to the DD systems. The DD Management Center Installation and Administration Guide provides instructions.

3. Install PowerProtect Data Manager from the download file.

The PowerProtect Data Manager Deployment Guide provides instructions.

4. Add external DD systems or DDMC to PowerProtect Data Manager.

PowerProtect Data Manager for Microsoft Application Agent Overview

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 11

The PowerProtect Data Manager Administration and User Guide provides instructions on how to add protection storage.

5. Install the application agent on the appropriate hosts and connect them to PowerProtect Data Manager according to the instructions in the next "Enabling" chapter.

This operation should be performed by DBAs.

6. Add new or approve pending agent requests in the PowerProtect Data Manager according to the instructions in the next "Enabling" chapter.

7. After the approval of agent requests, PowerProtect Data Manager must run a discovery operation to discover the assets.

The PowerProtect Data Manager Administration and User Guide provides information.

8. Add a protection policy for groups of assets that you want to back up.

You must add credentials to the database so that PowerProtect Data Manager can access the database and create backups. The PowerProtect Data Manager Administration and User Guide provides instructions.

Note: After you create a centralized protection job, the first backup is a full backup.

9. Add Service Level Objectives to the protection policy to verify that the protected assets meet the Service Level Agreements (SLAs).

The PowerProtect Data Manager Administration and User Guide provides instructions.

Configuration is complete.

10. Monitor protection compliance in the PowerProtect Data Manager dashboard.

PowerProtect Data Manager existing deployment overview Familiarize yourself with the high-level steps required to install PowerProtect Data Manager with the application agent in an existing environment.

Procedure

1. Install DD Management Center (DDMC).

PowerProtect Data Manager uses DDMC to connect to the DD systems. The DD Management Center Installation and Administration Guide provides instructions.

2. Install PowerProtect Data Manager from the download file.

The PowerProtect Data Manager Deployment Guide provides instructions.

3. Add external DD systems or DDMC to PowerProtect Data Manager.

The PowerProtect Data Manager Administration and User Guide provides instructions on how to add protection storage.

4. Upgrade the application agent or uninstall and then reinstall the application agent on the hosts and connect them to PowerProtect Data Manager according to the instructions in the next "Enabling" chapter.

This operation should be performed by DBAs.

5. Add new or approve pending agent requests in the PowerProtect Data Manager according to the instructions in the next "Enabling" chapter.

6. After the approval of agent requests, PowerProtect Data Manager must run a discovery operation to discover the assets.

The PowerProtect Data Manager Administration and User Guide provides information.

PowerProtect Data Manager for Microsoft Application Agent Overview

12 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

7. Add a protection policy for groups of assets that you want to back up.

You must add credentials to the database so that PowerProtect Data Manager can access the database and create backups. The PowerProtect Data Manager Administration and User Guide provides instructions.

Note: After you create a centralized protection job, the first backup is a full backup.

8. Add Service Level Objectives to the protection policy to verify that the protected assets meet the Service Level Agreements (SLAs).

The PowerProtect Data Manager Administration and User Guide provides instructions.

Configuration is complete.

9. Monitor protection compliance in the PowerProtect Data Manager dashboard.

PowerProtect Data Manager for Microsoft Application Agent Overview

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 13

PowerProtect Data Manager for Microsoft Application Agent Overview

14 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

CHAPTER 2

Enabling the Microsoft Application Agent for SQL

This chapter includes the following topics:

l Introducing the Microsoft application agent for SQL............................................................. 16 l Microsoft SQL Server data protection and replication requirements..................................... 16 l Protecting a stand-alone SQL Server.....................................................................................17 l Protecting SQL Server clustered environments..................................................................... 17 l Install and configure the application agent............................................................................. 18 l Manage the Microsoft application agent for SQL.................................................................. 23 l Support for existing Microsoft application agent backups with PowerProtect Data Manager

.............................................................................................................................................. 25 l Microsoft application agent for SQL Server application-aware protection............................ 27 l Troubleshooting self-service T-SQL backups with an IO error message................................ 29

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 15

Introducing the Microsoft application agent for SQL The Microsoft application agent enables an application administrator to protect and recover the SQL application data on the application host. PowerProtect Data Manager integrates with the Microsoft application agent to check and monitor back up compliance against protection policies. PowerProtect Data Manager also enables central scheduling for backups.

You can install the Microsoft application agent on a Windows SQL Server host by using the install wizard. Install and configure the application agent on page 18 provides instructions.

Note: PowerProtect Data Manager supports the coexistence of the Microsoft application agent and the File System agent on Windows.

To enable the discovery and scheduling of backups with PowerProtect Data Manager, you must approve the client in the PowerProtect Data Manager UI. Manage the Microsoft application agent for SQL on page 23 provides more information.

In the PowerProtect Data Manager UI, when you select Infrastructure > Assets > SQL Databases and click View Copies, the size of a Microsoft SQL database that is backed up through application-aware protection is displayed as 0 bytes. The correct size is displayed at the SQL database asset level. For a transaction log backup, the correct size is displayed at the Protection Copy Set (PCS) level.

Software compatibility information for the PowerProtect Data Manager software and application agents is provided in the eLab Navigator, available at https://elabnavigator.emc.com/eln/ modernHomeDataProtection.

Microsoft SQL Server data protection and replication requirements

PowerProtect Data Manager can manage and monitor data protection and replication for Microsoft SQL Server assets through integration with the Microsoft application agent.

After installing the Microsoft application agent, review the following information for additional requirements before adding the Microsoft application agent as an asset source in PowerProtect Data Manager and discovering the SQL Server assets.

Verify that the environment meets the following requirements:

l Ensure that you do not mix 32-bit and 64-bit instances on the same SQL Server host. PowerProtect Data Manager operations do not support hosts with a mix of 32-bit and 64-bit SQL Server instances.

l Ensure that all clocks on the SQL Server host, domain controller, and PowerProtect Data Manager are time-synced to the local NTP server to ensure discovery of the backups.

l Ensure that the SQL Server and the PowerProtect Data Manager system network can see and resolve each other.

l Ensure that port 7000 is open on the SQL Server host.

l Ensure that DNS is configured correctly on the application agent host for SQL Server.

l Ensure that DNS is configured correctly on the PowerProtect Data Manager host and the name resolution matches.

Enabling the Microsoft Application Agent for SQL

16 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

Protecting a stand-alone SQL Server Learn how to configure protection of a stand-alone SQL Server.

Procedure

1. Add storage for Data Domain Management Console or the external Data Domain.

Add protection storage on page 32 provides information.

2. Install the Microsoft application agent on the SQL Server host.

Install the Microsoft application agent on page 18 provides information.

3. Add or approve the Microsoft application agent in PowerProtect Data Manager.

Manage the Microsoft application agent for SQL on page 23 provides information.

4. Discover and add the credentials for the SQL application host.

Discover a SQL application host on page 34 provides information.

5. Create a protection policy to protect the SQL host.

Add a protection policy for SQL database protection on page 35 provides information.

Note: You cannot perform a backup to a secondary Data Domain device. You can only restore from a secondary Data Domain device

Protecting SQL Server clustered environments Learn how to configure protection of SQL Server clustered environments, including Always On availability groups and Failover Cluster Instances.

About this task

On each node in the cluster. repeat the steps to install the Microsoft application agent, and then add and discover the application host in PowerProtect Data Manager.

CAUTION Protection of Failover Cluster Instances (FCI) requires that all nodes in the cluster be registered to the PowerProtect Data Manager server. Prior to registration, the node must be the active node and own all the disks in the cluster. The recommended method is to failover all nodes to the registering node. Repeat this step for all nodes in the cluster and any nodes added to the cluster. Failure to perform this step results in unpredictable results during protection policy.

Procedure

1. Add a storage system.

Add protection storage on page 32 provides information.

2. Install the Microsoft application agent on each node in the cluster.

Install the Microsoft application agent on page 18 provides information.

3. Configure the required user privileges on each node in the cluster.

Required privileges for Application Direct backup and recovery of a Failover Cluster Instance or Always On Failover Cluster Instance on page 23 provides information.

4. Add or approve the Microsoft application agent on each node in the cluster.

Manage the Microsoft application agent for SQL on page 23 provides information.

Enabling the Microsoft Application Agent for SQL

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 17

5. Discover and add the credentials for each SQL application host.

Discover a SQL application host on page 34 provides information.

6. Create a protection policy to protect the cluster.

Add a protection policy for SQL database protection on page 35 provides information.

Note: You cannot perform a backup to a secondary Data Domain device. You can only restore from a secondary Data Domain device

Install and configure the application agent

Prerequisites Ensure that a SQL Server environment meets the following prerequisites before you install the Microsoft application agent:

l Install the following applications on the Windows host:

n Microsoft SQL Server

n SQL Server Management Studio (SSMS)

n .NET Framework 4.0

If you are installing ItemPoint for table-level recovery, install .NET Framework 4.5.

l In the PowerProtect Data Manager UI, select Agent Downloads from the System Settings menu, select the Microsoft application agent download package, msappagent194_win_x64.zip, and then download the package to the Windows SQL Server host.

l Log in to the SQL Server host as an administrator to install the Microsoft application agent.

l To deploy the Common Language Runtime (CLR) assembly, ensure that you have administrator access to the SQL Server host and the master database. If the SQL Server host is running in a domain, ensure that you have access as a Domain administrator.

l Ensure that the required permissions are set up for the discovery of SQL instances and databases by the PowerProtect agent service. Required privileges for Application Direct backup and recovery of a stand-alone server on page 21 and the "Required privileges" topics after it provide more information.

Install the Microsoft application agent Learn how to install the Microsoft application agent.

About this task

Note: In Always On availability group or cluster environments, you must install the Microsoft application agent on each node in the cluster.

Procedure

1. In the PowerProtect Data Manager UI:

a. Select Agent Downloads from the System Settings menu.

b. Select the Microsoft application agent download package, msappagent194_win_x64.zip.

c. Download the package to the host where you want to install the Microsoft application agent.

Enabling the Microsoft Application Agent for SQL

18 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

2. Open msappagent194_win_x64.zip with WinZip.

When you are prompted for a password, type the password that you received with the software license.

3. Use WinZip to unzip the msappagent194_win_x64.zip file.

4. In the unzipped folder, launch emcmsappagent-19.4.0.0.exe.

The installation wizard appears.

5. On the Welcome Wizard page, select I agree to the license term and agreements, and then click Next.

6. On the Change Install Location page, perform one of the following tasks:

l To install the Microsoft application agent in the default folder, leave the installation location as is.

The default installation folder is C:\Program Files\DPSAPPS\MSAPPAGENT.

l To specify a different installation location, perform the following steps:

a. Click Change.

b. In the dialog box that appears, specify the installation location.

c. Click OK.

7. Click Next.

8. On the Configure Installation Options page, specify any of the following installation options, as required:

l To integrate the Microsoft application agent with PowerProtect for centralized or self- service protection of SQL Server data, select the following options, as required:

n To install the Microsoft application agent software, select Application Direct.

n To install the SQL Server Management Studio plug-in user interface, select SSMS Plug-in.

You can use the SSMS plug-in to perform self-service SQL Server backup and restore operations.

n To enable table-level restores, select ItemPoint.

This option installs ItemPoint for Microsoft SQL Server, which you can use to perform table-level restores.

n You must specify the PowerProtect appliance details by performing the following steps:

a. Select PowerProtect Data Manager Integration.

b. In the Appliance Hostname or IP address field, type the hostname or IP address of the PowerProtect server.

Note: Installation of the Microsoft application agent requires port 7000 on SQL Server and port 8443 on PowerProtect to be open bidirectionally. These ports enable communication between the Microsoft application agent and PowerProtect.

l To install the VM Direct Engine to recover application-aware SQL virtual machine backups, select the following options, as required:

Note: By default, when a SQL Server virtual machine is added to a virtual machine application-aware protection policy in PowerProtect Data Manager, the Microsoft application agent and ItemPoint are silently installed on the protected virtual

Enabling the Microsoft Application Agent for SQL

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 19

machine. Before you restore a VM Direct Engine backup to an alternate virtual machine that is not part of a protection policy, you must install the Microsoft application agent on the target virtual machine.

n Select VM Direct Engine.

Note: The PowerProtect appliance details are disabled when you select the VM Direct Engine option.

n To install the SQL Server Management Studio plug-in user interface, select SSMS Plug-in.

n To enable table-level restores, select ItemPoint.

This option installs ItemPoint for Microsoft SQL Server, which you can use to perform table-level restores.

9. Click Install >.

10. On the CLR assembly deployment wizard page, perform the following steps:

a. Select or clear the SQL Server instances on which you want to deploy the CLR Assembly. By default, all the SQL Server instances are selected.

b. To deploy CLR Assembly, select one of the following authentication options:

l Current Windows User

l Use Windows Authentication

l Use Database Authentication

c. In the User name and Password fields respectively, type the username and the password of the user who has the privileges to deploy the CLR Assembly.

d. Click Deploy.

e. Click Install.

f. After the deployment completes, click Next.

11. On the Complete the Setup page, click Finish.

Upgrade the Microsoft application agent The Microsoft application agent 19.4 does not support a direct upgrade from an earlier version if you are using an earlier version of PowerProtect Data Manager.

To upgrade to version 19.4, you must uninstall and then reinstall the Microsoft application agent.

Note: If a table-level restore was performed since the last reboot of the host, Microsoft application agent requests a reboot during an uninstall operation. The Microsoft application agent installs and uses the Eldos CBFS driver for table-level restores. The Eldos CBFS driver is loaded during the table-level restore operation. Microsoft requires the driver to be unloaded prior to an uninstall operation.

The following sections provide instructions:

l Uninstall the Microsoft application agent with the setup file on page 20

l Install the Microsoft application agent on page 18

Uninstall the Microsoft application agent with the setup file About this task

To uninstall the Microsoft application agent for SQL Server with the setup file, perform the following steps.

Enabling the Microsoft Application Agent for SQL

20 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

Note: If a table-level restore was performed since the last reboot of the host, Microsoft application agent requests a reboot during an uninstall operation. The Microsoft application agent installs and uses the Eldos CBFS driver for table-level restores. The Eldos CBFS driver is loaded during the table-level restore operation. Microsoft requires the driver to be unloaded prior to an uninstall operation.

Procedure

1. Launch emcmsappagent-19.4.0.0.exe.

2. On the Install Modification page, select Remove, and then click Next.

3. On the Configure Uninstallation Options page, click Remove.

4. On the Removing the CLR assembly page:

a. Select the required SQL Server instances to remove the CLR Assembly.

By default, all the SQL Server instances are selected.

b. Select one of the following options to remove the CLR assembly:

l Use Windows Authentication

l Use Database Authentication

c. In the User name and Password fields, type the credentials for the user who has the privileges to remove CLR assembly.

d. Click Remove.

e. After the removal completes, click Next.

5. On the Complete the Setup page, click Finish.

Required privileges for Application Direct backup and recovery of a stand-alone server

Learn about the user requirements for Application Direct stand-alone backup and recovery.

Required SQL Server roles

Assign the user the following SQL Server roles:

l sysadmin

l public

Required Windows user permissions

Create a local or domain Windows user account and assign the following roles:

l For table-level backup and recovery, assign the administrative privileges.

l For database-level backup and recovery, assign the following permissions:

n Add the user to the "Create global objects" Windows policy.

n Assign the following permissions to the data and log folder of the database:

Read

Write

List folder contents

The default data and log folder is the SQL Server installation folder. For example, for SQL Server 2012, the default folder is C:\Program Files\Microsoft SQL Server \MSSQL12.MSSQLSERVER\MSSQL\.

Enabling the Microsoft Application Agent for SQL

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 21

Setting the sysadmin privilege for the SQL Server host

To enable the integration with PowerProtect Data Manager on the SQL Server host, the NT AUTHORITY\SYSTEM account on the host requires the sysadmin privilege.

Before you register the SQL Server host with PowerProtect Data Manager, complete the following steps on the host to set the required sysadmin privilege.

1. Log in to each SQL instance, open the SQL Server Management Studio (SSMS), and select View > Object Explorer.

2. In the Object Explorer, expand Security and then expand Logins.

3. Right-click NT AUTHORITY\SYSTEM, and then select Properties.

4. In the Login properties window, select Server Roles.

5. Select sysadmin under Server roles, and then click OK.

Required privileges for Application Direct backup and recovery of an Always On availability group

Learn about the user requirements for Application Direct backup and recovery of an Always On availability group.

Required SQL Server roles

Assign the user the following SQL Server roles:

l sysadmin

l public

Required Windows user permissions

Create a Windows user account with one of the following configurations:

l The built-in Windows Administrator.

l A domain user added to the Administrators user group.

l A local user account added to the Administrators user group on each node in the cluster. The username and password must be the same on each node.

Note: If you use an account that you created (an account that is not the built-in Windows Administrator), you must launch the tool where you will perform the backup or recovery with elevated permissions (run as administrator).

Setting the sysadmin privilege for the SQL Server hosts

To enable the integration with PowerProtect Data Manager on each SQL Server host, the NT AUTHORITY\SYSTEM account on each host requires the sysadmin privilege.

Before you register any SQL Server host with PowerProtect Data Manager, complete the following steps on each host to set the required sysadmin privilege.

1. Log in to each SQL instance, open the SQL Server Management Studio (SSMS), and select View > Object Explorer.

2. In the Object Explorer, expand Security and then expand Logins.

3. Right-click NT AUTHORITY\SYSTEM, and then select Properties.

4. In the Login properties window, select Server Roles.

5. Select sysadmin under Server roles, and then click OK.

Enabling the Microsoft Application Agent for SQL

22 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

Required privileges for Application Direct backup and recovery of a Failover Cluster Instance or Always On Failover Cluster Instance

Learn about the user requirements for Application Direct backup and recovery of a Failover Cluster Instance or Always On Failover Cluster Instance.

Required SQL Server roles

Assign the user the following SQL Server roles:

l sysadmin

l public

Required Windows user permissions

Create a Windows user account with one of the following configurations:

l The built-in Windows Administrator.

l A domain user added to the Administrators user group.

Note: If you use an account that you created (an account that is not the built-in Windows Administrator), you must launch the tool where you will perform the backup or recovery with elevated permissions (run as administrator).

Setting the sysadmin privilege for the SQL Server hosts

To enable the integration with PowerProtect Data Manager on each SQL Server host, the NT AUTHORITY\SYSTEM account on each host requires the sysadmin privilege.

Before you register any SQL Server host with PowerProtect Data Manager, complete the following steps on each host to set the required sysadmin privilege.

1. Log in to each SQL instance, open the SQL Server Management Studio (SSMS), and select View > Object Explorer.

2. In the Object Explorer, expand Security and then expand Logins.

3. Right-click NT AUTHORITY\SYSTEM, and then select Properties.

4. In the Login properties window, select Server Roles.

5. Select sysadmin under Server roles, and then click OK.

Stagger SQL discovery jobs in host scale-out environments In a host scale-out environment with a large number of SQL hosts to register to PowerProtect Data Manager, consider the following method for staggering the SQL discovery jobs.

Kick off the installer in smaller group of hosts

When you install the Microsoft application agent by script, kick off the installer in smaller groups of hosts. The discovery jobs will kick off after the agent installation. Distributing the installer in smaller groups helps to stagger the incoming discovery results to PowerProtect Data Manager.

Manage the Microsoft application agent for SQL You can use the PowerProtect Data Manager UI to add a Microsoft application agent for SQL data protection, approve and reject pending agent requests, and edit and delete existing agents.

Procedure

1. Select Infrastructure > Application Agents.

The Application Agents window appears.

Enabling the Microsoft Application Agent for SQL

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 23

Note: If the PowerProtect agent service was able to register during the installation, the SQL host is already listed and does not need to be added.

2. Click Add.

The Add Application/FS Agent window appears.

3. Select one of the following options:

l Add IP Address or CSV Filename.

This process is also called whitelisting.

n If you select Add IP Address, perform the following steps:

a. Type the IP Address for the application agent.

b. Specify the date until which the application agent is preapproved.

c. Click Save.

n If you select CSV Filename, perform the following steps:

a. Click the Choose File icon.

Note: The contents of the .csv file must be in the following format, for example:

"10.25.115.113" "10.25.115.112" "10.25.115.145"

The Explorer window appears.

b. Select the .csv file, and then click Open.

The file appears in the Application/FS Agents window.

c. Select the date until which the application agent is preapproved.

d. Click Save.

l If you have disabled Auto whitelist, perform the following steps:

Note: The Auto whitelist option is disabled by default. When Auto whitelist is enabled, all preapproved application agents are automatically approved.

a. Select the required application agent.

b. Click one of the following options:

n Approve

n Reject

n Edit, then make the required changes.

n Remove

c. Click Save.

After you finish

Discover a SQL application host on page 34 describes how to set the host credentials before you schedule a backup.

Enabling the Microsoft Application Agent for SQL

24 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

Support for existing Microsoft application agent backups with PowerProtect Data Manager

The Microsoft application agent provides the capability to onboard existing stand-alone deployments, including their existing backups, to PowerProtect Data Manager. Existing backups are Microsoft application agent backups that you performed before integrating the Microsoft application agent with the PowerProtect Data Manager software and before adding an asset to a PowerProtect Data Manager protection policy.

Note: Onboarding of SQL backup copies to PowerProtect Data Manager is supported only from backups performed with Microsoft application agent 4.7 and later.

You can onboard up to three previous months of existing backups.

Retention lock is not supported for discovered existing backups in PowerProtect Data Manager.

Onboarding of DD Boost-over-FC backups is not supported.

With the onboarding capability, PowerProtect provides the following centralized features:

l Visibility of both existing backups and any new self-service or PowerProtect Data Manager policy-driven backups of onboarded assets.

l Automatic configuration of target protection storage based on the PowerProtect Data Manager protection policies that are used for your database.

l All the other functionality that is provided for PowerProtect Data Manager protection policies.

When you create a protection policy, the PowerProtect Data Manager software creates a storage unit on the specified DD system backup host that is managed by PowerProtect Data Manager. All subsequent backups of assets in that protection policy will go to this new storage unit. This implementation overrides the storage unit information that is provided in any running backup through scripts (T-SQL or CLI scripts) or the SSMS UI with the storage unit information that is provided by PowerProtect Data Manager.

Supporting existing Microsoft application agent backups with PowerProtect Data Manager

Learn how to support the existing Microsoft application agent backups.

Procedure

1. Upgrade the Microsoft application agent on the SQL Server host.

Upgrade the Microsoft application agent on page 20 provides information.

2. Run the backup discovery tool, AgentBackupDiscovery.exe, to enable management of existing Microsoft application agent backups with PowerProtect Data Manager.

Using the backup discovery tool for PowerProtect Data Manager management of existing backups on page 26 provides information.

Note: This step enables the discovery of old backup copies that the Microsoft application agent created during self-service backups with stand-alone deployments.

3. Register and approve the Microsoft application agent in PowerProtect Data Manager.

Manage the Microsoft application agent for SQL on page 23 provides information.

After a few minutes of approving the SQL host, older backup copies are discovered. Depending on the number of backups, the discovery and subsequent visibility of the backups

Enabling the Microsoft Application Agent for SQL

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 25

in PowerProtect Data Manager can take some time. The retention time of the discovered existing backup copies equals the retention time that was set in the protection policy plus 14 days, rounded off to the next day.

4. Discover and add the credentials for the SQL application host.

Discover a SQL application host on page 34 provides information.

5. Create a protection policy to protect the SQL host. For onboarding assets, only a subset of databases can be onboarded. It is not mandatory for all the databases on the host to be onboarded.

Add a protection policy for SQL database protection on page 35 provides information.

The first backup after onboarding must be a full backup:

l The first centralized backup is automatically promoted to a full backup.

l The first self-service backup is automatically performed as a full backup.

Note: You cannot perform a backup to a secondary DD system device. You can restore only from a secondary DD system device.

6. Perform a self-service backup of the Microsoft SQL databases. Onboarded assets can be part of either a centralized or self-service protection policy.

Performing self-service backups of Microsoft SQL databases on page 44 provides information.

Using the backup discovery tool for PowerProtect Data Manager management of existing backups

To enable the PowerProtect Data Manager management of existing backups after you have upgraded from a previous version of Microsoft application agent or onboarded the current version, you must run the backup discovery tool, AgentBackupDiscovery.exe. Existing backups are Microsoft application agent backups that you performed before integrating the Microsoft application agent with the PowerProtect Data Manager software.

At the end of an upgrade of the Microsoft application agent with the installer, the wizard displays a message about running the AgentBackupDiscovery.exe tool to discover existing backups and manage them in PowerProtect Data Manager software.

CAUTION Onboarding of SQL backup copies to PowerProtect Data Manager is supported only from backups performed with Microsoft application agent 4.7 and later.

Retention lock is not supported for discovered existing backups in PowerProtect Data Manager.

You cannot use the backup discovery tool to discover existing DD Boost-over-FC backups.

If you delete a SQL database before onboarding but the database backup copies exist on the Data Domain system, then after onboarding, PowerProtect Data Manager will not manage those backup copies.

After you run the backup discovery tool, you can continue to use the existing backup scripts to perform the Microsoft application agent backups. Ensure that all the databases backed up with a particular script are added to a single protection policy. By default, the PowerProtect Data Manager overrides the Data Domain details by using the storage unit from the protection policy. If you do not want the Data Domain details to be overridden, use the -a "SKIP_DD_OVERRIDE=TRUE" option in the backup scripts.

To discover the existing backups by using the backup discovery tool, perform the following steps.

Enabling the Microsoft Application Agent for SQL

26 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

1. In the Microsoft application agent installation directory, C:\Program Files\DPSAPPS \MSAPPAGENT\bin, run AgentBackupDiscovery.exe as the administrator.

The Discovery of existing backups dialog box appears.

Note: If the program does not start but displays the following message, an ongoing backup discovery process is running, as invoked by the PowerProtect Data Manager:

Backup discovery is in progress. Please wait for it to complete.

When the discovery process is complete, you can run the backup discovery tool.

2. In the Data Domain system list in the dialog box, select the appropriate Data Domain IP address or hostname, storage unit, and username for the existing backups that you want the PowerProtect Data Manager software to discover.

Note: Select only one storage unit at a time. After discovery is complete for the storage unit, you can run the backup discovery tool again to discover the backups of another storage unit.

3. In the Client hostname field, you can change the client hostname from the default local hostname as needed.

To enable the backup discovery for an AAG or FCI, you must specify the appropriate client hostname:

l If the host is part of an AAG, specify the Windows cluster name.

l If the host is part of a SQL virtual server or FCI, specify the virtual server name.

4. In the Backup discovery time period field, select the number of months for the time period, as the time in the past when the backups were performed. You can select 1 month, 2 months, or 3 months for the time period.

5. After you have specified the required field values, click Generate.

When the PowerProtect Data Manager software completes the generation of the backup metadata or breadcrumbs, the following message appears in the dialog box. Depending on the number of old backups, the generation of breadcrumbs can take some time:

Breadcrumbs generated successfully. The retention time for the discovered backup will be same as the expiration time set when the backup was taken.

Microsoft application agent for SQL Server application-aware protection

The Microsoft application agent is a component of the PowerProtect Data Manager data protection solution for VMware virtual machines.

A PowerProtect Data Manager application-aware VM protection policy uses the Microsoft application agent to provide advanced application-consistent protection for the following SQL workloads:

l SQL Server full backup to Data DomainConfigure a PowerProtect Data Manager protection policy with the application-aware option to perform a SQL Server backup to a Data Domain device as part of a VMware image-level backup. A SQL Server full backup is performed during the in-guest quiesce by VMware Tools. When the backup is performed as part of the VMware image-level backup, the SQL data files are backed up as part of the VMDKs during the VM Direct backup. After completing the backup, the Microsoft application agent is automatically run on the virtual machine to catalog the SQL server backup on the Data Domain associated with the protection policy.

Enabling the Microsoft Application Agent for SQL

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 27

l Transaction log backupWhen configuring a PowerProtect Data Manager protection policy with the Application Aware option, set an interval for Transaction log backup to enable transaction log backups for SQL instances running on the virtual machine, and specify the frequency of backups. The Microsoft application agent is run on the virtual machine to perform the transaction log backup. Backups are written directly to the Data Domain associated with the protection policy. A transaction log backup is only performed for databases in the proper state; otherwise, databases are skipped.

l Database restore, flat file restore, table-level restore, or database Instant Access restore to the source virtual machine or an alternate virtual machine. To perform restores to an alternate virtual machine, that virtual machine must be an asset of PowerProtect Data Manager. However, instance-level restores can only be performed to the original source instance. For more details on how to use Microsoft application agent to restore SQL databases backed up with an application-aware VM protection policy, see the PowerProtect Microsoft Application Agent SQL Server User Guide.

The Microsoft application agent software package is bundled with the PowerProtect Data Manager appliance, and is automatically configured on a virtual machine when you add the virtual machine asset to a VM application-aware protection policy. As part of the VM protection policy configuration, both the VM Direct Agent and the Microsoft application agent are installed on the virtual machine. The Microsoft application agent installation includes the software components required for self-service restore, including the SQL Server Management Studio Microsoft App Agent plug-in and ItemPoint. After the agent installations, configuration information for the Data Domain is also sent to the virtual machine, calling the Microsoft application agent to perform the lockbox configuration. Subsequent protection policy backups and self-service restore operations jobs will also use this information without any further action required. During application-aware SQL Server full backups and transaction log backups, PowerProtect Data Manager upgrades the VM Direct Agent and Microsoft application agent software packages as required.

The virtual machine credentials provided in the protection policy or within the virtual machine asset are used during Microsoft application agent installation and during SQL Server full and transaction log backups. The Microsoft application agent is first called to validate the virtual machine SQL configuration. The agent verifies that the SQL Server is installed and running, and that the provided virtual machine credentials have the necessary permissions to perform an SQL Server backup.

In order to perform SQL Server application-consistent data protection for virtual machines, the Microsoft application agent requires the following:

l The Microsoft application agent runs under the virtual machine credentials provided in the VM protection policy or virtual machine asset for installation and data protection operations. Configure all SQL Server instances on the virtual machine to grant account rights for this account to perform SQL database backup and recovery operations:

n Add the account to SQL logins.

n Grant the account the sysadmin role.

Note: The Microsoft application agent supports only the following accounts for the virtual machine credentials:

n Local Administrator account for the virtual machine.

n Domain Administrator account.

l Network connectivity, hostname resolution, and firewall ports between the Data Domain device and the virtual machines that are part of SQL Server application-consistent protection policies and restore to alternate operations. This connectivity is required to enable the Microsoft application agent to perform client direct operations to Data Domain.

Enabling the Microsoft Application Agent for SQL

28 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

l VMware vCenter server version 6.5 or later.

l VMware ESXi server version 6.5 or later.

l VMware Tools version 10.1 or later.

l Enable the UUID attribute (disk.EnableUUID=TRUE) in the vSphere Client.

Note: After you set disk.EnableUUID to TRUE, ensure that you reboot the virtual machine.

l The virtual machine must use SCSI disks only, and the number of available SCSI slots must at least match the number of disks. For example, a virtual machine with 7 disks will only require one SCSI controller, but a virtual machine with 8 disks will require 2 SCSI controllers.

l The VM Direct Engine requires live network connectivity to the ESXi where the targeted SQL virtual machine resides.

Troubleshooting self-service T-SQL backups with an IO error message

When the Microsoft application agent is integrated with the PowerProtect Data Manager, a successful self-service Transact-SQL (T-SQL) backup might display the following error message:

IO error: Permission denied

To prevent this error message during self-service T-SQL backups, ensure the that SQL instance service runs as the OS/SQL user, instead of NT SERVICE\ .

Enabling the Microsoft Application Agent for SQL

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 29

Enabling the Microsoft Application Agent for SQL

30 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

CHAPTER 3

Managing Storage, Assets, and Protection

This chapter includes the following topics:

l Add protection storage .........................................................................................................32 l Setting the sysadmin privilege for Microsoft SQL hosts........................................................33 l Discover a SQL application host............................................................................................ 34 l Add a protection policy for SQL database protection............................................................ 35 l Manage the PowerProtect agent service.............................................................................. 38

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 31

Add protection storage About this task

The PowerProtect Data Manager UI enables users with administrator credentials to add the following storage types:

l DD Management Center

l External DD system

Note: References to Data Domain systems in this documentation, in the UI, and elsewhere in the product include PowerProtect DD systems and older Data Domain systems.

Procedure

1. Select Infrastructure > Storage.

The Storage window appears.

2. In the Protection Storage tab, click Add.

3. In the Add Storage dialog box, select a storage system (DD System, DD Management Center).

Note: If using the Storage Direct agent to move snapshot backups from a VMAX storage array to a DD system, you do not need to add a DD Management Center.

4. Specify the storage system attributes:

a. In the Name field, specify a storage name.

b. In the Address field, specify the hostname, fully qualified domain name (FQDN), or the IP address.

If you specify a virtual machine for the storage name, use the FQDN.

c. In the Port field, specify the port for SSL communication. Default is 3009.

5. Under Host Credentials click Add, if you have already configured DD credentials that are common across DD systems, select an existing password from the Select Keychain list. Alternatively, you can add new credentials, and then click Save .

6. If a trusted certificate does not exist on the storage system, a dialog box appears requesting certificate approval. Click Verify to review the certificate, and then click Accept.

7. Click Save to exit the Add Storage dialog and initiate the discovery of the storage system.

A dialog box appears to indicate that the request to add storage has been initiated.

8. In the Storage window, click Discover to refresh the window with any newly discovered storage systems.

When a discovery completes successfully, the Status column updates to OK.

9. To modify a storage system location:

a. In the Storage window, select the storage system from the table.

b. Click Set Location.

The Set Location window appears.

c. Click Location > Add.

The Add Location window appears.

Managing Storage, Assets, and Protection

32 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

d. In the Name field, type a location name for the asset, and click Save.

10. To manage MTrees in the Storage window, select the storage system from the table and click View storage unit.

Results

PowerProtect Data Manager displays External DD systems only in the Storage window Name column. PowerProtect Data Manager displays DD Management Center storage types in the Managed By column.

Troubleshooting protection policy for DD storage unit

When adding a protection policy in PowerProtect Data Manager, creation of a storage unit on the selected DD system fails if you reach the maximum MTree count on the DD system. PowerProtect Data Manager enables you to finish adding the protection policy without the storage unit. However, if you subsequently run a backup of this protection policy, the backup process is suspended indefinitely with no error message.

To continue backup operations on this device, you must perform a cleanup on the DD system.

Viewing the DD Boost storage unit password PowerProtect Data Manager provides a script to retrieve the password of a DD Boost unit that is configured as a backup target.

Before you begin

This process requires the name of the DD MTree where the DD Boost storage unit resides.

Procedure

1. SSH to the PowerProtect Data Manager appliance as the admin user.

2. Navigate to the /usr/local/brs/puppet/scripts directory.

3. Obtain the DD Boost storage unit password by typing the following command:

./get_dd_mtree_credential.py PLC-PROTECTION-1551667983302

Setting the sysadmin privilege for Microsoft SQL hosts To enable the integration with PowerProtect Data Manager on each Microsoft SQL host, the NT AUTHORITY\SYSTEM account on each host requires the sysadmin privilege.

About this task

Before you register any SQL host with PowerProtect Data Manager, complete the following steps on each SQL host to set the required sysadmin privilege.

Procedure

1. Log in to each SQL instance, open the SQL Server Management Studio (SSMS), and select View > Object Explorer.

2. In the Object Explorer, expand Security and then expand Logins.

3. Right-click NT AUTHORITY\SYSTEM, and then select Properties.

4. In the Login properties window, select Server Roles.

5. Select sysadmin under Server roles.

6. Click OK.

Managing Storage, Assets, and Protection

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 33

The Login properties window closes.

Discover a SQL application host After you register an application host with PowerProtect Data Manager, the host appears in the Asset Sources window. Then you can select the host, perform discovery, and modify the application host credentials. For application hosts, discovery is required if you want to schedule a backup. Assets must also be discovered for the centralized copy management of self-service backups.

About this task

Perform the following steps to discover a SQL application host as an asset source in the PowerProtect Data Manager UI.

Procedure

1. Select Infrastructure > Asset Sources.

The Asset Sources window appears.

2. Select the App/File System Host tab.

3. If you are adding a SQL database, select the host entry and click Edit Credentials.

The Edit Credentials dialog appears.

Note: The supported syntax for credentials is @ or @ . The syntax \ or \ is not supported.

4. If you are adding credentials for a SQL database, ensure that you specify the OS credentials for the SQL host. Ensure that these credentials have the rights to perform the Microsoft SQL Server backup and restore operations.

5. Click Save.

6. On the App/File System Host tab, select the application host and click Discover to perform discovery.

The Discover dialog appears with an option to set the discovery schedule.

7. From the Discovery Schedule list, select the time of day to initiate the discovery, or select Manual to disable scheduled discovery. You can also select the Discover Now check box to perform the discovery upon completion of this procedure.

Note: On the App/File System Host tab, you can click Discover at any time if any additions or other changes to your Asset Sources have taken place outside of the PowerProtect Data Manager environment. Asset discovery is also initiated by default after registration of the host to PowerProtect Data Manager and at hourly intervals. Discovery time is based on networking bandwidth. Each time you initiate a discovery process, the resources that are discovered and those that are handling the discovery impact the system performance.

8. Click Save.

Results

If the application host is properly configured and discovery is successful, the database assets can now be added to a PowerProtect Data Manager protection policy.

Managing Storage, Assets, and Protection

34 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

Add a protection policy for SQL database protection Use the PowerProtect Data Manager UI to add a protection policy group for the purposes of SQL database protection.

About this task

Note: If a database is protected in an Always On availability group, you cannot configure stand- alone backups of that database in a protection policy group.

Procedure

1. Select Protection > Protection Policy.

The Protection Policy window appears.

2. Click Add.

The Add Policy wizard appears.

3. On the Type page, specify the new protection policies group fields. For example, if you are creating a protection policy for daily backups in the SQL production environment:

a. In the Name field, specify the name of the protection policy. For example, specify SQL Prod Databases.

Note: The name that you specify becomes part of the Data Domain MTree entry.

b. In the Description field, specify a short description of the protection policy. For example, specify SQL Prod Daily Backups.

c. In theType field, select Microsoft SQL database.

d. Click Next.

The Purpose page appears.

4. On the Purpose page, specify the following fields to indicate the purpose of the new protection policy group:

a. Select the type of protection policies group. For a SQL database, you can select from three types:

l To use PowerProtect Data Manager to manage all protection centrally, click Centralized Protection.

Centralized protection means that PowerProtect Data Manager will schedule the backups and manage the life cycle of the copies.

l To use SQL to create local backup protection, click Self-Service Protection. PowerProtect Data Manager creates a protection policy and manages extra stages.

Self-service protection means that DBAs will schedule that backups but PowerProtect Data Manager will discover and manage the life cycle of the copies.

l If there are SQL assets within the protection policy that you plan to exclude from data protection operations, click Exclusion.

b. To specify the credentials, click Set Credentials. You can specify new credentials or select existing credentials from the list.

Note:

Managing Storage, Assets, and Protection

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 35

The supported syntax for credentials is @ or @ . The syntax \ or \ is not supported.

The host-level credentials take precedence over protection policy-level credentials.

c. Click Save.

d. Click Next.

The Assets page appears.

5. Select the unprotected assets that you want to add to the backup of this protection policy group. The window enables you to filter by asset name to locate the required assets.

Note: A SQL database asset can be protected by only one protection policy at a time.

6. Click Next.

If you selected Exclusion in the Purpose page, the Summary page appears. Proceed to the final two steps.

If you selected Centralized Protection or Self-Service Protection, the Schedule page appears.

7. Click + Backup.

The Add Primary Backup dialog box appears.

8. Specify the backup schedule fields:

l For centralized protection:

a. In the Recurrence field, select the interval at which the backup job runs within the window that you specify.

Recurrence relates to Start Time and End Time fields.

When you select Hourly, Daily,Weekly, and Monthly recurrence, you are selecting the interval at which the backup job runs within the window that you specify.

b. In the Create Full field, specify the interval in hours to create a full backup. The interval should be between 1 and 12 hours.

c. To create an incremental differential backup, click Differential, and then specify the interval in minutes.

d. To create a log, click Log, and then specify the interval in minutes.

e. In the Keep For field, specify the retention time.

f. In the Start Time field, specify the time when new backups are initiated in this policy.

g. In the End Time field, specify the time after which no new backups are initiated in this policy. It does not mean that any policy that is running is stopped.

h. Click OK. Note: When a new asset is added to a protection policy during a scheduled backup window, the backup starts right away. However, if an asset is added to a protection policy outside of the scheduled backup window, the backup does not start until the next time that backups are configured to run.

If a new asset is added to a protection policy that has a weekly or monthly backup schedule and the current time is within the scheduled Start Time and End Time, the backup runs right away, regardless of the date. If the current time is not within the scheduled Start Time and End Time, the backup does not start until the next time that backups are configured to run.

Managing Storage, Assets, and Protection

36 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

The Schedule page updates with the newly added backup schedule.

l For self-service protection:

a. In the Keep For field, specify the retention time.

b. Click OK.

After you complete a backup schedule, you can change any schedule details by selecting the check box next to the added schedule and clicking Edit.

9. If you want a differential or log backup for a specific asset to be promoted to a full backup when Microsoft application agent cannot perform the differential or log backup, turn on auto promotion:

Note: If auto promotion is disabled, the differential or log backup is skipped for the asset.

a. Select the check box next to the added schedule, and click + Backup.

b. Complete the schedule details in the Add Promotion Backup dialog box, and then click OK.

10. To replicate these backups to a remote Data Domain system:

a. Select the check box next to the added schedule, and click Replicate.

b. Complete the schedule details in the Add Primary Replication dialog box, and then click OK.

Note: To enable replication, ensure that you add a remote Data Domain system as the replication location.

11. Select the check box next to the added schedule.

When you select the check box, the SLA, Storage Name, and Network interface lists are enabled for selection.

12. From the SLA list, select an existing service level agreement that you want to apply to this schedule, or select Add to create a SLA within the Add Backup Service Level Agreement window.

13. From the Storage Name list:

l Select the backup destination from the list of existing Data Domain systems.

l To add a system, select Add, and complete the details in the Storage Target window.

When you select the destination storage, the Space field updates with the available capacity on the system.

14. From the Network interface list, select a network interface card (NIC), if applicable.

15. Click Set Storage Quotas to set storage space restrictions for a Data Domain MTree or storage unit to prevent the consumption of excess space. There are two kinds of quota limits, hard limits and soft limits. You can set either a soft or hard limit or both a soft and hard limit. Both values must be integers, and the soft value must be less than the hard value.

Note: When you set a soft limit and the limit is reached, an alert is generated but data can still be written to the Data Domain. When you set a hard limit and the limit is reached, data cannot be written to the MTree. Therefore, all data protection operations fail until data is deleted from the MTree. The Data Domain Operating System Administration Guide provides more information about MTree quota configuration.

a. Capacity QuotaSpecifies the total size of pre-compression data written to the Data Domain.

Managing Storage, Assets, and Protection

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 37

b. Stream QuotaSpecifies the number of concurrent streams allowed on the system during data protection operations. Setting a Stream Quota limit can help ensure that system performance is not impacted negatively if a data protection operation consumes too many system resources.

16. Select the Retention Lock check box to enable retention locking for these backups on the selected system.

Note: Primary backups are assigned a default retention lock period of 14 days. Replicated backups are not assigned a default retention lock period. If you select this check box for a replicated backup, ensure that you set the Keep For field in the Add Primary Replicate backup schedule dialog box to a minimum number of 14 days so that the replicated backup does not expire before the primary backup.

17. Click Next.

The Summary page appears.

18. Review the protection policy group configuration details. You can click Edit next to any completed window's details to change any information. When completed, click Finish.

An informational message appears to confirm that PowerProtect Data Manager has saved the protection policy. When a new protection policy is created, PowerProtect Data Manager performs the first full backup and subsequent backups according to the specified schedule.

19. Click OK to exit the window, or click Go to Jobs to open the Jobs window to monitor the backup of the new protection policy group.

Manage the PowerProtect agent service The PowerProtect agent service provides important functionality for the application agent operations with the PowerProtect Data Manager.

Review the following topics to ensure that you enable and manage the PowerProtect agent service functionality as required for application agent operations.

About the PowerProtect agent service The PowerProtect agent service is a REST API based service that is installed by the application agent on the application host. The agent service provides services and APIs for discovery, protection, restore, instant access, and other related operations. The PowerProtect Data Manager uses the agent service to provide integrated data protection for the application assets.

This section uses to represent the PowerProtect agent service installation directory. By default, the agent service installation location is C:\Program Files\DPSAPPS\AgentService on Windows and /opt/dpsapps/ agentsvc on Linux. All files that are referenced in this section are the relative paths to the agent service installation location.

The PowerProtect agent service performs the following operations:

l Addon detectionAn addon integrates the application agent into the agent service. The agent service automatically detects the addons on the system for each application asset type and notifies the PowerProtect Data Manager. While multiple addons can operate with different asset types, only one agent service runs on the application host. Specific asset types can coexist on the same application host.

l DiscoveryThe agent service discovers both stand-alone and clustered database servers (application systems), databases and file systems (assets), and their backup copies on the application agent host. After the initial discovery, when the agent service discovers any new

Managing Storage, Assets, and Protection

38 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

application systems, assets, or copies, the agent service notifies the PowerProtect Data Manager.

l Self-service configurationThe agent service can configure the application agent for self- service operations by using information that is provided by the PowerProtect Data Manager. When you add an asset to a protection policy for self-service or centralized protection, or modify the protection policy, including changing the DD Boost credentials, the PowerProtect Data Manager automatically pushes the protection configuration to the agents.

l Centralized backupsThe agent service performs the centralized backups as requested by the PowerProtect Data Manager.

l Centralized restoresThe agent service performs the centralized restores as requested by the PowerProtect Data Manager.

Note: In the current release, the centralized restores are only available for the File System agent and Storage Direct agent.

l Backup deletion and catalog cleanupThe PowerProtect Data Manager deletes the backup files directly from the protection storage when a backup expires or an explicit delete request is received and no dependent (incremental or log) backups exist. The PowerProtect Data Manager goes through the agent service to perform the following operations during deletion:

n Deletion of the catalog entries from the database vendor's catalog and the agent's local datastore.

n Deletion of the older backups that the stand-alone application agent created, before the asset was added to PowerProtect Data Manager.

The agent service is started during the agent installation by the installer. The agent service runs in the background as a service and you do not interact with it directly.

The config.yml file contains the configuration information for the agent service, including several parameter settings that you can change within the file. The config.yml file is located in the directory.

The agent service periodically starts subprocesses to perform the discovery jobs. You can see the type and frequency of these jobs in the jobs: section of the config.yml file. The job interval unit is minutes.

The agent service maintains a datastore in the /dbs/v1 directory, which contains information about the application system, assets, and backups discovered on the system. The size of the datastore files depends on the number of applications and copies on the host. The agent service periodically creates a backup of its datastore in the /dbs/v1/backups directory, as used to recover the datastore if this datastore is lost.

Note: The size of each datastore backup is the same as the datastore itself. By default, a backup is created every hour. To save space on the file system, you can reduce this datastore backup frequency for large datastores. By default, the datastore backup is retained for one week. You can change the datastore backup frequency, retention period, and backup location in the config.yml file.

Start, stop, or obtain the status of the PowerProtect agent service The PowerProtect agent service is started during the agent installation by the installer. If needed, you can use the appropriate procedure to start, stop, or obtain the status of the agent service.

On Windows, you can start, stop, or obtain the status of the PowerProtect agent service from the Services Manager, similar to other Windows services. The name of the service in the Services Manager is PowerProtect Agent Service.

Managing Storage, Assets, and Protection

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 39

Troubleshoot the PowerProtect agent service installation The PowerProtect agent service installation might fail with the following error message:

Service 'PowerProtect Agent Service' (AgentService) could not be installed. Verify that you have sufficient privileges to install system services.

Possible causes of the installation failure are as follows:

l The installation was attempted on a passive node of a Failover Cluster Instance (FCI).

l The installation was canceled and a rollback left some stale entries of PowerProtect agent services.

As a workaround, clean up the PowerProtect agent service entries, and retry the installation.

Troubleshoot the PowerProtect agent service operations To troubleshoot the agent service operations, you can check the agent service log file agentsvc.log, which is created in the agent service home directory. To modify the log level and retention of temporary files, you can modify specific parameter settings in the config.yml file.

About this task

To modify the log level and retention of temporary files, you can perform the following steps.

Procedure

1. Stop the agent service by using the appropriate procedure from the preceding topic.

2. Open the config.yml file in an editor.

3. Modify the log-level settings in the following parameters, as required:

Note: These parameters are listed in order of decreasing number of messages in the debug information output. The default log-level is INFO.

l DEBUG l INFO l WARNING l ERROR l CRITICAL

4. To retain the temporary files, set the keepTempFiles parameter to True in the config.yml file.

Note: The agent service and application agent communicate through the temporary files, which are typically deleted after use but can be useful for troubleshooting purposes. Do not leave the keepTempFiles parameter set to True permanently, or the temporary files can use excessive space on the file system.

5. Start the agent service by using the appropriate procedure from the preceding topic.

Managing Storage, Assets, and Protection

40 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

Register the PowerProtect agent service to a different server address The PowerProtect agent service is registered to a particular PowerProtect Data Manager server during the agent installation by the installer. If needed, you can register the agent service to a different PowerProtect Data Manager server address.

The agent service can only be registered to a single PowerProtect Data Manager server. When you register the agent service to a new server, the agent service will automatically unregister from the previous server address.

On Windows, you can change the PowerProtect Data Manager server address by launching the agent installer and selecting the change option. Change the PowerProtect Data Manager service address from the Configuration Install Options page.

Recovering the PowerProtect agent service from a disaster You can perform self-service restores of application assets by using a file system or application agent, regardless of the state of the agent service or PowerProtect Data Manager. The information in the this section describes how to bring the agent service to an operational state to continue if a disaster occurs and the agent service datastore is lost.

The agent service periodically creates a backup of its datastore in the /dbs/v1/backups repository. If all these backups are lost, the agent service can still start. The agent service discovers all the application systems, assets, and backup copies on the system again, and notifies PowerProtect Data Manager. Depending on when the failure occurred, the agent service might not be able to find older backup copies for some asset types. As a result, the centralized deletion operations might fail when cleaning up the database vendor catalog or removing older backups that are taken before the asset is added to PowerProtect Data Manager.

By default, the agent service backs up consistent copies of its datastore files to the local disk every hour and keeps the copies for 7 days. Each time the agent service backs up the contents of the datastore, it creates a subdirectory under the /dbs/v1/backups repository. The subdirectories are named after the time the operation occurred, in the format YYYY-MM-DD_HH- MM-SS_epochTime.

By default, the datastore repository is on the local disk. To ensure that the agent service datastore and its local backups are not lost, it is recommended that you back up the datastore through file system backups. You can also change the datastore backup location to a different location that is not local to the system. To change the datastore backup location, update the values in the config.yml file.

Restore the PowerProtect Data Manager agent service datastore

Before you begin

Note: Ensure that the agent service is powered off. Do not start the agent service until disaster recovery is complete.

About this task

You can restore the datastore from the datastore backup repository. If the repository is no longer on the local disk, restore the datastore from file system backups first.

To restore the datastore from a backup in the datastore backup repository, complete the following steps:

Managing Storage, Assets, and Protection

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 41

Procedure

1. Move the files in the /dbs/v1 directory to a location for safe keeping.

Note: Do not move or delete any /dbs/v1 subdirectories.

2. Select the most recent datastore backup.

The directories in the datastore backup repository are named after the time the backup was created.

3. Copy the contents of the datastore backup directory to the /dbs/v1 directory.

After the copy operation is complete, the /dbs/v1 directory should contain the following files:

l copies.db l objects.db l resources.db l sessions.db

4. Start the agent service.

Managing Storage, Assets, and Protection

42 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

CHAPTER 4

Performing Self-Service Backups and Restores of Microsoft SQL Databases

This chapter includes the following topics:

l Performing self-service backups of Microsoft SQL databases.............................................. 44 l Restore a SQL application host............................................................................................. 44

PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide 43

Performing self-service backups of Microsoft SQL databases To enable self-service protection, when you create the SQL protection policy, select Self-Service Protection.

When performing a self-service stand-alone backup of an AAG asset, the backups display under the AAG asset.

Chapter 4, Backing Up SQL Server with Application Direct, in the PowerProtect Microsoft Application Agent SQL Server User Guide provides instructions on how to perform self-service SQL Server backups.

Restore a SQL application host You can perform database or table-level restores directly to the SQL application host using the Microsoft application agent.

Chapter 6, Restoring Application Direct Backups, in the PowerProtect Microsoft Application Agent SQL Server User Guide provides instructions on how to restore an application-aware SQL Server backup.

Performing Self-Service Backups and Restores of Microsoft SQL Databases

44 PowerProtect Data Manager for Microsoft Application Agent SQL Server User Guide

Manualsnet FAQs

If you want to find out how the 19.4 Dell works, you can view and download the Dell PowerProtect 19.4 Data Manager SQL Server User Guide on the Manualsnet website.

Yes, we have the SQL Server User Guide for Dell 19.4 as well as other Dell manuals. All you need to do is to use our search bar and find the user manual that you are looking for.

The SQL Server User Guide should include all the details that are needed to use a Dell 19.4. Full manuals and user guide PDFs can be downloaded from Manualsnet.com.

The best way to navigate the Dell PowerProtect 19.4 Data Manager SQL Server User Guide is by checking the Table of Contents at the top of the page where available. This allows you to navigate a manual by jumping to the section you are looking for.

This Dell PowerProtect 19.4 Data Manager SQL Server User Guide consists of sections like Table of Contents, to name a few. For easier navigation, use the Table of Contents in the upper left corner.

You can download Dell PowerProtect 19.4 Data Manager SQL Server User Guide free of charge simply by clicking the “download” button in the upper right corner of any manuals page. This feature allows you to download any manual in a couple of seconds and is generally in PDF format. You can also save a manual for later by adding it to your saved documents in the user profile.

To be able to print Dell PowerProtect 19.4 Data Manager SQL Server User Guide, simply download the document to your computer. Once downloaded, open the PDF file and print the Dell PowerProtect 19.4 Data Manager SQL Server User Guide as you would any other document. This can usually be achieved by clicking on “File” and then “Print” from the menu bar.