Deploying AlwaysOn Availability Groups
Note: Availability groups are supported in production on SQL Server 2012 Enterprise edition only and is a feature that is an alternative to database mirroring.
Unlike mirroring that is limited to a principal and a mirror database, availability groups support a set of read-write primary databases and up to four sets of secondary replicated databases.
1. Creating a mirroring endpoint
The Database Engine can create the appropriate mirroring endpoint automatically as part of the availability group creation process if you are using a domain-based account for all SQL Server services that will participate in the availability group.
You can check whether there are any mirroring endpoints on an instance by querying the sys.database_mirroring_endpoints catalog view.
We can do this using PowerShell
$endpoint = New-SqlHadrEndpoint AlwaysOnEndpoint -Port 7028 -Path SQLSERVER:\SQL\SQL-A\ALTERNATE
Starting the endpoint using PowerShell we use the following command:
Set-SqlHadrEndpoint -InputObject $endpoint -State "Started"
2. Enabling AlwaysOn
We must enable the AlwaysOn Availability Groups functionality at the instance level using SQL Server Configuration Manager before we can create an AlwaysOn Availability Group. Using PowerShell it would look like this
Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\<server_name>\ALTERNATE
3. Creating an availability group
There are limitations with the New Availability Wizard and Add Database To Availability Group Wizard as we cannot do the following:
- add a database to an availability group if that database is encrypted or contains a Database Encryption Key.
- add replicas that use different paths for database and log files. You must add these replicas manually.
We can use the CREATE AVAILABILITY GROUP Transact-SQL statement to create an availability group which would contain the below properties:
- Name: <my_group_name>
- Database: <my_database_name>
- Replica instances: <server1_name>.myDomain.com\newinstance, <server2_name>.myDomain.com\newinstance
- Endpoint TCP port: 7030
- Failover mode: Manual
- Availability mode: Asynchronous
Using T-SQL it would look something like this…
CREATE AVAILABILITY GROUP FOR DATABASE REPLICA ON '\newinstance' WITH ( ENDPOINT_URL = 'TCP://.myDomain.com:7030', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL ), '\newinstance' WITH ( ENDPOINT_URL = 'TCP://.myDomain.com:7030', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL ); GO
4. Creating an availability group listener
An availability group listener is a network connectivity endpoint for an availability group.
Clients connect to the listener, which in turn connects them to the availability group’s primary instance.
We can create one listener per availability group by using SQL Server Management Studio but if we decide that we require more than one listener for an availability group, we can do this by either using Windows PowerShell or the Failover Cluster Manager console.
To create an availability group listener, you must be connected to the Database Engine instance that hosts the primary replica.
ALTER AVAILABILITY GROUP [<group_name>] ADD LISTENER '<listener_name>' (with IP (('10.0.0.222','255.0.0.0')), PORT=7028); GO
Creating a new availability group listener on instance <server_name>\AlwaysOn named Gamma-Listener to the chosen availability group that uses IP address 10.0.0.224, subnet mask 255.0.0.0, and port 7030, we can use the PowerShell command:
New-SqlAvailabilityGroupListener -Name Gamma-Listener -StaticIP '10.0.0.224/255.0.0.0'-Port 7030 -Path SQLSERVER:\SQL\<server_name>\ALWAYSON\AvailabilityGroups\<group_name>
5. Adding a secondary replica
To add secondary replicas to an existing availability group can be done only under the following conditions:
- The availability group has fewer than four secondary replicas.
- The primary replica of the availability group is online.
- You are connected to the Database Engine instance that will host the secondary replica.
- The Database Engine instance that will host the secondary replica can connect to the mirroring endpoint on the primary replica.
- You have enabled AlwaysOn Availability Groups on the Database Engine instance that will host the secondary replica.
Below are the steps to join a secondary replica to an availability group by using SQL Server Management Studio
- On the Database Engine instance that hosts the secondary replica
- Right-click the secondary replica under the AlwaysOn High Availability\Availability Groups node
- Click Join To Availability Group
- In the Join Replica To Availability Group dialog box, verify the details and click OK
Below is the T-SQL command to join our chosen availability group
ALTER AVAILABILITY GROUP <group_name> JOIN;
Using PowerShell to join the <server_name>\AlwaysOn instance to our chosen availability group
Join-SqlAvailabilityGroup -Path SERVER:\SQL\<server_name>\AlwaysOn -Name '<group_name>'
Using Availability Groups on Failover Cluster Instance
- Only one failover cluster instance partner can host a replica. A failover partner cannot host a secondary replica for the same availability group
- Failover cluster instances support only manual failover. You cannot configure AlwaysOn 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
Suggested external links
As I have been working though my study notes I cross reference a lot of material so to save time I am adding some links related to the topic above