March 21, 2012 at 2:08 pm
Hi all:
I'm trying to test my understanding of log shipping here. I'm currently trying to get it working on 2 servers for now. After going through the setup, all steps indicate a success. I can see the backup and alerts jobs successfully created on the primary server, but I do not see the copy and restore jobs on the secondary server. Oddly enough, I do see the Adventureworks data files on the secondary server and in SSMS I see it in "(restoring...)" mode on the secondary.
I have both instances' sql server service & agent service running under the domain account we have set up for this. I have this domain account set up in the sql user and sql agent groups on both servers. The domain account has sysadmin rights on both servers. The domain account has modify permissions in the copy folder that I've set up on the secondary.
What have I overlooked? Again, the problem seems to be with the secondary and specifically the jobs not getting created there.
Thanks,
March 21, 2012 at 2:49 pm
what is the error message?
March 21, 2012 at 2:51 pm
SS999 (3/21/2012)
what is the error message?
There is no error message. When Log Shipping is set up it reports all steps are a success.
March 21, 2012 at 2:56 pm
Can u tell what does the copy job history say?
March 21, 2012 at 3:02 pm
SS999 (3/21/2012)
Can u tell what does the copy job history say?
There is no job history. Part of the problem is that the copy job is not getting created. Thus, there can be no history.
March 22, 2012 at 11:38 am
Additional info:
select @@servername returns NULL
select ServerProperty('servername') returns 'SQLDR'
The server is virtualized - I don't know the version of hypervisor. Previously, ServerProperty was returning 'TBS-01' until I did a sp_dropserver and sp_addserver to correct the issue.
March 23, 2012 at 7:20 am
Hi!, well, if you do not have job, TLS will not works. You should try to script TLS configuration, pressing bottom 'Script Configuration', and create the job re-executing this backuped configuration. In deed, this is a good practice, so if you need to reconfiguring TLS, just uncheck 'Enable this a primary database in a log shipping configuration' option to delete previously created jobs, and by execution of this scripts, will be on road again.
I hope this help you.
Best regards
eng. Fernando Paez Becker
March 23, 2012 at 7:29 am
Thanks. I have attempted this multiple times. The jobs never get created.
March 23, 2012 at 7:36 am
Ok, please could you tell us what version of Ms Sql Server do you have? Can you copy created script configurations TLS, so we can find an workaround.
March 23, 2012 at 7:42 am
Using SQL 2005, SP3
The source instance is named 2-BQZ5DP1\DELS2005. I have tried substituting the IP address as well. Thanks for giving this a look. 🙂
-- ****** Begin: Script to be run at Secondary: [sqldr] ******
DECLARE @LS_Secondary__CopyJobIdAS uniqueidentifier
DECLARE @LS_Secondary__RestoreJobIdAS uniqueidentifier
DECLARE @LS_Secondary__SecondaryIdAS uniqueidentifier
DECLARE @LS_Add_RetCodeAs int
EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary
@primary_server = N'10.0.0.165\DELS2005'
,@primary_database = N'AdventureWorks'
,@backup_source_directory = N'\\SQL01\Databases\LSBackups'
,@backup_destination_directory = N'F:\LSCopies '
,@copy_job_name = N'LSCopy_2-BQZ5DP1\DELS2005_AdventureWorks'
,@restore_job_name = N'LSRestore_2-BQZ5DP1\DELS2005_AdventureWorks'
,@file_retention_period = 4320
,@monitor_server = N'10.0.0.165\DELS2005'
,@monitor_server_security_mode = 1
,@overwrite = 1
,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT
,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT
,@secondary_id = @LS_Secondary__SecondaryId OUTPUT
IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
BEGIN
DECLARE @LS_SecondaryCopyJobScheduleUIDAs uniqueidentifier
DECLARE @LS_SecondaryCopyJobScheduleIDAS int
EXEC msdb.dbo.sp_add_schedule
@schedule_name =N'DefaultCopyJobSchedule'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 15
,@freq_recurrence_factor = 0
,@active_start_date = 20120322
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT
,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_Secondary__CopyJobId
,@schedule_id = @LS_SecondaryCopyJobScheduleID
DECLARE @LS_SecondaryRestoreJobScheduleUIDAs uniqueidentifier
DECLARE @LS_SecondaryRestoreJobScheduleIDAS int
EXEC msdb.dbo.sp_add_schedule
@schedule_name =N'DefaultRestoreJobSchedule'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 5
,@freq_recurrence_factor = 0
,@active_start_date = 20120322
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT
,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_Secondary__RestoreJobId
,@schedule_id = @LS_SecondaryRestoreJobScheduleID
END
DECLARE @LS_Add_RetCode2As int
IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)
BEGIN
EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database
@secondary_database = N'AdventureWorks'
,@primary_server = N'10.0.0.165\DELS2005'
,@primary_database = N'AdventureWorks'
,@restore_delay = 0
,@restore_mode = 0
,@disconnect_users= 0
,@restore_threshold = 45
,@threshold_alert_enabled = 1
,@history_retention_period= 5760
,@overwrite = 1
END
IF (@@error = 0 AND @LS_Add_RetCode = 0)
BEGIN
EXEC msdb.dbo.sp_update_job
@job_id = @LS_Secondary__CopyJobId
,@enabled = 1
EXEC msdb.dbo.sp_update_job
@job_id = @LS_Secondary__RestoreJobId
,@enabled = 1
END
-- ****** End: Script to be run at Secondary: [sqldr] ******
March 23, 2012 at 9:36 am
This script is good enough and it must create those JOBS...
Did you try to execute this script at destination server without get any has an output? sems quit perplexing
Well, then.... You should create manually each job, (copy & restore).
Copy
Naming: LSCopy_sourcesrvname,pornumber_DBSource
, step 1, type Operating system (cmdexec)
"C:\apps_srv\mssql\100\Tools\Binn\sqllogship.exe" -Copy "NRO_GUID_1" -server "destination_server_instance"
Restore
Naming: LSRestore_sourcesrvname,pornumber_DBDestination
, step 1, type Operating system (cmdexec)
"C:\apps_srv\mssql\100\Tools\Binn\sqllogship.exe" -Restore "NRO_GUID_1" -server "destination_server_instance"
NRO_GUID_1-> id in msdb.dbo.log_shipping_secondary create by some like
insert into msdb.dbo.log_shipping_secondary(NEWID(),'sourceserver,portnumber',
primary_database,
backup_source_directory,
backup_destination_directory,
file_retention_period,
copy_job_id ,--> id from select * from sysjobs create early
restore_job_id ,--> id from select * from sysjobs create early
monitor_server,
monitor_server_security_mode,
user_specified_monitor,
last_copied_file - null,
last_copied_date -null)
So, first create both jobs with "id_guid" as a text, then insert into table take guid number and edit job command as needeed... and tell us how it work.
Regards! FPB
March 27, 2012 at 8:44 am
Thanks, Al-Rahim, I may try that later. Since this is a test, I have also tried log shipping to another secondary. For those trials, the jobs get created successfully, but the copy job fails with an "Could not retrieve copy settings for secondary id '*****'. Then, I tried to log ship to this same secondary without providing a monitor. In this case, all is working.
I am continuing to research this, as I need to logship to the original secondary and I must have a monitor.
July 15, 2013 at 5:15 am
Good Morning Del Lee,
I too facing the same issue.Did you get the solution for this issue?.
It will be great if you share it.
Regards
July 15, 2013 at 6:41 am
Did you get the solution for this issue?
No, I was never able to get this resolved. In the end, I believe it was a network issue because the secondary was on a different network. I'm sure there is a solution for this scenario, but it was not critical in our case to pursue it beyond what I had already done. Good luck with your situation.
July 15, 2013 at 8:27 am
The error refers to an account entitlement error... so give properly permissions to secondary agent server account. You should have same account in both primary and secondary servers. Also see permissions at windows level (the secondary agent server account should have read permission for destination backup folder)
That will solve your issue.. Please, let us know.
Regards.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply