January 26, 2011 at 11:10 am
Hi,
We have SQL Server 2008 Primary instance in ABC domain and the secondary server is in XYZ domain.
We want configure log shipping between those two servers.
We cannot make the Domain trust between them. In this case, is it possible to configure log shipping? please advice
January 26, 2011 at 11:42 am
I've done log shipping via FTP before, so yeah, it's possible. You may not be able to do it via a wizard or whatever, but you can certain ship log files and restore them via domains, it's just a more manual process.
What would you do if you needed to ship any other type of file from one to the other?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 26, 2011 at 12:06 pm
If we cannot use Wizard, then can we just run log backups every 15 mins and then set up a copy job to copy to secondary and a restore job to restore the backups.
Here, could you advice me how to set up the copy job using SQLAGENT to copy the logbackup files, which are backedup every 15 mins with different timestamps?
January 26, 2011 at 1:29 pm
domain authentication would be out here, so a local user on each of the SQL Servers with matching passwords should cover the access to the shares on each machine for log file backups. The rest is pretty standard. The following script depicts a typical log shipping implementation
-- The script needs to be run at the Primary in the context of the [msdb] database.
-------------------------------------------------------------------------------------
-- Adding the Log Shipping configuration
DECLARE @LS_BackupJobIdAS uniqueidentifier
DECLARE @LS_PrimaryIdAS uniqueidentifier
DECLARE @SP_Add_RetCodeAs int
EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
@database = N'AdventureWorks'
,@backup_directory = N'D:\MSSQL\Backups'
,@backup_share = N'\\myserver\backups'
,@backup_job_name = N'LSBackup_AdventureWorks'
,@backup_retention_period = 4320
,@monitor_server = N'MYMONITORSRV'
,@monitor_server_security_mode = 1
,@backup_threshold = 60
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@backup_job_id = @LS_BackupJobId OUTPUT
,@primary_id = @LS_PrimaryId OUTPUT
,@overwrite = 1
,@ignoreremotemonitor = 1
IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)
BEGIN
DECLARE @LS_BackUpScheduleUIDAs uniqueidentifier
DECLARE @LS_BackUpScheduleIDAS int
EXEC msdb.dbo.sp_add_schedule
@schedule_name =N'LSBackupSchedule_MyPrimaryServerJobSch'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 8
,@freq_subday_interval = 4
,@freq_recurrence_factor = 0
,@active_start_date = 20110127
,@active_end_date = 99991231
,@active_start_time = 81500
,@active_end_time = 171500
,@schedule_uid = @LS_BackUpScheduleUID OUTPUT
,@schedule_id = @LS_BackUpScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_BackupJobId
,@schedule_id = @LS_BackUpScheduleID
EXEC msdb.dbo.sp_update_job
@job_id = @LS_BackupJobId
,@enabled = 1
END
EXEC master.dbo.sp_add_log_shipping_primary_secondary
@primary_database = N'AdventureWorks'
,@secondary_server = N'MySecondaryServer'
,@secondary_database = N'AdventureWorks'
,@overwrite = 1
--Script to be run at Monitor:
EXEC msdb.dbo.sp_processlogshippingmonitorprimary
@mode = 1
,@primary_id = N''
,@primary_server = N'MyPrimaryServer'
,@monitor_server = N'MYMONITORSRV'
,@monitor_server_security_mode = 1
,@primary_database = N'AdventureWorks'
,@backup_threshold = 60
,@threshold_alert = 14420
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
-- Execute the following statements at the Secondary to configure Log Shipping
-- the script needs to be run at the Secondary in the context of the [msdb] database.
-------------------------------------------------------------------------------------
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'MyPrimaryServer'
,@primary_database = N'AdventureWorks'
,@backup_source_directory = N'\\myserver\backups'
,@backup_destination_directory = N'D:\MSSQL\LogShip'
,@copy_job_name = N'LSCopy_MyPrimaryServer_AdventureWorks'
,@restore_job_name = N'LSRestore_MyPrimaryServer_AdventureWorks'
,@file_retention_period = 4320
,@monitor_server = N'MYMONITORSRV'
,@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 = 8
,@freq_subday_interval = 4
,@freq_recurrence_factor = 0
,@active_start_date = 20110127
,@active_end_date = 99991231
,@active_start_time = 83000
,@active_end_time = 173000
,@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 = 8
,@freq_subday_interval = 4
,@freq_recurrence_factor = 0
,@active_start_date = 20110127
,@active_end_date = 99991231
,@active_start_time = 84500
,@active_end_time = 174500
,@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'MyPrimaryServer'
,@primary_database = N'AdventureWorks'
,@restore_delay = 0
,@restore_mode = 1
,@disconnect_users= 1
,@restore_threshold = 1440
,@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
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 26, 2011 at 3:49 pm
Looks like you forgot to place the script!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply