Wednesday, October 22, 2014

12 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

12 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
  2. I can't define anything on these pictures. What is the topic of this master thesis? Firstly I need to look through the full version of your work and then it will be clear for me. I need someone to create my site and if you're skilled enough you can help me with this.

    ReplyDelete
  3. Thank you for allowing me to read it, welcome to the next in a recent article. And thanks for sharing the nice article, keep posting or updating news article.
    java training in chennai | java training in bangalore

    java training in tambaram | java training in velachery

    java training in omr

    ReplyDelete
  4. When I initially commented, I clicked the “Notify me when new comments are added” checkbox and now each time a comment is added I get several emails with the same comment. Is there any way you can remove people from that service? Thanks.

    AWS Training in Bangalore | Amazon Web Services Training in Bangalore

    Amazon Web Services Training in Pune | Best AWS Training in Pune

    AWS Online Training | Online AWS Certification Course - Gangboard

    ReplyDelete
  5. Hello! This is my first visit to your blog! We are a team of volunteers and starting a new initiative in a community in the same niche. Your blog provided us useful information to work on. You have done an outstanding job.
    Best AWS Training in Chennai | Amazon Web Services Training in Chennai

    AWS Training in Bangalore | Amazon Web Services Training in Bangalore

    Amazon Web Services Training in Pune | Best AWS Training in Pune

    ReplyDelete
  6. mytectra placement Portal is a Web based portal brings Potentials Employers and myTectra Candidates on a common platform for placement assistance.

    ReplyDelete
  7. Read all the information that i've given in above article. It'll give u the whole idea about it.
    python online training
    python training in OMR
    python training course in chennai

    ReplyDelete
  8. This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.

    iOS Training in Chennai
    Android Training in Chennai
    Mobile Apps Training in Chennai

    ReplyDelete
  9. Thanks for the informative article. This is one of the best resources I have found in quite some time. Nicely written and great info. I really cannot thank you enough for sharing.
    Devops training in sholinganallur
    Devops training in velachery
    Devops training in annanagar
    Devops training in tambaram

    ReplyDelete
  10. This is most informative and also this post most user friendly and super navigation to all posts... Thank you so much for giving this information to me.. 

    best rpa training in chennai | rpa online training |
    rpa training in chennai |
    rpa training in bangalore
    rpa training in pune
    rpa training in marathahalli
    rpa training in btm

    ReplyDelete

 

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