Realizing the Benefits of Upgrading Microsoft SQL Server

Realizing the Benefits of Upgrading Microsoft SQL Server

Spark the future. May 4 8, 2015 Chicago, IL BRK2579 Realizing the Benefits of Upgrading Microsoft SQL Server Victor Isakov MCA, MCM, MCT, MVP [email protected] Abstract With Microsoft planning to release a new version of SQL Server every couple of years your organisation should take a new approach to upgrading your SQL Server solution(s) so as to realise numerous benefits and maximize your return on investment. You should consider an upgrade project as an opportunity to make your SQL Server infrastructure more

resilient, agile and optimized. In this session we will concentrate on the refactoring opportunities that you can take advantage of to improve performance, manageability or availability. With useful tips and examples along the way this should prove to be a very practical session that can help you build the case for upgrading SQL Server within your organisation. Rationale / Motivation Business As Usual Mentality Upgrade like-for-like No innovation No analysis of new capabilities to see if there is a fit No re-investigation if current solution is still optimal / relevant Groundhog Day Databases Database stuck doing the same thing day in, day out Organizations not getting value for

investment Licensing Cost: Enterprise Agree / Software Assurance Storage Cost No improvement in throughput which would translate to business benefits Goal: Change the conversation with business Agenda Current State Upgrade Process Database Engine Configuration Manageability High Availability Database Refactoring Opportunities Future State Current State

Industry Observations Microsoft is planning on releasing a new version of SQL Server every 18-24 months SQL Azure updated continuously Industry is still managing SQL Server like they were in the 1990s In a lot of cases even more risk averse Need to be more agile Less processes for SQL Server infrastructure compared to other IT infrastructure Change Management Release Management Incident Management SQL Server 2005 support ends April 12, 2016 Upgrade Blockers Vendor Software not supported on later SQL Server editions

Learn from mistakes of past Risk Database / code will break after Unknown dependencies A lot of risk is perceived risk Lack of Resources IT Pro / Developers Domain level knowledge There are plenty of tools out there than can help analyze your current state [BRK4551] Upgrading and Migrating SQL Server (Bob Taylor) [BRK2577] Upgrading to Microsoft SQL Server 2014 and to Microsoft Azure (Mike Shelton; Ross LoForte) Caveats Applying Previous Knowledge / Experience Example: Failover Clustering

Automatically Using New Functionality Examples: Availability Groups Failover Clustering: Fileshare Witness Business As Usual Forever (BAUF) Mentality Start engaging the business Start thinking tactically, strategically Processes Technology by itself rarely delivers Need to change you processes Upgrade Process Opportunity An upgrade project represents an opportunity to standardize, optimize, consolidate, etc

Learn from the mistakes of the past Develop SOE for Windows Standardization Disk layout Windows Core Develop SOE for SQL Server Standardization Configuration Define Tiers Tiers Define Tiers for your SQL Server infrastructure For most organizations 3 tiers are sufficient Consider future growth Consider Azure

Define Service Levels for Tiers Important to engage the business for Service Levels: Importance to business Performance Availability Map applications to appropriate Tiers Decouple an application from a SQL Server instance An application becomes a service that gets delivered to the business Goal is provide agility at the application level Tiers (Concept) TIER 1 A DATABAS E LOB App

B A B C Separate LUNs for Data and Log Separate LUNs for TEMPDBs Data and Log Separate LUNs for Data and Log

8 vCPUs 128 GB AG 4 vCPUs 32 GB Cluster 4

vCPUs 64 GB Cluster 4 vCPUs 32 GB 4 vCPUs 16 GB X

X BizTalk X VMware ESX C TIER 3 SharePoint


2 vCPUs 8 GB B 1 vCPU 4 GB Environments Developer (DEV) Can use SQL Server Developer Edition to reduce licensing costs User, Acceptance & Testing (UAT)

Can use SQL Server Developer Edition to reduce licensing costs Production (PROD) Consider using cloud for UAT (and DEV) environments Upgrade Process I still see organizations not doing some of the basics as part of the upgrade process In some cases problems persist through subsequent upgrades Run these basics: Run DBCC CHECKDB with the DATA_PURITY option Run DBCC UPDATEUSAGE Change page verification to CHECKSUM Can use TDE to ensure checksum is calculated for all database pages Change database compatibility flag as appropriate SQL Server Features

SQL Server Time Line Microsoft is more aggressively releasing new version of SQL Server going forward SQL Server Version RTM Date Delta SQL Server 2016 Summer 2016* SQL Server 2014 April 2014 25 months

SQL Server 2012 March 2012 23 months SQL Server 2008 R2 April 2010 20 months SQL Server 2008 August 2008 33 months

SQL Server 2005 November 2005 120 months SQL Server 2000 November 2000 24 months SQL Server 7.0 November 1998 - Approx. 24 months

SQL Server 2008 (R2) Compression Page compression Row compression Backup Compression Added to SQL Server Standard Edition in SQL Server 2008 R2 Extended Events FILESTREAM New DATE and TIME Data Types SQL Server 2012 Availability Groups Evolution of Database Mirroring Secondary replicas can be readable Can have multiple readable secondary replicas Can run DBCC / backup operations off secondary replicas

Columnstore Indexes Read-only Nonclustered SQL Server 2014 Clustered Columnstore Indexes Buffer Pool Extensions Available in all SQL Server SKUs Availability Group Enhancements 8 replicas Improved availability with the readable secondaries when the primary replica being offline In-Memory OLTP Tables Memory-optimized database engine integrated into the SQL Server engine In-Memory OLTP is optimized for OLTP

Delayed Durability Database Engine Configuration Maximum Memory Support SQL Server 2014 Standard Edition Supports 128GB Earlier versions of SQL supported SQL Server 2012: SQL Server 2008R2: SQL Server 2008: SQL Server 2005: Server Standard Edition were limited with the amount of RAM 64GB 64GB Operating system maximum

Operating system maximum Take Advantage of Window Server Core Much smaller memory footprint Additional benefits Reduced surface area Less patching required* Ability to achieve higher virtualization densities Supported since SQL Server 2012 Buffer Pool Extension Extend SQL Servers Buffer Pool to Flash Buffer Storage Designed for OLTP workloads Only clean pages are written to BPE file No need for RAID Offload I/O path from SAN, SMB shares, etc

Benefits Increased random I/O throughput Reduced I/O latency Increased transaction throughput Improved read performance with a larger hybrid buffer pool A caching architecture that can take advantage of present and future low-cost memory drives Database Engine Configuration Options optimize for ad hoc workloads Improves efficiency of the plan cache for workloads that contain many single use ad hoc batches When a batch is compiled for the first time the Database Engine only stores a small compiled plan stub in the plan cache, instead of the full compiled plan Helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused

backup compression default Determines whether the server instance creates compressed backups by default blocked process threshold Specifies the threshold (in seconds) at which blocked process reports are generated Uses the Deadlock Monitor background thread The blocked process report is done on a best effort basis There is no guarantee of any real-time or even close to real-time reporting Deadlocks Contention is often overlooked as root cause for performance problems 1204 and 1222 Trace Flags are so old school Investigate how to use Extended Events instead Email developers through database engine whenever a deadlock occurs Great way of getting deadlocks solved

Trace Flags Investigate trace flags and assess whether they are appropriate for your SQL Server SOEs TRACE DESCRIPTION Source FLAG 1117 Ensures equal autogrowth of all files in file group 1118 [tempdb] system database optimization (Use uniform extents only) Fast Track DW 2.0

Architecture BOL 1204 / 1222 Deadlock detection BOL 3023 CHECKSUM option is automatically enabled for the BACKUP command KB2656988 3226 Prevents successful backup operations from being logged to ERRORLOG

BOL 4199 Query Optimizer fixes BOL Manageability Backup Compression Released in SQL Server 2008 Supported in Standard Edition since SQL Server 2008 R2 Controlled at Different Levels Server Level BACKUP statement Trace Flag 3023 (Backup CHECKSUMs)

Trace flag 3023 automatically enables the CHECKSUM option for the BACKUP command Validation of page checksum if the database has the PAGE_VERIFY option set to CHECKSUM and the database page was last written by using checksum protection Generation of a backup checksum over the backup streams that are written to the backup file Documented: KB2656988 Use to make sure that all backups use the backup CHECKSUM You do not have to rewrite all the existing backup scripts You use utilities such as log shipping or maintenance plans These utilities and the associated stored procedures do not provide a CHECKSUM option SQL Server 2014: backup checksum default High Availability High Availability

Availability Groups seem to be the high availability de jour Make sure you understand the use case for Failover Clustering versus Availability Groups It very much depends on your application Very highly recommended to implement a Proof-of-Concept Failover Clustering Be careful with applying previous knowledge and experience Failover Clustering has substantially changed in Windows Server 2012 / R2 Quorum types Fileshare Witness Dynamic Quorum Network configuration NIC Teaming SMB 3 Support

Make sure you re-evaluate Windows Server 2012 / R2 Failover Clustering: Configuration Classic configuration 2 separate networks 1 dedicated to cluster traffic 1 dedicated to cluster and client traffic IPv6 disabled (Fileshare Witness) Availability Groups Multiple replicas with their own storage 2 replicas in failover pair Asynchronous / synchronous Replicas can be utilized: Read-only workloads Backups Database consistency checks

Caveats Databases must use FULL recovery model Can affect application behaviour / performance Job management more complex Real World Example: Batch Process CRM database solution 2TB OLTP database 6TB document store database Existing solution SQL Server 2008 R2 Failover Cluster + disk-based SAN New solution SQL Server 2014 Availability Groups with local PCIeSSDs

Key batch processes End-of-Day End-of-Week Availability Group Overhead Nightly Batch Process Daily Overnight Rebuild Weekend Overnight Rebuild 160 700 138 140 600

120 102 100 84 80 72 70 81 81 29 34

68 468 452 436 300 212 40 475 494 522 500 248

227 200 100 20 0 500 513 400 80 60 40

634 No AG AG (FO) AG (FO + Sync) Min Average Max FCI 0

No AG AG (FO) AG (FO + Sync) Min Average Max FCI Delayed Durability Allows client applications to continue without waiting for acknowledgement of COMMIT from transaction log Not hardening logs effectively before commit

Potentially very useful for Availability Groups BE AWARE OF IMPLICATIONS! Database Database File Layout Databases are only getting larger Impacts recoverability Impacts disaster recovery Difficult to manage Take advantage of file groups Improved backup / restore capabilities Improved storage / capacity / performance management SELECT * FROM sys.dm_io_virtual_file_stats Real World Use Case: File Groups Database file layout:

Real World Use Case: File Groups sys.dm_io_virtual_file_stats File Name Number of Reads Number of Bytes Read 3,307,566,972,9 28 System 21,167,016 Log 35,307 63,196,548,096

1,224,097,431,5 18,295,835 52 4,674 9,367,928,832 84 5,005,312 84 5,005,312 2,371,033,858,0 1,830,963 48 Indexes Deprecated Audit Temp AccountMedia AccountRebuildLog

I/O Stall Read I/O Stall Number Number of I/O Stall I/O Stall Queued of Writes Bytes Write Queued Read Written (ms) Write (ms) (ms) 13,926,11 36,838,93 430,417,707,0 26,817,93 9 0

6 08 0 53,295,31 597,217,190,4 28,414,50 88,158 0 9 00 7 11,704,40 26,995,16 252,918,013,9 18,969,15 6 0 0 52 0 30,768 0 269

6,307,840 158 87 0 16 131,072 5 75 0 16 131,072 5 38,852,55 6 0 9,865 129,916,928 4,109 Entry

594,016 403,912,130,560 1,244,190 3,432,744,927,2 14,745,79 9,375,144 32 0 Entry_Indexes 2,473,552 562,829,582,336 3,615,644 TransactionAudit TransactionAudit_Index es Data 259,983 543,635,234,816 1,276,844 5,898,013 733,996,007,424 4,606,510 137

17,883,136 49 0 46,657 634,077,184 33,863 0 67,365 2,995,478,528 54,579 42,354,139,13 0 4,645,614 6 3,916,374 0

35,225 558,620,672 12,388 52,926,455,80 0 5,890,031 8 4,223,188 0 24 524,288 21 I/O Stall Size on Disk (bytes) 40,744,04 90,855,440,38 9 4 28,502,66 104,857,600,0

0 5 00 30,673,55 48,003,153,92 0 6 0 0 30,926 403,701,760 0 92 104,857,600 0 80 104,857,600 38,856,66 81,893,785,60 0 5 0 13,258,981,37 01,278,053

6 14,800,36 106,430,464,0 0 9 00 24,851,251,20 07,532,018 0 31,876,710,40 01,289,232 0 39,321,600,00 08,829,698 0 0 70 104,857,600 0 Database Compression

Available since SQL Server 2008 Effectively fit more data in the Buffer Pool Query performance improvement Reduced backup size and duration Row Compression Page Compression SQL Server Compression Estimator Real World Use Case: Database Compression Page compression was implemented when SQL Server 2005 was upgraded to SQL Server 2008 Selectively enabled on tables

with appropriate benefit Still have a 40% storage saving over the last Query performance improved Database backups improved Size Duration Real World Use Case: Database Compression Large DSS / OLAP tables Report queries reduced from 2+ hours to >30 minutes No application change required Columnstore Indexes Available since SQL Server 2012 Nonclustered Columnstore indexes were read-only

Clustered Columnstore available in SQL Server 2014 Columnstore index representation In-Memory Columnstore Stores data in columnar format for massive compression Both memory and disk Built-in to core RDBMS engine 10-100x faster Work on existing hardware Easy upgrade Easy deployment C1 C2 C3 C4 C5 C6 SQL Server 2014: Clustered

Columnstore Indexes Updatable, clustered Columnstore now supported No need for separate base table No need for other indexes New COLUMNSTORE_ARCHIVE compression Better compression at higher CPU cost: 80-90% compression Far fewer data type restrictions than SQL Server 2012 More operators support batch mode processing Need to refactor your Transact-SQL code back potentially Clustered Columnstore Batch Processing

Biggest advancement in query processing in years! Data moves in batch through query plan operators Highly efficient algorithms, better parallelism SQL Server 2014 Support for all flavours of JOINs OUTER JOIN Semi-join: IN, NOT IN UNION ALL Scalar aggregates Mixed mode plans Real World Use Case: DSS/OLAP No application change required Substantial improvement in query performance Notice there is no index usage for columnstore indexes

No nonclustered indexes Real World Use Case: Auditing Business Requirements Compliance reasons Necessary evil Workloads Predominantly INSERT activity Hardly ever queried / Never queried Technology Clustered Columnstore Partitioning Opportunity Minimal risk Minimal refactoring

Real World Use Case: Auditing (Storage) Heap: Baseline Page Compression: 38.5% Clustered Columnstore: CCI: 13.4% ACCI: 10.4% Real World Use Case: Auditing (Querying) No such thing as free lunch As expected we pay a performance penalty with point queries

Refactoring Opportunities Technical Debt Start paying your technical debt Data types deprecated in SQL Server Better data types Better programming patterns Need to develop strategy to mitigate future risk Release Management Change Management Cloud readiness Deprecated Data Types TEXT / IMAGE data types are deprecated Variety of options VARBINARY(MAX)

FILESTREAM FILETABLE Considerations Performance Storage Cost Windows Deduplication Buffer Pool / WIN32 path Dont forget impact on backup strategy 20 minutes versus 3 hours Better Data Types Consider NOT using DATETIME data type DATE TIME DATETIME2 Better Programming Patterns Safer Programming

TRY / CATCH TRY_PARSE TRY_COVERT Review TRANSACTIONs BEGIN TRAN / COMMIT TRAN SET XACT_ABORT Common Table Expressions Sequential patterns Contained Databases Azure SQL Database version V12 Future State Goals Database is a service that meets business SLAs

Engage the business ASAP in the Upgrade Project Decouple database(s) from SQL Server instances Agility between Tiers at database level Ability to upgrade at database level Documentation Release Management Change Management Cloud Readiness Please evaluate this session Your feedback is important to us! Visit Myignite at

or download and use the Ignite Mobile 2015 Microsoft Corporation. All rights reserved.

Recently Viewed Presentations

  • PowerPoint Sunusu

    PowerPoint Sunusu

    C-TPAT relies on a multi-layered approach consisting of the following five goals: Ensure that C-TPAT partners improve the security of their supply chains pursuant to C-TPAT security criteria. Provide incentives and benefits to include expedited processing of C-TPAT shipments to...
  • Meeting of the Steering Group for Simulation Issues

    Meeting of the Steering Group for Simulation Issues

    Background "Correlation Function" or "Meta-Model" is the tool chosen in this project for evaluating WLTP-based CO 2. emissions from NEDC-based ones and vice-versa given the technical features of a given vehicle. Different approaches can be followed, with theoretical foundations, different...
  • Classification of Animals Review

    Classification of Animals Review

    If you were an animal in the desert, why would you burrow underground during the day and become active at night?
  • Global Change 1: Physical Processes Environ 110 Biol

    Global Change 1: Physical Processes Environ 110 Biol

    Click on LectureTools at bottom left. Choose "enter LectureTools" Fill in the required fields for registration. You will automatically be added to LectureTools. 2. Click on 'Student Registration' 3. Enter "481" as the zip code (upper right) 4. Choose 'University...
  • Math 260 - Faculty Server Contact | UMass Lowell

    Math 260 - Faculty Server Contact | UMass Lowell

    Ch 8.1 Numerical Methods: The Euler or Tangent Line Method The methods we have discussed for solving differential equations have emphasized analytical techniques, such as integration or series solutions, to find exact solutions.
  • Suffix Trees and their applications - BGU

    Suffix Trees and their applications - BGU

    Trie and Compressed Trie. Suffix Tree. Trivial Construction Algorithm O(N^2) Exact string matching. Generalized suffix tree. Applications. Trie. A tree representing a set of strings.(Assume no string is a prefix of another) ... Suffix Trees and their applications
  • 2.5 Linear Equations and Formulas:

    2.5 Linear Equations and Formulas:

    FACTORING ax2 + bx + c . Procedure: 1) Always look for the GCF of all the terms. 2) Factor the remaining terms - pay close attention to the value of coefficient . a. and follow the proper steps. 3)...
  • $100 $100 $100 $100 $200 $200 $200 $200

    $100 $100 $100 $100 $200 $200 $200 $200

    Why is water considered polar? One side is (-), One side is (+) Even distribution of charges It has a north & south pole It has a negative and positive side A chemical reaction in which polypeptides are broken down...