Modern Storage Strategies For SQL Server
Modern StorageStrategies forSQL ServerWritten byDavid Klee,Microsoft MVP & VMware vExpertand James Green,VMware vExpert
TheGorilla Guide To.Modern Storage Strategies forSQL ServerWritten byDavid KleeMicrosoft MVP and VMware vExpertand James GreenVMware vExpert
The Gorilla Guide to Modern Storage Strategies for SQL ServerAuthor:David Klee, Microsoft MVP and VMware vExpert,James Green, VMware vExpertEditors:Hillary Kirchener, Dream Write CreativeBook Design:Braeden Black, Avalon Media ProductionsGeordie Carswell, ActualTech MediaLayout:Braeden Black, Avalon Media ProductionsProject Manager: Amy Short, ActualTech MediaCopyright 2016 by ActualTech MediaAll rights reserved. This book or any portion there of may not be reproduced or usedin any manner whatsoever without the express written permission of the publisherexcept for the use of brief quotations in a book review.Printed in the United States of AmericaFirst Printing, 2016ISBN 978-1-943952-10-6ActualTech MediaOkatie Village Ste 103-157Bluffton, SC 29909www.actualtechmedia.com
About the AuthorsDavid Klee,Founder and Chief Architect,Heraflux TechnologiesDavid Klee is a Microsoft MVP and VMwarevExpert with over seventeen years of IT experience. David spends his days focusing on theconvergence of data and infrastructure as theFounder of Heraflux Technologies. His areasof expertise are virtualization and performance,datacenter architecture, and risk mitigationthrough high availability and disaster recovery.When he is not geeking out on technologies,David is an aspiring amateur photographer. Youcan read his blog at davidklee.net, and reach himon Twitter at @kleegeek.David speaks at a number of national andregional technology related events, includingthe PASS Summit, VMware VMworld, IT/Dev Connections, SQL Saturday events, SQLCruise, PASS virtual chapter webinars, andmany SQL Server and VMware User Groups.James Green,Partner, ActualTech MediaJames writes, speaks, and consults on EnterpriseIT. He has worked in the IT industry as anadministrator, architect, and consultant, andhas also published numerous articles, whitepapers, and books. James is a 2014 - 2016 vExpertand VCAP-DCD/DCA.iv
About ActualTech MediaActualTech Media provides enterprise IT decision makers with theinformation they need to make informed, strategic decisions as theymodernize and optimize their IT operations.Leading 3rd party IT industry influencers Scott D. Lowe, DavidM. Davis and special technical partners cover hot topics from thesoftware-defined data center to hyperconvergence andvirtualization.Cutting through the hype, noise and claims around new data centertechnologies isn’t easy, but ActualTech Media helps find the signal inthe noise. Analysis, authorship and events produced by ActualTechMedia provide an essential piece of the technology evaluation puzzle.More information available at www.actualtechmedia.comv
About Tegile SystemsTegile Systems is pioneering a new generation of flash-driven enterprisestorage arrays that balance performance, capacity, features and pricefor virtualization, file services and database applications. With Tegile’sline of all-flash and hybrid storage arrays, the company is redefiningthe traditional approach to storage by providing a family of arrays thataccelerate business critical enterprise applications and allow customersto significantly consolidate mixed workloads in virtualized environments.Tegile’s patented IntelliFlash technology accelerates performanceand enables inline deduplication and compression of data so eacharray has a usable capacity far greater than its raw capacity. Tegile’saward-winning solutions enable customers to better address therequirements of server virtualization, virtual desktop integration anddatabase integration than any other offerings. Featuring both NASand SAN connectivity, Tegile arrays are easy-to-use, fully redundantand highly scalable. They come complete with built-in snapshot,remote-replication, near-instant recovery, onsite or offsite failover, andVM-aware features.For more information, visit www.tegile.comvi
Table of ContentsChapter 1:Introduction to SQL Server and Storage . . . . . . . . . . . . . 11Data and Infrastructure Intersect . . . . . . . . . . . . . . . . . . . . .12The Storage Layer . . . . . . . . . . . . . . . . . . . . . . . . . . . 14The Physical Server . . . . . . . . . . . . . . . . . . . . . . . . . . 15Virtualization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15Interconnects and Networking . . . . . . . . . . . . . . . . . . . 16The Operating System and Applications . . . . . . . . . . . . 16Database Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . 16Modern Data Center Challenges . . . . . . . . . . . . . . . . . . . . .17Complementary Technologies . . . . . . . . . . . . . . . . . . . 18Chapter 2:SQL Server I/O . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .19SQL Server I/O Performance . . . . . . . . . . . . . . . . . . . . . . 19SQL Server Workload Patterns . . . . . . . . . . . . . . . . . . . . . . 20Architecting the SQL Server I/O Stack . . . . . . . . . . . . . . . . 22SQL Server and Virtualization . . . . . . . . . . . . . . . . . . . . . . 24SQL Server Instance . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26Chapter 3:Storage Considerations with SQL Server . . . . . . . . . . . . . 29Performance Metrics . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29Latency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30IOPs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30Throughput . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .31Infrastructure Layer Metrics . . . . . . . . . . . . . . . . . . . . .31Maximums vs. Steady State . . . . . . . . . . . . . . . . . . . . . 33Cache vs. Primary Storage . . . . . . . . . . . . . . . . . . . . . . . . 33Disk Configurations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35Traditional . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36Tiered . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36Hybrid . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37vii
All-Flash . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38Efficiencies and Data Reduction . . . . . . . . . . . . . . . . . . 39Compression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39Deduplication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40Data Savings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40Metadata Management . . . . . . . . . . . . . . . . . . . . . . . .41Chapter 4:Business Continuity & Disaster Recovery . . . . . . . . . . . . .43BC and Disaster Recovery By the Storage . . . . . . . . . . . . . . 43RAID . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43Snapshots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45BC and Disaster Recovery by SQL Server . . . . . . . . . . . . . . . 47Database and Transaction Log Backups . . . . . . . . . . . . 47Log Shipping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47Mirroring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47Availability Groups . . . . . . . . . . . . . . . . . . . . . . . . . . 48Competing or Complementary? . . . . . . . . . . . . . . . . . . . . . 48Chapter 5:SQL Server Considerations . . . . . . . . . . . . . . . . . . . . . .50SQL Server Licensing Reduction . . . . . . . . . . . . . . . . . . . . 50SQL Server vs. Array Features . . . . . . . . . . . . . . . . . . . . . . 53Compression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53Transparent Data Encryption . . . . . . . . . . . . . . . . . . . . 54Partitioning and File Groups . . . . . . . . . . . . . . . . . . . . 55Buffer Pool Extensions . . . . . . . . . . . . . . . . . . . . . . . . 56Monitoring, Management, and Support . . . . . . . . . . . . . . . . 58Monitoring . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Chapter 6:Storage Best Practices . . . . . . . . . . . . . . . . . . . . . . . . 60Hardware and Bottleneck Detection . . . . . . . . . . . . . . . . . . 61Storage Bottlenecks . . . . . . . . . . . . . . . . . . . . . . . . . 61Operating System Bottlenecks . . . . . . . . . . . . . . . . . . . 63Block Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64Tuning for Flash . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66SQL Server Storage Requirements and Budget . . . . . . . . . . . 68Chapter 7:Modernizing SQL Server . . . . . . . . . . . . . . . . . . . . . . . . 71Features in the Latest Version . . . . . . . . . . . . . . . . . . . . . . 71In-Memory OLTP . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71Stretch Database . . . . . . . . . . . . . . . . . . . . . . . . . . . 72Azure Integration . . . . . . . . . . . . . . . . . . . . . . . . . . . 72Buffer Pool Extensions . . . . . . . . . . . . . . . . . . . . . . . . 73Clustered Columnstore Indexes . . . . . . . . . . . . . . . . . . 73SMB3-Based Network Shares . . . . . . . . . . . . . . . . . . . 73Benefit Analysis of Upgrading . . . . . . . . . . . . . . . . . . . . . . 74Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74Widespread Business Benefits . . . . . . . . . . . . . . . . . . . 75Maximizing SQL Server with Flash . . . . . . . . . . . . . . . . . . . 76Perception and Justification . . . . . . . . . . . . . . . . . . . . 76Adoption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77ix
Gorilla Guide FeaturesFood For ThoughtIn the these sections, readers are served tasty morsels ofimportant information to help you expand your thinking.School HouseThis is a special place where readers can learn a bit moreabout ancillary topics presented in the book.Bright IdeaWhen we have a great thought, we express them through aseries of grunts in the Bright Idea section.Dive DeepTakes readers into the deep, dark depths of a particular topic.Executive CornerDiscusses items of strategic interest to business leaders.x
1Introduction to SQLServer and StorageThe volume of data retained and accessed by today’s organizations isexploding at an unprecedented rate. Business expects the IT department to keep data online and accessible indefinitely.Smartphones and unlimited apps require data at speeds and volumesthat have the carriers bursting at the seams. The unstructured datarecorded by the Internet-of-Things phenomenon is in its infancy butalready provides incredible data to the manufacturers. “Big Data” andbusiness intelligence require the storage and immediate retrieval ofterabytes, or even petabytes (and beyond), of data.So of course businesses struggle to keep up. All of this data has to bestored somewhere, somehow. The space must be available to store thedata on. The speed must be there to return the data to the users at thespeed of business, which is always “right now,” with no delay. Thisdata-storage platform must also be readily available in spite of hardwarefailure, natural disasters, or human error.The systems intended to deal with this data have been historicallyvery complex. Full-time infrastructure and database administrators
spend careers protecting the data and the infrastructure around it.Each system component requires planning and testing to make surethat a failure will not hurt the business. Plus, each layer requiresin-depth architecture and careful planning to ensure that the stack willperform at its fullest potential. This means best-of-breed components,cutting-edge techniques, and highly trained staff are needed to engineerthese platforms.As a result, the investment by the business is quite significant. Afterall, the data it protects, and the systems that it resides on, make up thecore of most businesses. If the data is unavailable, or even worse — it’slost — the business is doomed. Sometimes businesses will actually failbecause of the loss of data.Therefore, it is the administrator’s responsibility, first and foremost,to ensure that data is not lost, no matter what event occurs. Ensuringthat the data is presented to the application in a timely manner is a veryclose second.Data and Infrastructure IntersectAs shown in Figure 1-1, many layers exist in the core of these dataplatforms. One traditional challenge with these complex infrastructuresis how the layers interact with each other, and how they are managedas a whole. A best practice for one layer might be a horrible idea foranother layer. An application-specific practice for a layer might hurt theeffectiveness of others.Learning how these layers interact, complement each other, andpotentially compete with each other is vital to architecting the modernon-premises data center.12Chapter 1
ApplicationOperating SystemInterconnectsVirtualizationPhysical ServerNetworkingDatabaseStorageFigure 1-1: The layers of a data centerAdding further complication into the mix is the continued growthof the public cloud. Permutations of public cloud offerings cancomplement and even replace some of the layers in this infrastructure.Applications and/or databases-as-a-service can remove the requirementto manage the operating system underneath them. Applications,databases, and VMs can be replicated to the public cloud for DRpurposes, or can even replace the need to have these componentson-premises altogether.Introduction to SQL Server and Storage13
Data Replication SystemServer InfrastructureCloud Server InfrastructureCloud ProviderYour DatacenterFigure 1-2: Hybrid Cloud DatacenterThe Storage LayerThe bottom layer in this data center model (Figure 1-2) is the storagesubsystem. This device is arguably the most critical layer in the datacenter. It physically stores the data into bits and bytes onto persistentstorage, which is usually made up of spindle-based magnetic orsolid-state flash hard drives.While the storage can be contained within a physical server, thisconfiguration presents a single point of failure in that physical server.14Chapter 1
It is usually configured as a shared device in a Storage Area Network,or SAN. Usually, SANs are in place for simplifying management andreducing the footprint in the data center, and not necessarily because oftheir speed. SANs are designed to be robust and protect the data at allcosts, and to protect against failures.Ordinarily, the SAN is the slowest layer of the infrastructure stack.SANs usually measure response time in milliseconds, while servermemory or CPUs are measured in nanoseconds.The Physical ServerThe next layer is the physical server, which contains the rest of thecompute hardware (CPU, memory, and interconnect adapters). Thislayer is where the actual work of the application is performed. The datais still stored on the SAN.VirtualizationThe physical server normally contains some type of virtualization,which is an added layer treated as an extension of the physical server.This layer, called a hypervisor, allows multiple compartmentalizedoperating systems and their respective applications to coexist independently on the same hardware. The virtual machines submit requestsfor compute resources, and the hypervisor coordinates the access to thephysical compute resource to fulfill the request.By allowing more than one of these virtual machines to run on thesame physical server at the same time, the efficiency of the environmentimproves, and things such as the actual physical server count, rackspace, power, cooling, and interconnect cabling, all get reduced. Thisequipment reduction saves overall data center costs.Introduction to SQL Server and Storage15
While not mandatory, most data centers use virtualization to encapsulate workloads and improve the agility of the data center in order toincrease the responsiveness to an ever-changing world.Interconnects and NetworkingThe physical server must be connected to the SAN and to other serversthrough an interconnect, usually fiber optics or high-speed networking,which provides one or more layers of interconnect switching.Data centers commonly have a dedicated equipment to handle storagecommunication, and another set to handle application communicationso that one does not hinder the performance of the other.The Operating System and ApplicationsThe logical server, be it physical or virtual, contains an operating systemwhere different applications servers are to be installed onto. Some ofthese applications servers contain applications which present the datato the end users, such as a web server, while other applications serversare the database servers that contain the data, such as a SQL Server.Database ServersData must be stored in a system that is designed to store and retrievethe data efficiently. The database server is the gateway to the criticalbusiness data that is stored on the SAN. Microsoft’s flagship dataplatform, SQL Server, is one of the most widely used database enginesin the world, and is at the core of this guide.16Chapter 1
Modern Data Center ChallengesData center topologies are amazingly complex. No two data centers arethe same, but all face common challenges with storing and retrievinglarge volumes of data. These challenges include: Data age. Businesses demand that a lifetime of data remains accessible. This means that the data must all be online, all the time. Cost. Data volumes are growing exponentially but contrary topopular myth, storage prices are not getting any cheaper. SANsare sometimes prohibitively expensive, and are almost always themost expensive component of a data center. Complexity. The complexity of managing data increases dramatically as the volume and performance demands increase. Theagility and flexibility in the environment is reduced, and grows
Microsoft MVP & VMware vExpert and James Green, VMware vExpert Written by Modern Storage Strategies for SQL Server . Written by David Klee . Architecting the SQL Server I/O Stack 22 SQL Server and Virtualization 24 SQL Server Instance 26 .
Bruksanvisning för bilstereo . Bruksanvisning for bilstereo . Instrukcja obsługi samochodowego odtwarzacza stereo . Operating Instructions for Car Stereo . 610-104 . SV . Bruksanvisning i original
SQL Server supports ANSI SQL, which is the standard SQL (Structured Query Language) language. However, SQL Server comes with its own implementation of the SQL language, T-SQL (Transact- SQL). T-SQL is a Microsoft propriety Language known as Transact-SQL. It provides further capab
MS SQL Server: MS SQL Server 2017, MS SQL Server 2016, MS SQL Server 2014, MS SQL Server 2012, MS SQL Server 2008 R2, 2008, 2008 (64 bit), 2008 Express, MS SQL Server 2005, 2005 (64 bit), 2005 Express, MS SQL Server 2000, 2000 (64 bit), 7.0 and mixed formats. To install the software, follow the steps: 1. Double-click Stellar Repair for MS SQL.exe.
Server 2005 , SQL Server 2008 , SQL Server 2008 R2 , SQL Server 2012 , SQL Server 2014 , SQL Server 2005 Express Edition , SQL Server 2008 Express SQL Server 2008 R2 Express , SQL Server 2012 Express , SQL Server 2014 Express .NET Framework 4.0, .NET Framework 2.0,
70 Microsoft SQL Server 2008: A Beginner’s Guide SQL_2008 / Microsoft SQL Server 2008: ABG / Petkovic / 154638-3 / Chapter 4 In Transact-SQL, the use of double quotation marks is defined using the QUOTED_ IDENTIFIER option of the SET statement. If this option is set to ON, which is theFile Size: 387KBPage Count: 26Explore furtherLanguage Elements (Transact-SQL) - SQL Server Microsoft Docsdocs.microsoft.comThe 33 languages of SQL Server Joe Webb Blogweblogs.sqlteam.comThe Language of SQL Pdf - libribooklibribook.comSql And The Standard Language For Relational Database .www.bartleby.comdatabase - What are good alternatives to SQL (the language .stackoverflow.comRecommended to you based on what's popular Feedback
Use \i FULL_PATH_ass1.sql to load your ass1.sql where FULL_PATH_ass1.sql is the full path of your answer file (ass1.sql) Use \i FULL_PATH_check1.sql to load check1.sql where FULL_PATH_check1.sql is the full path of the check file (check1.sql) reate a new database for mymy2 database. Repeat the above steps using mymy2.dump and check2.sql
SQL Server 2005 SQL Server 2008 (New for V3.01) SQL Server 2008 R2 (New for V3.60) SQL Server 2012 (New for V3.80) SQL Server 2012 R2 (New for V3.95) SQL Server 2014 (New for V3.97) SQL Server 2016 (New for V3.98) SQL Server 2017 (New for V3.99) (Recommend Latest Service Pack) Note: SQL Server Express is supported for most situations. Contact .
BasiC Counselling skills Let’s get down to basics. The word ‘basic’, when used in conjunction with counselling skills, implies a repertoire of central counselling skills on which you can base your helping practice. Another related meaning of the term ‘basic’ is that of being fundamental or primary rather than advanced. The quality of the helper–client relationship is essential to .
