what dbas should know about windows server 2012 · pdf file•performance and scalability...

November 6-9, Seattle, WA

What DBAs Should Know About Windows Server 2012 [DBA-208]

Victor Isakov

Database Architect | Trainer

SQL Server Solutions

Victor Isakov

Victor Isakov is a Database Architect / Trainer who enjoys a reputation of being

one of the most knowledgeable subject matter experts on SQL Server and

related technology. Victor has provided consulting and training services around

the world for over 15 years. Victor Isakov has authored a number of books on

SQL Server and worked with Microsoft to develop the SQL Server exams and

certification.

• SQL Server / Business Intelligence / SharePoint Training

• SQL Server / Business Intelligence / SharePoint Consulting

• Performance Tuning / Troubleshooting

• SQL Server Infrastructure Reviews (“Health-checks”)

• Solution Architecture Design / Review

Email: [email protected]

Blog: www.victorisakov.com

Twitter: @victorisakov

LinkedIn: www.linkedin.com/in/victorisakov

2 | What DBAs Should Know About Windows Server 2012

SQL 2012: What’s New in the Data Platform

The release of Microsoft SQL 2012 server revolutionises how organisations will deploy highly-available and scalable database solutions on premise, and in the cloud. Microsoft has also taken the opportunity with this release to redesign their certification programme “for the cloud”.

In this seminar Victor Isakov (Microsoft Certified Architect, Microsoft Certified Master and Microsoft Valuable Professional (MVP)) will explore the key new features for the Data Platform in SQL Server 2012.

This seminar will enable both IT professionals and decision makers to evaluate the functionality and business value of SQL Server and help mitigate the risks inherent in an upgrade scenario.


• Agenda

• New SQL Server certification

• SQL Server 2012 courses

• Licensing and Edition changes

• SQL Server AlwaysOn technology for Mission Critical

environments

• Performance and Scalability improvements

• What’s new for database administrators

• What’s new for developers

• Upgrade process

• Business benefits of upgrading to SQL Server 2012


• Date

• Wednesday, 14 November 2012

• 5:00 – 5:30pm Registration (light food and beverage

will be provided)

• 5:30 – 6:30pm Presentation

• Location

• Saxons Training Facilities

• Level 10, 10 Barrack Street, Sydney

• RSVP: [email protected]

I’m from Australia…

• So I travel a lot…

• Question: What’s the best thing about travelling?

• Answer: Many things…

• Question: What’s the worst thing about

travelling?

• Answer: Being able to compare prices!

[Session Code]

Razor Blades

• What’s the price in US?

• USD $16.99

• AUD $16.33

• What’s the price in Australia?

• USD $23.63

• AUD $22.73

[Session Code]

Cheese


• USD $15.99/lb (USD $35.18/kg )

• AUD $ 15.37/lb (AUD $33.83/kg)


• USD $37.34/lb (USD $82.15/kg)

• AUD $35.91/lb (AUD $79.00/kg)

[Session Code]

80” LED TV


• USD $ 4,499

• AUD $4,327

• Currently on sale at Best Buy

• USD $3,999

• AUD $3,846


• USD $11,437

• AUD $10,999

[Session Code]

Abstract

Windows Server 2012 is here! As a DBA, you should understand Microsoft's new operating system and its important set of features that will change the way you deploy SQL Server and design high availability solutions in your environment.

In this session, you'll learn about the benefits that Windows Server 2012 brings to SQL Server, see how to install and configure Windows Server 2012 for a SQL Server deployment, and explore new features that will affect your virtualization and high availability architectures.

[Session Code] 10 | What DBAs Should Know About Windows Server 2012

Let’s Start the Show with a Quiz…

Scenario: I am a developer.

Question: What does that mean?

Answer: I don’t have to know anything about

SQL Server!

Scenario: I am a DBA.


Answer: I don’t have to know anything about

Windows!


Agenda

• Editions

• Licensing

• Features

• Hyper-V

• SMB

11/14/2012 |


Windows Server 2012 Editions

• Licensing greatly simplified

• Hint! Hint! SQL Server team…

• Available in two general purpose editions

• Standard

• Datacenter

• Both editions will have the same technical

capabilities

• Both editions licensed by the same paired-

processor model


Recap: Windows Server 2008 Limits

Edition Processors Memory Features

Standard 4 4GB

Enterprise 8 64GB

Datacenter 32 64GB


x86

x64 Edition Processors Memory Features

Standard 4 32GB

Enterprise 8 2TB

Datacenter 64 2TB

Recap: Windows Server 2008 R2 Limits

Edition Processors Memor

y

Foundation 1 8GB

Standard 4 32GB • Licensed for 1 VM

Enterprise 8 2TB • Licensed for 4 VMs

Datacenter 64

(256 logical)

2TB • Unlimited virtualization rights


Only available on 64-bit architecture

Windows Server 2012 Limits

Edition Processors Memory Features

Foundation 1 32GB

Essentials 2 64GB Licensed either in 1 VM or 1 physical

Standard 64 4TB Licensed for 2 VMs

Datacenter 64 4TB Licensed for unlimited VMs

Hyper-V 4TB


Virtualization Rights

• Windows Server 2012 Datacenter includes

unlimited virtualization rights

• Windows Server 2012 Standard includes the

right to run up to two VMs per license

• Windows Server 2012 Standard licenses may be

stacked to run more VMs

• Example: If a server has 2 processors and your

organization wants to run 8 VMs simultaneously

on this server, you may assign the server 4

Windows Server Standard 2012 licenses


Licensing

• Each Standard and Datacenter server license

covers 2 physical processors

• The number of processor cores in a physical

processor has no impact on licensing rules

• You must buy sufficient licenses to cover all

processors in a server

• Licenses are sold in pairs

• A license pair cannot be split across physical

servers


Features

• Windows Core

• ReFS

• Storage Spaces

• Data Deduplication

• CHKDSK

• NIC Teaming

• Failover Clustering


Windows Core

• Benefits • Reduced memory footprint

• Reduced storage footprint

• Less patches to apply • Does not necessarily mean less “reboots”

• Use cases • Virtualization

• Availability

• Security

• SQL Server 2012 supports installation on Windows Server Core 2012 • Windows Server Core 2008 R2 SP1


Storage Today

• Larger volumes

• Large capacity drives

• Number of current storage problems

• Torn writes

• Bit rot

• RAID becoming “unreliable”

• “Industry need” to move away from hardware

RAID


ReFS (Resilient File System)

• Need for a new file system

• ReFS goals

• Maintain high degree of NTFS compatibility

• Verify and auto-correct data

• Never take the file system offline

• Optimize for extreme scale

• Provide a full end-to-end resiliency architecture


ReFS Features

• Data corruption avoided

• Allocate-on-write eliminates corruption from in-place update errors

• Checksums detect whether the data on disk has changed since it was last written

• Data integrity protection

• File system metadata is always protected, maximizing online operation

• Optional user data protection (integrity streams)

• Configurable on a per-volume, per-directory, and per-file basis


ReFS Features

• Rapid recovery from file system corruption

without affecting volume availability

• If redundant copy available, automatic correction

• If not, salvage done online and only corrupt

portion of namespace affected

• Scalability

• ReFS efficiently scales to PB datasets comprising

very large files

• Uses B+ tree internally


ReFS – Current Limitations

• Only available on Windows Server SKUs

• Cannot boot from ReFS

• Cannot convert NTFS to ReFS


ReFS vs NTFS


• User data transactions

• Sparse

• Hard-links

• Extended attributes

• Quotas

• NTFS features not implemented

• Named streams

• Object IDs

• Short names

• Compression

• File level

encryption (EFS)

ReFS and SQL Server

• Should you use ReFS for SQL Server?

• Nope!

• Actually…

• ReFS is NOT supported

• But…

• I do think it can still be useful. Where?

• Storing database backups!

• Especially when combined with Storage

Pools


Storage Spaces

• New paradigm

• Powerful new platform abstractions • Storage pools:

• Units of aggregation, administration, isolation

• Storage spaces:

• Resiliency, provisioning, and performance

• Simple (similar to RAID 0)

• Mirror (similar to RAID 1)

• Parity (similar to RAID 5)

• Industry standard commodity storage • Interconnects: SAS, USB, and SATA

• Enclosures: Shared SAS JBOD arrays


Storage Deployment Strategies


Cost-efficient Storage (New) Traditional Storage

Block Access

File Access

JBODs App Servers

SAS

JBODs File Servers

SAS

App Servers

File

\db

App Servers

FC

iSCSI

File Servers

FC

iSCSI

App Servers

File

\db

Physical Deployment Topologies


Better Hardware Utilization

Enables mixed deployments

• Utilize both SSD and HDD media types

• Control placement at storage space granularity


Storage Spaces Model

11/14/2012 |

Windows Server

2012

Capabilities

File and Storage Manager

Failover Clustering & CSV

v2

ReFS & NTFS

Hyper-V

SMB v3 RDMA

Storage Mgmt.

PowerShell

Virtualized Resilient Storage

Mirror Space

Parity Space

Mirror Space

Mirror Space

Mirror Space

Parity Space

Parity Space

Mirror Space

Mirror Space

Mirror Space

Mirror Space

Parity Space

Cost-efficient

shared SAS

JBOD arrays

Physical or Virtualized Servers


Storage Spaces: Optimized and Flexible Capacity Utilization

• On-demand provisioning with automated capacity reclamation (trim/unmap)

• Capacity reclamation for deleted files (NTFS and ReFS)

• Sub-file capacity reclamation (e.g. through Hyper-V)

• Scheduled optimization for slab reclamation

• Elastic capacity expansion • Simply add more drives

11/14/2012 |

60TB Space

60TB Space

60TB Space

60TB Space

Storage Pool A

60TB Space

60TB Space

60TB Space

60TB Space

Storage Pool B


Storage Spaces: Resiliency to Drive Failure

• Resilient storage

• Mirror spaces with dirty region tracking

• Parity spaces with integrated journaling

• Rapid recovery via per-pool hot spares

11/14/2012 |

Mirror Space

Parity Space

Mirror Space

Mirror Space

Mirror Space

Parity Space

Mirror Space

Mirror Space

Resilient Spaces


0

1000

2000

3000

4000

5000

1 2 4 8 12 16 20 24 28 32

Th

rou

gh

pu

t M

B/s

Simple Spaces vs. Native Disks

Read Throughput Simple

Spaces

1MB Sequential Read, 7.2K RPM SAS

0

1000

2000

3000

4000

5000

1 2 4 8 12 16 20 24 28 32

Th

rou

gh

pu

t M

B/s


Write Throughput Simple

Spaces

1MB Sequential Write, 7.2K RPM SAS

0

1000

2000

3000

4000

5000

6000

7000

1 2 4 8 12 16 20 24 28 32

IOP

S


Read IOPS Simple

Spaces

4K Random Read, 7.2K RPM SAS

0

1000

2000

3000

4000

5000

6000

1 2 4 8 12 16 20 24 28 32

IOP

S


Write IOPS Simple

Spaces

4K Random Write, 7.2K RPM SAS


Storage Spaces: Performance

ReFS and Storage Spaces

Data Volume

NTFS

Scrubber

Data Volume

ReFS

Data

Copy0

Data

Copy1

Data

Copy2

On media failure, NTFS

will get an alternate

copy of the data

With NTFS, Scrubber

verifies all copies can

be read

Checksum is inspected

on all copies of the

data

If corruption detected,

trigger auto-correct

and return a healthy

copy of the data

With ReFS, Scrubber

verifies checksums

on all copies and auto-

corrects as needed

Copy1

Data C

H

K

Copy2

Data C

H

K

Copy0

Data C

H

K

Mirror Space

Mirror Space



Storage Spaces Demo

Data Deduplication

• New to Windows Server 2012

• Increases storage capacity while using less

physical storage space

• Not the same as compression

• SQL Server use cases

• Large FILESTREAM objects

• Database backups

• Databases?


Data Deduplication – Sub-file Level

• Does not work at the file level

• Sub-file chunking • Segments files into variable-sizes • 32-128 kilobyte chunks

• Chunking module splits a file into a sequence of chunks in a content dependent manner

• Chunks have an average size of 64KB • Chunks are compressed

• Chunks are placed into a chunk store located in a hidden folder at the root of the volume called the System Volume Information, or “SVI folder”.


Data Deduplication Architecture


Deduplication Evaluation

• Portable deduplication evaluation tool to helps

evaluation of datasets

• When the feature is installed, DDPEval.exe is

installed to the \Windows\System32\ directory

• Copy tool and run on Windows 7 or later

systems to determine the expected savings

• Local drives

• Mapped remote shares

• Unmapped remote shares


CHKDSK

• CHKDSK performance has traditionally been

related to the number of files and size of

volume

• NTFS self-healing feature was introduced in

Windows Server 2008 to reduce the need to run

CHKDSK

11/14/2012 |


CHKDSK Improvements

• Online self-healing • Windows Server 2012 increased the number of issues that

can be fixed online

• If all issues that are detected are self-healed online, there is no need for an offline repair.

• Online verification • Some corruptions are intermittent due to memory issues

and may not be a result of an actual corruption on the disk

• New service: Spot Verification Service

• Filter system driver which gets triggered by file system driver

• Verifies actual corruption

• Background service that does not effect normal operations


CHKDSK Improvements

• Online identification and logging • When an issue is verified an online scan of the file system is triggered which

runs as a maintenance task in the file system

• As file system is scanned issues that are found and logged for later correction

• Precise and Rapid Correction • When appropriate volume can be taken offline & the logged corruptions fixed

• Downtime from this “Spotfix” operation takes only seconds

• With cluster shared volumes, there is no downtime

• Now the CHKDSK offline run time is now directly proportional to the number

of corruptions, not the number of files.

• ReFS follows a different model for resiliency • Does not require an offline chkdsk to repair corruptions

• No need to run the traditional chkdsk utility


CHKDSK: Process

11/14/2012 |


CHKDSK: Performance


NIC Teaming

• Combines two or more network adapters so that the software above the team perceives them as a single adapter that incorporates failure protection and bandwidth aggregation

• Load Balancing and Failover (LBFO)

• Microsoft’s NIC teaming is NIC agnostic

• Microsoft supported

• SQL Server also takes advantage of Windows Server 2012’s improved Quality of Service (QoS) support.

• QoS lets you set minimum and maximum bandwidth management for multiple SQL Server services over a network adapter.

• Ensures that SQL Server can always get the network bandwidth that it requires


Failover Clustering

• Increased scale out and scale up

• Scale out to 64-nodes

• Scale up to 4,000 VMs per cluster

• Much faster failover times

• Cluster Shared Volumes (CSV) v2 supported for

general use


Hyper-V

• Scalability

• VHDX

• NUMA Support


Scalability

System Resource

Maximum number Improvement

factor WS 2008 R2 WS 2012

Host

Logical processors on hardware 64 320 5×

Physical memory 1 TB 4 TB 4×

Virtual processors per host 512 2,048 2×

Virtual

machine

Virtual processors per virtual

machine 4 64 16×

Memory per virtual machine 64 GB 1 TB 16×

Active virtual machines per host 384 1,024 2.7×

Virtual disk size 2 TB 64 TB 32×

Cluster Nodes 16 64 4×

Virtual machines 1,000 8,000 8×

160

2 TB

2x

2x

2048 -

64 -

1 TB -

512 2x

32 2x

3000 2.7x

2 TB 32x


Hyper-V: Virtual Disks

• New VHDX format for virtual disks

• Better: • Performance

• Support native 4KB disks (with no 512 emulation) with no emulation

• Offer 4KB aligned VHDX file

• Capacity

• 64TB (versus 2TB for VHDs)

• Reliability

• Number of mechanisms to prevent corruption within VHDX files in the event of a power failure

• Example: Updates are logged to the VHDX metadata as a fallback mechanism


Hyper-V: NUMA

• Hyper-V makes the

guest OS aware of

NUMA on the host

• Windows VMs can

then schedule their

internal processes

and memory

according to the

NUMA boundaries

of that VM


Summary: Scalability Improvements

System Resource Maximum Number

Improvement WS 2008 R2 WS 2012

Host

Logical processors on hardware 64 320 5×

Physical memory 1 TB 4 TB 4×

Virtual processors per host 512 1,024 2×

Virtual

machine

Virtual processors per virtual

machine 4 64 16×

Memory per virtual machine 64 GB 1 TB 16×

Active virtual machines 384 1,024 2.7×

Virtual disk size 2 TB 64 TB 32×

Cluster Nodes 16 64 4×

Virtual machines 1,000 4,000 4×


SMB

• What is SMB?

• SMB 3.0

• Performance

• Features

• Scale-Out File Server


What is SMB?

• A brief history of the Server Message Block protocol

• SMB – IBM Technical Reference 6322916 (1984)

• Renamed to CIFS in 1996

• SMB over TCP/IP – Windows 2000 (1999)

• SMB 2.0 – Windows Vista / Server 2008 (2006)

• Improved scalability

• Improved performance; larger reads/writes

• More secure

• SMB 2.1 – Windows 7 / Server 2008 R2 (2009)

• File leasing improvements

• Larger MTU support

• BranchCache


(Perceived) Problems with SMB

• CPU for TCP/IP processing

• Network bandwidth

• Alleviated with teaming, but unsupported

• Slow timeouts

• Active/Passive clustering


SMB 3.0

• Really SMB 2.2

• But it has so much GOODNESS it deserved a

better version number!

• SMB MultiChannel

• SMB Direct

• SMB Transparent Failover

• SMB Encryption

• Potential game changer for how SQL Server

infrastructure is designed


SQL TPC-C Benchmark

• Transactions per Second

4315 (100%)

1175 (27.23%)

4270 (98.95%)

Direct (4Gbps FC) SMB 2 (1 GbE) SMB 3 (1 GbE)


SQL Server Support for SMB

• SMB support was enabled via trace flags in

earlier versions of SQL Server

• Earlier versions of SQL Server only supported

user databases on SMB shares

• SQL Server 2012 now supports both user

databases and system databases on SMB shares


SQL Server Support for SMB


SMB Direct

• SMB over Remote Direct Memory Access (RDMA)

• Enables direct memory-to-memory data transfers between servers

• Minimal CPU utilization

• Low latency

• Requires using standard RDMA-capable network adapters (R-NIC)

• iWARP, InfiniBand, and RoCE

• Easy to provision

• No application changes

• Cheaper alternative to FC!

File Client File Server

SMB Server SMB Client

User

Kernel

Application

Disk

R-NIC

Network w/

RDMA

support

NTFS

SCSI

Network w/

RDMA

support

R-NIC


SMB MultiChannel

• Uses multiple TCP connections for each SMB session if available

• Improved throughput • Bandwidth aggregation with multiple NICs

• Utilizes multiple CPUs for network processing with multiple or RSS-capable NICs

• Can combine with LBFO teams

• Automatic failover • Implements end-to-end failure detection and recovery

• Leverages NIC teaming if present

• Does not require it

• Automatic configuration • SMB detects and uses multiple network paths


SMB MultiChannel Performance

• Preliminary results using four 10GbE NICs simultaneously

• Linear bandwidth scaling

• 1 NIC – 1150 MB/sec

• 2 NICs – 2330 MB/sec



• Leverages NIC support for RSS (Receive Side Scaling)

• Bandwidth for small IOs is bottlenecked on CPU

0

500

1000

1500

2000

2500

3000

3500

4000

4500

MB

/sec

I/O Size (in bytes)

SMB 2.2 Client Interface Scaling -

Throughput

1 x 10 GbE 2 x 10 GbE 3 x 10 GbE 4 x 10 GbE


NIC Teaming vs SMB MultiChannel

• Reliability

• NIC Teaming provides protection against failures

in the host

• SMB Multichannel provides multi-path

protections

• More bandwidth is always a good thing

• NIC Teaming and SMB Multichannel both provide

bandwidth aggregation when possible

• NIC Teaming and SMB Multichannel work

together! 64 | What DBAs Should Know About Windows Server 2012

SMB MultiChannel – Examples

• Vertical lines are logical channels

Multiple 1GbE NICs

SMB Server

SMB Client

Switch

1GbE

NIC

1GbE

NIC

1GbE

Switch

1GbE

NIC

1GbE

NIC

1GbE

Multiple RDMA NICs

(cannot be teamed)

SMB Server

SMB Client

NIC

10GbE/IB

NIC

10GbE/IB

Switch

10GbE/IB

NIC

10GbE/IB

NIC

10GbE/IB

Switch

10GbE/IB

Multiple 10GbE

in a NIC team

SMB Server

SMB Client

NIC Teaming

NIC Teaming

Switch

10GbE

NIC

10GbE NIC

10GbE

NIC

10GbE NIC

10GbE

Switch

10GbE

Single 10GbE

RSS-capable NIC

SMB Server

SMB Client

Switch

10GbE

NIC

10GbE

NIC

10GbE

RSS

RSS


SMB Transparent Failover

• Failover transparent to server application

• Zero downtime

• Just short I/O delay during failover

• Supports both planned and unplanned failovers

• Resilient for both file and directory operations

• Witness service allows failover without waiting for TCP timeout on client

File Server Cluster

SQL Server

Failover to

Node B

Normal

operation

Connections &

handles auto-

recovered

Application I/O

continues with

no errors

File Server Node A File Server Node B

\\fs1\share \\fs1\share


SMB Transparent Failover - Internals

• SMB 3.0 Client

• Client operation replay

• End-to-end support for replay of read, write and delete operations

• SMB 3.0 Server

• Support for network state persistence

• Files are always opened Write-Through


SMB 3.0 Server

SMB 3.0 Client SMB 3.0 Server

User

Kernel

User

Kernel

Witness

Service

Witness

Client

Witness

Protocol

Redirector

File System

Resume Key

Filter

Server

SMB 3.0

Protocol

Operation

Replay

State

Persistence

User

Kernel

SMB Transparent Failover - Internals

• Resume Key

• Resume handle state after planned or unplanned failover

• Fence handle state information

• Mask some NTFS failover issues

• Witness Service

• Proactive notification of server failure

• Enables dynamic reallocation of load


SMB 3.0 Server

SMB 3.0 Client SMB 3.0 Server

User

Kernel

User

Kernel

Witness

Service

Witness

Client

Witness

Protocol

Redirector

File System

Resume Key

Filter

Server

SMB 3.0

Protocol

Operation

Replay

State

Persistence

User

Kernel

SMB Transparent Failover Configuration

• Need to set up as …


SMB Encryption

• Encrypt sensitive “data in flight”

• Configure per share or server

• Only SMB 3.0 clients can access

• Hardware accelerated

• Industry standard AES


Scale-Out File Server

• “Active-Active” file server cluster

• Increased bandwidth

• CHKDSK with zero downtime

• Simpler management

• SAN or JBOD shared storage

• Continuously available shares

• Low-metadata workloads only

Scale-Out File Server

Cluster Shared Volume

Single Logical File Server (\\fs\share)

Single File System Namespace


SQL Server with SMB Small Size Architecture

• Single-Node File Server

• Low cost

• Shares not continuously available

• Limited scalability (~100 spindles)


File Server Cluster

SQL Server

Share 1 Share 2

SQL Server

Shares SAS Storage

SQL Server with SMB Medium Size Architecture

• Dual-Node Cluster File Server • Medium cost

• Shares continuously available

• Medium scalability (~200 spindles)


Node A

SQL Server

Share 1

Node B

SQL Server

Share 1

Fiber Channel Storage Array

SQL Server with SMB Large Size Architecture

• Multi-Node Cluster File Server • Higher cost

• Shares continuously available

• Highest scalability (~1000 spindles)


Node A

SQL Server

Share 1

Node B

SQL Server

Share 2

Node C

SQL Server

Share 1

Node D

SQL Server

Share 2

Summary

Scenario: I am a DBA.


Answer: I know enough about Windows to be

VERY DANGEROUS!



Thank you for attending this session and

the 2012 PASS Summit in Seattle

[Session Code]

what dbas should know about windows server 2012 · pdf file•performance and scalability...

Documents