olahallengren / sql-server-maintenance-solution

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

OLA DatabaseBackup bug with Clusterless AG #603

Open MartyFigueroa opened 2 years ago

MartyFigueroa commented 2 years ago

Description of the issue DatabaseBackup not working for servers that use Clusterless AG (Clusterless Availability Groups) It appears that there is no check for Clusterless AG (in Clusterless AG setups, the servers are not a part of a windows cluster )

SQL Server version and edition Execute SELECT @@VERSION

Microsoft SQL Server 2017 (RTM-CU27) (KB5006944) - 14.0.3421.10 (X64) Oct 14 2021 00:47:52 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

Version of the script Check the header of the stored procedure


--// Source: https://ola.hallengren.com //-- --// License: https://ola.hallengren.com/license.html //-- --// GitHub: https://github.com/olahallengren/sql-server-maintenance-solution //-- --// Version: 2020-12-31 18:58:56 //--

What command are you executing?

DECLARE @Weekday INT , @Retention VARCHAR(9) , @DataDomainBoostHost VARCHAR(25) , @DataDomainBoostUser VARCHAR(25) , @DataDomainBoostDevicePath VARCHAR(25)

SELECT @DataDomainBoostHost = Param_value FROM DBAUtility.dbo.DBAParameters WHERE Param_name = 'DataDomainBoostHost'

SELECT @DataDomainBoostUser = Param_value FROM DBAUtility.dbo.DBAParameters WHERE Param_name = 'DataDomainBoostUser'

SELECT @DataDomainBoostDevicePath = Param_value FROM DBAUtility.dbo.DBAParameters WHERE Param_name = 'DataDomainBoostDevicePath'

SET @Weekday = DAY(getdate());

IF ((@Weekday < 8) AND (DATENAME(DW, GETDATE()) = 'Monday')) BEGIN PRINT 'Today is the first week of the month. Retention is 366 days.' SET @Retention = '8784' -- Keep backup for 1 year (366 days) END ELSE BEGIN PRINT 'Today is not the first week of the month. Retention is 14 days.' SET @Retention = '337' -- Keep backup for 14 days END

EXECUTE dbo.DatabaseBackup @Databases = 'ARC_Integration', @BackupType = 'FULL', @CheckSum = 'Y', @BackupSoftware = 'DATA_DOMAIN_BOOST', @DataDomainBoostHost = @DataDomainBoostHost, @DataDomainBoostUser = @DataDomainBoostUser, @DataDomainBoostDevicePath = @DataDomainBoostDevicePath, @DataDomainBoostLockboxPath = 'C:\Program Files\DPSAPPS\common\lockbox', @CleanupTime = @Retention, @NumberOfFiles = 6, @Verify = 'N', @Compress='N'

What output are you getting?

Msg 50000, Level 16, State 1, Procedure dbo.CommandExecute, Line 156 [Batch Start Line 0] The value for the parameter @Command is not supported.

-- ** NOTE: ** NOTE: ** NOTE: -- We found a workaround, but this really needs to be fixed for the user community.

DatabaseBackup stored proc :

-- -- -- Changed following: --SET @CurrentCommand += ' -c ' + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster ELSE CAST(SERVERPROPERTY('MachineName') AS nvarchar) END

-- -- -- TO: SET @CurrentCommand += ' -c ' + CAST(SERVERPROPERTY('MachineName') AS nvarchar)

-- ** END NOTE:

MartyFigueroa commented 2 years ago

-- ** NOTE: ** NOTE: ** NOTE: -- We found a workaround, but this really needs to be fixed for the user community.

DatabaseBackup stored proc :

-- -- -- Changed following: --SET @CurrentCommand += ' -c ' + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @cluster ELSE CAST(SERVERPROPERTY('MachineName') AS nvarchar) END

-- -- -- TO: SET @CurrentCommand += ' -c ' + CAST(SERVERPROPERTY('MachineName') AS nvarchar)

-- ** END NOTE:

MartyFigueroa commented 2 years ago

-- ** NOTE: ** NOTE: ** NOTE: -- We found a workaround, but this really needs to be fixed for the user community.

DatabaseBackup stored proc :

-- -- -- Changed following: --SET @CurrentCommand += ' -c ' + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @cluster ELSE CAST(SERVERPROPERTY('MachineName') AS nvarchar) END

-- -- -- TO: SET @CurrentCommand += ' -c ' + CAST(SERVERPROPERTY('MachineName') AS nvarchar)

-- ** END NOTE: