Microsoft SQL Server 2014 Fast Track
34-TB Certified Data Warehouse
103-TB Maximum User Data
Tegile Systems Solution Review
2U Design: Featuring Tegile T3800 All-Flash Storage Array
http://www.tegile.com/solutions/sql
www.tegile.com/solutiuons/sql
Table of Contents
Executive Summary:................................................................................................................................... 3
Introduction to Fast Track for SQL Server Data Warehouse ............................................................... 3
Why choose the Tegile solution: .............................................................................................................. 3
How is Tegile DWFT solutions different? ................................................................................................ 4
Tegile Intelliflash™ Features...................................................................................................................... 5
High Level Tegile Array Architecture: ...................................................................................................... 6
Data Warehouse Fast Track Certification: .............................................................................................. 7
Tegile T3800 Performance Overview ...................................................................................................... 8
Hardware Overview ................................................................................................................................... 9
Summary ................................................................................................................................................... 10
2
www.tegile.com/solutions/sql
Document:DW-3 Edit Version1.0
Executive Summary:
The Microsoft SQL Server Data Warehouse Fast Track (DWFT) reference architecture is designed to
eliminate the complexity of properly sizing hardware for your SQL Server-based Data Warehouse. This
will streamline the decision-making process and reduce hardware and maintenance costs. The sizing
technique used in SQL Server DWFT will properly size servers, based on I/O throughput and CPU
transaction capability. This consumption-based approach ensures that you can take full advantage of
your hardware investment.
Target audience: This document is intended for CIOs, CTOs, decision makers and managers, as well as
individual (BI) Architects, DBAs and developers. Tegile has also published a detailed installation and
configuration guide that includes step-by-step instructions.
Introduction to Fast Track for SQL Server Data Warehouse
Data Warehouse Fast Track (DWFT) reference architectures provide tested and validated configurations
and associated resources to help customers identify the right environment for their data warehouse
solutions. Following these recommended configuration best practices and guidelines will yield these
tangible benefits:

Accelerate data warehouse projects with pre-tested hardware and SQL Server configurations

Reduce hardware and maintenance costs by purchasing a balanced hardware solution and
optimizing it for a data warehouse workload

Reduce planning and setup costs by leveraging the certified reference architecture
configurations

Ensure predictable performance by configuring the system correctly and taking advantage of the
tuning directions
The DWFT reference architectures provide a balanced server, memory, network, and storage hardware
configuration, enabling you to avoid the risk of improperly designed and configured hardware systems.
These guidelines ensure that you get be most capability and throughput out of your storage investment.
Why choose the Tegile solution:
(1) Tegile is the only storage company offering you a FULL 5-year performance and capacity
warranty on all of its Flash Arrays; this includes the actual flash media (eMLC)
(2) Tegile is the only storage company, which delivers true All-Flash arrays that are expandable
based on your needs with (a) All Flash, (b) Hybrid or (c) Hard Disk Drives (HDD) only. This allows
you to add more performance, more space and or both. Your decision on what to add can be
deferred until such time, when you know in which direction your DW needs to grow.
(3) Tegile delivers advanced data management, deduplication and compression features, which will
further expand the capacity and usability of the arrays with no additional software licensing.
Everything comes in the box.
3
www.tegile.com/solutions/sql
Document:DW-3 Edit Version1.0
How is Tegile DWFT solutions different?
Essentially, there are three types of Data Warehouse Architectures:
The traditional solution calls for separate server(s) and a huge allocation of spinning media on
traditional HDD based storage array with all the drawbacks associated with 50-year-old
architecture.
The converged solution calls for stuffing PCIe flash cards into a single server and creating fast,
but highly volatile solution with no enterprise features such as failover, scale out, non-disruptive
updates etc.
The modern solution is designed using enterprise deployment standards with multiple servers,
and with lightning-fast response times, highly available, expandable all-flash and hybrid storage.
The traditional solution:
A legacy Data Warehouse Reference Architectures are well established in the industry with the focus on
extracting the maximum performance from a disk based array. Unfortunately, the architectures were
limited by legacy spinning media technology at the time of creation. Many do a great job working
around the problems normally encountered by the administrators and DBAs. Understandably, some of
those steps were time-consuming, mostly focused on sequentially loading data into a data warehouse in
order to achieve true physically sequential layouts of data. These steps included single-threaded loading
steps, ordering data between steps, and utilizing multiple staging tables. It traded overall loading speed
for a physically sequential data layout in order to minimize fragmentation. As a result, it was tailored to
limitations of legacy spinning media. This was created to satisfy the requirement for predictable
throughput during range scans (sequential reads), which is the typical workload of a data warehouse
system. The intent was to try to minimize movement of the read/write heads on the disks. This is
optimal when designing for spinning media. Tegile IntelliFlash storage arrays remove this requirement and
allow the administrators to take the quickest path with parallel loads and reloading data at will.
The converged solution:
Converged solutions are designed to eliminate performance bottlenecks by placing storage as close to
the CPU processing as possible—in this case, directly inside the server that’s running the application.
While this is very fast solution, it can also be very expensive and has many serious drawbacks, which
should give anyone looking for real DW a serious pause.
First: In order to avoid catastrophic failure of PCIe Cards, PCIe flash cards need to be doubled and
mirrored inside the server. Cutting the maximum usable capacity in half (or doubling the cost in terms of
PCIe flash, depending on your point of view). PCIe flash failures are quite common due to poor
ventilation of the server cases. They produce a tremendous amount of heat at the exact moment you
need them the most.
Second: Using a single server creates a single point of failure. To overcome this, SQL Enterprise must be
deployed in an always-on configuration. This will require another (ideally) identical server with
approximately the same amount of flash (PCIe) storage crammed into it. This effectively doubles the
4
www.tegile.com/solutions/sql
Document:DW-3 Edit Version1.0
cost of the deployment, again. For example, one organization is currently paying for the flash storage
four times—2 times in each of the 2 servers—in order to create a high-availability solution. Furthermore,
this flash can only be used by one solution and cannot be shared across the organization when it’s no
longer needed.
Third: There is no easy way to add storage performance and capacity. One must add a new server to
increase the performance and/or capacity. While some DBAs will say sharding DBs is an option, it is
complex and difficult to maintain for a reasonable period.
The modern solution:
The modern solution is based on the proven strategy of separating highly available, fault-tolerant
storage and computing resources. Tegile storage arrays are not only highly available with two activeactive controllers, they also protect all data inside the array with an industry standard RAID
configuration. Tegile Arrays deliver all-flash performance that can be expanded over time with (a) all
flash, (b) hybrid or (c) HDD-based shelves, depending on growing needs. The Tegile IntelliFlash™
operating system ensures that frequently accessed data is always stored in the fastest media and less
frequently used data is stored on more cost-effective media. Furthermore, Tegile IntelliFlash ™
operating system dynamically allocates the performance of the array based on the requirements placed
on each LUN or share.
Tegile Intelligent Flash Array Features
High IOPS: Tegile storage arrays are able to achieve very high IOPS by writing each I/O
block into DRAM and flash on the array. Using advanced algorithms, the most
important data are always kept at the fastest layer of the array. Tegile storage arrays
can come equipped with several hundred terabytes of enterprise-grade (eMLC) solidstate drives. The actual usable space is even larger due to inline compression and deduplication. These data reduction techniques free up cache space in DRAM and flash
for faster reads and writes.
Compression: Tegile storage arrays use inline compression, which can remove multiple
write operations and simplify read operations in real time. Compression also delivers
space savings and eliminates multiple IOs, effectively increasing the throughput of the
system. Compression performed inline without compromising read or write
performance.
Inline Deduplication: Tegile storage arrays come with low overhead inline deduplication. For most SQL Server workloads, this feature is only effective if the data is
highly repetitive. However, this feature has amazing results for Virtualized servers or
multiple copies of the same database.
Note: This feature was disabled for DW FT Testing as the test data can be highly
repetitive and therefore would result in unrealistically high storage savings. For a real
5
www.tegile.com/solutions/sql
Document:DW-3 Edit Version1.0
world environment, this would be very likely turned on since the latency penalty is
minimal and space savings are dramatic.
Snapshots (application-consistent): Tegile storage arrays can be used to present
multiple copies of the same data from a single source. When copies are updated, only
deltas will be saved. Using this feature can result in significant savings when
Development, Test, Production and DW and BI environments all “use” the same copy of
the data. Snapshots are created instantly regardless of the size of the database and
therefore are a great way to create instant replicas of the database.
Note: While many other vendors have Snapshots, they all face two core problems: (a)
they are unable to deliver the required IOPS to serve all of the “copies” or (b) the
snapshots themselves are not application-consistent. The vendor is missing the VSS
driver. Both of these problems render the feature effectively useless. In both cases, the
solution such a vendor will recommend is to copy the data someplace else causing very
poor utilization of expensive storage resources and will be a time consuming activity.
Replication: IntelliFlash storage arrays can do asynchronous replication for both in-site
and remote-site scenarios. The scheduling granularity is completely controllable by the
customer and has zero impact on latency at the Primary Site. Replication can be used
either alone or in concert with SQL Server AlwaysOn to provide fast in-site and remotesite replication and high availability when business continuity is critical.
Cloud Analytics: Tegile IntelliCare delivers industry leading cloud analytics platform,
which is for reporting, advanced troubleshooting and proactive support of customer and
the Tegile support team.
High Level Tegile Array Architecture:
6
www.tegile.com/solutions/sql
Document:DW-3 Edit Version1.0
Data Warehouse Fast Track Certification:
7
www.tegile.com/solutions/sql
Document:DW-3 Edit Version1.0
Tegile T3800 Performance Overview
The testing and certification process was completed by running Microsoft designed and controlled
benchmarking system that includes a full set of tests that simulate real world situations and is based on
terabytes of TPC-H-like data.
Tegile purposefully chose very inexpensive server with total of only 16 cores and 128 GB of RAM to
demonstrate the power of all-flash storage.
The certificate above essentially certifies the Tegile T3800 single shelf (2U) array for up to 34TB of active
DW with 103 TB as the maximum size of such data warehouse.
It is important to point out that Microsoft considers the total amount of data loaded into the system as
the metric used to sizing. Meaning, SQL Server DW running on top of Tegile T3800 could fit in up to 103
TB of data, when it is in a raw format sitting on an uncompressed drive. It is obviously not going to take
up 103TB of flash storage on the Tegile array.
As these 103 TB are loaded into SQL server, SQL Server will apply data compression reducing the
amount of data the first time. In addition, Tegile will apply hardware level compression and deduplication as the data is saved by SQL server into the storage array, allowing all of 103TB of data fit into
a single 2U T3800 shelf.
A real world example from Tegile Array management UI is included below. The below is an example of
production virtulized SQL Server deployment. The 7.69TB is the data which was saved by SQL Server
onto the array. Inline compression reduced that to 4.68TB and inline deduplication reduced that to
2.72TB for a total saving of 64%. This does not include any savings due to snapshots which of course
dramatically compound the space as well.
8
www.tegile.com/solutions/sql
Document:DW-3 Edit Version1.0
Hardware Overview
The hardware chosen for this FT RA solution is highlighted below. The goal of this section is to provide a
high level layout of the hardware environment.
Component
Server
CPU
Total Sockets
Cores Per Socket
Total Physical Cores
Total Logical Cores
Memory
Internal Storage
HBA
Component
Storage Array
Operating System
SQL Server
Details
Tegile SM- SM X9DRW-7/iTPF
Intel Xeon E5-2670, 2.6GHz with Hyper-Threading
2
8
16
32
128 GB DDR3 (8 X 16GB DIMM's)
2x Intel 520 Series SSD (200GB configured in RAID 1)
3x QLogic QLE2562 (dual port) (8Gbps)
Details
Tegile IntelliFlash T3800
Windows Server 2012 R2 Standard
SQL Server 2014 Enterprise CU4 (Build 12.0.2430)
As of April 2015, Teilge All Flash Arrays can scale
upto 336TB or RAW flash.
With compression and deduplication the usable
range is 1.0 Petabytes to 1.6 Petabytes in 10U
configuration.
All with 5 YEARS OF FRESH FLASH warranty.
9
www.tegile.com/solutions/sql
Document:DW-3 Edit Version1.0
Summary
The Tegile’s Microsoft SQL Server 2014 Fast Track Reference Architecture delivers price performance
balanced architecture and ease of manageability for demanding Data Warehousing and BI scenarios.
Tegile solution is certified for upto 34TB Data Warehouse and can hold upto 103TB of Data. Tegile does
this with100% Flash solutions, while preserving time proven enterprise architecture. The configuration is
easy to expand in size or performance . It is easy and simple to utilize for single solution or multi-tenant
databases and as a service scenario with complex billing. The system represents exceptionally reliable
and predictable infrastructure. With a total required space of 2U for storage, the system saves space
and power as compared to many other solutions with the same performance parameters. The
evolution of enterprise storage has placed flash memory in a strategic position to provide the most
usable storage compared to dollars spent over spinning disks. Tegile is pushing the envelope even
further by ensuring the best utilization of Flash across the entire storage subsystem. Tegile offers a wide
array of All-Flash and Hybrid solutions that meet demanding performance requirements without
breaking the budgets.

For more information, visit www.tegile.com/solutions/sql

To find out how to buy the solution or get a POC, contact sales at [email protected] and mention
SQL Data Warehouse Fast Track solution.
5 YEARS OF FRESH FLASH
Enterprise workloads require enterprise-grade SSDs and we guarantee it for 5 years, even in
high write I/O environments. Furthermore, we will upgrade your storage controllers at no
additional charge after 5 years.
© Copyright 2014 Tegile Systems, Inc. The information contained herein is subject to change without notice. The only warranties
for Tegile Systems products and services are set forth in the express warranty statements accompanying such products and
services. Nothing herein should be construed as constituting an additional warranty. Tegile Systems shall not be liable for technical
or editorial errors or omissions contained herein.
Microsoft, Windows, and SQL Server are U.S. registered trademarks of Microsoft Corporation. Intel and Xeon are trademarks of
Intel Corporation in the U.S. and other countries. All other trademarks and copyrights are property of their respective owners. All
rights reserved.
10
www.tegile.com/solutions/sql
Document:DW-3 Edit Version1.0