• No results found

Microsoft SQL Server 2014 Fast Track

N/A
N/A
Protected

Academic year: 2021

Share "Microsoft SQL Server 2014 Fast Track"

Copied!
10
0
0

Loading.... (view fulltext now)

Full text

(1)

www.tegile.com/solutiuons/sql

Microsoft SQL Server 2014 Fast Track

34-TB Certified Data Warehouse

103-TB Maximum User Data

Tegile Systems Solution Review

(2)

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

(3)

www.tegile.com/solutions/sql

Document:DW-3 Edit Version1.0

3

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.

(4)

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.

(5)

www.tegile.com/solutions/sql

Document:DW-3 Edit Version1.0

5

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 active-active 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) solid-state drives. The actual usable space is even larger due to inline compression and de-duplication. 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

de-duplication. 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

(6)

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 remote-site 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.

(7)

www.tegile.com/solutions/sql

Document:DW-3 Edit Version1.0

7

(8)

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

de-duplication 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.

(9)

www.tegile.com/solutions/sql

Document:DW-3 Edit Version1.0

9

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

Details

Server

Tegile SM-

SM X9DRW-7/iTPF

CPU

Intel Xeon E5-2670, 2.6GHz with Hyper-Threading

Total Sockets

2

Cores Per Socket

8

Total Physical Cores

16

Total Logical Cores

32

Memory

128 GB DDR3 (8 X 16GB DIMM's)

Internal Storage

2x Intel 520 Series SSD (200GB configured in RAID 1)

HBA

3x QLogic QLE2562 (dual port) (8Gbps)

Component

Details

Storage Array

Tegile IntelliFlash T3800

Operating System

Windows Server 2012 R2 Standard

SQL Server

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.

(10)

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 sales@tegile.com 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.

References

Related documents

Microsoft Data Warehouse Fast Track (DWFT) for SQL Server 2014 on System x3850 X6 improves time to value for your data warehouse with a new, scalable reference architecture

This Reference architecture examines the storage efficiencies and the performance profile of Microsoft SQL Server 2014 OLTP workloads when using the always-on Inline Data

Students will learn how to create a data warehouse with Microsoft SQL Server 2014, implement ETL with SQL Server Integration Services, and validate and cleanse data with SQL

Delegates will learn how to create a data warehouse with Microsoft SQL Server 2014, implement ETL with SQL Server Integration Services, and validate and cleanse data with SQL

Students will learn how to create a data warehouse with Microsoft SQL Server 2014, implement ETL with SQL Server Integration Services, and validate and cleanse data with SQL Server

Students will learn how to create a data warehouse with Microsoft SQL Server 2014, implement ETL with SQL Server Integration Services, and validate and cleanse data with SQL

§  Fast Track Data Warehouse Reference Guide for SQL Server

This module describes Analysis Services tabular data models and explains how to develop a tabular data model by using the SQL Server Data Tools for BI add-in for Visual