Posts

Showing posts from June, 2017

Script to add database in existing availability group by Tsql.

This is the script to include database in existing Always on availability group by passing below parameters.

1. Primary server name, port number

2.Secodnary server name , port number

3. Database name

4.Availability group name

5.Shared path on primary server with read and write access for dba id required to take backup from primary server for HADR configuration.

6. This script to be executed in SQLCMD only.


--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE. :Connect Primary-Server_Name,5008
USE [master]
GO
ALTER AVAILABILITY GROUP [Availability group NAME] ADD DATABASE [Database Name];
GO
:Connect Primary-Server_Name,Port_Number
BACKUP DATABASE [Database Name] TO  DISK = N'\\Primary-Server_Name\Always_on_Backup \Database Name.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
GO
:Connect Secondary-Server_Name,Port_Number
RESTORE DATABASE [Database Name] FROM  DISK = N'\ \Primary-Server_Name\Always_on_Backup\Database Name.bak' WIT…

Steps to apply patches in Always on availability group replica servers

How do apply patch in always on availability group replica servers?

1. Make sure to connect the instances using 'Multisubnetfailover=TRUE' as this required to perform failover.

2.perform the patch apply in the secondary server replica .

3.Reboot the secondary server replica and perform health check .

4.Ensure databases are in synchronising mode in the secondary server replica .

-- select * from sys. Dm_hadr_database_replica_states.

Just check the synchronisation_state_desc and synchronisation_health_desc  column value for health values.

5.If Always on is configured in Asynchronous mode , then make sure to change it to Synchronous mode to avoid data loss.( Revert back to asynchronous mode once all steps for patch completed in both servers).

Alter  availability group group_name
Modify replica on 'server name '

With

({ availability _mode =synchronous_commit|asynchronous_commit})

6.Failover the Always on group to the Secondary server replica.

Use master
Go
Alter availabil…

Resume data movement for availability group databases # Resume HADR

while I noticed that on the secondary replica there was a pause icon next to the Availability Databases. The primary had shown both were "green", but there was an option on the secondary to Resume Data Movement. I resumed the first database, and immediately the In Recovery status message was removed. 

A minute later it changed from Not Synchronizing to Synchronized, and everything worked as expected.
Note you can also use TSQL on the secondary to resume replication on multiple database at the same time:

ALTER DATABASE [Patch] SET HADR RESUME; ALTER DATABASE [test] SET HADR RESUME;
GO

Always on availability group -HADR Questions &Answers

What about a sync mode secondary replica of always on availability group goes offline for 3 hours and come back online it self?

Answer:

Once the secondary replica server goes offline ,data movement between replica would not happen and hence the transaction log on primary grows and keep on accumulating transactions.
This would cause log file to grow;So please make sure to you have enough space in place for the log file drive in the primary replica server.

Once secondary server comes online , always on group will synchronise itself to make sure primary and secondary data in SYNC Without data loss.

No need to resume manually Always on availability session .MANUAL RESUME is used in manual/ planned failover.