36 SQL2014: Create AlwaysOn High availability group

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.

Environment

• 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.

Enable AlwaysOn

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.

-- On source

Backup database [AdventureWorks2014] to disk='\\SGC\ShareDisk\Backup\AdventureWorks2014.bak'

with init,stats=10

GO

BACKUP  LOG [AdventureWorks2014] to disk ='\\SGC\ShareDisk\Backup\AdventureWorks2014.trn' with init 

GO

-- On target

restore  database [AdventureWorks2014] from disk='\\SGC\ShareDisk\Backup\AdventureWorks2014.bak'

with norecovery, move 'AdventureWorks2014_Data' to 'C:\Database\MSSQL12.SGC5SQL2014\MSSQL\DATA\AdventureWorks2014_Data.mdf', move 'AdventureWorks2014_Log' to 'C:\Database\MSSQL12.SGC5SQL2014\MSSQL\DATA\AdventureWorks2014_Data.ldf',

stats=10

GO

RESTORE LOG [AdventureWorks2014] 

  FROM DISK = '\\SGC\ShareDisk\Backup\AdventureWorks2014.trn' 

  with NORECOVERY

GO

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.

AlwaysOn dashboard

If we use cluster manager connect to the cluster, you will see the listener ”pandasql1”  is registered as cluster resource.

Reference

http://msdn.microsoft.com/en-us/library/ff878259.aspx