Sunday, October 26, 2014

42 SQL2014:AlwaysOn High availability group-Add additional replica

Below is the walk through for how to add another replica to the existing high availability group.

prerequisites

Back up the primary database and log , then restore to the new replica with NORECOVERY

 

From SSMs Availability replicas –> Add Replica

image


Connect to the existing replica

image

image

image

image

Above steps can be replaced with below script . However, the scripts needs to be executed under SQLCMD mode.

image

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect SGC8\SGC8SQL2014

USE [master]

GO

CREATE LOGIN [Stargate\sqlprodid] FROM WINDOWS

GO

:Connect SGC4,8001

use [master]

GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [STARGATE\sqlprodid]

GO

:Connect SGC5\SGC5SQL2014

use [master]

GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [STARGATE\SQLPRODID]

GO

:Connect SGC8\SGC8SQL2014

USE [master]

GO

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)

GO

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
    ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END


GO

use [master]

GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Stargate\sqlprodid]

GO

:Connect SGC8\SGC8SQL2014

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END

GO

:Connect SGC4,8001

USE [master]

GO

ALTER AVAILABILITY GROUP [pandaAlwaysOnDemo]
ADD REPLICA ON N'SGC8\SGC8SQL2014' WITH (ENDPOINT_URL = N'TCP://SGC8.stargate.com:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));

GO

:Connect SGC8\SGC8SQL2014

ALTER AVAILABILITY GROUP [pandaAlwaysOnDemo] JOIN;

GO

:Connect SGC8\SGC8SQL2014


-- Wait for the replica to start communicating
begin try
declare @conn bit
declare @count int
declare @replica_id uniqueidentifier
declare @group_id uniqueidentifier
set @conn = 0
set @count = 30 -- wait for 5 minutes

if (serverproperty('IsHadrEnabled') = 1)
    and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
    and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)
begin
    select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'pandaAlwaysOnDemo'
    select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id
    while @conn <> 1 and @count > 0
    begin
        set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
        if @conn = 1
        begin
            -- exit loop when the replica is connected, or if the query cannot find the replica status
            break
        end
        waitfor delay '00:00:10'
        set @count = @count - 1
    end
end
end try
begin catch
    -- If the wait loop fails, do not stop execution of the alter database statement
end catch
ALTER DATABASE [AdventureWorks2014] SET HADR AVAILABILITY GROUP = [pandaAlwaysOnDemo];

GO


GO

42 comments:

  1. Thank you for this share. I have get more information from here. If you want essay related topic you can go for essay writing help will get better result.

    ReplyDelete
  2. Is there any chance for an updated version? Or should I go reading the review of Acad-Write.com until you post one? :) thank you for your work anyway, this will do for now :)

    ReplyDelete
  3. Quel est notre produit? - La meilleure marque de luxe au monde regarde des kopie horloges de la plus haute qualité! Si vous voulez avoir ces Kopie Horloges IWC ,Kopie Horloges cartier de luxe, vous voulez porter ces montres sur votre poignet, mais arrêtez-vous en raison de leur prix élevé. Alors s'il vous pla?t arrêtez vos pas, c'est votre endroit le plus correct..

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

    ReplyDelete
  5. I am really admired for the great info is visible in this blog that to lot of benefits for visiting the nice info in this website.
    Thanks a lot for using the nice info is visible in this blog.
    Selenium Online Training
    Oracle Online Training

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. I appreciate that you produced this wonderful article to help us get more knowledge about this topic. I know, it is not an easy task to write such a big article in one day, I've tried that and I've failed. But, here you are, trying the big task and finishing it off and getting good comments and ratings. That is one hell of a job done!
    Share Point online training

    ReplyDelete
  8. It has been simply incredibly generous with you to provide openly what exactly many individuals would’ve marketed for an eBook to end up making some cash for their end, primarily given that you could have tried it in the event you wanted.
    Data Science Course in Chennai | Best Data Science Training in Chennai
    Python Course in Chennai | Best Python Training Institutes in Chennai
    RPA Course in Chennai | Best RPA Training in Chennai
    Digital Marketing Course in Chennai | Best Digital Marketing Training in Chennai

    ReplyDelete
  9. Very informative blog and useful article thank you for sharing with us , keep posting learn more about aws with cloud computing

    AWS Online Training

    AWS Certification

    AI Training

    ReplyDelete

  10. تنظيف منازل بالدمام شركة تنظيف
    تنظيف منازل بالاحساء شركة تنظيف منازل بالاحساء
    تنظيف منازل بمكة شركة تنظيف منازل بمكة
    تنظيف منازل بجدة شركة تنظيف منازل بجدة
    تنظيف منازل بالمدينة المنورة شركة تنظيف المنازل بالمدينة المنورة

    ReplyDelete
  11. Thanks for post ing such an useful and informative stuff.SVR Technologies is the best online training institute for Selenium Online Training and we also offer self learning on Selenium Tutorials which will be very helpful for Selenium Tutorial for Beginners to learn from scratch to an advanced level.

    ReplyDelete
  12. Your blog is educational and I value your effort.I trust I will see all the more great post in coming future. Thanks for sharing this. 192.168.l.254 is an IP address used by many routers and modems as the default gateway

    ReplyDelete
  13. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
    Selenium Training in Chennai

    ReplyDelete
  14. This comment has been removed by the author.

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete
  16. This comment has been removed by the author.

    ReplyDelete
  17. This comment has been removed by the author.

    ReplyDelete
  18. Great post! I am actually getting ready to across this information, It’s very helpful for this blog. Also great with all of the valuable information you have Keep up the good work you are doing well.
    CRS Info Solutions Salesforce training for beginners

    ReplyDelete
  19. Nice post I have been searching for a useful post like this on salesforce course details, it is highly helpful for me and I have a great experience with this   
    Salesforce Training India 

    ReplyDelete
  20. The better part of PHP training and certification offers quick job availability in small, mid-size, big enterprises, and online mega stores effortlessly. machine learning courses in hyderabad

    ReplyDelete
  21. Extremely helpful post. This is my first time visiting here. I discovered such a large number of intriguing stuff in your blog particularly its exchange. Truly its extraordinary article. Keep it up. windows 10 professional product key UK

    ReplyDelete
  22. Easily, the article is actually the best topic on this registry related issue. I fit in with your conclusions and will eagerly look forward to your next updates. Just saying thanks will not just be sufficient, for the fantasti c lucidity in your writing. I will instantly grab your rss feed to stay informed of any updates.
    Digital Marketing Training Institutes in Hyderabadad

    ReplyDelete
  23. Nice Interesting blog. You are posts are just amazing and very creative. Keep on share it.

    Content com android browser home
    Hotschedules login

    ReplyDelete
  24. Thanks for Sharing a Very Informative Post & I read Your Article & I must say that is very helpful post for us.
    Data Science
    Selenium
    AWS
    Python Online Classes

    ReplyDelete
  25. I am another client of this site so here I saw different articles and posts posted by this site,I inquisitive more enthusiasm for some of them trust you will give more data on this points in your next articles. data scientist training

    ReplyDelete
  26. I find your opinion quite interesting, but the other day I stumbled upon a completely different advice from another blogger, I need to think that one through, thanks for posting.
    data analytics courses in malaysia

    ReplyDelete
  27. I like this post,And I figure that they making some incredible memories to scrutinize this post,they may take a good site to make an information,thanks for sharing it to me
    certification of data science

    ReplyDelete
  28. You totally coordinate our desire and the assortment of our data.
    data science course delhi

    ReplyDelete
  29. This is so fun! What a great idea. Also I love how authentic you seem to be.
    bakhar nabieva

    ReplyDelete
  30. This comment has been removed by the author.

    ReplyDelete
  31. Thanks for posting the best information and the blog is very helpful.digital marketing institute in hyderabad

    ReplyDelete
  32. The author has discussed a very resourceful topic in times of the pandemic. We, as a nation, cannot keep our children out of schools forever. Hence a plan needs to be implemented where children can safely go to school without getting mass infected. However, it is not recommended for parents or children to go physically to the shops for book supplies. Hence there is a need for an online book shop such as KitaabNow.

    ReplyDelete
  33. I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here...

    Data Science Training in Hyderabad

    ReplyDelete
  34. I really appreciate this great post that you have provided us. I guarantee this will benefit most people and myself. thank you very much!
    mycherrycreek

    ReplyDelete
  35. I amazed with the research you made to make this actual submit incredible.
    Great activity!

    Have a look at my homepage;오피사이트

    ReplyDelete
  36. Nice and very informative blog, glad to learn something through you.
    ai course in aurangabad

    ReplyDelete
  37. Love your writing skill. Thanks for sharing this wonderful post.

    Microsoft Windows 10 pro activation key 100% original license key.
    https://microsoftprokey.com/

    ReplyDelete
  38. Thanks for creating a useful content and well-written post.
    Digital Marketing In Telugu

    ReplyDelete

 

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