search

olahallengren / sql-server-maintenance-solution

SQL Server Maintenance Solution
https://ola.hallengren.com
MIT License
2.91k stars 756 forks source link

Log backups are not working for availability groups with backup preference set to 'Prefer Secondary' #779

Closed mitesh-p closed 2 weeks ago

mitesh-p commented 1 year ago

Description of the issue The log backups are not being taken for the databases which are in availability groups with backup preference set to "Prefer Secondary"

SQL Server version and edition Microsoft SQL Server 2022 (RTM-CU8) (KB5029666) - 16.0.4075.1 (X64) Aug 23 2023 14:04:50 Copyright (C) 2022 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2022 Standard 10.0 (Build 20348: ) (Hypervisor)

Version of the script 2022-12-03 17:23:44

What command are you executing? EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = NULL, @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 72, @CheckSum = 'Y', @LogToTable = 'Y'

What output are you getting? The log backups are not being taken at all. The backup script is executing on primary. The full and diff backups are running on the primary but those backups are working only 'LOG' backups are not working. The other databases which are not in the availability group are working fine. I suspect something wrong in the following code block (on line 2809)

IF @CurrentDatabaseState = 'ONLINE'
AND NOT (@CurrentUserAccess = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0)
AND NOT (@CurrentInStandby = 1)
AND NOT (@CurrentBackupType = 'LOG' AND @CurrentRecoveryModel = 'SIMPLE')
AND NOT (@CurrentBackupType = 'LOG' AND @CurrentRecoveryModel IN('FULL','BULK_LOGGED') AND @CurrentLogLSN IS NULL)
AND NOT (@CurrentBackupType = 'DIFF' AND @CurrentDifferentialBaseLSN IS NULL)
AND NOT (@CurrentBackupType IN('DIFF','LOG') AND @CurrentDatabaseName = 'master')
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'FULL' AND @CopyOnly = 'N' AND (@CurrentAvailabilityGroupRole <> 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL))
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'FULL' AND @CopyOnly = 'Y' AND (@CurrentIsPreferredBackupReplica <> 1 OR @CurrentIsPreferredBackupReplica IS NULL) AND @OverrideBackupPreference = 'N')
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'DIFF' AND (@CurrentAvailabilityGroupRole <> 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL))
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'LOG' AND @CopyOnly = 'N' AND (@CurrentIsPreferredBackupReplica <> 1 OR @CurrentIsPreferredBackupReplica IS NULL) AND @OverrideBackupPreference = 'N')
AND NOT (@CurrentAvailabilityGroup IS NOT NULL AND @CurrentBackupType = 'LOG' AND @CopyOnly = 'Y' AND (@CurrentAvailabilityGroupRole <> 'PRIMARY' OR @CurrentAvailabilityGroupRole IS NULL))
AND NOT ((@CurrentLogShippingRole = 'PRIMARY' AND @CurrentLogShippingRole IS NOT NULL) AND @CurrentBackupType = 'LOG' AND @ExcludeLogShippedFromLogBackup = 'Y')
AND NOT (@CurrentIsReadOnly = 1 AND @Updateability = 'READ_WRITE')
AND NOT (@CurrentIsReadOnly = 0 AND @Updateability = 'READ_ONLY')
AND NOT (@CurrentBackupType = 'LOG' AND @LogSizeSinceLastLogBackup IS NOT NULL AND @TimeSinceLastLogBackup IS NOT NULL AND NOT(@CurrentLogSizeSinceLastLogBackup >= @LogSizeSinceLastLogBackup OR @CurrentLogSizeSinceLastLogBackup IS NULL OR DATEDIFF(SECOND,@CurrentLastLogBackup,SYSDATETIME()) >= @TimeSinceLastLogBackup OR @CurrentLastLogBackup IS NULL))
AND NOT (@CurrentBackupType = 'LOG' AND @Updateability = 'READ_ONLY' AND @BackupSoftware = 'DATA_DOMAIN_BOOST')
griffitmatt commented 12 months ago

" The full and diff backups are running on the primary but those backups are working only 'LOG' backups are not working."

Hi Mitesh checking are you running the backup on the primary or secondary replica?

mitesh-p commented 12 months ago

Hi,

I am running backups on the primary.

Thanks, Mitesh

On Mon, Nov 27, 2023 at 8:00 AM griffitmatt @.***> wrote:

" The full and diff backups are running on the primary but those backups are working only 'LOG' backups are not working."

Hi Mitesh checking are you running the backup on the primary or secondary replica?

— Reply to this email directly, view it on GitHub https://github.com/olahallengren/sql-server-maintenance-solution/issues/779#issuecomment-1826902560, or unsubscribe https://github.com/notifications/unsubscribe-auth/AITCT3FYHHMHQFGQYDZUB43YGOYJBAVCNFSM6AAAAAA62CLJ6OVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMRWHEYDENJWGA . You are receiving this because you authored the thread.Message ID: <olahallengren/sql-server-maintenance-solution/issues/779/1826902560@ github.com>

griffitmatt commented 12 months ago

refer to https://ola.hallengren.com/frequently-asked-questions.html

How should I configure DatabaseBackup to back up an Availability Group? First you need to decide where you want the backups to be performed; on the primary replica or on the secondary replica. SQL Server has an option called backup preference that you can set on the availability group. By default it is set to Prefer Secondary, but you can change it to Primary if you prefer that.

Not all backup types are supported on the secondary replica. Here are the backup types that are supported:

Copy-only full backups Log backups (non copy-only) DatabaseBackup will perform these types of backups on the preferred backup replica. It is using the function sys.fn_hadr_backup_is_preferred_replica to determine if the current replica is the preferred replica.

Full backups (non copy-only) and differential backups are only supported on the primary replica. DatabaseBackup will always perform these types of backups on the primary replica.

Here are some different backup strategies that you can use: Perform all the backups on the primary replica. With this strategy it works just like with any database. You can perform full (non copy-only), differential, and log backups. All you need to do is to set the backup preference to Primary for the availability group.

Perform all the backups on the secondary replica. With this strategy you can only perform copy-only full, and log backups (non copy-only). You need to check that the backup preference is set to Prefer Secondary for the availability group. You also need to add @CopyOnly = 'Y' to the full backup jobs, and disable the differential backup jobs.

Perform full (non copy-only) and differential backups on the primary replica, and log backups on the secondary replica. You need to check that the backup preference is set to Prefer Secondary for the availability group. You don't need to add any additional parameters to the full, differential, or log backup jobs. The jobs should be configured identically and be enabled and scheduled on all the replicas.

Boran commented 6 months ago

I have a similar experience. using SQL 2019, standard edition, which only allows backups to be done on the primary.

With standard edition: ALTER AVAILABILITY GROUP FooAG SET (AUTOMATED_BACKUP_PREFERENCE = PRIMARY) "The specified command is invalid because the Always On Availability Groups backup preference feature is not supported by this edition of SQL Server."

Step: DatabaseBackup - USER_DATABASES - LOG EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = NULL, @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = NULL, @CheckSum = 'Y', @LogToTable = 'Y'

Also tried @AvailabilityGroups='ALL_AVAILABILITY_GROUPS'

In the commandLog table I only ever see just one AG.

The following runs one for each AG on the primary: select sys.fn_hadr_backup_is_preferred_replica ( 'fooAG' )

Boran commented 6 months ago

To answer my own question, the reason was not to do with AGs, but the fact that LogShipping was also enabled on some DBs. By setting @ExcludeLogShippedFromLogBackup='N' , this is now a LOG backup (in addition to log shipping, which is also a redundant backup of transactions)

olahallengren commented 3 weeks ago

Description of the issue The log backups are not being taken for the databases which are in availability groups with backup preference set to "Prefer Secondary"

What output are you getting? The log backups are not being taken at all. The backup script is executing on primary. The full and diff backups are running on the primary but those backups are working only 'LOG' backups are not working. The other databases which are not in the availability group are working fine. I suspect something wrong in the following code block (on line 2809)

You should run the backup jobs on all replicas (primary and secondary). The log backups would be performed by the log backup job on the secondary replica.