Always On Availability Groups 101 Using SQL Server

Always. On Availability Groups 101 Using SQL Server 2012

About Me § § § Jeff Reinhard 20 years database development experience @jreiny Jeff@Reinhard. com Employment Highlights: § § § Ernst & Young Internet Security Systems/IBM Air. Watch Web. MD Check. Free 2 | 11/6/2014 | Always. On Availability Groups 101

Thank You § Microsoft for the facility § For sponsoring tonight’s event: Pyramid Analytics 3 | 11/6/2014 | Always. On Availability Groups 101

Agenda § § § § What is Always. On Prerequisites Setting Up Monitoring Always. On and Your Application Lessons Learned Q&A 34 slides, so I need to move fast 4 | 11/6/2014 | Always. On Availability Groups 101

Two Types of Always. On ? ? ? § Always. On Failover Cluster Instances § leverages Windows Server Failover Clustering (WSFC) functionality to provide local high availability through redundancy at the serverinstance level—a failover cluster instance (FCI). § Previously known as SQL Clusters § Works with SQL Server Standard Edition § Always. On Availability Groups (AG) § This presentation discusses further § Requires SQL Server Enterprise Edition 5 | 11/6/2014 | Always. On Availability Groups 101

What Is Always. On AG? § Always. On is SQL Servers best available technology for SQL High Availability. § Always. On is a new integrated, flexible, cost-efficient high availability and disaster recovery solution. It can provide data and hardware redundancy within and across data centers, and improves application failover time to increase the availability of your mission-critical applications. Always. On provides flexibility in configuration and enables reuse of existing hardware investments. § The principal goal of a high availability solution is to minimize or mitigate the impact of downtime. § It is recommended for scenarios where high availability is required and the application will benefit from readable copies of the primary databases. 6 | 11/6/2014 | Always. On Availability Groups 101

Always. On Architecture Overview 7 | 11/6/2014 | Always. On Availability Groups 101

Prerequisites – Before you begin Prerequisites, Restrictions, and Recommendations for Always. On Availability Groups § http: //msdn. microsoft. com/en-us/library/ff 878487. aspx Always. On Failover § http: //msdn. microsoft. com/en-us/library/ff 929171. aspx Do not use the Failover Cluster Manager to manipulate availability groups, for example: § § § Do not add or remove resources in the clustered service (resource group) for the availability group. Do not change any availability group properties, such as the possible owners and preferred owners. These properties are set automatically by the availability group. Do not use the Failover Cluster Manager to move availability groups to different nodes or to fail over availability groups. The Failover Cluster Manager is not aware of the synchronization status of the availability replicas, and doing so can lead to extended downtime. You must use Transact-SQL or SQL Server Management Studio. 8 | 11/6/2014 | Always. On Availability Groups 101

Prerequisites – Before you begin (cont. ) Always. On Quorum Resources § § http: //blogs. msdn. com/b/sqlalwayson/archive/2012/03/13/quorum-vote-configuration-check-in-alwayson-availability-group-wizards-andyjing. aspx http: //msdn. microsoft. com/en-us/library/hh 270280. aspx http: //msdn. microsoft. com/en-us/library/hh 270281. aspx http: //technet. microsoft. com/en-us/library/cc 770620(v=ws. 10). aspx Connecting with multi-subnet failover § § § http: //msdn. microsoft. com/en-us/library/hh 205662. aspx http: //technet. microsoft. com/en-us/library/ff 878716. aspx Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server) - http: //technet. microsoft. com/enus/library/hh 213417. aspx Creating a listener § § http: //technet. microsoft. com/en-us/library/hh 213080. aspx Prerequisites and requirements § § Only one listener can be created though SQL Server. If you need an additional listener, it can be created thought WSFC. Recommendation, use a static IP for multiple subnet configurations. You must be connected to the instance that hosts that primary replica If using static IP addresses, the listener will have a static IP for each subnet that has a replica. Configure Read-Only Routing for an AG § http: //technet. microsoft. com/en-us/library/hh 710054. aspx 9 | 11/6/2014 | Always. On Availability Groups 101

Prerequisites - Windows Cluster Go into Server Manager, features, add features 10 | 11/6/2014 | Always. On Availability Groups 101

Prerequisites - Windows Cluster (cont. ) Enable Failover Clustering § Make sure cluster services are available on all participating nodes 11 | 11/6/2014 | Always. On Availability Groups 101

Prerequisites - Windows Cluster (cont. ) § Add the database servers to the cluster § Run validations, tests, etc. § Some warnings are due to no storage available to the cluster, which is correct for Always. On. § Create the cluster § Create Active Directory computer account for cluster name 12 | 11/6/2014 | Always. On Availability Groups 101

Prerequisites – SQL Server Configuration § Open SQL Server Configuration Manager § Select SQL Server Services, right click on SQL Server, select properties 13 | 11/6/2014 | Always. On Availability Groups 101

Always. On Set Up 14 | 11/6/2014 | Always. On Availability Groups 101

Always. On Setup - Overview § § § § § Working Primary Node Available “blank slate” secondary Create Availability Group Select Databases Specify Replicas Create the AG Listener Select full data synchronization Run the wizard Configure Read Only Routing 15 | 11/6/2014 | Always. On Availability Groups 101

Always. On Setup - Working Primary Node § Ensure your application is working properly § Review logins and users and permissions § Using Contained databases is recommended but not required § If not using contained databases, export the logins § http: //support. microsoft. com/kb/918992 16 | 11/6/2014 | Always. On Availability Groups 101

Always. On Setup - Available “blank slate” secondary § Secondary Node(s) should match the primary in configuration, version, etc. § Make sure the databases and files on the primary do not exist on the secondary § If not using contained databases, import the logins used by the application. § Don’t import default and windows authentication accounts § It is vital that the login SIDS match between the primary and secondary node(s) 17 | 11/6/2014 | Always. On Availability Groups 101

Always. On Setup - Create Availability Group § Using SSMS, from your primary database node, start the New Availability Group Wizard and give it a name 18 | 11/6/2014 | Always. On Availability Groups 101

Always. On Setup - Select Databases § Select all of the databases that work together as a set for your application; possibly including Reporting Services database(s). 19 | 11/6/2014 | Always. On Availability Groups 101

Always. On Setup - Specify Replicas § The wizard will select your primary node, add your secondary node(s) 20 | 11/6/2014 | Always. On Availability Groups 101

Always. On Setup - Specify Replicas (cont. ) § For all, select Synchronous, Automatic Failover, Readable Secondary = Yes 21 | 11/6/2014 | Always. On Availability Groups 101

Always. On Setup - Create the AG Listener § On the listener tab, enter name and port 1433, then click add for IP 22 | 11/6/2014 | Always. On Availability Groups 101

Always. On Setup - Create the AG Listener (cont. ) § Enter the IP address for the listener 23 | 11/6/2014 | Always. On Availability Groups 101

Select full data synchronization § Create the folder, preferably on a secondary node, then enter into the wizard 24 | 11/6/2014 | Always. On Availability Groups 101

Run the wizard § Perform the validation, next, then finish to create the AG 25 | 11/6/2014 | Always. On Availability Groups 101

Configure Read Only Routing § I have a script that with the setting of a few variables, will generate the code to perform the configuration. Contact me and provide payment if interested. § http: //www. sqlservercentral. com/scripts/Always. On/116992/ § What is important after configuration § Endpoint_url uses DNS name and port 5022 § Available mode is synchronous § Failover mode is automatic 26 | 11/6/2014 | Always. On Availability Groups 101

Configure Read Only Routing (cont. ) § What is important after configuration (cont. ) § Primary role allow connections is ALL § Secondary role allow connections is READ_ONLY § Read. Only Routing URL is IP and port 1433 27 | 11/6/2014 | Always. On Availability Groups 101

Always. On Monitoring - Dashboard § The first place to start is the dashboard 28 | 11/6/2014 | Always. On Availability Groups 101

Always. On Monitoring – Top Section § Look for any errors, warnings, check that failover mode is automatic, AG group state is healthy, check the links on the right for any errors or warnings 29 | 11/6/2014 | Always. On Availability Groups 101

Always. On Monitoring - Replicas § Check the health icon, failover mode is automatic, synchronization state is good, and no issues reported 30 | 11/6/2014 | Always. On Availability Groups 101

Always. On Monitoring - Databases § Check the primary and secondary, health state, synchronization state, failover readiness, issues 31 | 11/6/2014 | Always. On Availability Groups 101

Always. On Monitoring (cont. ) § There are many DMV queries that can be used § Microsoft SQL Server 2012 Always. On Monitoring Management Pack § Always. On Availability Groups Troubleshooting and Monitoring Guide § http: //technet. microsoft. com/enus/library/dn 135328(v=sql. 110). aspx 32 | 11/6/2014 | Always. On Availability Groups 101

Always. On and Your Application § The connection strings should all go to the listener, not directly to any database server instance § To have your application use the primary mode, no change is required in your connection string § To have your application use a readable secondary, add to the connection string: § ; Application. Intent=Read. Only 33 | 11/6/2014 | Always. On Availability Groups 101

Always. On Lessons Learned § Request the DNS name and IP for windows cluster and Availability Group Listener early, they can take a while § Research prerequisites, make sure all patches, versions, components are optimal to support Always. On § Schedule and plan out in advance, get key players on board in the beginning § Schedule implementation in two phases: § Configuration of clusters, storage, SQL server stand alone instances § Implementation of Always. On 34 | 11/6/2014 | Always. On Availability Groups 101

Bonus Trick § Sometimes, when a database is in the state of “not synchronizing”, and all other issues have been resolved, running the below command might get it back to healthy: ALTER DATABASE [XXXXX] SET HADR RESUME; 35 | 11/6/2014 | Always. On Availability Groups 101

Questions? Thank you for your time! 36 | 11/6/2014 | Always. On Availability Groups 101