- LSPrimary01 - Log Shipping Primary
- LSSecondary01 - Log Shipping Secondary
- OddServer03 - This server is not part of the LS configuration, the odd man out
- Sean\LSServiceAccount - The domain user service account for log shipping on all servers
A colleague of mine and I were configuring Log Shipping in a test environment. We took a full
COPY_ONLY backup of a database on LSPrimary01, a log backup, copied to LSSecondary01 and restored the backup and log
WITH NORECOVERY. Then used the GUI on LSPrimary01 to configure Log Shipping. We already configured file sharing and proper permissions for Sean\LSServiceAccount.
The following error was the result:
- START OF TRANSACTION LOG BACKUP
- Error: Could not retrieve backup settings for primary ID'cb1564b4-4ffd-a42d'.(Microsoft.SqlServer.Management.LogShipping)
- Error: Failed to connect to server OddServer03.(Microsoft.SqlServer.ConnectionInfo)
- Error: Login failed for user 'Sean\LSServiceAccount'.(Net SqlClient Data Provider)
- END OF TRANSACTION LOG BACKUP
I ran the following query to verify my Log Shipping configuration:
SELECT [primary_database] ,[backup_directory] ,[backup_share] ,[backup_retention_period] ,[monitor_server] ,[user_specified_monitor] ,[monitor_server_security_mode] ,[last_backup_file] ,[last_backup_date] FROM [msdb].[dbo].[log_shipping_primary_databases]
Everything in the result set was correct, save monitor_server, it had the value of OddServer03! As many of you know, when configuring Log Shipping through the GUI, it is damn near impossible to "accidentally" configure this option.
We never intended having a monitor server in the mix. We use Quest Spotlight to monitor the aforementioned servers which will yield alerts when jobs fail or when log shipping gets behind.
One other perplexing matter, the job that is getting created is
LSAlert_OddServer03 instead of
Why is the service account trying to authenticate to a server that isn't part of the configuration?