October 3, 2012 at 5:50 am
Hi All,
I'm after some thoughts from the collective wisdom
Shortly 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.
Regards
Tony
October 3, 2012 at 6:15 am
My understanding is that if you use the built in MS maintenance plans, SQL is aware of which node is active and which is not.
One thing to keep in mind - if you are taking fulls from the secondary with copy_only, and then tlog backups, you do not have a continuous log chain - if you needed a restore to a point in time after a month of activity, you would need to restore a full that was NOT copy_only, then every susbsequent tlog backup.
Disclaimer - Just my understanding of how this works from a SQL Saturday session, I have not actually used.
October 3, 2012 at 7:24 am
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.
I'm not a fan of the built in Maintenance Plans, preferring to use my own backup routines that I have developed since I started playing with SQL Server 4.2. I'm Currently playing with a test/development environment, so will attempt to see if COPY_ONLY full backups can be used as the start of a backup chain, it is my understanding that they can
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply