Wednesday, October 22, 2014

2 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

image

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

image

This can be checked from Server property in SSMS as well.

image

Enable-SqlAlwaysOn : ChangeHADRService failed for Service 'MSSQL$SGC4SQL2014'.

image_thumb[6]

Enable-SqlAlwaysOn : Could not obtain information about SQL Server Service 'MSSQL$SGC5SQL2014,8001'.

image_thumb[9]

image_thumb[7]

image_thumb[8]

image

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.

image

image

image

image

image

image

image

setup the listener name

image

image

image

Now we can connect to the SQL server with listener name.

image

image

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.

image

AlwaysOn dashboard

image

image

image

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

image

Reference

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

2 comments:

  1. It surely nice to see old sql versions that were used in the past such as this project Always on High availability group in SQL 2014. Very useful, creative and where you can see more writing samples.

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates