June 9, 2014 at 8:21 pm
I have 2 SQL Server replicas configured on SQL Server 2012 AlwaysOn. e.g. SQL1 & SQL2.
I have configured backup job on both SQL Server with the following statement. and the job occurs every 10 minutes.
•declare @DBNAME sysname,@sqlstr varchar(500)
set @DBNAME = 'dba'
IF (sys.fn_hadr_backup_is_preferred_replica(@DBNAME)=0)
BEGIN
--Select 'This is not the preferred replica, exiting with success';
RETURN -- This is a normal, expected condition, so the script returns success
END
set @sqlstr = 'backup log dba to disk = ''E:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\Backup\dba_'+left(replace(replace(convert(varchar(30),getdate() ,126),'-',''),':',''),15)+'.trn'' with compression;';
print @sqlstr;
exec (@sqlstr)
I turned off SQL2 for Windows maintenance. So there is only SQL1 is online. Afterwards. I checked the backup folder and didn't see any new backup files was created after SQL2 was offline. I rerun the job. It still doesn't backup database on the Primary Replica. Then I searched on SQL Server Book online. It says
Prefer Secondary
Specifies that backups should occur on a secondary replica except when the primary replica is the only replica online. In that case, the backup should occur on the primary replica. This is the default option.
According to what it says, it should backup on the Primary Replica. Does anyone experience this issue before?
June 9, 2014 at 9:24 pm
It works after changed backup priority.
USE [master]
GO
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQL2' WITH (BACKUP_PRIORITY = 80)
GO
USE [master]
GO
ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON N'SQL1' WITH (BACKUP_PRIORITY = 20)
GO
It was 50/50 before
August 24, 2014 at 9:15 pm
Hi Shawn, I am trying to implement the same solution and having the same issue. Can you confirm that this will work if you failover to SQL2 and then take SQL1 down as it would seem you will have your original problem?
Cheers,
Peter.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply