microsoft sql server consolidation solution for … · 8 microsoft sql server consolidation...

MICROSOFT SQL SERVER CONSOLIDATION SOLUTION FOR DELL EMC UNITY September 2016

ABSTRACT

This paper describes the Microsoft SQL Server database consolidation

solution with Dell EMC Unity storage platform. The solution focuses on the

consolidated environment, high performance databases, copy management,

disaster recovery, and data backup.

H15142

DELL EMC SOLUTIONS

WHITE PAPER

Copyright

2 Microsoft SQL Server Consolidation Solution for Dell EMC Unity

EMC2, EMC, the EMC logo, Connectrix, Data Domain, FAST, FAST Cache, NetWorker, PowerPath, RecoverPoint,

Unisphere, Unity, UnityVSA, VPLEX, are registered trademarks or trademarks of EMC Corporation in the United States and

other countries. All other trademarks used herein are the property of their respective owners.

© Copyright 2016 EMC Corporation. All rights reserved. Published in the USA.

EMC believes the information in this document is accurate as of its publication date. The information is subject to change

without notice.

EMC is now part of the Dell group of companies

Contents


Contents

Overview ........................................................................................................................................ 4

Architecture and design ............................................................................................................... 6

Solution configuration .................................................................................................................. 9

Storage provisioning best practices .......................................................................................... 11

Performance validation ............................................................................................................... 13

Copy data management with AppSync ...................................................................................... 17

Data protection and recovery ..................................................................................................... 19

Fast integrated backup ............................................................................................................... 23

References ................................................................................................................................... 26

Overview


Overview

IT organizations need a cost-effective, high performance storage solution for database

management systems (DBMSs), such as Microsoft SQL Server, to support mission-critical

applications. SQL Server databases require a storage solution that is designed for both

low-latency transactional I/O and high-throughput analytic workloads. Workload

consolidation becomes inevitable for IT organizations with decreasing budgets and

increasing pressure to maintain the same or higher level of performance. Dell EMC

Unity™ is the simple, modern, flexible, and affordable storage solution for IT organizations

looking to consolidate their mission-critical database applications.

Unity is a new family of storage systems that helps simplify and modernize the data

center. Unity delivers flexible yet affordable file and block storage for small and medium-

sized IT departments. Unity comes in two variants, Hybrid and All-Flash. The Unity All-

Flash series joins Dell EMC's portfolio of all-flash storage arrays—XtremIO®, VMAX® All

Flash and DSSD™ D5™—to ensure that no matter what a customer needs, EMC has a

solution to affordably fit any data center use case.

Figure 1. Dell EMC Unity Family

Unity is perfect for IT organizations that need a consolidated storage solution for mission-

critical SQL Server applications. Unity is very simple to manage with a clean and

integrated HTML5 user interface that streamlines operations. It offers proactive

management and monitoring through a task-oriented interface that leads the user through

daily operations and integrates with VMware and Microsoft ecosystems. Unity storage has

a modern 2U design with advanced All-Flash configuration for a high performance

system. Its performance meets the requirement of both low-latency transactional I/O and

high-throughput analytics workloads. Unity is highly affordable for any small or medium

sized IT organizations.

This white paper presents methodologies and best practices based on validated test

results for designing a consolidated SQL Server solution for Unity.

Document

purpose

Overview


Unity 300F All-Flash series (identical configuration between production and protection site)

10x 3.2TB SSD drives, RAID5 (8+1) configuration with 1 hot spare

1x 4 ports Fibre Channel (FC) I/O module per SP at 8 Gb

This white paper is intended for IT administrators, storage architects, database

administrators, partners, Dell EMC employees and any other individuals involved in the

evaluation, acquisition, management, operation, or design of a Dell EMC networked

storage environment utilizing the Unity family of storage systems.

Table 1 provides definitions for some of the terms used in this white paper.

Table 1. Terminology

Term Definition

Dell EMC AppSync™ Data-protection software that simplifies and automates the

process of generating and consuming copies of production

data.

Dell EMC DD Boost™ A software option that increases backup performance,

improves backup reliability, improves operational and disaster

recovery, and allows you to better use your current

infrastructure investments.

Fibre Channel (FC) A protocol used to perform Internet Protocol (IP) and Small

Computer Systems Interface (SCSI) commands over an FC

network.

Network-attached storage

(NAS)

A file-level computer data storage server connected to a

computer network providing data access to a heterogeneous

group of clients.

Logical Unit Number (LUN) A block-level storage device that can be shared out using a

protocol such as iSCSI.

Online transaction processing

(OLTP)

A class of information systems that facilitate and manage

transaction-oriented applications, typically for data entry and

retrieval transaction processing.

Pool A repository of disks from which storage resources such as

LUNs and file systems can be created.

Dell EMC RecoverPoint® for

Virtual Machines

A disaster recovery solution for virtualized applications in

VMware environments.

REpresentational State

Transfer (REST)

A lightweight communications architecture style that enables

the execution of discrete actions against web services.

Snapshot A point-in-time view of data stored on a storage resource. A

user can recover files from a snapshot, restore a storage

resource from a snapshot, or provide access to a host.

Storage Processor (SP) A storage node that provides the processing resources for

performing storage operations as well as servicing I/O between

storage and hosts.

Components

Audience

Terminology

Architecture and design


Term Definition

Dell EMC Unisphere®

An HTML5-based graphical user interface used to manage

Unity storage systems.

Unisphere Command Line

Interface (UEMCLI)

An interface that allows a user to perform tasks on the storage

system by typing commands instead of using the graphical

user interface.

Dell EMC and the authors of this document welcome your feedback on the solution and

the solution documentation. Contact [email protected] with your

comments.

Authors: Basuki Winoto, Andrew Vandamme, Joshua Roch, Traci Morrison


This solution is based on the Unity 300F series storage platform, which is made highly

available across production and protection sites. The solution consolidates 16 Microsoft

SQL Server databases with OLTP-like workloads. The workloads simulate a highly

transactional, real-time application such as an ordering system. Unity All-Flash storage is

used to achieve high I/O throughput while maintaining minimal storage related latencies.

Additional software included in this solution includes AppSync, RecoverPoint for Virtual

Machines, and DD Boost.

The Unity system is available in four different models with two different variants or series.

The platform starts with the Unity 300/F and scales up to the Unity600/F. The suffix F

indicates the Unity All-Flash series. Unity 300F is used in this solution because it meets

the criteria of a modern storage system: simple, flexible, and affordable.

Based on the powerful new family of Intel E5-2600 processors, Unity All-Flash storage

systems implement an integrated architecture for block, file, and VMware Virtual Volumes

with concurrent support for native NAS, iSCSI, and FC protocols. Each system leverages

dual storage processors, 12 GB SAS back-end connectivity, and a multi-core operating

environment to deliver unparalleled performance and efficiency. Additional storage

capacity can be added via Disk Array Enclosures (DAEs).

A detailed view of the Unity 300F specification is provided below.

Model: Unity 300F

Processor: 2x Intel six-core 1.6Ghz

Memory: 48 GB

Array enclosure: 2U DPE with 25x 2.5” drives

Min/max drives: 4/150 drives

Max raw capacity: 1.5 PBs

Max I/O modules: Four modules per SP

Base 12 GB/s SAS back end: 2 x 4 lane

Max SAN hosts: 512

We value your

feedback!

Overview

Hardware

overview

mailto:[email protected]?subject=Feedback:%20White%20paper:%20Microsoft%20SQL%20Server%20Consolidation%20Solution%20for%20EMC%20Unity%20(H15142)



Max number of LUNs: 500

Max LUN size: 256 TB

Refer to the Unity All-Flash Specification Sheet for more technical details. The drive

configuration is discussed in the next section.

Unity OE

Dell EMC Unity OE offers both block and file provisioning in the same enclosure. Drives

are provisioned into Pools that can be used to host both block and file data. Connectivity

is offered for both block and file protocols. For block connectivity, iSCSI and/or FC may be

used to access LUNs, consistency groups, VMware Datastores (VMFS), and VMware

Virtual Volumes.

All storage resources are provisioned from Pools. A Pool is a collection of physical disks

arranged into an aggregate group, with a form of RAID applied to the drives to provide

redundancy. The Unity All-Flash series uses either a RAID5 (8+1) or a RAID6 (14+2)

configuration.

LUNs are block-level storage resources that can be accessed by hosts over iSCSI or FC

connections. A user can create, view, manage, and delete LUNs in any of the

management interfaces – Unisphere, Unisphere CLI, and REST API. A Pool is required to

provision LUNs. LUNs may be replicated in either an asynchronous or synchronous

manner, and snapshots of LUNs may be taken.

AppSync

AppSync simplifies and automates the process of generating and consuming copies of

production data. By abstracting the underlying storage and replication technologies, and

through deep application integration, AppSync empowers application owners to satisfy

copy demand for operational recovery and data repurposing on their own. In turn, storage

administrators need only be concerned with initial setup and policy management, resulting

in an agile, frictionless environment.

AppSync automatically discovers application databases, learns the database structure,

and maps it through the virtualization layer to the underlying storage LUN. It then

orchestrates all the activities required from copy creation and validation through mounting

at the target host and launching or recovering the application. Supported workflows also

include refresh, expire, and restore production.

RecoverPoint for Virtual Machines

RecoverPoint for Virtual Machines is a hypervisor-based, software-only data protection

solution for protecting VMware virtual machines and their datastores. RecoverPoint for

Virtual Machines enables local and remote replication, allowing recovery to any point-in-

time. RecoverPoint for Virtual Machines consists of a VMware vSphere web client plug-in,

a RecoverPoint write-splitter embedded in the ESX hypervisor, which enables replication

from any storage type to any storage type, as well as a virtual appliance integrated in the

VMware ESXi server environment. RecoverPoint for Virtual Machines supports up to two

non-production copies, local and remote, allowing recovery to any point in time.

Software

overview

http://www.emc.com/collateral/data-sheet/h14957-unity-all-flash-family-ss.pdf



Data Domain Boost for Enterprise Applications

Dell EMC Data Domain®

Boost for Enterprise Applications (DDBEA) is an application-

direct solution that uses an enterprise application data protection framework to provide an

application centric data protection. DDBEA integrates with the following technologies:

Data Domain, a purpose-built backup appliance

DD Boost, software that distributes parts of the deduplication process to the backup server or application clients

SQL Server

As a database server, SQL Server is a software product with the primary function of

storing and retrieving data as requested by other software applications, which may run

either on the same computer or on another computer across a network (including the

Internet). The SQL Server physical components determine how the data is stored in the

file system of the operating system. The selection of the number and types of table

columns and index design has a major impact on the requirements for physical storage.

VMware

VMware vSphere 6.0 provides an ideal platform on which customers can virtualize their

business-critical applications. This solution chooses VMware vSphere 6.0 to consolidate

multiple SQL Server instances.

DiskSpd

The storage performance baseline is tested with DiskSpd Utility based on Microsoft

recommendations. This tool is a feature-rich and versatile storage testing tool that

combines robust and granular I/O workload definition with flexible runtime and output

options, creating an ideal tool for synthetic storage subsystem testing and validation.

For more information, refer to DiskSpd Utility: A Robust Storage Testing Tool.

Table 2 lists the software versions used in this solution.

Table 2. Software versions

Software Version

Dell EMC AppSync 3.0.0.0 PR-3.0.0.0-83

Dell EMC DD Boost and DD/VE 3.2.1.2-522808 and 5.7.30.2-525061

Dell EMC DDBEA (Microsoft application

agent)

3.0.0.0.0 Build 199

Dell EMC RecoverPoint for Virtual Machines 4.3.1.2(h.255)

Dell EMC Unity OE 4.0.0.7329527

Microsoft SQL Server 2012

VMware vCenter Server 6.0.0 3634793

VMware vSphere/ESXi Server 6.0.0 3825889

https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223

Solution configuration



This section provides the architecture diagram and configuration for this solution.

Figure 2 shows the solution architecture diagram.

Figure 2. Solution architecture diagram

The configuration for this solution is provided below.

SQL Server

16x 250 GB VMFS (Data + Temp + Log + OS + SQL Server)

16x SQL server virtual machines (2 vCPUs/32 GB RAM)

Active Directory: local drive

Two domain controllers (2 vCPUs, 4 GB RAM)

Storage configuration

Unity 300F All-Flash

10x 3.2 TB SSD

21.97 TB usable

8-port FC (4-ports per SP) with Dell EMC PowerPath®

The storage array in this solution is configured according to the following layout:

1 Pool configured for 21.9 TB

Overview

Initial

configuration



32 LUNs at 250 GB thin (used for testing)

1 LUN at 500 GB thin (used for management)

1 LUN at 1 TB thin (used for cloning management)

1 LUN at 250 GB thin (used for management)

Figure 3 and Figure 4 show the system view and storage array.

Figure 3. System view

Storage provisioning best practices


Figure 4. Storage array


Storage design is one of the most important elements of a successful SQL Server

deployment. Follow the storage guidelines described in this white paper to achieve a

storage design for optimal reliability, performance, and ease of use.

This section provides general best practices for deploying SQL Server on Unity storage

systems. Because virtualization of a SQL Server environment requires its own set of

considerations, this section also includes guidance on best practices for SQL on VMware.

For the SQL Server configuration, begin by considering the following basic requirements:

OS/SQL Server binaries

In a typical SQL Server implementation, the server is dedicated for SQL Server and

binaries are on the same LUN as the operating system. Follow Microsoft’s

recommendation for the operating system type and SQL Server version and

consider the overhead for applications that you need to install on that server.

Typical LUN sizes for the operating system, SQL Server binaries, and system

databases are 60 GB to 120 GB.

System databases

Overview

General SQL

Server storage

best practices



In most environments, system databases are not frequently changed or modified

and they can be on the same LUN as the operating system. System databases

available in SQL Server 2005 and later versions include Master, Resource, MSDB,

MODEL, TempDB, Distribution, ReportServer and ReportServerTempDB.

Logs for user databases

Logs for user databases typically need low IOPs (mostly sequential writes) and are

typically not very demanding.

Tempdb

Tempdb is a global resource that is shared by all databases within an SQL Server

instance. There are many processes that use tempdb, including:

Temporary objects such as global or local temporary tables, temporary stored procedures, table variables, or cursors.

Internal objects created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.

Row versions generated by data modification transactions that use read-committed using row versioning isolation or snapshot isolation transactions.

Row versions generated by data modification transactions for features, such as online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Because of the wide range of uses for tempdb, it is not possible to know how

transactional or I/O intensive workloads will affect performance without

performance data collection. When the tempdb database is heavily used, SQL

Server may experience contention when it tries to allocate pages. Depending

on the degree of contention, this may cause queries and requests that involve

tempdb to be unresponsive for short periods of time. Having multiple data files

adds scalability advantages especially for tempdb where activities tend to be

allocation-heavy.

User databases

User database LUNs are typically the main focus for storage design. Thin LUNs are

recommended for effective storage capacity use. The pool is configured with RAID 5 for

balanced performance and protection.

General SQL Server best practices:

Select the Lock pages in memory policy for the SQL Server start account to prevent SQL Server from swapping memory.

Set Autoshrink to Off for data and log files.

Make data files of equal size in the same file group. SQL Server uses a proportional fill algorithm that favors allocations in files with more free space.

Schedule index fragmentation checks and only reorganize or rebuild indexes that will significantly improve performance.

File group and file considerations

File group considerations in SQL Server:

File groups can be accessed in parallel.

SQL Server

basic best

practices

Performance validation


Organize SQL Server data files with similar performance and protection needs into a file group when designing a database.

Create one log file in a typical environment. More log files will not improve performance.

For more information, refer to Using Files and Filegroups.

Basic best practices for storage

The following are high-level basic best practices for storage design:

Plan for performance, capacity, and protection.

When creating a volume in Windows, set the Windows allocation unit to 64 K for SQL Server database and log LUNs.

Use up-to-date manufacturer-recommended host bus adapter (HBA) drivers.

Ensure that storage array software is up-to-date.

Dell EMC strongly recommends using multipathing for availability/redundancy and

throughput optimization.

SQL Server performance characteristics can vary substantially from one environment to

another, depending on the application. These characteristics fall into two general

categories: OLTP generates mostly random read workloads, and data warehouse

generates mostly sequential read workloads. In an OLTP environment, use read/write

IOPs for storage sizing. For a data warehouse environment, use bandwidth (MBs) for

storage sizing.

For accurate performance estimates, work with Dell EMC presales to perform a workload

performance assessment using Mitrend. Mitrend is a pre-assessment tool that helps you

make informed infrastructure decisions using detailed storage utilization, configuration,

and performance information. Run tests in conditions as close to “real world” as possible.

During these tests, use performance monitor logs to capture the characteristics (such as

IOs in reads/second and writes/second and bandwidth in MB/seconds) of the volumes

used to store database files.


The performance and scalability of the array was measured to serve as the baseline.

This test used DiskSpd, which is available from Microsoft

(https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223). This tool

supersedes SQLIO and is recommended for storage testing and validation.

Storage sizing

best practices

Overview

https://technet.microsoft.com/en-us/library/ms187087(v=sql.105).aspx

https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223



The solution was tested with the scenarios listed in Table 3 to simulate a sustained

workload from 16 hosts with different block sizes and a different number of hosts.

Table 3. Test scenarios for this solution

Scenario Host count Block size Sequential/random Read/write

ratio

Test #1 –

Baseline

16 8K Random 90/10,

80/20.

70/30

Test #2 –

Different

block sizes

16 8K, 16K,

32K, 64K

Random 70/30

Test #3 –

Different host

counts

8, 16, 24 8K Random 70/30

Throughout the tests, we set a sub-millisecond storage latency as the performance goal.

The following test parameters were chosen to reflect the most common conditions when

deploying a Unity storage system for the database workload:

1. All-Flash (production site), All-Flash (secondary site)

2. Virtualized environment with VMware hypervisor

3. Thin LUNs using block FC protocol

Test results are highly dependent on workload, specific application requirements, and system design and implementation. Relative system performance will vary because of these and other factors. Therefore, you should not use the solution workloads as a substitute for a specific customer application benchmark for critical capacity-planning and product-evaluation decisions.

All performance data contained in this white paper were obtained in a rigorously controlled environment. Results obtained in other operating environments might vary significantly. Dell EMC does not warranty or represent that a user can or will achieve similar performance.

The success criteria for this baseline test are:

The test demonstrated that Unity is capable of showing steady, high performance under a consolidated workload.

The test demonstrated that the solution can perform at <1 ms latency with the defined workload.

The test demonstrated that Unity scales well under the defined workload.

Unity demonstrated steady and high performance under a consolidated workload. Figure

5 shows the baseline workload profile with different read/write ratios.

Requirements

Notes on

performance

results

Results



Figure 5. Baseline workload profiles of 10 percent, 20 percent, and 30 percent writes

Figure 5 shows the three different workloads used in the baseline performance test. All

workloads are random at 8K block size with different read/write ratios. We created three

workloads at 10 percent, 20 percent, and 30 percent writes to reflect common

transactional workloads.

As shown in Figure 6, the test results indicate that we achieved a sub-millisecond

response time goal with the baseline performance test. The goal was achieved in the 10

and 20 percent writes tests. The 30 percent writes test did not meet the goal, but are still

close to the 1 ms mark, which is considered excellent. The test results also indicate that

the high performance can be sustained over a period of 6 hours. At higher write levels, the

response time may pass the 1 ms mark.



Figure 6. Baseline performance of 10 percent, 20 percent, and 30 percent writes

The next test demonstrated the impact of a larger block size to overall performance. Using

the same 16 hosts and 30 percent writes, testing with a larger block size showed an

increase in response time. A larger block size impacted overall performance, as shown in

Figure 7.

Figure 7. Impact of I/O sizes to performance

Table 4 is a summary of scaling impact to performance. Adding hosts to the total workload

in the array increased the overall response time. Table 4 shows that the response time

changed at a slower pace compared to the increase of the workload size. In the range

Copy data management with AppSync


between 8 to 24 hosts, incrementing the number of hosts by 100 percent causes the

response time to increase by only 33 percent.

Table 4. Response times

Number of Hosts Scale Average Response

Time (ms)

% Response Time

Increase

8 1x 0.68 0%

16 2x 0.90 33%

24 3x 1.09 60%

The test results show that the Unity platform is a great fit for consolidating workloads. It is

a perfect choice for IT organizations looking for affordable and high-performance storage.

Unity 300F, the entry level of Unity All-Flash array, can achieve a steady, sub-millisecond

performance under a sustained workload from multiple hosts. It also scales very well,

which makes Unity the perfect platform for consolidating multiple hosts without

compromising performance.


AppSync, integrated with Unity snapshots, simplifies and automates the process of

generating and consuming copies of production data. This solution uses AppSync to

address copy management use cases in a consolidated database environment. AppSync

automatically discovers application databases, learns the database structure, and maps it

through the virtualization layer to the underlying storage LUN. It then orchestrates all the

activities required from copy creation and validation through mounting at the target host

and launching or recovering the application. Supported workflows include refreshing,

expiring, and restoring the production database.

For more information about using AppSync to create and managing copies of SQL Server

databases, refer to Dell EMC AppSync User and Administration Guide.

This section addresses the two main use cases in copy data management with AppSync:

database recovery from copy and copy management for database repurposing. These are

the two most common use cases found in a consolidated database environment. Admins

are required to perform a point-in-time recovery, which requires the admin to create a

copy and recover from the created copy. Admins are also required to create a copy and

mount the created copy for development, testing, or other purposes.

The main objective of this test was to show a simple copy data management operation in

a consolidated database environment.

The operational workflow test was done using the HammerDB tool to simulate SQL

Server activity during copy management work. The test parameter was configured at 5

users per server with 1 million transactions per user to reflect a common database

consolidation scenario.

Overview

Requirements

http://www.emc.com/collateral/TechnicalDocument/docu68979.pdf



Unisphere, the Unity management software, provided a single, configurable panel to

observe the performance metrics during the copy data management workflow. This

allowed all tests be observed in Unisphere.

Figure 8 shows the Unisphere user interface.

Figure 8. Unisphere user interface

The main success scenario was achieved when the operational test workflow was

completed. Impact to performance is expected, but should not cause any significant

performance degradation.

The two use cases used the following test workflow:

1. Create database copies for backup acceleration:

a. Create SQL service plan: Schedule or select on demand the type of service plan: Gold, Silver, or Bronze.

b. Select Create Copy options: Server backup type Full, Copy, Non VDI – Set Expiration – Choose Storage Preference Snapshot.

c. Run service plan.

2. Restore a database back to production:

a. Select copy.

b. Back up the transaction logs.

c. Restore options.

d. Configure storage.

3. Mount copies.

Figure 9 shows that the backup process required a very short time because AppSync

uses Unity snapshots to create copies. Impact to performance is also observed after copy

creation. Unity performance remained high and still met the requirements of the

Operational test

workflow

Results

Data protection and recovery


consolidated database environment. Restoring and mounting from the copy are all made

simpler with AppSync integration with Unity snapshots. Administrators can restore and

mount databases from AppSync copies while maintaining steady performance.

Figure 9. Backup process with AppSync using Unity snapshots to create copies


RecoverPoint for Virtual Machines was chosen to address data protection and recovery

because it provides a simple and automated solution, which helps admins easily manage

their consolidated database environment. RecoverPoint for Virtual Machines allows

replication of virtual machines with virtual machine level granularity. RecoverPoint for

Virtual Machines uses a write-splitter embedded in the ESXi hypervisor, which enables

replication.

This section covers the data protection and recovery use case with two sites: the

production site and the secondary site. With RecoverPoint for Virtual Machines integration

into Unity, the data protection and recovery scenario can be achieved by replicating the

SQL Server instances at the production site and failing over to the replicas at the

secondary site.

For more information about using RecoverPoint for Virtual Machines to create replica and

failover scenarios, refer to Dell EMC RecoverPoint for Virtual Machines Product Guide.

We confirmed the following before starting this use case:

RecoverPoint for Virtual Machines was licensed and registered.

All ESXi clusters that host protected virtual machines or their copies were registered.

All datastores used for copy and production journals were registered.

Virtual machines in the same consistency group did not have the same name.

Overview



The main objective of this test was to show a simple data protection and recovery operation in a consolidated database environment.

The operational workflow test was done with the HammerDB tool to simulate SQL Server activity during copy management work. The test parameter was configured at 5 users per server with 1 million transactions per user to reflect a common database consolidation scenario.

The test aimed to demonstrate that the solution is capable of performing a complete data protection and failover scenario.

This use case used the following workflow:

1. To protect:

a. Select the virtual machine protection method. Create a consistency group for this virtual machine. Note that adding a virtual machine to an existing consistency group results in journal loss for that group. The best practice is to protect each virtual machine in its own consistency group. Refer to the RecoverPoint for Virtual Machines administration guide for more information.

b. Define the production journal and set the advanced production settings.

c. Run protection and observe the status from the Unisphere user interface.

Figure 10 shows the storage activity at production site activity during data

protection process.

Figure 10. Storage activity at production site during data protection process

The consistency group begins an initialization process. The initialization can take

a long time. After initialization, the consistency group becomes active.

Requirements

Operational test

workflow



Figure 11. Failover Wizard

2. To fail over:

a. Define the scope. Select whether you want to test the consistency group or the group set. If there are no group sets, the option is unavailable.

Note: A group set is a collection of consistency groups. In this test, we used the

consistency group and did not create any group sets.

b. Select the image. Select the image to access. You may want to start with the last known valid image.

c. Define the testing environment. The best practice is to avoid IP address conflicts between the production virtual machine and the copy virtual machine by using a dedicated testing network.

d. Complete. Run fail over and observe the status from the Unisphere user interface. Figure 12 shows the storage activity at the secondary site when the failover process is completed and the workload is picked up by the replica.



Figure 12. Storage activity at the secondary site

After failover, the production and copy virtual machine change roles, but the names do not

change. Therefore, after failover, the old production virtual machine should be

<YourVMName>, and the old copy should remain <YourVMName>.copy.

The marking information in the production journal is deleted, the copy journal is deleted,

and the consistency group undergoes a full-sweep synchronization.

Before failing back to the production, the Recovery wizard is displayed, which enables you

to select an image at the production site that predates your failover. You should verify the

image before permanently selecting it as the image you want to fail back to.

The test showed that a complete data protection and recovery use case can be performed

with this solution. The operational steps are simplified with RecoverPoint for Virtual

Machines automation.

RecoverPoint for Virtual Machines integration with Unity creates a simple data protection

and recovery system in a consolidated database environment. In this scenario, multiple

database server virtual machines are protected on the secondary site with RecoverPoint

for Virtual Machines. Both protection and failover workflows are automated. Figure 13

shows the performance impact during each data protection process and failover process.

Results

Fast integrated backup


Figure 13. Performance impact during each data protection process and failover process


Data Domain (DD) Boost aggregates multiple links into dynamic interface groups between

the application and Data Domain system. DD Boost is a perfect choice for a fast

integrated backup solution. With DD Boost, the server only sends unique data segments

to a Data Domain system.

DD Boost for Enterprise Applications (DDBEA) uses DD Boost technology for client-side

deduplication to deliver faster application backups. It also has an agent that integrates

with SQL Server.

In a consolidated database environment with Unity, DDBEA dramatically improves the

network utilization efficiency by reducing the amount of data transferred over the network.

A full backup traditionally takes a long time, requires as much space as the original data,

and consumes bandwidth as large as the data being transferred. DDBEA eliminates these

pain points from the backup process.

For more information about using DD Boost and DDBEA to optimize backup performance,

refer to Efficient Backup and Long-Term Retention for Microsoft SQL Server White Paper.

The main objective of this test was to show a fast and efficient backup operation in a

consolidated database environment.

The operational workflow test was done using the HammerDB tool to simulate SQL

Server activity during the backup process. The test parameter was configured at 5 users

per server with 1 million transactions per user to reflect a common database consolidation

scenario.

Overview

Requirements



The test aimed to demonstrate that the solution is capable of performing a complete fast

integrated backup process. It also aimed to demonstrate that the Unity storage platform

combined with DD Boost is a cost-effective backup solution for consolidated database

environments.

This use case used the following workflow:

1. To back up:

a. Start the initial backup for all data.

Figure 14. Data Domain System Manager

b. Update day 1 to 7. Simulate an incremental daily backup for 7 days. See Table 5 for the data reduction comparison.

c. Run a full backup.

2. To restore:

a. Restore from backup.

Unity storage performance during the backup process can be observed directly in the

Unisphere user interface, as shown in Figure 15.

Operational test

workflow

Results



Figure 15. Performance while running the entire backup process

Backup efficiency is gained from the data reduction. Table 5 shows the data reduction

comparison in each backup session. The smaller backup size resulted in the overall

reduced backup duration because it only needed to write a reduced size from the original

dataset. The second full backup duration is also shorter compared to the initial full

backup.

Table 5. Data reduction comparison in each backup session

Backup Round Total Dataset Backup Size Backup Duration

Initial Full Backup 114.0 GiB 61.7 GiB 14m 25s

Incremental Backup –

Day 1

118.8 GiB 64.4 GiB 01m 01s


Day 2

128.5 GiB 67.3 GiB 01m 13s


Day 3

143.0 GiB 69.9 GiB 01m 20s


Day 4

162.3 GiB 72.6 GiB 01m 45s


Day 5

186.5 GiB 75.4 GiB 01m 53s


Day 6

215.5 GiB 78.1 GiB 02m 11s


Day 7

249.3 GiB 80.9 GiB 02m 26s

Full Backup 397.0 GiB 81.7 GiB 07m 06s

References


Table 6 shows that a full restore requires about the same duration required for the initial

full backup.

Table 6. Full restore

Restore Total Dataset Backup Size Restore Duration

Full Restore 397.0 GiB 81.7 GiB 14m 25s

Figure 16 shows a detailed view from the backup and restore workflow.

Figure 16. Detailed view from the backup and restore workflow

DDBEA improved the backup efficiency from backup data reduction at the application

level. The overall backup size achieved a significant reduction. In this example, it realized

a reduction ratio of 4.86 with seven daily incremental simulated backups and one full

simulated backup. With a reduced amount of backup data, the restore process also

realized the benefit of a shorter, full restore duration.

References

The following documentation provides additional and relevant information. Access to

these documents depends on your login credentials. If you do not have access to a

document, contact your Dell EMC representative.

Dell EMC AppSync User and Administration Guide

Dell EMC Data Domain Operating System Command Reference Guide

Dell EMC Data Domain Operating System Administration Guide

Dell EMC RecoverPoint for Virtual Machines Administrator’s Guide

Dell EMC

documentation

http://www.emc.com/collateral/TechnicalDocument/docu68979.pdf

https://support.emc.com/docu61788_Data-Domain-Operating-System-5.7.1-Command-Reference-Guide.pdf?language=en_US

https://www.emc.com/collateral/TechnicalDocument/docu61787.pdf

http://www.emc.com/collateral/TechnicalDocument/RPVMs4.3_administrators_guide.pdf

References


Dell EMC RecoverPoint for Virtual Machines Installation and Deployment Guide

The following documentation on the VMware website provides additional and relevant

information:

Architecting Microsoft SQL Server on VMware vSphere

The following documentation on the Microsoft website provides additional and relevant

information:

Using Files and Filegroups

SQL Server 2016 Technical Documentation

Microsoft SQL Server Best Practices and Design Guidelines for Dell EMC Storage Solution Guide

VMware

documentation

Microsoft

documentation

https://www.emc.com/collateral/TechnicalDocument/docu66572.pdf

http://www.vmware.com/

http://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf

https://technet.microsoft.com/

https://technet.microsoft.com/en-us/library/ms187087(v=sql.105).aspx

https://msdn.microsoft.com/en-us/library/ms130214.aspx

https://www.emc.com/collateral/technical-documentation/h14621-microsoft-sql-server-best-practice.pdf

https://www.emc.com/collateral/technical-documentation/h14621-microsoft-sql-server-best-practice.pdf

microsoft sql server consolidation solution for … · 8 microsoft sql server consolidation...

Documents