How Available is SQL Server 2016 Warwick Rudd

How Available is SQL Server 2016? Warwick Rudd – Warwick@sqlmastersconsulting. com. au Henry Rooney – Henry. Rooney@williamhill. com. au DAT 336

Warwick Rudd @Warwick_Rudd Warwick@sqlmastersconsulting. com. au www. sqlmastersconsulting. com. au

Henry Rooney @Henners 72 Henry. Rooney@williamhill. com. au www. williamhill. com. au

Abstract

High Availability

High Availability Technologies Accessibility Workloads Recovery Point Objectives SQL Server Edition Malicious Attacks Single Site Disasters Accidental Data loss Multi-Site Recovery Level Objectives Operating System Failures SQL Server Version Up-Time Nines Failover Recovery Time Objectives Hardware Failures Database Failures

Availability 2005 / 2008 R 2 v v Failover Clustering Database Mirroring Replication Transaction Log Shipping 2012 / 2014 v Always. On Availability Groups 2016

SQL Server 2016

SQL Server 2016 Availability Enhanced Always. On v Increased Auto Failover Targets v Automatic failover based on DB Health v Cross / No Domain Support v DTC Support v Round robin load balancing of replicas v SSISDB Support v GMSA support v Online Operations v Log Transport Performance v Standard Edition Support Performance Security Operational Analytics Always Encrypted In-memory OLTP Row-level Security v Disk & In-memory OLTP tables v Increased T-SQL surface area v Increased supported memory v Increased number of parallel CPUs Query data store v Monitor and optimize query plans Native JSON v Expanded support for JSON data Temporal Databases v Query data as points in time v Sensitive data remains encrypted at all times v Applies fine grained access control to table rows Dynamic Data masking v Real-time obfuscation of data to prevent unauthorised access Other Enhancements v Audit success/failure of database operations v TDE support for In-memory OLTP tables v Enhanced auditing for OLTP with ability to track history of record changes

Windows Server Failover Clustering

Always. On Availability Groups

Availability Groups Windows Server Failover Cluster Availability Group Automatic Failover Cloud Basic Synchronous Commit Virtual Computer Object Primary Replica Availability Group Listener Readable Secondaries Database Health Secondary Backups Availability Database Manual Failover Advanced On-Premises Virtual Network Name IP Addresses Secondary Replicas Hybrid Cloud Synchronous Commit

AG_Listener AG AG Gold. Coast 1 Brisbane 1 Sunny. Coast 1 (Secondary) (Primary) AG (Secondary) AG Brisbane 2 (Secondary) AG Gold. Coast 2 AG Brisbane 3 (Secondary) Multi-Subnet Support (Secondary) AG Gold. Coast 3 (Secondary) Multi-Domain Support AG Sunny. Coast 2 (Secondary) AG Sunny. Coast 3 (Secondary) No Domain Support

AG_Listener Auto Failover AG AG Brisbane 1 Brisbane 2 (Primary) (Secondary) Synchronous data Movement AG Brisbane 3 (Secondary)

AG_Listener Manual Failover AG AG Brisbane 1 Brisbane 2 (Secondary) (Primary) Synchronous data Movement AG Brisbane 3 (Secondary)

AG_Listener Auto Failover AG AG Brisbane 1 Brisbane 2 (Primary) (Secondary) Synchronous data Movement AG Brisbane 3 (Secondary)

AG_Listener Auto Failover AG AG Brisbane 1 Brisbane 2 (Secondary) (Primary) Synchronous data Movement AG Brisbane 3 (Secondary)

AG_Listener AG AG AG Brisbane 1 Brisbane 2 Brisbane 3 Gold. Coast 1 (Primary) (Secondary) AG Sunny. Coast 1 (Secondary) Read_Only_Routing_List = ((‘Brisbane 2’, ’Brisbane 3’, ’Gold. Coast 1’), ’Sunny. Coast 1)

Caveats AG_Listener Cross DB Same Instance – Not Supported Cross DB Cross Instance - Supported AG AG AG Brisbane 1 Brisbane 2 (Primary) DTC Support (Secondary) Failover rules Brisbane 3 (Secondary)

AG_Listener AG AG AG Brisbane 1 Brisbane 2 (Primary) SSISDB Support (Secondary) Brisbane 3 (Secondary) Online Operations

AG_Listener AG AG AG Brisbane 1 Brisbane 2 (Primary) GMSA Support (Secondary) Brisbane 3 (Secondary) Log Transport Performance

Commit Ack Network AG AG Sydney Brisbane (Secondary) (Primary) Log Transport Performance

500 467 450 400 350 300 250 200 150 117 120 110 109 100 52 50 12 0 CPU (%) 22 XTP Transactions/sec (x 1000) Log Bytes Received (MB/s) SQL 2014 SP 1 Redone Bytes/sec (MB/s) SQL 2016 CTP 2. 2

Feature Set v Single Availability Database v Only 2 Replicas AG Brisbane (Primary) AG Sydney (Secondary) v No Readable Secondaries v No Availability Group Listener v No Backup configuration on Secondary v Asynchronous or Synchronous Standard Edition Support

Glimpse into the Future

AG_Listener ASynchronous Auto Failover AG Synchronous Brisbane 1 (Primary) AG AG Brisbane 2 Brisbane 3 (Secondary) AG Synchronous Sydney 1 (Forwarder) AG Sydney 2 (Secondary) AG Sydney 3 (Secondary)

In Summary 2005 / 2008 R 2 v v 2012 / 2014 Failover Clustering v Always. On Availability Database Mirroring Groups Replication Transaction Log Shipping 2016 v Increased Auto Failover Targets v Automatic failover based on DB Health v Cross / No Domain Support v DTC Support v Round robin load balancing of replicas v SSISDB Support v GMSA support v Online Operations v Log Transport Performance v Standard Edition Support

My Ignite

Continue your Ignite learning path Visit Microsoft Virtual Academy for free online training visit https: //www. microsoftvirtualacademy. com Visit Channel 9 to access a wide range of Microsoft training and event recordings https: //channel 9. msdn. com/ Head to the Tech. Net Eval Centre to download trials of the latest Microsoft products http: //Microsoft. com/en-us/evalcenter/

© 2015 Microsoft Corporation. All rights reserved. Microsoft, Windows and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.