This reviewer is still part of lesson in Implementing High Availability in SQL Server 2012. This covers about 12% of the certification exam. This part covers the AlwaysOn Availability Groups, a high availability feature that is new in SQL Server 2012.
What is AlwaysOn?
The feature AlwaysOn Availability Groups (AG) is an alternative to the feature database mirroring. An availability group is a collection of user databases that can failover together. Availability group can support up to four sets of secondary databases unlike in the database mirroring that is limited to a principal and secondary. Availability group can support a read-write but it also enables you to configure one or more sets of secondary databases so that they are accessible only for read operation.
Failover occurs on the per replica and all the database in that replica fail over. The failover is caused by factors at the instance level not at the database level.
Availability Group Prerequisites
Only SQL Server 2012 Enterprise editions support the AlwaysOn Availability Groups. During your planning stage for implementing AlwaysOn Availability Groups, you must the meet the conditions below;
- Host servers must not be domain controllers
- Host servers must be part of the Windows Server failover cluster
- All hotfixes are applied to the host server operating system.
- If you are using Kerberos authentication in your production environment, the SQL Service account on each participating node must use the same domain account.
- Still for Kerberos authentication, the Service Principal Name (SPN) for the virtual network name (VNN) of the Availability Group (AG) listener domain account must be manual registered.
- It is a best practice to make sure that all host systems participating in the AG can handle identical workloads.
- It is also a best practice that each host servers are provided with a separate network adapter dedicated to handle AG traffic.
- The Time To Live (TTL) must be set to 60 seconds on the zone that hosts DNS records related to the AG.
AlwaysOn AG supports the similar modes to database mirroring. The availability modes is configured on a per replica basis.
Asynchronous-commit mode – You use this availability mode when your production environment is deployed at geographically dispersed locations. In this mode, the primary will not wait for secondaries to harden the log and will run with minimum transaction latency.
Synchronous-commit mode – This availability mode increases transaction latency but minimises data loss percentage in the event of automatic failover. Each transaction is applied to the secondary replica before being written to the local log file. The primary verifies that the transaction has been applied to the secondary before entering a synchronised mode.
You can configure the availability mode from the properties page of the Availability Group. Alternatively you can use the ALTER AVAILABILITY GROUP T-SQL statement with AVAILABILITY_MODE option to change.
Selecting Failover Modes
Failover involves making another instance the primary replica and the original replica becoming a secondary replica. Keep in mind that the failing over happens at the replica level. There are 3 failover forms that is supported.
Automatic Failover – This form occurs without any administration intervention. There is no data loss during the failover. Automatic failover can only be configure if you are using synchronous commit mode.
Planned manual failover – As the name suggests, this failover is planned and with the intervention of the administrator. This is very handy when you have to perform necessary maintenance on the current primary replica. You can perform planned manual failover if the primary and the secondary replica is both in the synchronous commit mode.
Force manual failover – this form of failover has the possibility of data loss. Use this mode when no secondary replica is in the synchronised state or when the primary replica is not available. This is the only form that is supported when the asynchronous commit mode is used in the primary replica.
To launch the Failover Availability Group Wizard, from the availability group, right click, and click on Failover. On Select New Primary Replica page, select the instance on which to perform the failover. Click Next.
To do the failover via the T-SQL statement, you can execute the statement below;
ALTER AVAILABILITY GROUP <NewAG> FAILOVER;
You can use the ALTER AVAILABILITY GROUP statement with the FORCE_FAILOVER_ALLOW_DATA_LOSS option on the replica that want to make primary replica to force failover.
ALTER AVAILABILITY GROUP <NewAG> FORCE_FAILOVER_ALLOW_DATA_LOSS;
Deploying AlwaysOn Availability Groups
Even after completing all the prerequisites for setting up Windows Server failover cluster, there are still tasks to perform when deploying AlwaysOn Availability Groups. In sequential order, the following tasks must be performed.
- Creating a mirror endpoint
- Enabling AlwaysOn
- Creating an Availability Group
- Creating an Availability Group Listener
- Adding a secondary replica
Creating a mirror endpoint
Prior to creating the endpoint, verify first if there is an existing endpoint on the instance because there is only one endpoint that can exist per instance. You may query from the view sys.database_mirroring_endpoints.
If you are using domain based account on all your SQL Service accounts that will participate in the availability group, the database engine can create the endpoint automatically during the availability group creation process.
Enabling AlwaysOn Availability Groups
To enable AlwaysOn Availability Groups;
- From your SQL Server Configuration Manager, navigate to the SQL Server Services.
- Right click on SQL Service service on the instance where you want to setup the AlwaysOn AG.
- On the AlwaysOn High Availability tab, click on checkbox Enable AlwaysOn Availability Groups. The name of the failover cluster to which the node belongs should be displayed automatically. See figure below
You need to restart your SQL Server services. Before doing this to the other instances, you need to wait after this SQL Server has restarted and the AlwaysOn is already enabled.
Creating an Availability Group
After enabling the AlwaysOn Availability Group from the steps above, you can proceed to create the Availability Group (AG). To do this follow the steps below;
- From your SSMS, expand on AlwaysOn High Availability.
- On the Specify Availability Group Name page, enter the name for your AG.
- On Select User Databases For the Availability Group page, select the databases you will add to the AG. This page also tells you if the database you will select ‘Meets prerequisites’.
- On the Specify Replicas page, click on Add Replica. In the Connect To Server window, enter the credentials to connect and click connect. Add the instances that will function as replica.
- On Select Initial Data Synchronisation page, specify the location of a network share that allows read/write access to SQL Server service account.
- On the Validation page, verify that all the settings except Checking The Listener Configuration are completed successfully.
- Review all settings and complete the wizard.
You cannot use the New Availability Group wizard or Add Database To Availability Group Wizard to add a database that is encrypted or contains a database encryption key.
You cannot use the same wizard for adding replicas that use different paths for database and log files. You must add these replicas manually. More on this below.
Creating or Adding an Availability Group Listener
An AG Listener is a network connectivity endpoint for an AG. Clients connect to the listener, which connects them to the AG’s primary instance. You can create one AG listener per availability group that you setup. To add more AG listener for a single AG you can use the Failover Cluster Manager console from the Server Manager in Windows Server.
To create the AG listener, you must be connected to the database engine primary replica then perform the following steps;
- From your SSMS, navigate to the AlwaysOn High Availability node and expand the Availability Groups node. Right click on availability group for which you will add the AG listener and click Add Listener.
- On the new New Availability Group Listener page, specify a Listener DNS Name and a TCP port. For these values, you need to get clearance from your domain admin. In the Network Mode, select either DHCP or Static IP.
Alternatively, you can run the statement below;
ALTER AVAILABILITY GROUP <NewAG> ADD LISTENER ‘NewListener’ (with IP ((‘100.100.100.100’, ‘255.0.0.0’)), PORT =7028)
where the value ‘100.100.100.100’ should be replaced with your Static IP address, the value ‘255.0.0.0’ should be replaced with your subnet mask and the value ‘7028’ should be replaced with the port used in your server. You will be needing your friendly domain admins’ assistance for this.
Adding Secondary Replicas
You can add secondary replicas to your existing availably group under the following conditions;
- Your existing AG has less than four secondary replicas
- Your primary replica must be online
- You are connected to the database engine that will host the secondary replica.
- This database engine that will host the secondary replica must be able to connect to mirroring endpoint of the primary replica.
- The AlwaysOn Availability Groups is enabled on the database instance that will host the secondary replica
To join a secondary replica to your existing AG, from your SSMS (remember that you are in the database instance that you want to add as secondary replica) right click on the AlwaysOn High Availability Group node and click Join To Availability Group.
Or you may simply run the statement below;
ALTER AVAILABILITY GROUP <NewAG> JOIN;
Using Availability Groups on Failover Cluster Instances
Although you must deploy availability groups on a host server that is a member of the Windows Server failover cluster, the instance on which you deploy AG is not usually a failover cluster instance. You can still use AG with SQL Server failover cluster instances but you can use all AG functionality.
The following restrictions apply to this case
- Only one failover cluster instance partner can host a replica.
- A failover partner cannot host a secondary replica for the same AG
- Failover cluster instances support only manual failover. You cannot configure to use automatic failover to a replica on a failover cluster instance.
- Failover cluster instances do not support initial data synchronisation by using the New Availability Group Wizard, Add Database To Availability Group Wizard or Add Replica To Availability Group Wizard.
If you are using a failover cluster instance with AlwaysOn Availability Groups, you must prepare the secondary database on the instance by using a different method such as backup and restore and then join that secondary database to your AG. When going this way, always remember to have a full database backup and a transaction log backup. When doing the restore always use the NORECOVERY option.
I had the privilege of setting up an AlwaysOn Availability Group in one of our web projects that recently went live, and I added secondary databases to my AG via the backup and restore method. Less headaches. 🙂
If you have questions about my AlwaysOn High Availability Group setup and scenario, just email me. I might be able to help or we can compare notes.
Next post, implementing replication!