Sunday, October 26, 2014

3 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

3 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

 

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