July 21, 2016 at 8:05 am
[font="Tahoma"]Hello guys,
I'm in the process of creating a powershell function which will call .sql scripts to perform Log Shipping, FailOver and Failback.
I've covered, according to my environment requirement, the first part of the
Log Shipping (Part 1)
Shipping log from Prod DB to Test/DR DB. I've used the commands generated by SSMS to edit log shipping configuration at it works well.:-)
FailOver (Part 2)
I've used once again the same generated SSMS script for both servers/DBs, edited to fit the failover process: TEST DB now ships its logs to PROD DB.
BUT, when i check PROD DB in SSMS, it's ONLINE and still the primary database in the (Failover)Log Shipping configuration.
IF i run the following query, i can see that it's the secondary database in the (Failover)Log Shipping configuration and in STANDBY mode. But hell, not in SSMS which got me insane :crazy::w00t: i must admitt:-). Here's the query:
SELECT secondary_database,
restore_mode,
disconnect_users,
last_restored_file
FROM msdb.dbo.log_shipping_secondary_databases
Hence, the restore job on PROD DB is failing as the DB is not in either NORECOVERY or STANDBY mode.
Do you guys have an idea why, the DB on PROD DB is not going on STANDBY mode?
I know, how to put the DB in STANDBY or NORECOVERY but not really sure which process i should use to do it without breaking the LSN or getting an error such as no log files are ready to rollforward ????
Thanks a lot for taking the time to review this with me.
[/font]
July 22, 2016 at 6:52 am
Well, at least you have narrowed it down to being a restore problem. I know you have checked this already, but what does the Sql Server error log, windows application log, and the Log Shipping monitor say about why the restore is failing, anything? As you know, it mainly fails due to the LSN being out of sync as you mentioned.
Here is a query you can run on the secondary that gives lots of information -
select lss.primary_server,lsms.secondary_server,lss.primary_database,lsms.secondary_database,
lss.backup_source_directory,lss.backup_destination_directory,
lss.file_retention_period [backup file retention period on disk in mins],lss.last_copied_file,lss.last_copied_date,
lssd.restore_delay [Delay time set for resore (Mins)],lssd.disconnect_users [Dissconnect users while restore],
lsms.restore_threshold [Restore threshold in Mins],
lsms.last_copied_file,lsms.last_copied_date,lsms.last_restored_file,lsms.last_restored_date,DATEDIFF(MINUTE,lsms.last_restored_date,getdate()) [Restoration Not happened from (Mins)]
from
msdb.dbo.log_shipping_secondary lss join
msdb.dbo.log_shipping_secondary_databases lssd
on lss.secondary_id = lssd.secondary_id
join msdb.dbo.log_shipping_monitor_secondary lsms
on lss.secondary_id = lsms.secondary_id
July 24, 2016 at 7:33 am
Tecina (7/21/2016)
[font="Tahoma"]Hello guys,I'm in the process of creating a powershell function which will call .sql scripts to perform Log Shipping, FailOver and Failback.
I've covered, according to my environment requirement, the first part of the
Log Shipping (Part 1)
Shipping log from Prod DB to Test/DR DB. I've used the commands generated by SSMS to edit log shipping configuration at it works well.:-)
FailOver (Part 2)
I've used once again the same generated SSMS script for both servers/DBs, edited to fit the failover process: TEST DB now ships its logs to PROD DB.
BUT, when i check PROD DB in SSMS, it's ONLINE and still the primary database in the (Failover)Log Shipping configuration.
IF i run the following query, i can see that it's the secondary database in the (Failover)Log Shipping configuration and in STANDBY mode. But hell, not in SSMS which got me insane :crazy::w00t: i must admitt:-). Here's the query:
SELECT secondary_database,
restore_mode,
disconnect_users,
last_restored_file
FROM msdb.dbo.log_shipping_secondary_databases
Hence, the restore job on PROD DB is failing as the DB is not in either NORECOVERY or STANDBY mode.
Do you guys have an idea why, the DB on PROD DB is not going on STANDBY mode?
I know, how to put the DB in STANDBY or NORECOVERY but not really sure which process i should use to do it without breaking the LSN or getting an error such as no log files are ready to rollforward ????
Thanks a lot for taking the time to review this with me.
[/font]
The process to switch log shipping roles is detailed at the URL below, is this the process you have followed?
what errors do you see in the logs?
https://msdn.microsoft.com/en-GB/library/ms178117.aspx
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 25, 2016 at 12:40 am
Hello Rvarn,
Thanks for the detailed query. The LSN chain wast not out of sync. It did not even come to that. I could not grab any errors related to the process in the windows application logs nor in administrative logs.
So i fired up SQL profiler and i'm in the process of collecting data to see where it went wrong.
July 25, 2016 at 12:46 am
Perry Whittle (7/24/2016)
Tecina (7/21/2016)
[font="Tahoma"]Hello guys,I'm in the process of creating a powershell function which will call .sql scripts to perform Log Shipping, FailOver and Failback.
I've covered, according to my environment requirement, the first part of the
Log Shipping (Part 1)
Shipping log from Prod DB to Test/DR DB. I've used the commands generated by SSMS to edit log shipping configuration at it works well.:-)
FailOver (Part 2)
I've used once again the same generated SSMS script for both servers/DBs, edited to fit the failover process: TEST DB now ships its logs to PROD DB.
BUT, when i check PROD DB in SSMS, it's ONLINE and still the primary database in the (Failover)Log Shipping configuration.
IF i run the following query, i can see that it's the secondary database in the (Failover)Log Shipping configuration and in STANDBY mode. But hell, not in SSMS which got me insane :crazy::w00t: i must admitt:-). Here's the query:
SELECT secondary_database,
restore_mode,
disconnect_users,
last_restored_file
FROM msdb.dbo.log_shipping_secondary_databases
Hence, the restore job on PROD DB is failing as the DB is not in either NORECOVERY or STANDBY mode.
Do you guys have an idea why, the DB on PROD DB is not going on STANDBY mode?
I know, how to put the DB in STANDBY or NORECOVERY but not really sure which process i should use to do it without breaking the LSN or getting an error such as no log files are ready to rollforward ????
Thanks a lot for taking the time to review this with me.
[/font]
The process to switch log shipping roles is detailed at the URL below, is this the process you have followed?
what errors do you see in the logs?
Yes I've followed it by putting all the manual steps into a Powershell function.
A part from windows application logs, do you have a way to capture SQL logs?
I'm trying to pin point the relevant event in SQL profiler to trace so i could have a better understanding of what happening under the hood of this Log Shipping process.
Thanks
July 25, 2016 at 3:41 am
the sql server error log or the windows application log should provide detail
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply