Dell Avamar for SQL Server User Manual
Dell EMC Avamar for SQL Server
Version 19.2
User Guide
302-005-838
REV 01
November 2019
Copyright © 2001-2019 Dell Inc. or its subsidiaries. All rights reserved.
Dell believes the information in this publication is accurate as of its publication date. The information is subject to change without notice.
THE INFORMATION IN THIS PUBLICATION IS PROVIDED “AS-IS.” DELL MAKES NO REPRESENTATIONS OR WARRANTIES OF ANY KIND
WITH RESPECT TO THE INFORMATION IN THIS PUBLICATION, AND SPECIFICALLY DISCLAIMS IMPLIED WARRANTIES OF
MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. USE, COPYING, AND DISTRIBUTION OF ANY DELL SOFTWARE DESCRIBED
IN THIS PUBLICATION REQUIRES AN APPLICABLE SOFTWARE LICENSE.
Dell Technologies, Dell, EMC, Dell EMC and other trademarks are trademarks of Dell Inc. or its subsidiaries. Other trademarks may be the property
of their respective owners. Published in the USA.
Dell EMC
Hopkinton, Massachusetts 01748-9103
1-508-435-1000 In North America 1-866-464-7381
www.DellEMC.com
2 Dell EMC Avamar for SQL Server User Guide
CONTENTS
Preface
Chapter 1
7
Introduction 11
Architecture...................................................................................................... 12
Avamar components............................................................................. 12
How Avamar connects to SQL Server...................................................13
Failover Cluster Instances.....................................................................13
AlwaysOn availability groups.................................................................16
Database mirroring............................................................................... 21
Data Domain system support................................................................ 21
Server virtualization............................................................................. 22
Backup..............................................................................................................22
Backup types....................................................................................... 22
Backups in AlwaysOn availability groups.............................................. 25
Mixed backup storage..........................................................................25
Multistreaming.....................................................................................26
Database log truncation....................................................................... 26
Backups with other tools......................................................................26
Restore............................................................................................................. 27
Restore to the original location............................................................ 27
Restore to a new database in the original instance...............................27
Restore to a different instance on the original server...........................27
Restore to an instance on a different server........................................ 28
Restore to a file....................................................................................28
Restore to an AlwaysOn availability group............................................28
Restore of a database with an intact log file........................................ 30
Restore of system databases............................................................... 30
Tail-log backup..................................................................................... 31
Point-in-time restore............................................................................ 31
SQL Server recovery operations.......................................................... 32
Year 2038.............................................................................................33
Table level recovery.......................................................................................... 33
Disaster recovery..............................................................................................33
Chapter 2
Installation 35
Preparing to install the SQL Server plug-in.......................................................36
System requirements........................................................................... 36
Preparing a stand-alone server............................................................. 41
Preparing a cluster................................................................................41
Downloading the software....................................................................42
Verifying the environment....................................................................43
Installing the Avamar Plug-in for SQL Server....................................................44
Installing the Avamar client software................................................................ 45
Installation road maps.......................................................................... 45
Installing the Avamar Client for Windows.............................................45
Installing the Avamar Plug-in for SQL Server....................................... 46
Silent installation of Itempoint for SQL.................................................47
Dell EMC Avamar for SQL Server User Guide 3
Contents
Registering the client........................................................................... 47
Configuring the cluster client in a failover cluster................................ 48
Configuring the cluster client for an AlwaysOn availability group......... 49
Configuring the cluster client for an AlwaysOn availability group on
Amazon Web Services and Azure......................................................... 51
Upgrading the Avamar client software..............................................................53
Upgrading on a stand-alone server.......................................................53
Upgrading in a cluster.......................................................................... 53
Uninstalling the Avamar client software............................................................54
Uninstall road map............................................................................... 54
Uninstalling the cluster client in a failover cluster................................ 55
Uninstalling the cluster client for an AlwaysOn availability group......... 55
Chapter 3
Chapter 4
Backup 57
Perform an on-demand SQL backup by using AUI ............................................58
Configure the SQL server policy....................................................................... 59
Scheduling backups using the AUI Policy wizard...............................................60
Creating a dataset................................................................................ 61
Creating a backup policy...................................................................... 62
Enabling a scheduled backup for a backup policy................................. 62
Monitoring backups.......................................................................................... 62
Cancel backups.................................................................................................63
Verifying backups............................................................................................. 63
Enforcement of backups to Data Domain..........................................................64
Application Consistent SQL Virtual Machine Image Backup 65
Managing advanced policies............................................................................. 66
Prerequisites.....................................................................................................66
Add an advanced group policy.......................................................................... 66
Configure a source............................................................................... 67
Configure the SQL server policy.......................................................... 67
Configure members..............................................................................68
Configure a proxy for the image backup.............................................. 69
Remove an advanced policy..............................................................................69
Edit an advanced policy.................................................................................... 69
View advanced policy details.............................................................................70
Migrate existing Backup Policy to an Advanced Policy......................................70
View logs...........................................................................................................70
Chapter 5
4 Dell EMC Avamar for SQL Server User Guide
Restore 73
Restore requirements........................................................................................74
Determine the restore size for an SQL server database.................................... 76
Restore features available by using the AUI...................................................... 77
Software requirements for restore.......................................................74
Tail-log backup and point-in-time restore requirements.......................75
Requirements to restore secondary database files............................... 75
Requirements to restore the Report Server database.......................... 76
SQL Server write permission requirements.......................................... 76
Determine the space required to restore the system database.............76
Determine the space required to restore a single SQL server database....
76
Determine the space required to restore multiple SQL server databases
.............................................................................................................77
Restoring a database to the original client............................................77
Contents
Restoring a database to a different client or instance.......................... 78
Restoring to a new database in the original instance............................80
Restore SQL instance or database to a file.......................................... 82
Table Level restore...............................................................................84
Restore features available by using Avamar Administrator................................86
Finding a backup.................................................................................. 86
Restoring to the original location......................................................... 89
Restoring to a file................................................................................. 91
Restoring system databases.................................................................97
Restoring to an AlwaysOn availability group....................................... 103
Restoring a database with an intact log file........................................ 106
Setting restore options....................................................................... 107
Restore only on primary replica........................................................... 116
Monitor restores.............................................................................................. 116
Cancel restores................................................................................................ 117
Chapter 6
Appendix A
Appendix B
Disaster Recovery 119
Preparing for disaster recovery....................................................................... 120
Performing disaster recovery.......................................................................... 120
SQL Plug-in Options 121
How to set plug-in options...............................................................................122
SQL plug-in backup options.............................................................................122
Backup options................................................................................................126
SQL plug-in restore options.............................................................................132
SQL TLR plug-in options..................................................................................137
SQL TLR plug-in browse options.........................................................137
SQL TLR plug-in restore options.........................................................137
Command Line Interface 139
Overview of the SQL Server plug-in CLI..........................................................140
Command reference........................................................................................ 140
Synopsis............................................................................................. 140
Operations..........................................................................................140
Options...............................................................................................142
Specifying command line options.................................................................... 158
Password encoding......................................................................................... 159
Performing command line operations in a failover cluster................................159
Performing command line operations in an AlwaysOn environment................. 159
CLI examples................................................................................................... 160
Example browse commands................................................................160
Example backup commands................................................................ 161
Example restore commands................................................................164
avsqltlr command reference............................................................................ 170
avsqltlr synopsis................................................................................. 170
avsqltlr options................................................................................... 170
Monitoring CLI activities..................................................................................170
Appendix C
Troubleshooting 171
Backup problems and solutions........................................................................172
Restore problems and solutions....................................................................... 173
Upgrade problems and solutions...................................................................... 173
Dell EMC Avamar for SQL Server User Guide 5
Contents
Appendix D
Appendix E
Glossary
Cluster Configuration Tool CLI 175
Cluster Configuration Tool CLI Overview.........................................................176
Plugins.............................................................................................................176
Operations....................................................................................................... 177
SQL plugin configure options.............................................................. 177
SQL plugin remove options................................................................. 178
SQLAlwayson plugin configure options............................................... 179
SQLAlwayson plugin remove options.................................................. 180
Cluster Configuration Tool CLI examples..........................................................181
ItemPoint for Microsoft SQL Server Notes 183
Supported environments for ItemPoint for Microsoft SQL Server...................184
Limitations for ItemPoint for Microsoft SQL Server........................................ 185
187
6 Dell EMC Avamar for SQL Server User Guide
Preface
As part of an effort to improve the product lines, revisions of the software and hardware are
periodically released. Therefore, some functions that are 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 the technical support professional when a product does not function correctly or does not
function as described in this document.
Note: This document was accurate at publication time. To find the latest version of this
document, go to Online Support (https://support.EMC.com).
Purpose
This guide describes how to install Avamar in a Microsoft SQL Server database environment, and
how to back up and restore SQL Server databases.
Audience
The information in this guide is primarily intended for:
l
System administrators who are responsible for installing software and maintaining servers and
clients on a network
l
Microsoft SQL Server administrators who are responsible for backing up and maintaining
Microsoft SQL Servers
Persons using this guide should have current practical experience with the following topics:
l
Operating system shell commands on the SQL Server platform (root permission required)
l
The specific version of Microsoft SQL Server currently deployed at the site
Revision history
The following table presents the revision history of this document.
Revision history
Table 1
Revision history
Revision Date Description
01 November 15, 2019 GA release of Avamar 19.2
Related documentation
The following publications provide additional information:
l
E-LAB Navigator
l
Avamar Release Notes
l
Avamar Administration Guide
l
Avamar for Windows Server User Guide
l
Avamar and Data Domain System Integration Guide
l
Avamar Operational Best Practices Guide
l
Avamar for Hyper-V VSS User Guide
at https://elabnavigator.emc.com/eln/elnhome
Dell EMC Avamar for SQL Server User Guide
7
Preface
l
Avamar for VMware User Guide
Special notice conventions used in this document
These conventions are used for special notices.
DANGER Indicates a hazardous situation which, if not avoided, results in death or serious
injury.
WARNING Indicates a hazardous situation which, if not avoided, could result in death or
serious injury.
CAUTION Indicates a hazardous situation which, if not avoided, could result in minor or
moderate injury.
NOTICE Addresses practices that are not related to personal injury.
Note: Presents information that is important, but not hazard-related.
Typographical conventions
These type style conventions are used in this document.
Table 2 Typographical conventions
Bold Used for names of interface elements, such as names of windows,
dialog boxes, buttons, fields, tab names, key names, and menu paths
(what the user specifically selects or clicks)
Italic
Monospace
Monospace italic
Monospace bold
[ ] Square brackets enclose optional values
| Vertical bar indicates alternate selections - the bar means “or”
{ } Braces enclose content that the user must specify, such as x or y or
... Ellipses indicate nonessential information that is omitted from the
Used for full titles of publications that are referenced in text
Used for:
l
System code
l
System output, such as an error message or script
l
Pathnames, filenames, prompts, and syntax
l
Commands and options
Used for variables
Used for user input
z
example
Where to get help
The Avamar support page provides access to licensing information, product documentation,
advisories, and downloads, as well as how-to and troubleshooting information. This information
may resolve a product issue before contacting Customer Support.
To access the Avamar support page:
1. Go to https://www.dell.com/support/home/us/en/19.
2. Type a product name in the Enter a Service Tag, Serial Number, Service Request, Model,
or Keyword search box.
8 Dell EMC Avamar for SQL Server User Guide
Preface
3. Select the product from the list that appears. When you select a product, the Product
Support page loads automatically.
4. (Optional) Add the product to the My Products list by clicking Add to My Saved Products in
the upper right corner of the Product Support page.
Documentation
The Avamar product documentation provides a comprehensive set of feature overview, operational
task, and technical reference information. To supplement the information in product administration
and user guides, review the following documents:
l
Release notes provide an overview of new features and known limitations for a release.
l
Technical notes provide technical details about specific product features, including step-bystep tasks, where necessary.
l
White papers provide an in-depth technical perspective of a product or products as applied to
critical business issues or requirements.
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/home/us/en/19.
2. Under the Support tab, click Knowledge Base.
3. Type either the solution number or keywords in the search box. 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.
Online communities
Go to Community Network at http://community.EMC.com for peer contacts, conversations, and
content on product support and solutions. Interactively engage online with customers, partners,
and certified professionals for all products.
Live chat
To engage Customer Support by using live interactive chat, click Join Live Chat on the Service
Center panel of the Avamar support page.
Service Requests
For in-depth help from Customer Support, submit a service request by clicking Create Service
Requests on the Service Center panel of the Avamar support page.
Note:
To open a service request, you must have a valid support agreement. Contact a sales
representative for details about obtaining a valid support agreement or with questions about an
account.
To review an open service request, click the Service Center link on the Service Center panel, and
then click View and manage service requests.
Enhancing support
It is recommended to enable ConnectEMC and Email Home on all Avamar systems:
l
ConnectEMC automatically generates service requests for high priority events.
l
Email Home sends configuration, capacity, and general system information to Customer
Support.
Dell EMC Avamar for SQL Server User Guide 9
Preface
Comments and suggestions
Comments and suggestions help to continue to improve the accuracy, organization, and overall
quality of the user publications. Send comments and suggestions about this document to
DPAD.Doc.Feedback@emc.com.
Please include the following information:
l
Product name and version
l
Document name, part number, and revision (for example, 01)
l
Page numbers
l
Other details to help address documentation issues
10 Dell EMC Avamar for SQL Server User Guide
CHAPTER 1
Introduction
This chapter includes the following topics:
l
Architecture...........................................................................................................................12
l
Backup.................................................................................................................................. 22
l
Restore..................................................................................................................................27
l
Table level recovery...............................................................................................................33
l
Disaster recovery.................................................................................................................. 33
Dell EMC Avamar for SQL Server User Guide 11
Introduction
Architecture
You can use Avamar to back up and restore data in a variety of Microsoft SQL Server
environments.
Avamar components
Required Avamar software components in a SQL Server environment include the Avamar Client for
Windows, the Avamar Plug-in for SQL Server, and the Avamar Management Web UI (AUI) or
Avamar Administrator.
The following figure illustrates a basic system architecture, including required Avamar software
components, when you use Avamar to back up a SQL Server environment.
Figure 1 Avamar architecture with SQL Server
Install the Avamar Client for Windows and the Avamar Plug-in for SQL Server on the computer
that is running SQL Server. Access the AUI as a web browser and/or install Avamar Administrator
on either the computer that is running SQL Server or on a separate computer, as shown in the
figure.
Avamar Client for Windows
The Avamar Client for Windows installation includes the Avamar Plug-in for Windows File System
and the Avamar agent, which is required for the Avamar Plug-in for SQL Server. You can use the
Windows File System plug-in to back up operating system and SQL Server binary files, which are
required for disaster recovery.
Avamar Plug-in for SQL Server
The SQL Server plug-in enables you to back up and restore SQL Server instances and databases.
12 Dell EMC Avamar for SQL Server User Guide
Avamar Administrator
Avamar Administrator is a graphical management console software application for remote
administration of an Avamar system from a supported Windows or Linux client computer. You can
configure, perform, monitor, and manage backups and restores using the AUI or Avamar
Administrator . The
using each interface.
Avamar Administration Guide
How Avamar connects to SQL Server
The Avamar Plug-in for SQL Server uses the SQL Server virtual device interface (VDI) to back up
and restore databases and transaction logs.
Backup process overview
1. The SQL Server plug-in creates one or more VDI devices, and then executes the necessary
SQL commands to back up the database to the VDI devices.
The number of VDI devices that the SQL Server plug-in creates depends on the number of
data streams that you configure for a backup. Each data stream corresponds to a VDI device.
2. The Avamar avtar program is spawned in a standard input/output (stdio) mode.
3. The SQL Server plug-in reads data read from the VDI device and writes data to avtar
standard input.
4. The avtar program reads and sends the data to either the Avamar server or a configured Data
Domain system.
Introduction
provides complete instructions for accessing and
Restore process overview
During a restore, the SQL Server plug-in reads data from standard output and writes the data to
the VDI device, which communicates with SQL Server to restore the databases.
Authentication
Avamar connects to SQL Server for backup or restore by using either Windows authentication or
SQL Server authentication.
With Windows authentication, Avamar connects to SQL Server by using the Windows system
service account (NT AUTHORITY\SYSTEM), which has privileges in SQL Server. The account
must have the sysadmin server-level role.
With SQL Server authentication, Avamar connects to SQL Server by using a SQL Server login
account. The account must have the sysadmin server-level role. You must select the mixed
authentication mode for the Database Engine when you configure SQL Server.
If you do not specify an authentication method, then the SQL Server plug-in uses NT
authentication and logs in with the Windows system service account. This account has the public
and sysadmin server-level roles in SQL Server by default in SQL Server 2008 and 2008 R2. In SQL
Server 2012, 2014, and 2016, you must add the account to the SQL Server administrators group.
Adding sysadmin server-level role in SQL Server Management Studio on page 37 provides more
instructions on adding sysadmin server-level role in SQL Server Management Studio.
Failover Cluster Instances
You can install SQL Server in a Windows Server Failover Clustering (WSFC) cluster with two or
more nodes.
With Failover Cluster Instances (FCIs), SQL Server databases and log files are on storage that is
shared between the nodes. If one of the nodes fails, then the applications
another node. When the failed node comes back online, you can
other node.
Dell EMC Avamar for SQL Server User Guide 13
fail back
fail over
the applications from the
to and run on
Introduction
SQL Server can run as either active/passive or active/active in a cluster.
Active/passive cluster environments
In an active/passive configuration, SQL Server runs on the active node, which is the primary node.
The passive node is a standby node. SQL Server does not run on the passive node.
If a failure occurs on the primary active node, then SQL Server fails over to the passive node. The
passive node becomes the active node. When the primary active node comes back online, you can
fail back SQL Server from the standby node to the primary active node. The passive node returns
to its standby role.
Avamar can perform both on-demand and scheduled backups of SQL Server data while SQL
Server is running on either the primary active node or on a standby node.
The following figure illustrates an active/passive cluster environment with SQL Server and
Avamar.
Figure 2 Avamar architecture with SQL Server in an active/passive cluster
Install the Avamar Client for Windows and the Avamar Plug-in for SQL Server on each node in the
cluster. Then run the Cluster Configuration Tool to configure the Avamar cluster client.
The Avamar server sends backup requests to the Avamar cluster client, which passes the request
to the SQL Server plug-in on the active node. The plug-in on the active node sends the backup
data and metadata to the Avamar server.
On Windows Server 2012 R2, the Avamar Plug-in for SQL Server supports storage of SQL Server
databases and log files in an active/passive cluster on shared disks, clustered shared volumes
14 Dell EMC Avamar for SQL Server User Guide
(CSVs), and shared virtual hard disk (VHDX) files. Shared VHDX files must be on either a CSV or
on a Server Message Block (SMB) 3.0 file share.
Note: For a SQL cluster on CSV, the CSV disk does not display as part of the SQL Cluster in
the Avamar Windows Cluster Configuration wizard. This is because the powershell
commands do not provide the associated disk for the SQL cluster in a CSV environment. To
configure a client for the SQL cluster, a non-csv disk needs to be assigned to the SQL Server
role.
Active/active cluster environments
In an active/active configuration, SQL Server runs on both nodes. The SQL Server installation on
each node manages its own separate databases.
If a failure occurs on one of the nodes, then the SQL Server installation on the other node assumes
responsibility for managing the databases for both nodes. When the failed node comes back online,
you can return the management responsibility for the databases to the original SQL Server
installation on each node.
Avamar can perform both on-demand and scheduled backups of SQL Server data regardless of
which active node is managing the data at the time of the backup.
The following figure illustrates an active/active cluster environment with SQL Server and Avamar.
Figure 3 Avamar architecture with SQL Server in an active/active cluster
Introduction
You install the Avamar Client for Windows and the Avamar Plug-in for SQL Server on each node in
the cluster. Then you run the Cluster Configuration Tool to configure the Avamar cluster client.
Dell EMC Avamar for SQL Server User Guide 15
Introduction
When you perform a backup or restore, you select the cluster client as the client to back up or the
target client for the restore.
The Avamar server sends backup requests to the Avamar cluster client, which passes the request
to the SQL Server plug-in on both active nodes. The plug-ins on the active nodes back up the SQL
Server data that they are managing to the Avamar server.
AlwaysOn availability groups
You can configure high availability of groups of databases by using AlwaysOn availability groups
(AGs) in SQL Server 2012, 2014, and 2016.
With AGs, SQL Server is installed in a WSFC cluster, but the data is not stored on a shared drive.
Instead, the data is stored on each node, and SQL Server synchronizes the data from the primary
version of the database to any secondary versions on other nodes.
You can group user databases together in an availability group. Databases in an availability group
must use the full recovery model. All databases in an availability group fail over together from one
node to another.
A SQL Server instance on a cluster node that hosts an availability group is called an
replica
. Each availability replica of an availability group must reside on a different node of the same
cluster. There are two types of availability replicas: one primary replica and one or more secondary
replicas. The primary replica handles read/write activity from clients and sends transaction log
updates to the secondary replicas. Each secondary replica applies the transaction log updates to
its databases.
During failover of an AG, the target secondary replica assumes the primary role and becomes the
new primary replica. The target secondary replica brings its databases online as the primary
databases, and client applications can connect to them. When the former primary replica is
available, it assumes the secondary role and becomes a secondary replica.
availability
NOTICE
groups on an availability replica that is hosted by a SQL Server Failover Cluster Instance (FCI).
To back up databases in such an environment, you must install the Avamar client software on
the node with the primary replica as a stand-alone client, and then perform backups on only
that node.
The Avamar Plug-in for SQL Server does not support backups of AlwaysOn availability
Architecture and workflow of backups in AlwaysOn availability groups
Avamar can perform both on-demand and scheduled backups of databases in either the primary
replica or a secondary replica for an availability group.
Install the Avamar Client for Windows and the Avamar Plug-in for SQL Server on each node in the
cluster. Then run the Cluster Configuration Tool to configure the Avamar cluster client for the
availability group listener. When you perform a backup or restore, select the cluster client for the
availability group listener as the client to back up or the target client for the restore.
Backups on the primary replica
The following figure illustrates the data workflow of SQL Server plug-in backups on the primary
replica.
16 Dell EMC Avamar for SQL Server User Guide
Figure 4 Backup workflow on a primary replica in an AlwaysOn environment
Introduction
The Avamar server sends a backup request to the Avamar cluster client for the availability group
listener, which passes the request to the primary replica. The SQL Server plug-in on the node with
the primary replica performs the backup and sends the backup data and metadata to the Avamar
server.
Backups on the secondary replica
The following figure illustrates the data workflow of SQL Server plug-in backups on a secondary
replica.
Dell EMC Avamar for SQL Server User Guide 17
Introduction
Figure 5 Backup workflow on a secondary replica in an AlwaysOn environment
The Avamar server sends a backup request to the Avamar cluster client for the availability group
listener, which passes the request to the primary replica. The SQL Server plug-in on the node with
the primary replica determines the secondary replica on which to perform the backup, and
forwards the backup request to the SQL Server plug-in on the node with the secondary replica.
The plug-in on the node with the secondary replica performs the backup and then sends the
backup data to the Avamar server. The plug-in on the secondary replica then sends the metadata
to the Avamar server and notifies the plug-in on the primary replica whether the backup completed
successfully. The primary replica sends the final summary to the Avamar server.
Architecture and workflow of restores in AlwaysOn availability groups
You can use the backup from either the primary replica or a secondary replica to restore the
databases in an AlwaysOn availability group. You can restore to only the primary replica, or restore
to both the primary and secondary replicas.
Restore to only the primary replica
The following figure illustrates the process to restore to only the primary replica in an AlwaysOn
availability group.
18 Dell EMC Avamar for SQL Server User Guide
Figure 6 Restore workflow to only the primary replica in an AlwaysOn environment
Introduction
The Avamar server sends a restore work order to the cluster client for the availability group
listener, which passes the work order to the SQL Server plug-in on the node with the primary
replica. The plug-in on the primary replica removes the databases that you are restoring from the
availability group and restores the databases. After the restore completes, the plug-in on the
primary replica adds the restored databases to the availability group and sends information about
the restore to the Avamar server.
If there is a corresponding database on the secondary replicas when you restore a database only
on the primary replica, then the database on the secondary replicas is in a restoring state. To
restore the databases on the secondary replicas as part of the availability group, manually prepare
and restore the databases, and join them to the availability group on the secondary replica.
You can also set the database on a secondary replica online without rejoining it to the availability
group by restoring the database with the RECOVERY recovery operation (RESTORE database
WITH RECOVERY). The SQL Server documentation on the Microsoft TechNet website provides
details.
Restore to both the primary and secondary replicas
The following figure illustrates the process to restore to both the primary and secondary replicas.
Dell EMC Avamar for SQL Server User Guide 19
Introduction
Figure 7 Restore workflow to the primary and secondary replicas in an AlwaysOn environment
During the restore, the Avamar server sends a restore work order to the SQL Server plug-in on the
node with the primary replica. The plug-in on the node with the primary replica then sends the
restore work order to the plug-in on the nodes with the secondary replicas. The plug-ins on the
primary and secondary replicas remove the databases that you are restoring from the availability
group and restore the databases.
After the restore completes, the plug-in on the node with the primary replica adds the restored
databases to the availability group. Then the plug-ins on the nodes with the secondary replicas join
the restored databases to the availability groups. When the entire restore process completes on all
replicas, the plug-in on the node with the primary replica sends information about the restore to
the Avamar server.
Hybrid IT environments with AlwaysOn availability groups and Microsoft Azure
The Avamar Plug-in for SQL Server can perform both on-demand and scheduled backups of
databases in AlwaysOn availability groups in hybrid IT environments with Microsoft Azure.
The backup can occur on either the primary replica or the secondary replica of the availability
group, but the replica on which the backup occurs must be on-premise. Avamar cannot back up
databases on a replica on a Microsoft Azure virtual machine.
The Avamar installation process is the same in a hybrid IT environment as in a traditional AlwaysOn
availability group environment. Install the Avamar Client for Windows and the Avamar Plug-in for
20 Dell EMC Avamar for SQL Server User Guide
SQL Server on each node in the cluster, including the Microsoft Azure nodes. Then run the Cluster
Configuration Tool to configure the Avamar cluster client for the availability group listener.
When you perform a backup or restore, select the availability group listener as the client to back
up or the target client for the restore. Ensure that backups occur on an on-premise replica by
using SQL Server and Avamar settings.
If the primary replica is on-premise, restore data to only the primary replica. Do not attempt to
restore to both the primary and secondary replicas. If there is a corresponding database on the
secondary replicas when you restore a database only on the primary replica, then the database on
the secondary replicas is in a restoring state. To restore the databases on the secondary replicas
as part of the availability group, manually prepare and restore the databases, and join them to the
availability group on the secondary replica.
If the primary replica is on a Microsoft Azure virtual machine, then restore the database to
operating system files and then use SQL Server tools to restore the database to the availability
group.
Database mirroring
Database mirroring maintains multiple copies of a single database that must reside on different
SQL Server instances. Typically, these server instances reside on computers in different locations.
The principal server serves the database to clients, while the mirror servers serve as standby
servers.
Implement mirroring settings individually for each database.
Mirroring works only with databases that use the full recovery model. The simple and bulk-logged
recovery models do not support database mirroring, and you cannot mirror the master, msdb,
tempdb, or model databases.
You can use the Avamar Plug-in for SQL Server to back up mirrored SQL Server databases.
However, several conditions apply:
l
The SQL Server version must be 2008 or greater.
l
Perform backups of only the principal database, not the mirrors.
l
Use only the Avamar Plug-in for SQL Server to perform backups. If you use other backup
products in addition to the Avamar Plug-in for SQL Server, then log chain breaks may occur.
l
Backup and database administrators must use extra care. Some mirror operations cause log
chain breaks that cannot be detected.
l
When database mirroring is established, either initially or as the result of failover and failback,
then you must manually perform a new full backup. Otherwise, incremental and differential
backups that occur after the establishment of database mirroring are not valid for restore.
After you perform the full backup in this case, you cannot perform point-in-time recoveries to
a point in time before the full backup.
l
To restore a database, you must break the SQL mirror.
Introduction
Data Domain system support
You can store backups on either the Avamar server or a Data Domain® system. Backup metadata
is stored on the Avamar server.
Before you can store backups on a Data Domain system, add the Data Domain system to the
Avamar configuration by using Avamar Administrator. Then select the Data Domain system in the
plug-in options when you perform an on-demand backup or when you create a dataset for a
scheduled backup. You can also use the command line interface (CLI) to perform backups to a
Data Domain system.
Dell EMC Avamar for SQL Server User Guide 21
Introduction
The steps to restore backups are the same whether you restore from the Avamar server or a Data
Domain system. The restore process determines the location of the backup and restores the
backup.
Avamar also supports Data Domain Cloud Tier. DD Cloud Tier moves data from Data Domain to the
cloud. From the Avamar Administrator, you can configure cloud tiering to move Avamar backups
from Data Domain to the cloud, and perform seamless recovery of these backups.
The
Avamar and Data Domain System Integration Guide
Domain systems in an Avamar environment, including detailed steps to add a Data Domain system
to the Avamar configuration.
Server virtualization
You can install SQL Server in a server virtualization environment such as VMware or Microsoft
Hyper-V. There are multiple ways that you can install and use Avamar to back up and restore SQL
Server data in a server virtualization environment.
The
Avamar for Hyper-V VSS User Guide
additional system requirements, as well as installation and configuration procedures.
Backup
provides more information about Data
and
Avamar for VMware User Guide
provide details on
Backup types
When you perform a backup with the Avamar Plug-in for SQL Server, you can back up either all
SQL Server data on a specific server, one or more instances, or one or more databases.
You cannot use the Avamar Plug-in for SQL Server to back up individual filegroups, files, or tables.
To back up individual files, use the Avamar Client for Windows.
The SQL Server plug-in can back up both user databases and system databases such as the
master, msdb, and model databases. Backup of system databases in a SQL Server replication
environment, such as the publication, distribution, and subscription databases, is also supported.
The SQL Server plug-in does not support backup of either the Resource or tempdb database
because Microsoft SQL Server does not support backup and restore of those system databases.
Avamar can back up databases that use any of the three recovery models: simple, full, or bulklogged. However, the recovery model may determine the type of backup that you can perform of
the database.
The SQL Server plug-in provides the ability to include and exclude data from the backup dataset.
You can include and exclude data when creating or editing a policy, or when creating or editing a
dataset in the Settings pane. The
Note:
The Avamar AUI is only supported in stand-alone Windows and Linux environments only.
The Avamar Plug-in for SQL Server supports full, differential, transaction log (incremental), and
copy-only database backups.
The following figure illustrates the different types of data that are included in each of the backup
types.
Avamar Administration Guide
provides more information.
22 Dell EMC Avamar for SQL Server User Guide
Figure 8 Types of database backups
Introduction
Full backups
Full backups include the entire database, including all objects, system tables, and data. As the
backup operation progresses, it copies the transaction logs. The timing of the transaction log
backup ensures that you can recover the complete database to the state it was in when the
backup finished.
Differential backups
Differential backups include data that changed after the last full backup. The backup operation
also copies relevant portions of the transaction logs.
When you select a differential backup to restore a database, the restore process restores the last
full backup, followed by the differential backups performed after the full backup. Because a
differential backup only saves changes to data, the backup is smaller and faster than a full backup.
Therefore, you can perform a differential backup more often than a full backup.
Differential backups are used primarily in heavily utilized systems where you must bring a failed
database back online as quickly as possible. Differential backups are smaller than full backups, and
so have less effect on the system where they run.
Transaction log (incremental) backups
By default, transaction log backups only back up the transaction logs. Transaction logs are serial
records of all database modifications.
The logs are used in recovery operations to update the database with complete transactions and
roll back incomplete transactions. When you back up a transaction log, the backup stores all
changes after the last transaction log backup. Transaction log backups record the state of the
transaction log at the start of the backup (unlike full and differential backups, which record the
state of the data at the end of the backup).
When a transaction log backup is complete, the log truncates to remove any transactions that are
committed to the database. When restoring the database, restore the data to the state it was in at
Dell EMC Avamar for SQL Server User Guide 23
Introduction
the end of the last full or differential backup, and then sequentially restore the transaction log
backups in order.
To fully restore data from a transaction log backup, at least one full backup must exist. To ensure
data integrity, the Avamar Plug-in for SQL Server software always checks for the presence of a
full backup on the server. If there is a full backup, the transaction log backup proceeds (that is, the
backup includes only transaction logs). If there is no full backup and you select the Force full
backup option (the default setting), then the Avamar Plug-in for SQL Server software forces a full
backup to ensure data integrity.
Copy-only backups
Copy-only backups are special-purpose full backups that are created independently of the
sequence of full, differential, and transaction log backups. After you perform a copy-only backup,
the next differential or transaction log backup is based on the last full backup, not the new copyonly backup.
Copy-only backups are supported for all recovery models, and for performing backups at the
database, instance and stripe levels. Restoring from a copy-only backup is the same as restoring
from a full backup.
Copy-only backups cannot be used as a base for differential backups and do not truncate the
transaction log. If you perform a copy-only backup on a database without a current full backup, the
Avamar Plug-in for SQL Server software promotes the next differential or transaction log backup
to a full backup. Promotion to full is indicated in the Activity Window of the Avamar Administrator
with the status Completed with Promotion. When only some of the databases in the backup are
promoted to full, the status will still be indicated as Completed with Promotion.
Previous versions of Avamar identify copy-only backups as type Unknown.
Supported backup types for different recovery models
The type of database backup that you can perform depends on the recovery model of the
database.
You can perform full backups of all databases, regardless of the recovery model (simple, full, or
bulk-logged).
If the database uses the simple recovery model, then you cannot perform a transaction log backup
of the database. Databases with the simple recovery model do not support transaction log
backups. System databases such as the master and msdb databases typically use the simple
recovery model.
Microsoft SQL Server also does not allow differential backups of the master database. You can
only create full backups of the master database.
If a transaction log backup includes databases with the simple recovery model and databases with
other recovery models, then you can select how Avamar handles the databases with the simple
recovery model. You can exclude the databases with the simple recovery model and log either a
warning or an error message in the log, or you can automatically perform a full backup of the
databases. When you perform the backup, the For simple recovery model databases option
controls this behavior.
Databases in AlwaysOn availability groups must use the full recovery model.
If you change the recovery model of a database, perform a full backup before you try to perform a
differential or transaction log backup.
24 Dell EMC Avamar for SQL Server User Guide
Backups in AlwaysOn availability groups
Avamar can perform both on-demand and scheduled backups of databases in either the primary
replica or a secondary replica for an availability group. You can increase performance of the
primary replica when you perform backups on a secondary replica.
Settings in SQL Server and in Avamar enable you to specify a preference for the replica on which
the backup occurs:
l
Always on the primary replica
l
Always on a secondary replica
l
On a secondary replica if possible, otherwise on the primary replica
l
No preference defined
In a hybrid IT environment with Microsoft Azure and AlwaysOn availability groups, use these
settings to ensure that the backup occurs on an on-premise replica. Avamar cannot back up
databases on a replica on a Microsoft Azure virtual machine.
If you do not specify a preference, then other factors contribute to the selection of the replica for
backup, including the backup priority, operational status, and connection status of each replica.
If you perform the backup on a secondary replica, then the replica must meet the following
requirements:
l
The replica must be in a synchronized state.
l
The replica role setting in SQL Server must allow either read-only connections or all
connections.
l
If there are multiple secondary replicas, then set the priority for which secondary replica to use
for the backup.
If you select the option to perform the backup only on a secondary replica and no secondary
replicas that meet the requirements are available, then the backup fails with an error.
You can perform full, differential, and transaction log backups on the primary replica. You can
perform only full and transaction log backups on a secondary replica. If you try to perform a
differential backup on a secondary replica, then the backup fails with an error message.
SQL Server does not support system databases in an availability group. To back up system
databases with Avamar, you must perform a separate backup. During the backup, select the server
name of the physical node as the client to back up, and then select the system databases.
Introduction
Mixed backup storage
You can store backups on either the Avamar server or a Data Domain system. However, the full
backup for a client and all subsequent transaction log (incremental) and differential backups must
be stored on either the Avamar server or a single Data Domain system.
Avamar does not support the following scenarios:
l
Full backup on a Data Domain system, and transaction log or differential backups on the
Avamar server
l
Full backup on the Avamar server, and transaction log or differential backups on a Data Domain
system
l
Full backup on one Data Domain system, and transaction log or differential backups on another
Data Domain system
Dell EMC Avamar for SQL Server User Guide 25
Introduction
As a result, if you change the server on which backups for a client are stored, then you must
perform a full backup before any further transaction log or differential backups.
If you change the backup storage from one Data Domain system to another Data Domain system,
you can restore to a point in time between the two full backups.
Multistreaming
Multistreaming enables you to improve backup and restore performance by backing up and
restoring SQL Server data by using multiple parallel data streams.
You can either back up multiple databases in parallel with one stream per database, or back up a
single database with multiple parallel streams.
If you use multiple data streams to send backup data for a single database to the Avamar server or
Data Domain system, then the backup for the database is stored as multiple files. As a result, the
restore uses the same number of streams that you use for the backup.
You can specify a maximum of 10 streams for each backup, and the minimum size of a stream.
NOTICE An exception to this restriction is if you perform a tail-log backup during a restore.
The tail-log backup is stored on the Avamar server even if the other backups for the client are
stored on a Data Domain system.
Database log truncation
The Avamar Plug-in for SQL Server truncates the database transaction log after an incremental
(transaction log) backup of the database.
You can truncate the database transaction log by performing an incremental backup automatically
after a full backup in SQL Server 2008 and later.
You also can prevent truncation of database transaction logs after backups.
The Truncate database log option controls whether truncation occurs. To automatically perform
an incremental backup after a full backup, select the Force incremental backup after full backup
option.
When the backup process successfully truncates the log file, LOG_BACKUP appears in the header
of the database.
Note:
Truncation does not reduce the physical size of a log file. To reduce the physical size of
a log file, shrink the log file. The Microsoft TechNet website provides information on log
shrinking in SQL Server.
Backups with other tools
Issues can occur when you perform backups with other tools in addition to backups with the
Avamar Plug-in for SQL Server.
Backups with multiple tools can break the backup chain for all of the tools, especially if the
backups are truncating the database transaction log. Consider the following scenario:
1. You perform a transaction log backup of a database with the SQL Server plug-in. The backup
process truncates the log.
2. The next day you perform transaction log backup with SQL Server management tools. The
backup process truncates the log.
3. The next time that a transaction log backup with the SQL Server plug-in occurs, the backup is
missing the transactions from the last Avamar backup through the SQL Server backup.
Exclusive backups with the Avamar Plug-in for SQL Server are recommended to avoid breaks in
the backup chain.
26 Dell EMC Avamar for SQL Server User Guide
Restore
The Avamar Plug-in for SQL Server supports a variety of restore options to accommodate a wide
range of data recovery needs.
The SQL Server plug-in performs only offline restores of SQL Server data. Online restore is not
supported.
Note: The Avamar AUI is only supported in stand-alone Windows and Linux environments only.
Restore to the original location
There are two options when you use the Avamar Plug-in for SQL Server to restore a SQL Server
instance, database, or filegroup to its original location:
l
Perform a standard restore with a tail-log backup and recovery.
l
Use the SQL Server REPLACE option to completely overwrite the database.
A standard restore with a tail-log backup is perhaps the most common restore procedure. During
this procedure, a tail-log backup is created to capture transactions that are not in a backup. Then
Avamar restores the database from the most recent full backup and any differential or transaction
log backups.
You may need to use the SQL Server REPLACE option for a restore, for example, if a previous
database restore exited with the following SQL Server error in the Avamar SQL restore log:
Introduction
One or more devices or files already exist.
Reissue the statement using the WITH REPLACE
option to overwrite these files and devices.
NOTICE
an SQL WITH REPLACE clause statement to the restore Transact-SQL command. Use of this
statement overrides a SQL Server safety check to prevent you from accidentally overwriting a
different database or file. The
information about the safety check in the RESTORE command section.
When you use the Avamar plug-in option for the SQL Server REPLACE option, it adds
Microsoft Transact-SQL Reference Manual
Restore to a new database in the original instance
You can use the Avamar Plug-in for SQL Server to restore a backup of a database to its original
instance, but with a new name that creates a database in the instance.
When you restore to a new database in the original instance, you can perform a tail-log backup and
recovery to recover transactions that were not in the backup. You also can specify the path for the
database and log files.
If the database uses the full recovery model, then you can restore to either a specific date and
time or to a named mark in the transaction log.
Restore to a different instance on the original server
You can use the Avamar Plug-in for SQL Server to restore an instance, database, filegroup, or file
to a different instance on the original server.
When you restore to a different instance on the original server, you cannot perform a tail-log
backup. However, you can specify the path for the database and log files.
provides more
Dell EMC Avamar for SQL Server User Guide 27
Introduction
If you are restoring a database, you can leave the original database name or restore the database
with a new name. You can also restore to either a specific date and time or to a named mark in the
transaction log.
Restore to an instance on a different server
You can use the Avamar Plug-in for SQL Server to restore an instance, database, filegroup, or file
to an instance on a different server.
When you restore to an instance on a different server, you cannot perform a tail-log backup.
However, you can specify the path for the database and log files.
If you are restoring a database, you can leave the original database name or restore the database
with a new name. You also can restore to either a specific date and time or to a named mark in the
transaction log.
Restore to a file
If the Avamar Plug-in for SQL Server is not installed on the target server, or you want to use the
standard SQL Server restore tools for other features, then you can restore user or system
databases to operating system files. You can then use SQL Server tools, such as SQL Server
Management Studio, a Transact-SQL RESTORE command, or the Microsoft SQL Server sqlcmd
utility, to restore the databases.
The SQL Server plug-in restores the backup as one or more files to the specified destination in the
following path:
destination\client\instance\database\file
where:
l
destination
l
client
l
instance
l
database
l
file
is the name of the file.
is the destination for the files that you specified in the Set Destination dialog box.
is the name of the computer on which SQL Server is installed.
is the name of the SQL Server instance from the backup.
is the name of the database from the backup.
A single backup may include multiple files, depending on the number of streams in the backup.
The file name for each file is composed of the backup type and the stream number:
n
f-0 indicates a full backup.
n
d-n indicates a differential backup.
n
i-n indicates a transaction log (incremental) backup.
where n is the sequential number of the differential or incremental backup after the preceding
full backup.
For example, a full backup with two streams results in two files: f-0.stream0 and
f-0.stream1.
Restore to an AlwaysOn availability group
When you back up a database in an AlwaysOn availability group, you can restore the database to
the following locations:
l
To the original availability group
l
To a new database in the original availability group
l
To a different availability group
28 Dell EMC Avamar for SQL Server User Guide
l
To a different instance that does not use availability groups
l
To a file
You can restore an entire database, a filegroup, or a file. You also can perform point-in-time
restore of databases that use the full recovery model.
These restore options are available regardless of whether the backup was on the primary replica or
a secondary replica.
Restore to the original availability group
When you restore to the original availability group, the restore process can automatically restore
the databases on both the primary replica and the secondary replicas.
You can also restore databases only on the primary replica. When you restore a database only on
the primary replica, the corresponding database on the secondary replicas is in a restoring state.
To restore the databases on the secondary replicas as part of the availability group, manually
prepare and restore the databases, and join them to the availability group on the secondary
replicas.
You can also set the databases on a secondary replica online without rejoining them to the
availability group by restoring the databases with the RECOVERY recovery operation. The SQL
Server documentation on the Microsoft TechNet website provides details.
Introduction
Restore to a new database in the original availability group
You can restore a database to the original AlwaysOn availability group, but with a new name that
creates database in the availability group.
When you restore to a new database in the original instance, the restore process restores the
database to only the primary replica. You must then join the new database to the availability group.
To restore the database on the secondary replicas as part of the availability group, manually
restore the database and join the database to the availability group on each secondary replica.
Restore to a different availability group
When you restore a database to an availability group on a different server, the restore process
restores the database to only the primary replica.
To restore the database on the secondary replicas as part of the availability group, manually
restore the database and join the database to the availability group on each secondary replica.
NOTICE
You cannot restore a database to a different availability group in the same cluster.
Restore fails with an error message that indicates the existence of the database.
Restore to a different instance that does not use availability groups
You can restore a database to a different SQL Server instance that does not use AlwaysOn
availability groups. The steps are the same whether you restore from a backup of databases on a
stand-alone server, in a cluster, or in an availability group.
Restore of a database from an availability group to a file
You can restore a database from a backup of an availability group to operating system files. This
type of restore may be necessary if the Avamar Plug-in for SQL Server is not installed on the
target server, or you want to use the standard SQL Server restore tools for other features. The
steps are the same whether you restore from a backup of databases on a stand-alone server, in a
cluster, or in an availability group.
Dell EMC Avamar for SQL Server User Guide 29
Introduction
Restore to hybrid IT environments with Microsoft Azure
In a hybrid IT environment with AlwaysOn availability groups and Microsoft Azure, the steps to
restore a database depend on the location of the primary replica.
If the primary replica is on-premise, restore data to only the primary replica. Do not attempt to
restore to both the primary and secondary replicas. If there is a corresponding database on the
secondary replicas when you restore a database only on the primary replica, then the database on
the secondary replicas is in a restoring state. To restore the databases on the secondary replicas
as part of the availability group, manually prepare and restore the databases, and join them to the
availability group on the secondary replica.
If the primary replica is on a Microsoft Azure virtual machine, then restore the database to
operating system files and then use SQL Server tools to restore the database to the availability
group.
Restore of a database with an intact log file
If a database becomes corrupt or is otherwise lost but an intact database log file is available, you
can restore the database and use the log file to recover transactions after the most recent Avamar
backup.
To restore the database, perform a transaction log backup by using a Transact-SQL command.
Then, restore the database from the most recent Avamar backup by using Avamar Administrator.
Finally, restore the transaction log backup by using Transact-SQL commands.
Restore of system databases
Restore of only system databases is rare but may be necessary if one or more system databases
are damaged. It is more likely that you must restore system databases when you restore user
databases. When you select both system and user databases for restore, the system databases
restore first.
When you restore system databases, the Avamar Plug-in for SQL Server can automatically restore
the databases in the correct order and manage SQL Server services. However, you can also
restore individual system databases and manually manage the services.
NOTICE
SQL Server replication environment, including the publication, distribution, and subscription
databases. The SQL Server plug-in can automatically restore the replication system databases
in the correct order and manage SQL Server services. The "Back Up and Restore Replicated
Databases" topic in the SQL Server documentation on the MSDN website provides details on
backup strategies and the steps to manually restore databases in different types of replication
environments.
Automatic restore of system databases
When you restore multiple system databases, Avamar automatically restores the databases in the
correct order: master, msdb, and model.
Avamar can also automatically manage the stop and restart of the necessary SQL Server services
during the restore. For example:
l
When you restore the master database, Avamar can automatically stop the SQL Server
instance, including dependent services such as the SQL Server agent service and the Analysis
Service, and restart the instance in single-user mode before the restore. After the restore,
Avamar automatically restarts the instance.
The Avamar Plug-in for SQL Server can back up and restore system databases in a
30 Dell EMC Avamar for SQL Server User Guide
Loading...