Scribd
Upload
7 views

AlwaysOn Script

Uploaded by

Hafiz Waseem
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
7 views

AlwaysOn Script

Uploaded by

Hafiz Waseem
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 4

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:Connect LDC-SSC-DBA1

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 [NT Service\MSSQLSERVER]

GO

:Connect LDC-SSC-DBA2

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 [NT Service\MSSQLSERVER]

GO

:Connect LDC-SSC-DBA1
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 LDC-SSC-DBA2

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 LDC-SSC-DBA1

USE [master]

GO

CREATE AVAILABILITY GROUP [testAG]


WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE [NMSDB]
REPLICA ON N'LDC-SSC-DBA1' WITH (ENDPOINT_URL = N'TCP://LDC-SSC-
DBA1.sscbrands.local:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE =
SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),
N'LDC-SSC-DBA2' WITH (ENDPOINT_URL = N'TCP://LDC-SSC-
DBA2.sscbrands.local:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE =
ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS =
ALL));

GO

:Connect LDC-SSC-DBA2

ALTER AVAILABILITY GROUP [testAG] JOIN;

GO

:Connect LDC-SSC-DBA1

BACKUP DATABASE [NMSDB] TO DISK = N'\\LDC-SSC-DBA1\LDC-SSC-DBA1\NMSDB.bak' WITH


COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 5

GO

:Connect LDC-SSC-DBA2

RESTORE DATABASE [NMSDB] FROM DISK = N'\\LDC-SSC-DBA1\LDC-SSC-DBA1\NMSDB.bak' WITH


NORECOVERY, NOUNLOAD, STATS = 5

GO

:Connect LDC-SSC-DBA1

BACKUP LOG [NMSDB] TO DISK = N'\\LDC-SSC-DBA1\LDC-SSC-DBA1\


NMSDB_20151003060649.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD,
COMPRESSION, STATS = 5

GO

:Connect LDC-SSC-DBA2

RESTORE LOG [NMSDB] FROM DISK = N'\\LDC-SSC-DBA1\LDC-SSC-DBA1\


NMSDB_20151003060649.trn' WITH NORECOVERY, NOUNLOAD, STATS = 5

GO

:Connect LDC-SSC-DBA2

-- 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'testAG'
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 [NMSDB] SET HADR AVAILABILITY GROUP = [testAG];

GO

GO

You might also like