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

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

Preface		7
Chapter 1	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

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	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
Chapter 4	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	Restore	73
	Restore requirements........................................................................................	74
	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 restore size for an SQL server database....................................	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
	Restore features available by using the AUI......................................................	77
	Restoring a database to the original client............................................	77

4	Dell EMC Avamar for SQL Server User Guide

		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	Disaster Recovery	119
	Preparing for disaster recovery.......................................................................	120
	Performing disaster recovery..........................................................................	120
Appendix A	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
Appendix B	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

Contents

Appendix D	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
Appendix E	ItemPoint for Microsoft SQL Server Notes	183
	Supported environments for ItemPoint for Microsoft SQL Server...................	184
	Limitations for ItemPoint for Microsoft SQL Server........................................	185
Glossary		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:

•System administrators who are responsible for installing software and maintaining servers and clients on a network

•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:

•Operating system shell commands on the SQL Server platform (root permission required)

•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

Introduction

This chapter includes the following topics:

•

Architecture...........................................................................................................................	12
Backup..................................................................................................................................	22
Restore..................................................................................................................................	27
Table level recovery...............................................................................................................	33
Disaster recovery..................................................................................................................	33

Dell EMC Avamar for SQL Server User Guide

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

Introduction

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 Avamar Administration Guide provides complete instructions for accessing and using each interface.

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.

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 fail over to and run on another node. When the failed node comes back online, you can fail back the applications from the other node.

Dell EMC Avamar for SQL Server User Guide

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

Introduction

(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

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

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 availability 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.

NOTICE The Avamar Plug-in for SQL Server does not support backups of AlwaysOn availability 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.

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

Introduction

Figure 4 Backup workflow on a primary 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 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

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

Introduction

Figure 6 Restore workflow to only the primary replica in an AlwaysOn environment

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

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

Introduction

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:

•The SQL Server version must be 2008 or greater.

•Perform backups of only the principal database, not the mirrors.

•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.

•Backup and database administrators must use extra care. Some mirror operations cause log chain breaks that cannot be detected.

•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.

•To restore a database, you must break the SQL mirror.

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

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 provides more information about Data 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 and Avamar for VMware User Guide provide details on additional system requirements, as well as installation and configuration procedures.

Backup

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 Avamar Administration Guide provides more information.

Note: The Avamar AUI is only supported in stand-alone Windows and Linux environments only.

Backup types

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.

22 Dell EMC Avamar for SQL Server User Guide

Introduction

Figure 8 Types of database backups

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

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

Introduction

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:

•Always on the primary replica

•Always on a secondary replica

•On a secondary replica if possible, otherwise on the primary replica

•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:

•The replica must be in a synchronized state.

•The replica role setting in SQL Server must allow either read-only connections or all connections.

•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.

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:

•Full backup on a Data Domain system, and transaction log or differential backups on the Avamar server

•Full backup on the Avamar server, and transaction log or differential backups on a Data Domain system

•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

Introduction

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.

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.

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

Introduction

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:

•Perform a standard restore with a tail-log backup and recovery.

•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:

One or more devices or files already exist. Reissue the statement using the WITH REPLACE option to overwrite these files and devices.

NOTICE When you use the Avamar plug-in option for the SQL Server REPLACE option, it adds 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 Microsoft Transact-SQL Reference Manual provides more information about the safety check in the RESTORE command section.

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.

Dell EMC Avamar for SQL Server User Guide

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:

•destination is the destination for the files that you specified in the Set Destination dialog box.

•client is the name of the computer on which SQL Server is installed.

•instance is the name of the SQL Server instance from the backup.

•database is the name of the database from the backup.

•file is the name of the file.

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:

nf-0 indicates a full backup.

nd-n indicates a differential backup.

ni-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:

•To the original availability group

•To a new database in the original availability group

•To a different availability group

28 Dell EMC Avamar for SQL Server User Guide

Introduction

•To a different instance that does not use availability groups

•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.

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

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 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 The Avamar Plug-in for SQL Server can back up and restore system databases in a 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:

•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.

30 Dell EMC Avamar for SQL Server User Guide

+ 164 hidden pages

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	Used for full titles of publications that are referenced in text
Monospace	Used for:
	•	System code
	• System output, such as an error message or script
	• Pathnames, filenames, prompts, and syntax
	•	Commands and options
Monospace italic	Used for variables
Monospace bold	Used for user input
[ ]	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
	z
...	Ellipses indicate nonessential information that is omitted from the
	example

Dell Avamar for SQL Server User Manual

CONTENTS

Preface

Introduction

Architecture

Avamar components

How Avamar connects to SQL Server

Failover Cluster Instances

AlwaysOn availability groups

Database mirroring

Data Domain system support

Server virtualization

Backup

Backup types

Backups in AlwaysOn availability groups

Mixed backup storage

Multistreaming

Database log truncation

Backups with other tools

Restore

Restore to the original location

Restore to a new database in the original instance

Restore to a different instance on the original server

Restore to an instance on a different server

Restore to a file

Restore to an AlwaysOn availability group

Restore of a database with an intact log file

Restore of system databases