Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
TonyTheDBA
Posting Yak Master
121 Posts |
 Posted - 2012-10-03 : 07:56:17
|
Hi All,I'm after some thoughts from the collective wisdomShortly we will be implementing a Dual Node AlwaysOn cluster, and the one aspect that interests me is the ability to offload the backups to a secondary replica.Now we take Full Backups at the start and end of the day (COPY_ONLY on the replica I understand), and TLog backups evey 15 minutes.What I would like to know is, is there a way on each HA Node to determine which one is the Active node so that I can ensure that my backup scripts (In my sysadmindb) only run on the replica??On the AG Listner I can Run select SERVERPROPERTY ('ComputerNamePhysicalNetBIOS')which returns the active Node Name, but it returns NULL when run on either of the HA Nodes.RegardsTony |
|
|
TonyTheDBA
Posting Yak Master
121 Posts |
Posted - 2012-10-03 : 09:25:44
|
A bit of digging around in the System objects found what I needed.SELECT sys.fn_hadr_backup_is_preferred_replica ( 'DatabaseName' ) Returns a 1 if it is the secondary replica, and 0 on the primary.-- RegardsTony The DBA |
 |
|
|
|
|
|
2012 Availability groups and Offloading Backups