WIXLIB

Technical White PaperDell EMC DD Boost for Microsoft Applications(Application Direct) - SQL ProtectionAbstractThis white paper focuses on how the integration of Dell EMC DD Boost forMicrosoft Applications (Application Direct) and SQL creates a superior backupsolution for SQL environments.May 20211Dell EMC DD Boost for Microsoft Applications (Application Direct) - SQL Protection H10892.2

RevisionsDateDescriptionSeptember 2019Initial release and updated for 19.1 version releaseNovember 2020Updated for 19.5 version releaseMay 2021Updated for 19.8 version releaseAcknowledgmentsAuthor: Vinod Kumar KumaresanThe information in this publication is provided “as is.” Dell Inc. makes no representations or warranties of any kind with respect to the information in thispublication, and specifically disclaims implied warranties of merchantability or fitness for a particular purpose.Use, copying, and distribution of any software described in this publication requires an applicable software license.This document may contain certain words that are not consistent with Dell's current language guidelines. Dell plans to update the document oversubsequent future releases to revise these words accordingly.This document may contain language from third party content that is not under Dell's control and is not consistent with Dell's current guidelines for Dell'sown content. When such third-party content is updated by the relevant third parties, this document will be revised accordingly.Copyright 2021 Dell Inc. or its subsidiaries. All Rights Reserved. Dell Technologies, Dell, EMC, Dell EMC and other trademarks are trademarks of DellInc. or its subsidiaries. Other trademarks may be trademarks of their respective owners. [5/13/2021] [Technical White Paper][H10892.2]2Dell EMC DD Boost for Microsoft Applications (Application Direct) - SQL Protection H10892.2

Table of ContentsRevisions .2Acknowledgments .2Executive summary .4Audience.41Introduction .51.1SQL Backup and Recovery .51.1.1 SQL Backup Methods .51.1.2 SQL Recovery Models .51.2DD Series Deduplication Storage System .61.3Overview of Application Direct with SQL Server backups .71.3.1 Architecture - Microsoft App Agent for Application Direct with SQL Server .71.3.2 Lockbox Configuration: .81.3.3 Features and capabilities of Microsoft App Agent for Application Direct with SQL Server .81.3.4 Best practices to Backup and Restore SQL Server with Application Direct .102Scheduled and Manual SQL Server data Protection .122.1Scheduled SQL Server data protection: .122.2Manual SQL Server data Protection using Application Direct .122.2.1 SQL Server backup using the Microsoft App Agent for Application Direct SSMS Plug-in .122.2.2 SQL Server backup using Application Direct commands .142.2.3 SQL Server backup using the Microsoft Application Direct T-SQL scripts.153Restoring SQL Server Data with Application Direct .193.1SQL Server restore using Application Direct SSMS Plug-in .193.2SQL Server restore using Application Direct recover command .213.3SQL Server restore using Application Direct T-SQL scripts .223.3.1 SQL Server push restores using Application Direct T-SQL scripts .223.3.2 SQL Always On availability groups restore by using T-SQL scripts .233.4Performing table-level recovery for SQL Server data.233.4.1 Launching the Microsoft app agent for VM Direct SSMS plug-in .243.4.2 SQL table-level restore configuration: .244Performing SQL Server disaster recovery .27Conclusion .30ATechnical support and resources .31A.13Related resources.31Dell EMC DD Boost for Microsoft Applications (Application Direct) - SQL Protection H10892.2

Executive summaryChallenges for Database Administrators (DBAs)The data protection landscape is changing, application owners, such as DBA’s meeting the backup window isbecoming a problem due to explosion of data that needs to be protected, Businesses must meet strict Service LevelAgreements (SLAs) for backup and recovery – often referred to as Recovery Point Objectives (RPO) and RecoveryTime Objective (RTO). To meet these challenges, SQL DBAs are often forced to find solutions outside the normalbackup and recovery solution. In the context of data protection, Lets focus on the solution provided by Dell EMC totransform operations to a modern management approach.Solution: Application Direct for SQLDell EMC PowerProtect DD series appliance is a multiprotocol purpose built backup appliance that providesCIFS/NFS, VTL, NDMP, mainframe, IBM-I, Application Direct with and Cloud Tiering protocol capabilities.PowerProtect DD series appliance being cloud enabled, with native tiering of deduped data to public, private, orhybrid clouds for long-term retention and secure multitenancy capabilities that enable enterprises and serviceproviders to deliver DD series in a private/public cloud. In the past SQL DBAs were being asked to rely on IT forbackup and recovery using backup application.The combination of Application Direct with DD series yields impressive efficiency for backup and replication,capitalizing on DD series ability to reduce storage requirements and address the breadth of today’s dataprotection challenges. Client-side deduplication enabled by Application Direct speeds backups by 50% and reducesbandwidth requirements up to 98%. Where, environments can scale higher, backup and restore faster, andreduce the load on the server during backup.The new wave modern management is designed for self-service to empower application owners too backup directlyfrom the application data source to protection storage. This model is popular because it provides control directly inthe hands of data owners.Application Direct networking features for link failover, aggregation, and load balancing provide optimal networkutilization while ensuring that backup jobs complete. Application Direct gives application owners direct control ofbackup to DD series using Microsoft SQL Server Management Studio (SSMS). By giving application ownerscontrol of the recovery process through native utilities, they do not need to go through backup admins andso achieve faster recovery.AudienceThis white paper is intended for SQL database administrators, systems engineers, partners, and members of theDell EMC and Partner Professional Services community who are looking for more control of their applications, fasterand more efficient backups, and flexible data protection solutions.4Dell EMC DD Boost for Microsoft Applications (Application Direct) - SQL Protection H10892.2

1IntroductionThe purpose of this white paper is to discuss how the Application Direct for SQL Server Software provides aunique and a powerful integration between SQL database backup and DD series. The goal of Application Directfor SQL Server Software is to better serve database administrators by empowering them with the tools thatprovide them with superior data protection.1.1SQL Backup and RecoveryMicrosoft SQL Server is a business-critical application requiring a reliable and customizable data protectioninfrastructure. To create backup copies of Microsoft SQL data, the DBA leverages the SSMS application, OftenSQL administrators use the following methods to backup data:-1.1.1Customized backup processes within SSMSScheduled backup operations created by the backup administrator in a backup applicationSQL Backup MethodsMicrosoft App Agent for SQL Server backups can be performed on both SQL stand-alone and clusterenvironments using the below methods to backup all the databases or only the required database of a SQLServer instance.-1.1.2Using SSMS to perform the Microsoft App Agent for SQL Server backupsUsing CLI to perform the Microsoft App Agent for SQL Server backupsUsing T-SQL script to perform the Microsoft App Agent for SQL Server backupsSQL Recovery ModelsThere are three different recovery models of SQL Server, The three SQL Server recovery models: simple, full,and bulk-logged.Simple Recovery ModelEnables to recover the database to the point of the last backup. However, database cannot be restored to thepoint of failure or to a specific point-in-time.Full Recovery ModelEnables to recover the database to a specific point-in-time. However, restore can be done from backups thatare complete up to the point-in-time that is selected.Bulk-logged Recovery ModelThe bulk-logged recovery model is a special-purpose model that works in a similar manner to the full recoverymodel. The only difference is in the way it handles bulk data modification operations. The bulk-logged modelrecords these operations in the transaction log using a technique known as minimal logging. This savessignificantly on processing time but prevents using the point-in-time restore option.5Dell EMC DD Boost for Microsoft Applications (Application Direct) - SQL Protection H10892.2

1.2DD Series Deduplication Storage System DD series appliance originated as the groundbreaking purpose-built backup appliance – or PBBA revolutionizing data protection for thousands of users by dramatically reducing storage requirements andenabling efficient Disaster Recovery (DR) with deduplicationModern DD series systems and the DD OS powered portfolio of solution delivers the most trusted protectionstorage in the market. With unmatched efficiency brought about through Dell EMC’s industry-leadingdeduplication, scalability, reliability, and performanceProtection storage from Dell EMC is flexible, with the ability to backup directly from enterprise apps orprimary storage. DD series is available How You Want It depending upon the environment demands. DDseries is cloud enabled, with native tiering of deduped data to public, private, or hybrid clouds for long-termretention and secure multitenancy capabilities that enable enterprises and service providers to deliver DDseries in a private/public cloudHardware assisted compression in DDOS 7.0 offloads CPU workload with GZFAST compression providing30% more logical capacity comparing with previous versions. This feature is enabled by default on allDD6900/DD9400/DD9900 systemsDD series Data Invulnerability Architecture (DIA) lays out the industry's best defense against data integrityissues by providing unprecedented levels of data protection, data verification, and self-healing capabilities thatare unavailable in conventional disk or tape systems.6Dell EMC DD Boost for Microsoft Applications (Application Direct) - SQL Protection H10892.2