Log shipping setup problem

Last week, our log shipping setup had a problem, so I spent some time to dig into the log shipping.

there are 2 monitor tables after logshipping setup
1. log_shipping_monitor_primary
      This table has entries for all primary database on primary server and monitor server

2. log_shipping_monitor_secondary 
      This table has entries for all primary database on secondary server and monitor server

In my system, I use primary server as monitor server, so on primary server, log_shipping_monitor_primary should have the same entry number as log_shipping_monitor_secondary's. But after I setup 4 databases logshipping, in log_shipping_monitor_primary there were4 entries, however, in log_shipping_monitor_secondary there was only 1 entries

In order to troubleshooting the issue, I went through the logshipping setup steps first
1. Initialize the secondary database by restoring a full backup of the primary database on the secondary server.

2. On the primary server, execute master.dbo.sp_add_log_shipping_primary_database to add a primary database. The stored procedure returns the backup job ID and primary ID.
Internal Steps of master.dbo.sp_add_log_shipping_primary_database:
 o   Call sys.sp_MSlogshippingsysadmincheck to check the security

 o   Validate Parameter
 o   Check if database has already been in table log_shipping_primary_databases, if overwrite parameter is 1, then call sp_delete_log_shipping_primary_database to delete it
 o   If monitor server is not local server, then call msdb.sys.sp_MSprocesslogshipmonitorlink, which creates a link server for the remote server
 o   Call sys.sp_MSgetlogshipagentpath to get the tools\binn folder, by default it is 'C:\Program Files\Microsoft SQL Server\90\Tools\binn\'
 o   Call sys.fn_MSgetlogshippingjobname to get the new job name, this is where the job name “LSBackup_xxx”, “LSCopy_xxx”, “LSRestore_xxx”, and “LSAlert_xxx” come from.
 o   Call sys.sp_MSprocesslogshippingjob to create job
        In fact, sys.sp_MSprocesslogshippingjob  will call msdb.dbo.sp_add_job and msdb.dbo.sp_add_jobstep to create the job
 o   Add an entry in the log_shipping_primary_databases
 o   Call sp_MSprocesslogshippingmonitorprimary to setup monitor
  Before add monitor, it will delete info from msdb.dbo.log_shipping_monitor_error_detail, msdb.dbo.log_shipping_monitor_history_detail, msdb.dbo.log_shipping_monitor_primary,and call sys.sp_delete_log_shipping_alert_job_internal
        Then add entry in msdb.dbo.log_shipping_monitor_primary,  and call sys.sp_add_log_shipping_alert_job_internal to add alert if needed.
        If it is remote monitor server, call msdb.sys.sp_MSproxylogshippingmonitorprimary to setup monitor.

Briefly speaking,  sp_add_log_shipping_primary_database creates LSBackup_xxx job, add one row in log_shipping_primary_databases, and setup monitor, in the end, reture the new job id, and primaryid.

3.       On the primary server, execute sp_add_jobschedule to add a schedule for the backup job.
 Use the job id and primary id from step 2 to create job schedule for LSBackup_xxx job.

4. On the monitor server, execute sp_add_log_shipping_alert_job to add the alert job.
It calls sys.sp_add_log_shipping_alert_job_internal underlayer, which create alter job and add entry in msdb.dbo.log_shipping_monitor_alert.

5. On the primary server, enable the backup job.
 EXEC msdb.dbo.sp_update_job @job_id = @LS_BackupJobId ,@enabled = 1

6.  On the secondary server, execute sp_add_log_shipping_secondary_primary supplying the details of the primary server and database. This stored procedure returns the secondary ID and the copy and restore job IDs.
sp_add_log_shipping_secondary_primary does the similiar way as

7. On the secondary server, execute sp_add_jobschedule to set the schedule for the copy and restore jobs.
8. On the secondary server, execute sp_add_log_shipping_secondary_database to add a secondary database.
 Similar as sp_add_log_shipping_primary_database
9. On the primary server, execute sp_add_log_shipping_primary_secondary to add the required information about the new secondary database to the primary server.
10. On the secondary server, enable the copy and restore jobs.


let's return to my logshipping problem:
when I setup logshipping on secondary , I use script
                EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database
                                         @secondary_database = '[DATABASENAME]'
                                                ,@primary_server = '[PRIMARYSERVER]'
                                                ,@primary_database = '[DATABASENAME]'
                                                ,@restore_delay = 0
                                                ,@restore_mode = 1
                                                ,@disconnect_users       = 1
                                                ,@restore_threshold = 180  
                                                ,@threshold_alert_enabled = 1
                                                ,@history_retention_period      = 5760
                                                ,@overwrite = 1
It will get the monitor server name from table log_shipping_secondary, here is the script in sp_add_log_shipping_secondary_database
    select @secondary_id = secondary_id
            ,@monitor_server = monitor_server
            ,@monitor_server_security_mode = monitor_server_security_mode
    from msdb.dbo.log_shipping_secondary

However, I found some of the monitor servers don't have domain name, while others have domain name.

let’s continue tracking the  sp_add_log_shipping_secondary_database.  After getting the monitor server name, it will call sp_MSprocesslogshippingmonitorsecondary to setup the monitor table, in sp_MSprocesslogshippingmonitorsecondary,
    exec @retcode = sp_MSprocesslogshippingmonitorsecondary @mode = 1
                ,@secondary_server = @@servername, …………………
1. you can see that it use @@servername as secondary server name,  which doesn’t have domain name.
2. then insert one entry in local(secondary server) log_shipping_monitor_secondary table
3. then try to insert entry in monitor server by creating link server

Here we can see there are 2 linked server created on DR SQL because we have 2 different monitor servernames (have domain name and not have domain name)
4. then call sp_processlogshippingmonitorsecondary on the remote linked server(it is primary server in our system)
5. in sp_processlogshippingmonitorsecondary , we found the answer
    if (@monitor_server is null or upper(@monitor_server) != upper(@@servername)
            or upper(@monitor_server) = upper(@secondary_server))
        return 0

because my monitor_server has domain name, it is different with the @@servername which doesn’t have domain name, so sp_processlogshippingmonitorsecondary will not really execute,  this is why I have only 1 entry in log_shipping_monitor_secondary.
so I need to use server name without domain name as a standard in all scripts!