June 21, 2011 at 12:02 am
HI all,
Just to check with you all that i have tried to simulate a log shipping for two instances. (all on the same PC).
But althought initially in the job activity for the secondary database succeeded, but subsequent restore failed.
The error is as follows: Could not apply log backup file "C:\SecondaryBackup\Database_20110621054804.trn to secondary database 'Database'. Exclusive access could not be obtained because the database is in use. RESTORE LOG is terminating abnormally."
But i have checked the secondary database, it is currently in Standby /Read only mode. So how come the database is in used?
By the way, just to check what is the difference between Log shipping and mirroring? are they referring to the same thing? And replication is the same also?
June 21, 2011 at 1:31 am
Hi Kesmond
It seems like you have set LS up to go into standby when the log has finished restoring, there is an additonal check box on the LS section of the database properties which will disconnect any user sessions in the database when trying to restore the next log which is what you are missing I am guessing.
Will you be using the LS database to report from in your production environment or is it just for business continuity and disaster recovery?
If you do want to report then you will want to leave it in standby and disconnect the users, but if it is purely for DR then I would recommend setting LS up in the no recovery mode.
LogShipping (LS), Mirroring and Replication are three different things but they can be used for DR tasks it all depends on what your organisation needs and your acceptable data loss and recovery time objectives are.
We have a three server setup, for all production servers, one is the primary with mirroring to a secondary which is within the same building but in the secondary server room, the third is in the DR building around 30 miles away to which we use log shipping as we can afford to loose some data in the event of moving into a DR situation.
We have found that there is around a 1ms delay in mirroring per mile of fibre so this meant a 30ms delay in sending data from the main data centre to the DR data centre but as we could lose around 15 minutes of data we opted for LS.
Mirroring comes in sync and a-sync methods, replication in merge and snapshot / transactional and LS is just one method, I would look at what you actually need for BC/DR and use the correct method for your company.
Thanks
Ant
June 22, 2011 at 11:59 pm
hi, thanks for the prompt reply. Just to let you know that i try it on a new database, when i right click the database and select Transaction Log Shipping, i hit the attached error message.
June 23, 2011 at 12:04 am
Illegal_Operation (6/22/2011)
hi, thanks for the prompt reply. Just to let you know that i try it on a new database, when i right click the database and select Transaction Log Shipping, i hit the attached error message.
If i uncheck the "Enable this as a primary database in a log shipping configuration" checkbox, would it affect my current backup?
I am thinking whether could this be the cause of my backup missing problem which i encounter previously?
June 23, 2011 at 12:32 am
That error message indicates to me that either the first time logshipping was set up, it completed but missed the creation of a job, or and I would say this is more than likely, someone has deleted the job from the server.
If you uncheck the box, it wont affect your full backup, but you will stop taking transaction log backups.
As the logshipping plan doesnt perform full backups, you will have either a manual process or a maintenance plan to backup every night, this will still function as normal, but while your logshipping is disabled I would manually backup the logs until logshipping is re-established.
BACKUP LOG [DATABASENAME] TO DISK = '[PATH TO BACKUP FILES]'
June 23, 2011 at 12:44 am
anthony.green (6/23/2011)
That error message indicates to me that either the first time logshipping was set up, it completed but missed the creation of a job, or and I would say this is more than likely, someone has deleted the job from the server.If you uncheck the box, it wont affect your full backup, but you will stop taking transaction log backups.
As the logshipping plan doesnt perform full backups, you will have either a manual process or a maintenance plan to backup every night, this will still function as normal, but while your logshipping is disabled I would manually backup the logs until logshipping is re-established.
BACKUP LOG [DATABASENAME] TO DISK = '[PATH TO BACKUP FILES]'
This database was created fresh without configuring of any logshipping. Previously i did create a database of the same name with logshipping but i had dropped the database and recreate a new database with the same name. If that is the case, how come logshipping option was turn on?
So if i uncheck the box, i should reconfigure my transaction logs backup again? I am currently using maintenance plan to backup the transaction logs every 10 mins daily. So why u suggest doing manually backup the logs until the logship is reestablished? thanks
June 23, 2011 at 12:48 am
if you have already created a maintenance plan outside of log shipping to backup your logs then you are fine you dont need to do any manual backups.
in a log shipping environment you dont need to create such a maintenance plan as the log shipping plan deals with all this for you.
when you dropped the database did you remove log shipping first?
if not then the settings would still be within msdb, so when you create the database again, as it had the same name, it already thought log shipping was enabled, this could be the cause of the issue as the LSN's between the dropped database and the new created database would be different causing log shipping to fail.
June 23, 2011 at 2:46 am
anthony.green (6/23/2011)
if you have already created a maintenance plan outside of log shipping to backup your logs then you are fine you dont need to do any manual backups.in a log shipping environment you dont need to create such a maintenance plan as the log shipping plan deals with all this for you.
when you dropped the database did you remove log shipping first?
if not then the settings would still be within msdb, so when you create the database again, as it had the same name, it already thought log shipping was enabled, this could be the cause of the issue as the LSN's between the dropped database and the new created database would be different causing log shipping to fail.
Thanks. i will try it out and let u know if there is any problem. thanks
June 24, 2011 at 12:37 am
Hi all, i have setup a test environment for the transaction log shipping on the same machine with 2 instances.
From what i know,transaction log shipping is where the primary database will do the backup of the transaction log, and the server agent will copy and restore the transaction log on the secondary database.
The initial setup had been done and the secondary database had got the data as the same as the primary database. There is a problem whereby now when i update the primary database with new data, the secondary database is not getting the new data, it is still contains the initial data that was setup.
Is there any other configuration that i have missed out? thanks
June 24, 2011 at 12:47 am
can you detail the setup, backup/copy/restore/restore delay frequencies as well as the paths both local and UNC, or even better screen shots of the log shipping configuration.
it would seem that the secondary instance is either not copying the file or the restore isn't working.
June 24, 2011 at 1:20 am
Hi,
Attached please find the transaction log shipping setting.
I have checked and there are 1 *.bak file and lots of *.trn files in the backup folder and also there are *.trn files in the destination folder as well (C:\Program Files\Microsoft SQL Server\MSSQL10.SQLINSTANCE2\MSSQL\DATA)
June 24, 2011 at 1:33 am
Thanks, one thing I would say is enable the "disconnect users.............." check box if you are going into standby mode as this way it will not fail any restores and I take it you will be using the log shipped database for reporting aspects?
On the other note, what I would say is the timings on your jobs are staggered which is great but I personally would of set them up with the same frequency, just to start at a different time
E.g backup every 5 minutes between 09:00 and 23:59, then on secondary copy every 5 minutes starting at 09:01 till 00:00 and restore every 5 minutes starting at 09:01 till 00:00 but with a restore delay of 1 minute etc.
This way you are allowing time for the backup to finish before copying, and then waiting for the log to copy before restoring.
As for why the data isn't there, I would say that the log hadn't been restored on the secondary while you where checking it or the restore failed as there was someone in the secondary database.
June 24, 2011 at 2:12 am
anthony.green (6/24/2011)
Thanks, one thing I would say is enable the "disconnect users.............." check box if you are going into standby mode as this way it will not fail any restores and I take it you will be using the log shipped database for reporting aspects?On the other note, what I would say is the timings on your jobs are staggered which is great but I personally would of set them up with the same frequency, just to start at a different time
E.g backup every 5 minutes between 09:00 and 23:59, then on secondary copy every 5 minutes starting at 09:01 till 00:00 and restore every 5 minutes starting at 09:01 till 00:00 but with a restore delay of 1 minute etc.
This way you are allowing time for the backup to finish before copying, and then waiting for the log to copy before restoring.
As for why the data isn't there, I would say that the log hadn't been restored on the secondary while you where checking it or the restore failed as there was someone in the secondary database.
Attached please find the job log for secondary database - Restore. I not sure what happen.
June 24, 2011 at 2:19 am
Yep exactly what I thought, its because you dont have the "disconnect users................" setting checked, which I detailed in my first post and the one above.
What are your intentions for using logshipping?
Is it to provide a database which is 5 minutes away from live for reporting?
Is it to provide disaster recovery?
Or is it for both?
June 24, 2011 at 2:46 am
anthony.green (6/24/2011)
Yep exactly what I thought, its because you dont have the "disconnect users................" setting checked, which I detailed in my first post and the one above.What are your intentions for using logshipping?
Is it to provide a database which is 5 minutes away from live for reporting?
Is it to provide disaster recovery?
Or is it for both?
Hi, thanks. I just check the "disconnect users..." and i will wait and let u know the result.
My intention for using logshipping is just to test it out how it works and how it can apply to the production environment. I understand that is mainly use for report purposes, where the secondary databases can be used for reporting.
Just to check with you, is it advisable for it to be used for disaster recovery? What is it different from mirroring or replicating or failover? I am a bit confused over those mentioned.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply