Sql Server Architecture for World Domination Tristan Wilson

Sql Server Architecture for World Domination Tristan Wilson

Topics • • • Sql Server editions Scaling Sql Server Cloud + Virtual +Physical Hardware Migration strategies HADR Options

Definitions • High availability – Increased uptime • Disaster recovery – Protection from negative events • Scale-up = Bigger/faster servers • Scale-out = More servers

When do I scale up? • CPU is above threshold – Based on percent usage, or increased threads • Sql Server memory usage – page life expectancy, lazy writes, free list stalls • Increased I/O requirements • Look at your wait stats!

Scaling out Sql Server • Read-only – Always. On, Database mirroring, log shipping, replication, data loading tools • Read/write – Peer-to-peer replication, linked servers, distributed queries, sharding, custom solutions – Parallel data warehouse

Sql Server editions • Express Edition – Maximum 10 gb database size • Web Edition – Licensed through a hosting provider • Standard Edition – 16 -core 64 gb ram (128 gb for 2014+) – 2016 is now licensed for 2 -node Always. On

Sql Server editions • Business Intelligence Edition – Includes features specifically for BI – Same CPU/Ram limitations as Standard • Enterprise Edition – Coolest features, unlimited CPU/Ram

Data the database • • 25 -250 gb worth of data Low performance Moderate downtime Just a friendly database

The Cloud • Cloud databases • Virtual machines in the cloud • Vendors: – Azure, AWS – Managed hosting: , Rackspace, etc.

Cloud Database Pro: Con: • • • Low cost Low management Flexible, scalable Quick deployment Easy upgrades Limited features Limited control Platform dependencies Security Difficult migrations

Virtual Machines Pro: Con: • Cost effective • Highly availability • Flexible + Scalable • Limited resources • Performance overhead • Management overhead

Virtual Clusters • It does work! • Reduced downtime for reboot/patching • Check with your VM administrator

How do I scale-up Data? • Increase cloud or virtual resources

Data the database • • 250 gb-1 TB worth of data Increase performance Lower downtime Wasn’t this database supposed to be temporary?

Physical Hardware • When do I go with physical hardware? – Increased CPU/Memory/IO consumption – Maximize performance – Specialized hardware requirements

Selecting Hardware • Select your sockets + processors • Maximize ram • Don’t neglect local storage (get SSD’s!)

Selecting Processors • Maximize speed over core count • Intel E 5 -2637 v 3 – 4 cores at 3. 5 ghz • Intel E 5 -2640 v 3 – 8 cores at 2. 4 ghz

High Availability • Fail-over cluster • Database mirroring • Always. On

High Availability - Clustering Pro’s Con’s • Hardware/OS redundancy • Low downtime patching • Sliding upgrades • Idle second node • No redundant storage • Typically not a DR solution

High Availability – DB Mirroring Pro’s Con’s • Fully redundant solution • No shared storage • 2 -node in standard edition • • Deprecated feature Mirroring overhead Additional witness server Limited automatic fail-over

High Availability - Always. On Pro’s Con’s • • • Enterprise-only • Mirroring overhead • Double storage costs Fully redundant solution HA+DR Reporting+Maintenance No shared storage Easy migration

How do I scale-up Data? • Export/import of data from cloud • Backup/restore

Data the database • • 1 -3 TB+ worth of data High performance No downtime Getting’ tired of his crap

Disaster Recovery • Log Shipping • Always. On

DR – Log Shipping Pro’s Con’s • Web edition or higher • Only network connectivity • Low impact on primary • Some data loss • No automatic fail-over

DR – Always. On Pro’s Con’s • Low or no data loss • Must be in Windows cluster • Can be automatic fail-over • Enterprise only

How do I scale-up Data? • Sliding upgrade with Clustering or Always. On • Log shipping or database mirroring to new server

Hybrid Architecture • On-premise hardware, DR in cloud • Primary physical server, virtual DR • Reporting and maintenance with Always. On

Review • Choose the right edition • Start small and plan to scale • Include HADR solutions

Sql. Tristan@gmail. com @Sql. Tristan