none
Monitor Job is not created in monitor server

    Question

  • Hi Everyone,

    While Configuring Log Shipping , I noticed monitor job gets created when I specify @monitor_server as Primary Server , But not when i specify @monitor_server  as some other server  . How do i Diagnose this ?

    Any help would be highly appreciated.

    Many Thanks

    Chaithanya M




    Saturday, January 18, 2014 8:10 AM

Answers

  • Is it Mandatory to take log back up after full back up and restore it in secondary server ?  Restoring full backup and then subsequent log backups taken by job is enough right ? please correct me if i am wrong . 

    Yes its mandatory.

    Do we have to intialize the secondary database both with Fullbackup followed by log backup ? if so why ?

    I would like to mention that I have heard that log shipping can be configured with just full backup(restored on Secondary no log files restore required) it will work   .Now Log shipping is all about keeping secondary server as much synchronized as possible to Primary and if you are aware, transaction logs are linked to each other through log sequence number and are in Kind of sequence.So full backup followed by log backup keeps this log in sequence and also gives a path for further log backups to be restored and makes your secondary DB identical to primary.

    I would suggest you to read about log shipping please

    http://msdn.microsoft.com/en-us/library/ms187103(v=sql.105).aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    Tuesday, January 21, 2014 11:07 AM

All replies

  • Hello,

    Can you give more details about your problem , any document which you are referring to


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Saturday, January 18, 2014 11:57 AM
  • I am using a login which is a member of sysadmin  and the password i am specifying is correct(Checked by logging in to the management studio). so is authentication should be successfull. But job is not getting created in the server i specfied ?

    EXEC sp_add_log_shipping_primary_database @Database				   ='LogShippingPrimary'							,
    										  @Backup_Directory		   ='C:\BackupMyDb\LogShippingBackup'			,
    										  @Backup_Share			   ='\\MyMachineName\BackupMyDb\LogShippingBackup'	,
    										  @Backup_job_Name		   ='BackupJobForLogShipping'					,
    										  @backup_retention_period =60											, --60 Minutes
    										  @monitor_server		   ='10.10.10.10\InstanceName'						,
    										  @monitor_server_security_mode=0										, --0 is Sql server Authentication
    										  @monitor_server_login	   ='LoginName'								,
    										  @monitor_server_password ='Password'								,
    										  @backup_threshold		   =10											,  --10 Minutes
    										  @threshold_alert		   =14420										,  --Error Number .
    										  @history_retention_period=1440										,  --1440 Minutes(1 Day)
    										  @threshold_alert_enabled =1											,
    										  @backup_compression	   =0											,
    										  @Backup_job_id=@Job_id OUTPUT
    SELECT @Job_id

    above is the code i use to create an backup job in primary server and alert job in monitor server. Please help me why the job is not getting created . the above code run is successfull , as it returns me job id with out any errors.

    I also tried to created job using EXEC sp_add_log_shipping_alert_job @alert_job_id= 'jobid'  , which again didnt resolve my problem.

    Kindly help in resolving this issue .

    Many Thanks

    Chaithanya M


    Regards Chaithu.. If it is usefull,Mark this as Answer.


    Sunday, January 19, 2014 9:12 AM
  • Hello.

    I dont know what document you are referring .But query you posted will not create alert job .To create alert job you  have to run proc 'sp_add_log_shipping_alert_job' but you also ran it ans did not succeed.Its highly unlikely.Also make sure you run this proc in Master database.

    Please use below article to start from scratch,

    http://technet.microsoft.com/en-us/library/ms188708(v=sql.105).aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by vr.babu Tuesday, January 21, 2014 10:04 AM
    Sunday, January 19, 2014 4:38 PM
  • Hi shanky ,

    Thank you very much for your reply. Let me give you more details : 

    1.The above script will create a backup up job and alert job , if Primary server and monitor server are same.I tested it. so , if this is the case , why we need sp_add_log_shipping_alert_job .

    2. Let's say i have Monitor server, How do Monitor server , monitors the primary and secondary , Since priamary server and secondary server has monitor server details as we specify this while adding primary server and secondary to log shipping. But Monitor server doesn't has primary and secondary server info.

    so how do we map monitor server to primary and secondary servers ?

    Please help in getting out of this confusion .

    Many Thanks

    Chaithanya M


    Regards Chaithu.. If it is usefull,Mark this as Answer.

    Tuesday, January 21, 2014 8:04 AM
  • Hi shanky ,

    Thank you very much for your reply. Let me give you more details : 

    1.The above script will create a backup up job and alert job , if Primary server and monitor server are same.I tested it. so , if this is the case , why we need sp_add_log_shipping_alert_job .

    A: sp_add_log_shipping_alert_job procedure will check to see if alert job exists on the server if yes it wont create it if no it will create.So if you already have alert configured no need to run

    http://technet.microsoft.com/en-us/library/ms189509.aspx

    2. Let's say i have Monitor server, How do Monitor server , monitors the primary and secondary , Since priamary server and secondary server has monitor server details as we specify this while adding primary server and secondary to log shipping. But Monitor server doesn't has primary and secondary server info.

    so how do we map monitor server to primary and secondary servers ?


    2.Monitor server is necessary in scenarios below

    1. Suppose your secondary goes down and alert for restore job and copy job would be on Secondary in such case you wont even get alert and quick action which needs to be taken is ruled out .May be you get to notice after couple of days .So to get immediate alert Monitor server is required.

    2. Monitor server stores information that when log was backed on primary when it was copied on secondary and when restored.Suppose just in case your data center goes down ( may be flood) all your information is lost and if you have monitor server on different location you get the information about backup details.

    3. When monitor server is added it gets information about both primary and secondary and stored information about jobs(log shipping jobs) running on them.So this is how it is 'mapped' 



    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Proposed as answer by vr.babu Tuesday, January 21, 2014 10:04 AM
    • Edited by Shanky_621 Tuesday, January 21, 2014 10:07 AM
    Tuesday, January 21, 2014 9:39 AM
  • Ok Perfect. Assuming Monitor server will have the information after setting up log shipping , I have set up log shipping with Primary , seconday and monitor server . I then tried to shutdown the primary to see if somethign gets logged in the  monitor job  tables. But it is empty . Below is the script i have used to set up Log Shipping :

    IF DB_ID('LogShippingPrimary') IS NOT NULL
    DROP DATABASE LogShippingPrimary
    GO
    
    CREATE DATABASE LogShippingPrimary
    GO
    USE LogShippingPrimary
    GO
    
    CREATE TABLE LSTable(ID INT IDENTITY(1,1),Name VARCHAR(MAX))
    GO
    
    INSERT INTO LSTable(Name)
    SELECT 'A'
    UNION ALL
    SELECT 'B'
    
    GO
    SELECT * FROM LSTable
    ---------------------------------------------------------------------------------------------------------------------
    --Prerequisite : Restore Database in Secondary Server With Full Backup of LogShippingDemo(In Primary Server).
    ---------------------------------------------------------------------------------------------------------------------
    --Backup the Primary server Database
    BACKUP DATABASE LogShippingPrimary TO DISK ='C:\BackupMyDb\LogShippingPrimary.Bak'
    GO
    
    --Restore in Secondary Server
    
    RESTORE DATABASE LogShippingSecondary FROM DISK='C:\BackupMyDb\LogShippingPrimary.Bak'
    WITH MOVE 'LogShippingPrimary' TO 'C:\BackupMyDb\LogShippingSeconday_Data.mdf' ,
    MOVE 'LogShippingPrimary_log' TO 'C:\BackupMyDb\LogShippingSeconday_Log.Ldf',
    --NORECOVERY
    STANDBY ='C:\BackupMyDb\UnCommitedTransactions.Ldf',REPLACE
    --OR With StandBY
    
    ---------------------------------------------------------------------------------------------------------------------
    --Step 1 : Creating Backup Job .(Run In Primary Server)
    ---------------------------------------------------------------------------------------------------------------------
    --Adds Primary Database to Log Shipping and creates Backup Job(Disabled By Default)
    
    DECLARE @Job_id UNIQUEIDENTIFIER
    		
    
    EXEC sp_add_log_shipping_primary_database @Database				   ='LogShippingPrimary'							,
    										  @Backup_Directory		   ='C:\BackupMyDb\LogShippingBackup'			,
    										  @Backup_Share			   ='\\MyMachine\BackupMyDb\LogShippingBackup'	,
    										  @Backup_job_Name		   ='BackupJobForLogShipping'					,
    										  @backup_retention_period =60											, --60 Minutes
    										  @monitor_server		   ='MonitorServerInstance'						,
    										  @monitor_server_security_mode=0										, --0 is Sql server Authentication
    										  @monitor_server_login	   ='LoginName'								,
    										  @monitor_server_password ='*****'								,
    										  @backup_threshold		   =1											,  --10 Minutes
    										  @threshold_alert		   =14420										,  --Error Number .
    										  @history_retention_period=1440										,  --1440 Minutes(1 Day)
    										  @threshold_alert_enabled =1											,
    										  @backup_compression	   =0											,
    										  @Backup_job_id=@Job_id OUTPUT
    SELECT @Job_id
    
    
    
    
    
    ---------------------------------------------------------------------------------------------------------------------
    --Step 2 : Creating Schedule for the BackupJob .(Run In Primary Server)
    ---------------------------------------------------------------------------------------------------------------------							 
    --Below Configuration is for Occuring  Every Day Every 3 Minutes.
    											 
    EXEC msdb.dbo.sp_add_jobschedule @job_Id					=@Job_id				,
    								 @Name						='LogShippingBackupSch' ,
    								 @Enabled					=1						,  --Enabling Schedule
    								 @freq_Type					=4						,
    								 @freq_interval				=1						,
    								 @freq_subday_type			=4						,
    								 @freq_subday_interval		=1						,
    								 @active_start_date			=20140103
    
    
    EXEC msdb.dbo.sp_update_job @job_id=@Job_id,@Enabled=1                                  --Enabling Job
    
    ---------------------------------------------------------------------------------------------------------------------
    --Step 3: Creating Alert Job.(Run in Monitor Server)
    ---------------------------------------------------------------------------------------------------------------------							 
    --This will check if any alert job is created or not. If doesnt find any ,it creates a job and makes an entry of jobid in table msdb.dbo.log_shipping_monitor_alert
    
    EXEC sp_add_log_shipping_alert_job 
    
    SELECT * FROM msdb.dbo.log_shipping_monitor_alert
    --SELECT * FROM msdb.dbo.sysjobs WHERE job_id='DEE402A7-7EBB-4B1D-B76A-06EE76562B64'
    
    
    ---------------------------------------------------------------------------------------------------------------------
    --Step 1 : Creating Copy Job And Restore Job .(Run In Secondary Server)
    ---------------------------------------------------------------------------------------------------------------------
    --Adding Primary Database to LogShipping in secondary Server
    
    DECLARE @CopyJob_id		UNIQUEIDENTIFIER,
    		@RestoreJob_Id  UNIQUEIDENTIFIER
    		
    
    EXEC sp_add_log_shipping_Secondary_Primary     @Primary_server				    ='MyMachine\Chaithu'				,
    										       @Primary_Database				='LogShippingPrimary'				,
    											   @Backup_Source_Directory			='C:\BackupMyDb\LogShippingBackup'	,
    										       @backup_destination_directory	='C:\BackupMyDb\LogShippingCopy'	,
    										       @copy_job_name					='CopyJobForLogShipping'			,
    										       @restore_job_name				='RestoreJobForLogShipping'			,
    										       @file_retention_period           =40									,
    										       @monitor_server					='MonitorServerInstance'			    ,
    											   @monitor_server_security_mode	=0									, --0 is Sql server Authentication
    										       @monitor_server_login			='LoginName'						,
    										       @monitor_server_password			='****'					,
    										       @copy_job_id                     =@CopyJob_id	OUTPUT				,
    										       @restore_job_id					=@RestoreJob_Id OUTPUT
    											 
    
    SELECT 	@CopyJob_id AS CopyJobId,	@RestoreJob_Id AS RestoreJobID			
    
    
    ---------------------------------------------------------------------------------------------------------------------
    --Step 2 : Creating Schedule for the Copy Job And Restore Job.(Run In Secondary Server)
    ---------------------------------------------------------------------------------------------------------------------							 
    											 
    EXEC msdb.dbo.sp_add_jobschedule @job_Id=@CopyJob_id,
    @Name='LogShippingCopySch',@Enabled=1,@freq_Type=4,@freq_interval=1,@freq_subday_type=4,
    @freq_subday_interval=4,@active_start_date=20140103
    
    
    EXEC msdb.dbo.sp_update_job @job_id=@CopyJob_id,@Enabled=1
    
    EXEC msdb.dbo.sp_add_jobschedule @job_Id=@RestoreJob_Id,
    @Name='LogShippingRestoreSch',@Enabled=1,@freq_Type=4,@freq_interval=1,@freq_subday_type=4,
    @freq_subday_interval=3,@active_start_date=20140103
    
    
    EXEC msdb.dbo.sp_update_job @job_id=@RestoreJob_Id,@Enabled=1
    
    ---------------------------------------------------------------------------------------------------------------------
    --Step 3 : Add Secondary Database (Run this in Secondary Server)
    ---------------------------------------------------------------------------------------------------------------------							 
    
    EXEC sp_add_log_shipping_secondary_database @secondary_database='LogShippingSecondary'	,
    											@primary_server    ='MyMachine\Chaithu'	,
    											@primary_database  ='LogShippingPrimary'	,
    											@restore_mode	   =1					,
    											@disconnect_users  =1
    
    ---------------------------------------------------------------------------------------------------------------------
    --Step 4 : Add the required information of secondary server in Primary Server(Run this in Primary Server)
    ---------------------------------------------------------------------------------------------------------------------	
    EXEC sp_add_log_shipping_primary_secondary  @primary_database='LogShippingPrimary',
    											@secondary_server='MyMachine\Chaithu1',
    											@secondary_database='LogShippingSecondary'
    											
    
    											
    

    I now dont understand why the errors are not logged in monitor tables ??


    Regards Chaithu.. If it is usefull,Mark this as Answer.

    Tuesday, January 21, 2014 10:09 AM
  • Hello,

    I dont know how you managed to proceed without taking transaction log backup.In script i cannot see any transaction log backup neither any restore.

    You just took full backup.You need to add few steps like

    --on primary
    backup log LogShippingPrimary to disk='D:\LogShippingPrimary.trn'
    --on secondary
    
    restore log LogShippingSecondary FROM DISK='E:\LogShippingPrimary.trn'
    with standby ='E:\UnCommitedTransactions2.Ldf'
    Hope this helps .


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Tuesday, January 21, 2014 10:35 AM
    Tuesday, January 21, 2014 10:35 AM
  • Is it Mandatory to take log back up after full back up and restore it in secondary server ?  Restoring full backup and then subsequent log backups taken by job is enough right ? please correct me if i am wrong . 

    Do we have to intialize the secondary database both with Fullbackup followed by log backup ? if so why ?



    Regards Chaithu.. If it is usefull,Mark this as Answer.

    Tuesday, January 21, 2014 10:56 AM
  • Is it Mandatory to take log back up after full back up and restore it in secondary server ?  Restoring full backup and then subsequent log backups taken by job is enough right ? please correct me if i am wrong . 

    Yes its mandatory.

    Do we have to intialize the secondary database both with Fullbackup followed by log backup ? if so why ?

    I would like to mention that I have heard that log shipping can be configured with just full backup(restored on Secondary no log files restore required) it will work   .Now Log shipping is all about keeping secondary server as much synchronized as possible to Primary and if you are aware, transaction logs are linked to each other through log sequence number and are in Kind of sequence.So full backup followed by log backup keeps this log in sequence and also gives a path for further log backups to be restored and makes your secondary DB identical to primary.

    I would suggest you to read about log shipping please

    http://msdn.microsoft.com/en-us/library/ms187103(v=sql.105).aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    Tuesday, January 21, 2014 11:07 AM