sql consloidate DECN.pngSQL Server is a well  integrated data platform with many available services and a couple of editions that cover just about any data management scenario you can imagine.  How you mix and match those options with your code and creativity opens up an incredible world of possibilites.  You might think that finding the right supporting infrastructure given all these capabilties would be challenging, but it doesn't have to be.  Dell EMC is leading the industry in providing technologies for IT resource consolidation.  Yes, consolidation is a big topic that means different things to different people.  However, I think we should be able to agree that maintaining many isolated silos of equipment for each application in the enterprise has to be challenged vigorously.

 

Given the rich menu of RDBMS, BI, data pipeline and other services available in SQL Server, it is espeically important to protect against building everyting SQL Server out in silos.  The rest of this article is going to summarize a recently completed paper by Dell EMC describing how to achieve Microsoft SQL Server database consolidation with our Unity storage platform. The solution engineers focused on documenting the consolidation environment setup, performance testing, data copy management, disaster recovery, and data backup.  The results are avialble in a soluiton guide that you can download here.

 

unity arch.png

Solution Overview

The solution uses the Unity 300F All-Flash storage system.  Unity provides a single integrated architecture for block, file, and VMware Virtual Volumes that increases its applicability in consolidation scenarios.  The engineering team choose Diskspd for the storage performance baseline testing. Diskspd is a versatile storage testing tool that combines granular IO workload definition capability with flexible runtime and output options that DBAs and Windows administrators should download and try if you haven't already done so. It is so much better than its predecessor, SQLIO.


Performance

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

deployment.   The solution guide describes storage design and general best practices for deploying SQL Server on Unity storage systems. Because virtualization of a SQL Server environment requires its own set of considerations, the guide also includes a section on best practices for SQL on VMware along with references to more detailed white papers.


The solution resutls show  that Unity is capable of delivering steady rates of low latency I/O  under a wide range of DiskSpd settings.  Most of the test scenarios had latency of 1ms or less which is quite remarkable for an entry level all-flash array.

 

The team ran a wide range of scenarios that included various host counts, block sizes and I/O patterns as shown in this table:

 

Host CountBlock SizePattern
168K

Random with read/write ratios of  90/10, 80/20, 70/30

16

8K, 16K, 32K, 64K

Random with read/write ratio of 70/30
8, 16, 248K
Random with read/write ratio of 70/30


unity backup.pngFast Integrated Backup

In a consolidated database environment using Unity sotrage, DD Boost for Enterprise Applications (DDBEA) dramatically improves the

network utilization efficiency by reducing the amount of data transferred over the network.  With DD Boost, the server only sends unique data segments to the Data Domain system.  Traditional full backups take longer to complete, can require as much space as the original data, and consume bandwidth equal to the amount of data being transferred. DDBEA reduces resources in all these areas by applying intelligent backup data reduction at the client.


Copy Data Management

The solution also shows how AppSync simplifies and automates the process of generating and consuming database copies by abstracting and automating the underlying storage and replication technologies.  AppSync is used to orchestrate all the activities required from copy creation and validation through mounting at the target host and recovering a database.  The copy management use cases demonstrate how to automatically discover user databases and use the database structure to map objects required for the copy through the virtualization layer to the underlying storage LUN.


Data Protection and Recovery

RecoverPoint for Virtual Machines was chosen for the solution to address data protection and recovery across physical sites.  RP4VMs provides a simple to use automated solution to easily manage any consolidated database environment with multiple sites.  Unity integrates with RecoverPoint for Virtual Machines to protect SQL Server instances at the production site by enabling replication and recovery at a secondary site.


Next Steps

Download the full solution guide using the link below.  There are many more details on all of the topics that I briefly covered in this article.  Then contact your Dell EMC account team to arrange for a deeper discusion of the Unity storage platform along with our other solutions for SQL Server copy management, data protection and DR recovery.

 

pdf.jpg

 

http://www.emc.com/collateral/white-papers/h15142-unity-ms-sql-wp.pdf

 

Thanks for reading,

Phil Hummel, EMCDSA

On Twitter @GotDisk