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
Connect to the existing replica
Above steps can be replaced with below script . However, the scripts needs to be executed under SQLCMD mode.
--- 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
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.
ReplyDeleteGreat Article Artificial Intelligence Projects
DeleteProject Center in Chennai
JavaScript Training in Chennai
JavaScript Training in Chennai
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 :)
ReplyDeleteQuel 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..
ReplyDeletemyTectra Placement Portal is a Web based portal brings Potentials Employers and myTectra Candidates on a common platform for placement assistance
ReplyDeletegoogle account manager download megadownloader free frp bypass download
ReplyDeleteI 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.
ReplyDeleteThanks a lot for using the nice info is visible in this blog.
Selenium Online Training
Oracle Online Training
This comment has been removed by the author.
ReplyDeleteI 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!
ReplyDeleteShare Point online training
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.
ReplyDeleteData 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
Very informative blog and useful article thank you for sharing with us , keep posting learn more about aws with cloud computing
ReplyDeleteAWS Online Training
AWS Certification
AI Training
ReplyDeleteتنظيف منازل بالدمام شركة تنظيف
تنظيف منازل بالاحساء شركة تنظيف منازل بالاحساء
تنظيف منازل بمكة شركة تنظيف منازل بمكة
تنظيف منازل بجدة شركة تنظيف منازل بجدة
تنظيف منازل بالمدينة المنورة شركة تنظيف المنازل بالمدينة المنورة
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.
ReplyDeleteYour 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
ReplyDeleteGood 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.
ReplyDeleteSelenium Training in Chennai
This comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteGreat 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.
ReplyDeleteCRS Info Solutions Salesforce training for beginners
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
ReplyDeleteSalesforce Training India
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
ReplyDeleteExtremely 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
ReplyDeleteAmazing Article, Really useful information to all So, I hope you will share more information to be check and share here.
ReplyDeleteInternship near me
Inplant Training for cse
Inplant Training for IT
Inplant Training for ECE Students
Inplant Training for EEE Students
Inplant Training for MECHANICAL Students
inplant Training for CIVIL Students
Inplant Training for Aeronautical Engineering Students
Inplant Training for ICE Students
Inplant Training for BIOMEDICAL Engineering Students
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.
ReplyDeleteDigital Marketing Training Institutes in Hyderabadad
Nice Interesting blog. You are posts are just amazing and very creative. Keep on share it.
ReplyDeleteContent com android browser home
Hotschedules login
Thanks for Sharing a Very Informative Post & I read Your Article & I must say that is very helpful post for us.
ReplyDeleteData Science
Selenium
AWS
Python Online Classes
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
ReplyDeleteI 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.
ReplyDeletedata analytics courses in malaysia
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
ReplyDeletecertification of data science
You totally coordinate our desire and the assortment of our data.
ReplyDeletedata science course delhi
This is so fun! What a great idea. Also I love how authentic you seem to be.
ReplyDeletebakhar nabieva
This comment has been removed by the author.
ReplyDelete