Unable to Open Logshipping BackJob Properties for Database with AlwaysON AG Created

Recently while working on a production deployment, faced an issue with SQL Server LogShipping. As, it was planned maintenance, we had to find and fix the issue immediately. Writing this blog, so that others can get an idea on what can be done when faced with this type of issue.

Coming to the issue, we had three SQL Server 2012 servers Server01, Server02 and Server03. AlwaysON Availability Group had been setup between Server01 and Server02. Logshipping is setup between Server 01 and Server 03.

We had a planned maintenance where the two servers Server 01 & Server02, hardware was planned to be replaced, so we planned in a way that we will take down one server at a time and perform the maintenance, while the other server will be serving production traffic.

So, we failed-over the AlwaysoN AG with listener name as “Prod_RPT_LSTNR” from Server01 to Server02 which was successful. We disabled Logshipping backup job on Server01, disabled copy and restore jobs. on Server03. Configured logshipping from Server02 to Server03, which completed successfully, new backup, copy and restore jobs started working fine. Now Server01 was taken down for maintenance.

Later, logshipping started failing with errors related to backup path being incorrect. We checked and found that drive where we pointed backup path was removed and we were told that the drive cannot be added back, so we had to change the backup path in logshipping.

We tried to change the path and tried to click on the Logshipping backup job button in logshipping configuration tab, but it was returning below error


TITLE: Microsoft SQL Server Management Studio
------------------------------
SQL Server Management Studio could not save the configuration of 'Server01' as a Primary.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An entry for primary server Server01, primary database ReportServerTempDB does not exist on this secondary. Register the primary first. (Microsoft SQL Server, Error: 32023)
--------------------------------------------------------

TITLE: Microsoft SQL Server Management Studio
------------------------------

Cannot show requested dialog.

– After some troubleshooting and research, we found that, when we configured logshipping from Server02 to Server03, we connected to the SQL instance Server02 from SSMS via AlwaysON AG listener name which was Prod_RPT_LSTNR, but later when we tried to change the backup settings we connected to SQL instance Server02 SSMS using server name which was Server02. Now, we connected back again to SQL instance Server02 via SSMS using Listener name, then it allowed us to click on logshipping backup job button and we were able to open the job properties with out any errors.

Running below command on Secondary server, will provide us with the details of logshipping primary database like primary_server, primary_database, backup_source_directory, backup_destination_directory, file_retention_period, etc.

sp_help_log_shipping_secondary_primary
@primary_server = 'SQLInstanceName',
@primary_database = 'DatabaseName'

Running below command on Primary server will provide details of primary database settings

sp_help_log_shipping_primary_database
@database = 'DatabaseName'

Now, important thing to understand here is that, when we configure logshipping in environments where we have multiple ways of connecting a SQL instance, example via AlwaysON Listener name, IP Address, Alias, etc, we need to connect using the same method later as well, if we want to make any changes to the logshipping configuration. This happens because, the SQL Server instance name which we used to connect will be stored in MSDB logshipping metadata and will look for same server name later, so if later we try to connect using another method by specifying different instance name like IP Address/listener/alias, it will mis-match with the metadata and will return errors.

Hope this was helpful.

Thanks,
SQLServerF1 Team

 

Leave a Reply

Your email address will not be published. Required fields are marked *