Below is my note to create Always on High availability group in SQL 2014.
All SQL Server participate alwayson availability group must in the same WSFC.
Enhancement for SQL2014 AlwaysOn
- Support Azure as replica.
- The maximum number of secondary replicas is increased from 4 to 8.
- When disconnected from the primary replica or during cluster quorum loss, readable secondary replicas now remain available for read workloads.
- A new system function, sys.fn_hadr_is_primary_replica, and a new DMV, sys.dm_io_cluster_valid_path_names, is available.
- The following DMVs were enhanced and now return FCI information: sys.dm_hadr_cluster, sys.dm_hadr_cluster_members, and sys.dm_hadr_cluster_networks.
- SQL Server standalone 1: SGC4,8001
- SQL Server standalone 2: SGC5,8001
If you don’t have cluster feature enable, you will need to enable it on the windows host level. Refer here for how to enable windows clustering after SQL Server installed.
Repeat below steps on all three SQL Server instances
This can be checked from Server property in SSMS as well.
Enable-SqlAlwaysOn : ChangeHADRService failed for Service 'MSSQL$SGC4SQL2014'.
Enable-SqlAlwaysOn : Could not obtain information about SQL Server Service 'MSSQL$SGC5SQL2014,8001'.
Backup the Database and log
There are 2 ways for initial data sync:
- FULL: The wizard would take the full db backup and log backup, then restore to the target. However, the target DB must has same directory as source. Simply put, it does not know how to use WITH MOVE command.
- JOIN: We have to perform the manfully backup and restore for the participate database.
Create Availability group
Database must be in the FULL recovery mode.
setup the listener name
Now we can connect to the SQL server with listener name.
If we connect to the secondary replica, the database is not accessible , this is because we have not configure the readable copy of replica yet.
If we use cluster manager connect to the cluster, you will see the listener ”pandasql1” is registered as cluster resource.