Always On Availability Groups How to Setup and
Always. On Availability Groups How to Setup and Configure using Best Practices
About me Morgan Jung �SQL Database Administrator with One Command � 9 Years experience with SQL Server, Sybase, and My. SQL �Contact Me: �florencedba@gmail. com
Agenda �Always. On Availability Groups �Overview �Benefits �Requirements �Best Practices �Troubleshoot �Demo: Setup and Configure
Always. On Classification �Microsoft’s new catch phrase introduced in 2012 �Availability Groups �Failover clustered instances (FCI) �Database mirroring �Log-shipping �Replication
Availability Groups �Synchronization modes �Synchronous �Asynchronous �Replicas �Supports up to 9 secondary replicas (5 in 2012) �Up to 3 replicas can be synchronous � 1 Primary replica � 2 Secondary replicas �Up to 2 replicas can be automatic failover partners
Benefits �Redundancy �High availability �Offload reporting & DW loads �Offload backups �Copy only full backups �No differential �Local storage
Automatic Page Repair �Errors: 823, 824, and 829 �Requests a clean page from replica �Asynchronous repair �Integrity checks �Still run physical_only on primary
Replication �Publisher: Supports automatic redirect on failover �Subscriber: Supports failover* � Transactional replication configured the subscriber’s listener name � Merge subscribers require additional manual work �Distributor: Role failover not supported
Requirements �Windows Cluster �Recommend Windows 2012 �SQL Enterprise Edition �Replica Initialization �Listener (Optional) �Network Name �IP Address (Static)
Checklist Items �Kerberos �All SQL Servers need to use the same service account �Service Broker �SSISDB �Monitoring �SQL Agent jobs
Best Practices �Disk layout (Please!) �Same hardware �Same SQL Server features are installed on all replicas �Use a Quorum witness �Windows cluster �Latest patches �Perform a planned failover �Create a failover document
Best Practices (Cont. ) �Run performance tests �On primary and secondary replicas �Security policy �AD policy �Local policy � Perform volume maintenance tasks (Instant file initialization) �Perform a planned failover �Create a failover document
Worker Threads �Monitor Max Worker Threads �When set to 0 (default) SQL calculates the max thread based on CPU count Number of CPUs 32 -bit computer 64 -bit computer <= 4 processors 256 512 8 processors 288 576 16 processors 352 704 32 processors 480 960 �Mirroring process uses at least 2 threads per database
Noteworthy �Manual failovers of asynchronous databases �Snapshot isolation �Index page splits �Only 1 mirror endpoint per SQL instance �Watch out for log backups
Troubleshoot �SQL Server error log �Primary and secondary replica �Windows Event Viewer �Cluster log �Database(s) unavailable – Check the cluster �Search Exchange forums
Demo
Monitoring �Perfmon �Dashboard � 3 rd-party monitoring solution �Idera �SQL Sentry �http: //sqlmonitor. codeplex. com/
References �Always. On Checklist �Brentozar. com/go/alwayson �Overview on Always. On Availability Groups �http: //msdn. microsoft. com/enus/library/hh 510230. aspx �Replication with Always. On �http: //msdn. microsoft. com/enus/library/hh 710046. aspx
- Slides: 18