create procedure sys.sp_MSprocesslogshippingmonitorprimary
(
    @mode tinyint -- 1 = add, 2 = delete, 3 = update
    ,@primary_id uniqueidentifier
    ,@primary_server sysname = NULL -- needed for add
    ,@monitor_server sysname
    ,@monitor_server_security_mode bit = 1
    ,@primary_database sysname = NULL -- needed for add
    ,@backup_threshold int = NULL
    ,@threshold_alert int = NULL
    ,@threshold_alert_enabled bit = NULL
    ,@last_backup_file nvarchar(500) = NULL
    ,@last_backup_date datetime = NULL
    ,@last_backup_date_utc datetime = NULL
    ,@history_retention_period int = NULL
    ,@ignoreremotemonitor bit = 0
)
as
begin
    set nocount on
    declare @retcode int
                ,@linkcmd nvarchar(512)

    
    -- validate @mode
    
    if (@mode not in (1,2,3))
        return 1
    
    -- When adding - always delete first
    
    if (@mode in (1,2))
    begin
        
        -- clean up log_shipping_monitor_error_detail
        
        select @linkcmd = N'delete msdb.dbo.log_shipping_monitor_error_detail '
        + N'where agent_id = N''' + cast(@primary_id as nvarchar(50)) + N''' and agent_type = 0 '
        exec (@linkcmd)
        
        -- clean up log_shipping_monitor_history_detail
        
        select @linkcmd = N'delete msdb.dbo.log_shipping_monitor_history_detail '
        + N'where agent_id = N''' + cast(@primary_id as nvarchar(50)) + N''' and agent_type = 0 '
        exec (@linkcmd)
        
        -- remove entry from log_shipping_monitor_primary
        
        delete msdb.dbo.log_shipping_monitor_primary
        where (@primary_id is not null
                    and primary_id = @primary_id)
            or (@primary_server is not null and @primary_database is not null
                    and primary_server = upper(@primary_server)
                    and primary_database = @primary_database)
        
        -- drop alert job if needed
        
        if (upper(@monitor_server) = upper(@@servername))
        begin
            exec sys.sp_delete_log_shipping_alert_job_internal
        end
        
        -- specific processing for add
        
        if (@mode = 1)
        begin
            
            -- Add an entry in the log_shipping_monitor_primary
            
            insert into msdb.dbo.log_shipping_monitor_primary (
                primary_id
                ,primary_server
                ,primary_database
                ,backup_threshold
                ,threshold_alert
                ,threshold_alert_enabled
                ,last_backup_file
                ,last_backup_date
                ,last_backup_date_utc
                ,history_retention_period)
            values (
                @primary_id
                ,upper(@primary_server)
                ,@primary_database
                ,@backup_threshold
                ,@threshold_alert
                ,@threshold_alert_enabled
                ,@last_backup_file
                ,@last_backup_date
                ,@last_backup_date_utc
                ,@history_retention_period)
            if (@@error != 0)
                return 1
            
            -- add alert job if needed
            
            if (upper(@monitor_server) = upper(@@servername))
            begin
                exec sys.sp_add_log_shipping_alert_job_internal
            end
        end
    end
    else if (@mode = 3)
    begin
        
        -- update log_shipping_monitor_primary
        
        update msdb.dbo.log_shipping_monitor_primary
        set backup_threshold = case when (@backup_threshold is null) then backup_threshold else @backup_threshold end
             ,threshold_alert = case when (@threshold_alert is null) then threshold_alert else @threshold_alert end
             ,threshold_alert_enabled = case when (@threshold_alert_enabled is null) then threshold_alert_enabled else @threshold_alert_enabled end
             ,last_backup_file = case when (@last_backup_file is null) then last_backup_file else @last_backup_file end
             ,last_backup_date = case when (@last_backup_date is null) then last_backup_date else @last_backup_date end
             ,last_backup_date_utc = case when (@last_backup_date_utc is null) then last_backup_date_utc else @last_backup_date_utc end
             ,history_retention_period = case when (@history_retention_period is null) then history_retention_period else @history_retention_period end
        where primary_id = @primary_id
        if (@@error != 0)
            return 1
    end
    
    -- Do we need have remote monitor
    
    if (upper(@monitor_server) != upper(@primary_server)
            and upper(@monitor_server) != upper(@@servername)
            and @ignoreremotemonitor = 0)
    begin
        if (@monitor_server_security_mode = 0) and (suser_name() != SUSER_SNAME(0x01))
        begin
            
            -- Process remote monitor using proxy
            
            exec @retcode = msdb.sys.sp_MSproxylogshippingmonitorprimary
                    @mode = @mode
                    ,@primary_id = @primary_id
                    ,@primary_server = @primary_server
                    ,@monitor_server = @monitor_server
                    ,@primary_database = @primary_database
                    ,@backup_threshold = @backup_threshold
                    ,@threshold_alert = @threshold_alert
                    ,@threshold_alert_enabled = @threshold_alert_enabled
                    ,@last_backup_file = @last_backup_file
                    ,@last_backup_date = @last_backup_date
                    ,@last_backup_date_utc = @last_backup_date_utc
                    ,@history_retention_period = @history_retention_period
        end
        else
        begin
            
            -- integrated -do not use proxy
            
            select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.msdb.sys.sp_processlogshippingmonitorprimary'
            begin try
                exec @retcode = @linkcmd
                    @mode = @mode
                    ,@primary_id = @primary_id
                    ,@primary_server = @primary_server
                    ,@monitor_server = @monitor_server
                    ,@monitor_server_security_mode = 1
                    ,@primary_database = @primary_database
                    ,@backup_threshold = @backup_threshold
                    ,@threshold_alert = @threshold_alert
                    ,@threshold_alert_enabled = @threshold_alert_enabled
                    ,@last_backup_file = @last_backup_file
                    ,@last_backup_date = @last_backup_date
                    ,@last_backup_date_utc = @last_backup_date_utc
                    ,@history_retention_period = @history_retention_period
            end try
            begin catch
                select @retcode = 1
            end catch
        end
        if (@retcode != 0 or @@error != 0)
            return 1
    end
    
    -- all done
    
    return 0
end