Xrhrft

I am using the following code with Ola Hallalengren's script to run copy only backups on a 3 node AAG setup. I have configured to run backups using the "prefer secondary" option . When I triggered the job from the primary replica, only db's outside the AAG are backing up and when I trigger it from one of the secondary only AAG db's are backing up.

The priority for backups has been given like so

Primary - 50%

Secondary - 50%

Secondary1 - 40%

What am I missing?Please suggest. Thanks!

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'\SQL02Backups', @CopyOnly = 'Y', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = NULL, @CheckSum = 'Y', @LogToTable = 'Y'" -b

I have configured to run backups using the "prefer secondary" option.

Prefer Secondary - Will backup on secondary with highest priority. If no secondary's are available, it will backup on primary.

When I triggered the job from the primary replica, only db's outside
the AAG are backing up and when I trigger it from one of the secondary
only AAG db's are backing up.

First of all i would like to say that i am try to explain in concise way but due to function logic , elaborate a bit more.

Once you have your backup preferences and priority established, then it is time to set up a SQL Server Agent Job to perform the backups. Thankfully Microsoft has provided us with a system function called sys.fn_hadr_backup_is_preferred_replica. This function will return 0 if the current instance is not the preferred backup location or 1 if it is the preferred replica for backups according to the preference and priority settings.

IF (NOT sys.fn_hadr_backup_is_preferred_replica(@DBNAME))  

BEGIN  

      Select ‘This is not the preferred replica, exiting with success’;  

      RETURN 0 – This is a normal, expected condition, so the script returns success  

END  

BACKUP DATABASE @DBNAME TO DISK=<disk>  

   WITH COPY_ONLY; 

As per MSDB BOL Here To take the automated backup preference into account for a given availability group, on each server instance that hosts an availability replica whose backup priority is greater than zero (>0), you need to script backup jobs for the databases in the availability group. To determine whether the current replica is the preferred backup replica, use the sys.fn_hadr_backup_is_preferred_replica function in your backup script. If the availability replica that is hosted by the current server instance is the preferred replica for backups, this function returns 1. If not, the function returns 0. By running a simple script on each availability replica that queries this function, you can determine which replica should run a given backup job.

Scripting a backup job with this logic enables you to schedule the job to run on every availability replica on the same schedule. Each of these jobs looks at the same data to determine which job should run, so only one of the scheduled job actually proceeds to the backup stage.

Note:-There is no enforcement of AutomatedBackupPreference. The interpretation of this preference depends on the logic, if any, that you script into backup jobs for the databases in a given availability group. The automated backup preference setting has no impact on ad-hoc backups.

As,i would like to say that as when you are configuring auto backup of
AAG through maintenance Plan in 'Primary Replica' & when your Backup
preferrence will be 'Preferred Secondary Replica' in that case also
the Non AAG databases will be backup in primary replica backup
location, after the execution of Maintenance plan schedule backup. And
Availability databases you shall not find out in the backup location
in Primary Replica . But When you shall check the 'Synchronization
state' through ' Show Dashboard' you shall find out, the both Replica
in 'Synchronized state'.

For reference i am writing the last script, which had ran in my server (as on dated '15-10-2016') through 'maintenance plan backup'. Which script was as follows:-

DECLARE @preferredReplica int



SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica(''Database1''))



IF (@preferredReplica = 1)

BEGIN

    BACKUP DATABASE [Database1] TO  DISK = N''H:SQLSRV-N01_Backup_FilesAutomatic BackupDatabase1_backup_2016_10_15_000001_6209992.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''Database1_backup_2016_10_15_000001_6209992'', SKIP, REWIND, NOUNLOAD,  STATS = 10

END



GO

DECLARE @preferredReplica int



SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica(''Database2''))



IF (@preferredReplica = 1)

BEGIN

    BACKUP DATABASE [Database2] TO  DISK = N''H:SQLSRV-N01_Backup_FilesAutomatic BackupDatabase2_backup_2016_10_15_000001_7520058.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''Database2_backup_2016_10_15_000001_7520058'', SKIP, REWIND, NOUNLOAD,  STATS = 10

END



GO

DECLARE @preferredReplica int



SET @preferredReplica = (SELECT [master].sys.fn_hadr_backup_is_preferred_replica(''Database3''))



IF (@preferredReplica = 1)

BEGIN

    BACKUP DATABASE [Database3] TO  DISK = N''H:SQLSRV-N01_Backup_FilesAutomatic BackupDatabase3_backup_2016_10_15_000001_7770006.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''Database3_backup_2016_10_15_000001_7770006'', SKIP, REWIND, NOUNLOAD,  STATS = 10

END



GO

BACKUP DATABASE [Database4] TO  DISK = N''H:SQLSRV-N01_Backup_FilesAutomatic BackupDatabase4_backup_2016_10_15_000001_8030022.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''Database4_backup_2016_10_15_000001_8030022'', SKIP, REWIND, NOUNLOAD,  STATS = 10



GO

BACKUP DATABASE [Database5] TO  DISK = N''H:SQLSRV-N01_Backup_FilesAutomatic BackupDatabase5_backup_2016_10_15_000001_8280125.bak'' WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N''Database5_backup_2016_10_15_000001_8280125'', SKIP, REWIND, NOUNLOAD,  STATS = 10



GO

Here I have only changed the database name otherwise everything is as it's the script.
As you can see the above code Database1,Database2 & Database3 are AAG Databases. That's why
automatically it check through 'Scalar-value Functions' (i,e; sys.fn_hadr_backup_is_preferred_replica).
And For the database4 & database5 , it does not check through that function, because these databases are Non AAG Databases.
So, after execution of my 'primary server replica''maintenance plan schedular backup'' only i have found out the Non AAG Databases in specified backup location.

So, I can say that you are not missing anything in Ola Hallalengren's script.

For your Ref Backing Up SQL Server Databases Participating In AlwaysOn Availability Groups and Active Secondaries: Backup on Secondary Replicas (Always On Availability Groups)