does SQL 2005 allow log shipping between 2 instances
-
Hi.does anybody know?
Does SQL 2005 allow log shipping between 2 instances within the same server?
Thanks!
-
Yes you can log ship between instances.
If the inbuilt Log Shipping doesn't work, then put together your own scripts to backup and restore. There's probably some usable scripts in the script library already.
--------------------
Colt 45 - the original point and click interface
-
hi,
yes u can configure logshipping in sql 2005 b/w 2 instances in the same server,with local system account.
in that case u dont need a shared folder but if u created one its fine.create 2 shared folders,1 in which the tran log backup falls and other in which it is being copied into.[font="Verdana"]- Deepak[/font]
-
but pls note that y do u need to perform logshipping b/w 2 instances in the same server??? it does not server the purpose of logshipping,coz u need a standby if ur primary server fails,but in this case u have only 1 server and if it is down u r lost with everything...
but for a beginner to learn how to configure logshipping u can try this @home[font="Verdana"]- Deepak[/font]
-
Hi collins.Thanks for the quick response.
Hi Deepak.Because as i mentioned in the other post
since between two servers i tried log shipping and it doesn't work i thought of doing it between two instances just to test.But even that doesn't work.
Between the two instances,its the same problem.
Only backing up the transaction logs was successfull but it wasn't even copied to the other folder within the same server.
Haiz.....i already set the Agent for both instances to have Administrator log on account.
Surely i must be missing a step but i cant figure out.....
Need to show my boss something is working.......
-
Sorry...i address Phill Carter wrongly.
Thank you Phill!
-
If you post the error messages that are received when you're setting up the log shipping, we could maybe suggest what needs to change so that you can get it to work.
Log Shipping is really very simple. Backup from one database, transfer to location of second database, restore in norecovery mode. All the maintenance plans, lag time settings, monitor server, etc... are just added extras, but not necessarily required for Log Shipping to work.
--------------------
Colt 45 - the original point and click interface -
The thing that stumped me right now is there were no error messages after i finished configuring the log shipping.
Everything was successful.Even the primary database was restored to the second instance.
Hey!and now it got copied over to the folder that's supposed to hold the trans log before it can restored after i restart the agent....
I opened the Transaction Log shipping status under Reports and the column under Last Restored file was greyed out meaning it didn't occur.
No errors in the error log under SQL Agent for both instances.
Anywhere else im supposed to look at to find out what went wrong
Any idea why it's not restoring to the secondary database?
Thanks
-
i left this part out>>>.
i created a new table and new stored procedure.But it wasn't reflected back in secondary db
-
So you're running this via maintenance plans? Right-click on the maintenance plans folder and choose view history. For some reason MS thought they'd hide this away seperate from the SQL Agent job history.
--------------------
Colt 45 - the original point and click interface -
jus check manually in the folder where the tran log backup of log shipping db is falling and chek the time wen it happened,and also ensure tat the same trn file is copied and available in destination servers folder as well,if it is available @ both ends copy job was successfull,also chek if it is being restored coz the changes will get reflected only if it is restored properly,if any of the above steps fails the job history will clearly tell the reason.....if nuthin happens try giving the agent in secondary server a restart,coz it mite have been in hanged state
[font="Verdana"]- Deepak[/font]
-
Transaction logs was backup and copied successfully.
Guess it is using maintenance plan to restore to the secondary db as i used the wizard in object explorer to configure the log shipping.
Finally Found these errors under maintenance plan history ....
Message
The job failed. The Job was invoked by Schedule 35 (Log shipping alert job schedule.). The last step to run was step 1 (Log shipping alert job step.)
When i expand the error message,it says this
Message
Executed as user: SERVER1\Azlin. The log shipping secondary database SERVER1\STANDBYDBS.AxTest has restore threshold of 45 minutes and is out of sync. No restore was performed for 445 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421). The step failed.
Isit referring to SQL Agent log when it says agent log?
And for me to view logshipping monitor i've to run a query rite?
I did run a query before to view the monitor and the info doesnt make sense.
I used the Reporting Services tab and click on log shipping status and it shows
columns stating the job that was last ran.
Restored column was greyed out for secondary database meaning it didnt work.
Any idea why it doesn't work now?
-
just check which tran log was not restored and check if exist in destination servers folder where tran logs are copied,if exists then check the monitor server why didnt restore for any errors....
the out of sync error usually happens if the copied tran log is not restored for a certain amount of time mentioned while configuring log shipping...did u restart sql agent in destination server???
[font="Verdana"]- Deepak[/font]
-
Can somebody help me please!!!!
I've reconfigured everthing and now it's copying over to the folder for the secondary instance.
Turns out it was folder permission problem.
I had to add the logon account that the SQL services using and give permission to modify on the drive that contains the folder and the folder that will hold the copied logs.
But sadly it's still not restoring even though it says it restored up to the named transaction log file.
when you say use the monitor server to view the errors why it isn't restoring,you are referring to the sp_help_log_shipping_monitor query which we run in the query right?which is the same as clicking on the Report tab in Object explorer. Or am i wrong?
Here how it looks like.
Somebody Save me......
-
Ahhh ... you'll need to post the images to on of the many photo sharing websites and then post the urls for us to see the images.
--------------------
Colt 45 - the original point and click interface
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply