1

At one of our client sites, I implemented a high availability (HA) solution. However, during the backup process, I encountered an issue related to backup environment configuration. We had configured the backup environment using Ola Hallengren's Maintenance Solution script. The environment included a Windows Server hostname and @@serverproperty that were different, which led to the backup process getting halted with this error

(Message 50000)  Source: https://sqlhelps.com [SQLSTATE 01000] (Message 50000)       [SQLSTATE 01000] (Message 50000)  The @@SERVERNAME does not match SERVERPROPERTY('ServerName'). See https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server. [SQLSTATE 42000] (Error 50000)      [SQLSTATE 01000] (Message 50000)  Date and time: 2023-04-23 12:46:07 [SQLSTATE 01000] (Message 50000)       [SQLSTATE 01000] (Message 50000).  The step failed.

To resolve this issue, I commented out the T-SQL script shown below: --// Check @@SERVERNAME //-- --IF 'MainServer' <> CAST(SERVERPROPERTY('ServerName') AS nvarchar(max)) AND SERVERPROPERTY('IsHadrEnabled') = 1 --BEGIN -- INSERT INTO @Errors ([Message], Severity, [State]) -- SELECT 'The @@SERVERNAME does not match SERVERPROPERTY(''ServerName''). See ' + CASE WHEN SERVERPROPERTY('IsClustered') = 0 THEN 'https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server' WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/install/rename-a-sql-server-failover-cluster-instance' END + '.', 16, 1 --END

After commenting out this script, the backup process resumed normally, and both full and differential backups were taken. However, when attempting to take a transactional log backup on the primary replica, the transaction log did not get dumped to the destination.

Note that all these backup is performed on Primary relica with backup preferences to Primary

I would appreciate it if you could share your thoughts and ideas on how to address this issue. Thank you.

1 Answer 1

2

Kedar.

First of all, to fix the discrepancy between @@SERVERNAME and its SERVERPROPERTY counterpart, please run the following script:

https://github.com/MadeiraData/MadeiraToolbox/blob/master/Utility%20Scripts/Fix_ServerName_To_MachineName.sql

You will need to restart the SQL Server service afterward to apply the changes.

Secondly, in regard to backup preferences, please keep in mind Ola Hallengren's maintenance solution takes AG replica backup preferences into consideration when choosing whether to perform a backup or not. However, you can also use the @OverrideBackupPreference parameter to override it, by specifying @OverrideBackupPreference='Y'. This only works for copy-only backups and regular transaction log backups. However, I strongly recommend NOT doing this. Instead, you should properly configure the backup preferences of your Availability Group.

To learn how to configure the backup preferences for your availability group, please refer to the following:

https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-properties-new-availability-group-backup-preferences-page

In short:

Using SQL Server Management Studio to view and change the properties an availability group:

  1. In Object Explorer, connect to the server instance that hosts the primary replica, and expand the server tree.
  2. Expand the Always On High Availability node and the Availability Groups node.
  3. Right-click the availability group whose properties you want to view, and select the Properties command.
  4. In the Availability Group Properties dialog box, use the Backup Preferences page to change the properties of the selected availability group.

Where should backups occur?

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.

Secondary only

Specifies that backups should never be performed on the primary replica. If the primary replica is the only replica online, the backup should not occur.

Primary

Specifies that the backups should always occur on the primary replica. This option is useful if you need backup features, such as creating differential backups, that are not supported when backup is run on a secondary replica.

Any Replica

Specifies that you prefer that backup jobs ignore the role of the availability replicas when choosing the replica to perform backups. Note backup jobs might evaluate other factors such as backup priority of each availability replica in combination with its operational state and connected state.

Replica backup priorities

This grid displays the current backup priority of each server instance that hosts a replica for the availability group. Use this grid to change the backup priority of one or more availability replicas.

Server Instance

The name of the instance of SQL Server that hosts the availability replica.

Backup Priority (Lowest=1, Highest=100)

Specifies your priority for performing backups on this replica relative to the other replicas in the same availability group. The value is an integer in the range of 0..100. 1 indicates the lowest priority, and 100 indicates the highest priority. If Backup Priority = 1, the availability replica would be chosen for performing backups only if no higher priority availability replicas are currently available.

Exclude Replica

Select if you never want this availability replica to be chosen for performing backups. This is useful, for example, for a remote availability replica to which you never want backups to fail over.


Sources:

4
  • Thank you your answer, So my question is what is best practice of taking transactional backup?
    – kedar
    Apr 25, 2023 at 7:34
  • @kedar , normally, most shops configure backup jobs to run on the SECONDARY synchronous replica. That would reduce workloads from the primary replica. Apr 25, 2023 at 12:48
  • 1
    thank you for your answer.
    – kedar
    Apr 26, 2023 at 10:29
  • My pleasure, @kedar . If this resolved your issue, kindly mark my answer as the one chosen for your question. Thanks! May 3, 2023 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.