640T Data Warehouse Fast Track Reference Architecture For .
640TB Data Warehouse Fast Track ReferenceArchitecture for Microsoft SQL Server 2017 usingATOS BullSequana S800 and Dell EMC VMAX 250FConfiguration and performance resultsAbstractThis paper describes the design principles andguidelines used to achieve an optimally balanced640TB Data Warehouse Fast Track referencearchitecture for SQL Server 2017 using ATOSBullSequana S800 servers and Dell EMC VMAX 250Fall flash storage arrays.September 2018
RevisionsDateAuthor(s)20/10/2018 Roger Van Unen,Martin HalamekDescriptionFinal versionVersion1.0AcknowledgementsAuthors: Roger Van Unen ATOS, Martin Halamek Dell EMCSpecial thanks to Erwan Prevost, Greig Lilienfeld and Ryno Coetzee from Dell EMC, ArnaudAumonier, Benoit Gonsolin and Virgil Chetty from ATOS BDS and Jamie Reding and Donovan Whitefrom MicrosoftSQL Server 2017 DWH Fast Track Certification for ATOS BullSequana S800 and Dell EMC VMAX 250F2
Table of ContentsRevisions . 2Acknowledgements. 2Executive summary . 5Introduction . 6Target audience . 6Data warehouse workload challenges . 7SQL Server 2017 . 8SQL Server Data Warehouse Fast Track . 9Recommended reference architectures . 10Single server reference architecture . 10High available reference architecture. 12Hardware components . 14ATOS BullSequana S800 server . 14Emulex LPe31002-M6 16 Gbps Host Bus Adapter . 14Brocade 6510 switch . 15Dell EMC VMAX 250F . 15Scalable Performance . 15Mission-Critical Availability . 16Hyper Consolidation. 16Storage configuration . 17Hardware configuration of Dell EMC all flash array. 17Front and Rear View of the system. 17Engine Slots and FA Ports Layout. 18Logical configuration of the array . 18LUN / Disk configuration and FA port assignments . 19VMAX storage groups (SG). 20Read and write cache. 20Server connectivity and multi-pathing. 20Cabling. 21Cabling single server configuration. 21Cabling high available server configuration . 22BullSequana S800 server configuration . 23System BIOS . 23Emulex LPe31002-M6 16 Gbps Host Bus Adapter . 23Windows Server 2016 configuration . 25SQL Server 2017 DWH Fast Track Certification for ATOS BullSequana S800 and Dell EMC VMAX 250F3
Installation . 25Drivers and packages installed. 25Power plan . 26Lock pages in memory . 26Windows disks . 27MPIO . 29Windows Defender configuration. 30SQL Server 2017 Enterprise Edition Configuration . 31Grant perform volume maintenance task privilege. 31Tempdb configuration . 31Start-up parameters for the SQL Server instance . 31SQL Server maximum memory . 32Maximum Degree of parallelism (MAXDOP) . 32Resource governor . 33Database configuration. 33Additional considerations for the Highly Available (HA) reference architecture . 33DWFT certification for ATOS BullSequana S800 with Dell EMC VMAX 250F . 34Summary . 35Technical support and resources Dell EMC . 36Bill of Materials (BOM). 37SQL Server 2017 DWH Fast Track Certification for ATOS BullSequana S800 and Dell EMC VMAX 250F4
Executive summaryATOS, Dell EMC and Microsoft , in cooperation, provideguidelines and principles to assist customers in designing andimplementing a balanced configuration for Microsoft SQLServer data warehouse workloads to achieve out-of-the-boxscalable performance. These certified database referencearchitectures enable each of the components in the databasestack to provide optimal throughput to match the databasecapabilities of the specific setup.ATOS BullSequana S 3th generation servers, along with robustand cutting-edge Dell EMC VMAX flash array, form efficientcandidates for a high performing, large data warehousesolution. The in SQL Server 2017 integrated Python and Rlanguage modules makes it possible to perform, even in realtime, ML operations without moving data from the datawarehouse. The implementations of Python and R are based onthe open source implementations giving access to thousands ofsolutions for ML and statistics.This paper describes the design principles and guidelines usedto achieve an optimally balanced 640TB Data Warehouse FastTrack (DWFT) reference architecture for SQL Server 2017 usingBullSequana S800 servers and Dell EMC VMAX 250F flash array.The configuration used to achieve the performance numbers forthe reference configuration is presented in detail.SQL Server 2017 DWH Fast Track Certification for ATOS BullSequana S800 and Dell EMC VMAX 250F5
IntroductionToday’s enterprise businesses face a constant challengekeeping pace with the huge data processing and storagerequirements generated by all aspects of their business. Asthey face the daunting task of scaling their DBMS systems tomeet short term as well as long term requirements, one of thefirst decisions to be made is whether to scale up (addadditional resources to existing systems), or scale out (addadditional separate systems).Up until now, the choices for scaling up systems for mediumand large mission critical businesses has been very limited. Notonly must the system be able to scale past four sockets, itmust also support a storage system that can easily scalecapacity and/or performance as CPU and memory resourcesare increased. The ATOS BullSequana S400 and S800 with theDell EMC VMAX flash array sets a new pace for scalability andexpandability while ensuring flexibility for all transaction,analytical, and data warehouse workloads.The ATOS BullSequana S coupled with Dell EMC VMAX storagearrays makes an ideal scale up configuration for fast growingenvironments. With the ability to scale up to 8 CPU socketswith 12TB of memory and virtually unlimited storage capacityin a mission critical package, Microsoft SQL Server 2017 canmeet the need for all business sizes and requirements.Excellent manageability and support ensures that, in theunlikely event of a failure or fault, Dell EMC proactivemanagement systems can easily rectify (automatically in somecases) or mitigate issues before they become detrimental tomission critical system uptime.Target audienceThe target audience for this paper includes databaseadministrators, business intelligence architects, storageadministrators, IT directors, and data warehousing usersseeking sizing and design guidance for Business Intelligencesolutions with SQL Server 2017.SQL Server 2017 DWH Fast Track Certification for ATOS BullSequana S800 and Dell EMC VMAX 250F6
Data warehouseworkload challengesOrganizations use data warehouses to aggregate datacollected from operational systems and elsewhere andprepare data for analysis.A traditional data warehouse workload consists of: Periodic data load from operational data stores/applications. Complex queries run by business analysts to get insight intothe data for better decision making. Such queries aggregatelarge amounts of data across multiple tables, often running forlong durations of time while consuming significant I/Obandwidth.To speed up query performance, the data is pre-aggregatedfor the efficient execution of commonly occurring querypatterns. New challenges face both designers andadministrators managing mission-critical data warehouses.Data growthAs the number of IoT devices increase, the data in datawarehouses is growing exponentially. In this environment, it isimportant to use solutions that provide high data compressionwithout compromising query performance, while reducingstorage and I/O bandwidth. Integrated Machine Learning toolslike Python and R makes it possible to run ML models on datawithout moving the data.Both the ATOS BullSequana S400 and the S800 with the DellEMC VMAX are designed to grow with your needs. In thebeginning of 2019, the ATOS BullSequana will be able to scaleup to 32 CPU’s and the Dell EMC VMAX can scale up to 4 PB.Reducing data latencyData latency refers to the time required to access data foranalytics in a data warehouse. Data load and transformationcan be a resource-intensive operation that interferes with theongoing analytics workload. To minimize the impact onbusiness users, the extract, transform, and load (ETL) processtypically takes place during off-peak hours.In today’s global economy, however, there are no off-peakhours. Businesses are striving to reduce data latency bymaking data available for analytics within minutes or secondsof its arrival in operational data stores. This requires loadingincremental data into the data warehouse in real time or nearreal time.Faster query responseCustomers require most complex analytic queries to be able toreturn results in seconds—to enable interactive dataexploration at the speed of thought. ATOS and Dell EMCdeveloped a solution with a large amount of memory and flashstorage to address this need.SQL Server 2017 DWH Fast Track Certification for ATOS BullSequana S800 and Dell EMC VMAX 250F7
SQL Server 2017Microsoft SQL Server 2017 has made significant improvementsin data warehousing technologies and performance, includingcolumn-store features as well as many other improvements.Column-store indices offer great advantages over traditionalrow stores for analytics and data warehousing queries. Theyare ideally suited for the star schemas, and tables with billionsof rows, which are commonly seen. Among their advantagesfor analytics are: Up to 10X compression in data size: Data warehousesare very large by nature and the compression offeredby column-store index technologies offers both spaceand cost savings as well as significantly increasedperformance. These benefits are possible due to thedramatically reduced I/O requirements given by thecompression and coupled by the ability to only scanthe specific columns required by each query. Thiscompression also reduces the amount of memoryrequired to hold a given number of rows from thesource data warehouse. Additional indices: SQL Server 2017 adds the capabilityto add (B-Tree) indices to column store-based tables,which enables efficient single-row lookup.In addition to these architectural features, Microsoft SQLServer 2017 has made significant improvements in optimizingthe processing of queries in column-store indices in thefollowing ways: Operator pushdown: Pushdown refers to moving bothfilter and aggregation query operations closer to thedata, so that many of the filters and calculations canbe done in the scan operators, dramatically reducingthe volume of data that needs to be handled furtheron in query processing. Batch-mode processing: SQL Server 2017 includesenhancements in batch-mode processing that handlesmany rows at a time rather than serially doingcalculations on each individual row. These batchoperations are further optimized by leveraging SingleInstruction Multiple Data (SIMD) vector processingCPU instructions in the Intel architecturesSQL Server 2017 DWH Fast Track Certification for ATOS BullSequana S800 and Dell EMC VMAX 250F8
SQL Server DataWarehouse Fast TrackThe SQL Server Data Warehouse Fast Track (DWFT) program isdesigned to provide customers with standard and provensystem architectures optimized for a range of enterprise datawarehousing needs. The goal is to help enterprise customersdeploy data warehouse solutions with a recommendedhardware configuration appropriate for the requirements ofthe workload with reduced risk, cost, and complexity.Enterprises can purchase and build on referenceimplementations from participating system vendors orleverage the best practice guide provided through theprogram. The DWFT reference architecture program iscontinuously being improved to incorporate new SQL Serverfeatures and customer feedback.When enterprises use the DWFT program to set up a datawarehouse built on SQL Server, they lay the foundation for acomplete Data Management Platform for Analytics. They canthen take advantage of newer SQL Server features, includingin-memory column store technologies that improve theperformance of transactional and analytics workloads, as wellas the ability of SQL Server to run on either Windows or Linux.They also gain support for both traditional structuredrelational data and for unstructured big data, such as Internetof Things (IoT) data stored in Hadoop, Spark, or an Azure DataLake, all the while being able to query the data in languagessuch as T-SQL, Java, C/C , C#/VB.NET, PHP, Node.js, Pythonand Ruby. By using PolyBase, a feature in SQL Serveroptimized for data warehouse workloads, enterprisecustomers can also merge big data into the SQL Serveruniverse. PolyBase provides the ability to query both relationaldata and unstructured data, joining it together into a singleresult set without moving the data.This document defines DWFT component architecture andmethodology. The result is a set of SQL Server databasesystem architectures and configurations—including softwareand hardware—required to achieve and maintain a set ofbaseline performance levels out-of-box for a range of datawarehousing workloads.SQL Server 2017 DWH Fast Track Certification for ATOS BullSequana S800 and Dell EMC VMAX 250F9
RecommendedreferencearchitecturesThe following subsections describe the two different DWFTreference architectures for SQL Server 2017, comprised of ATOSBullSequana S800 servers and Dell EMC VMAX flash arrays.Single server referencearchitectureFigure 1 illustrates the single server reference architecture withthe major elements and Table 1 lists the component details.Public networkBullSequana S800Brocade 6510Brocade 6510Dell EMC VMAX 250FFigure 1: single server reference architectureSQL Server 2017 DWH Fast Track Certification for ATOS BullSequana S800 and Dell EMC VMAX 250F10
Table 1 Single server reference ionBullSequana S800ProcessorsTotal coresTotal Logical ProcessorsTotal RAMHost Bus AdaptersNetwork AdaptersOS disksOperating systemDatabase softwareArrayI/O cardsDisk drivesSAN switches8x Intel Xeon Platinum 8180M processors (2.5MHz, 28 cores and 56 threads)22444812 terabytes in 128 GB DIMM’s10x Emulex LPe31002-M6 adapter8x Intel X722 10 Gbps 8x Intel X722 SFP 800 GB SSD RAID1Windows Server 2016 Standard EditionSQL Server 2017 Enterprise Edition Core version2x Dell EMC VMAX 250FX VBRCK BASE 2048GB4x VMAX 250F 8MM 8 ports 16G FC64x VMAX 250 RAID5(7 1) 3840GB2x Brocade 6510 with 16Gbps SFPsSQL Server 2017 DWH Fast Track Certification for ATO
Dell EMC VMAX flash array sets a new pace for scalability and expandability while ensuring flexibility for all transaction, analytical, and data warehouse workloads. The ATOS BullSequana S coupled with Dell EMC VMAX storage arrays makes an ideal scale up configuration for fast growing environments. With the ability to scale up to 8 CPU sockets
Management under Master Data Define Warehouse Numbers. 2. Check the warehouse number assignment in Customizing for Extended Warehouse Management under Master Data Assign Warehouse Numbers. 3. Check the warehouse number control in Customizing for Extended Warehouse Management under Master Data Define Warehouse Number Control.
Issued by Manual Publication Section, Yamazaki Mazak Corporation, Japan 08. 2002 PROGRAMMING MANUAL for ALL MAZATROL TURNING CNC (including T-Plus) MAZATROL FUSION 640T NEXUS Progr
The MikroTik Fast Path and Conntrack's work together gave the name Fast Track. Fast Track Fast Path extentions Only Ipv4 TCP/UDP (Total Traffic %99) FastTrack management is left to network admin FastTrack can be used on devices with Fast Path support. After the first packet of the connection passing through the router is marked as Fast Track .
On-premises Fast Track: The Data Warehouse Fast Track program, built on a Symmetric Multiprocessing (SMP) Reference Architecture, is an on-premises solution for a data warehouse with up to 145 TB compute capability and 1.2 PB of storage capability. Customers can work with Microsoft partners to select the
location: fort worth, tx warehouse status: approved county: tarrant warehouse capacity: 85,000 warehouse code: 853007 001 location(s) warehouse name: eugene b smith & company , inc license type: unlicensed location: galveston, tx warehouse status: approved county: galveston warehouse capacity: 37,180 warehouse code: 858054 001 location(s)
location: fort worth, tx warehouse status: approved county: tarrant warehouse capacity: 85,000 warehouse code: 853007 001 location(s) warehouse name: eugene b smith & company , inc license type: unlicensed location: galveston, tx warehouse status: approved county: galveston warehouse capacity: 37,180 warehouse code: 858054 001 location(s)
1.3 Common Data Warehouse Tasks 1-4 1.4 Data Warehouse Architectures 1-5 1.4.1 Data Warehouse Architecture: Basic 1-5 1.4.2 Data Warehouse Architecture: with a Staging Area 1-6 1.4.3 Data Warehouse Architecture: with a Staging Area and Data Marts 1-6 2 Data Warehousing Logical Design 2.1 Logical Versus Physical Design in Data Warehouses 2-1
Banking on Cloud A discussion paper by the BBA and Pinsent Masons Outside of banking, public cloud computing has proven to be a driver of innovation, enabling new competitors, products and more flexible business models. By comparison, banks have been understandably slower in migrating products and services and leveraging the benefits of the public cloud, taking time first to focus on assessing .
