All the things you need to know before

All the things you need to know before setting up Always. On Michael Steineke SQL & BI Solution Lead Enterprise Architect Concurrency, Inc.

Terminology and Architecture Always. On Marketing name for the high availability features in SQL Server Includes Fail over Cluster Instances (FCI) Availability Groups (AG) Windows Server Failover Clustering (WSFC) All of Always. On uses WSFC

High Availability vs. Disaster Recovery High Availability Generally Quick same site Recovery Disaster Recovery Separate Slower Site Recovery

Site to Site Considerations How far apart are they? What is the connectivity between sites? Type Size of connection Latency of connection

Networking Server Ports to open on Firewalls TCP AG IP to server connectivity Ports 1433 & 1434 or specific SQL ports Endpoint – tcp 5022 Addresses DNS Names & IP Addresses Servers, Cluster Name, AGs

Windows Clustering What do I need to know as a DBA? Management Windows Failover Cluster Manager Power. Shell Windows Domain Joined Authentication SQL Server needs to run as a Domain Account Connectivity DNS Names and SQL Instances Availability Group Listener Ports Quorum

Why do I care about Quorum? Quorum Controls if the Cluster in Online Majority of votes What can vote? Shared Disk Servers – Active, Passive or Witness File Share – Witness Manual In Voting override is available Multi-Site configurations you need to decide where the voting members are located

Windows Server Version & Clustering Windows Many Server 2012 R 2 useful updates to clustering Dynamic Cluster Quorum automatically controls Voting Adjusts Allows Network Key the vote weight of each node for Last Man Standing Resiliency to making sure the Cluster is healthy

Cluster Network Objects Computers AD in AD OU Permissions Cluster and Listener Objects in AD

OU Delegation Create an OU and Delegate to the Cluster Account

SQL Server Version Comparison SQL 2012 Enterprise SQL 2014 Enterprise Multiple Node FCI Multi-Subnet Clusters AG’s – 5 Copies of Data AG’s – 8 Copies of Data 4 Secondaries Total 7 Secondaries 2 Synchronous Up to 4 Asynchronous Up to 7 Asynchronous 2 Automatic Failover Targets Readable Secondaries Azure Replica Backup to Azure Site Survivability for Read Intent

FCI or AG? If you need MSDTC, AGs don’t support MSDTC AGs require separate Disk for each Instance AGs can fail from node to node faster than FCI Synchronous AGs require transactions to commit(log harden) on both servers AG Secondaries can do workload Backup & Read-only Answer – It depends on your needs You might want to combine them!

MSDTC In Windows 2008 and later you either Create a clustered instance of the MSDTC resource for EVERY SQL Server instance /Group that requires its functionality or DO NOT CLUSTER MSDTC at all View msdtc mappings msdtc -tm. Mapping. View *

Availability Group Setup Shared Backup location Permissions Full on share on SQL Server Account

Multiples – Instances, AGs You can have multiple AGs in an instance Listeners Multiple are tied to AG Instances Each AG needs different ports for the endpoints AG’s on Private Network

Multi-subnet DNS Client App connectivity https: //blogs. msdn. microsoft. com/alwaysonpro/2015/12/01/improve d-multisubnet-listener-behavior-with-newly-released-sql-clientprovider-in-net-4 -6 -1/ JDBC - https: //msdn. microsoft. com/enus/library/gg 558121(v=sql. 110). aspx Azure Listener https: //blogs. msdn. microsoft. com/alwaysonpro/2014/03/31/usereadintent-routing-with-azure-alwayson-availability-group-listener/

Enabling AGs

What you need before the Wizard Network Names Active Directory Cluster name Permissions or Objects staged Listener Name Servers in OU Firewall Rules/ports open File System Listener Location of DB Files AG Shared Backup Location IP Addresses Each Server Cluster Name Each Listener

Useful Reference Links SQL Always. On Team Blog SQL Server Customer Advisory Team https: //blogs. msdn. microsoft. com/alwaysonpro/2014/01/15/msdtc-recommendations-on-sql-failover-cluster/ AGs on Private Network https: //technet. microsoft. com/en-us/library/dn 466519. aspx MSDTC https: //technet. microsoft. com/en-us/library/cc 770620(WS. 10). aspx Prestage Computer Objects in AD http: //social. msdn. microsoft. com/Forums/en-US/home? forum=sqlhadr Failover Cluster Guide to Quorum http: //technet. microsoft. com/en-US/sqlserver/dn 135309 SQL High Availability on MSDN http: //blogs. msdn. com/b/sqlcat/ SQL Server 2014 Technet Reference http: //blogs. msdn. com/b/sqlalwayson/ https: //blogs. msdn. microsoft. com/alwaysonpro/2013/11/01/configure-availability-group-to-communicate-on-a-dedicated-network/ SQL 2016 Changes to AGs https: //blogs. technet. microsoft. com/dataplatforminsider/2015/12/15/enhanced-always-on-availability-groups-in-sql-server-2016/

Thanks! msteineke@concurrency. com www. michaelsteineke. com Twitter @MSteineke