Log Shipping Hell
-
Ok, I'll try to make this brief.
I have Server "A" that is a production server. I have Server "B" that is supposed to be a warm stand by server. I want to log ship from server A to server B.
I made a complete backup and restored it on server b and left the database on read only mode.
I start a new maintenance plan and follow all the steps in setting up log shipping. When I complete the lst screen and click ok the server does it's thing and I get a message Log Shipping Created Successfully.
I wait for the time when I have set to perform log shipping and that time comes and goes. I look at the log shipping monitor and view the copy history and all looks good. I look at the load history and all looks good.
I go to the jobs icon in the SQL Server Agent section and look at the job history for the log shipping and all looks go.
In the production database there is a table called loginhistory which keeps a record of all the logins into the application that is connected to the database.
In my mind this table should be in sync. So when I query server b I expect the login time to be up to date less the time difference between the log shipping times.
Guess what, the login table is not in sync. and over a course of a week there is no new records in this or any other table.
If someone could help me with this is would be much appreciated.
My suspicion is I have the db in read only mode and I am thinking I need to set it to single user mode.
Thanks
Gary
-
When you do your full restore, use the "WITH NORECOVERY" clause. This leaves it capable of restoring a log backup. The log restores should use the "WITH STANDBY" clause which will place the db in read only, but able to restore additional logs. My log shipping is homegrown, so I have not used Microsoft's version. I assume that the "WITH STANDBY" would be taken care of behind the scenes. But you wouldn't want to manually place the db in read only mode. The only thing to do different is to use the "WITH NORECOVERY" on the full restore.
Steve
-
Gary,
For log shipping, the secondary server should be in read-only mode. There is nothing wrong with it. With you having mentioned that all the copy and restore jobs are successfully, i can't think of anything else. Can you verify that the loginhistory table is in the log shipped database ?
-
That's really curious. I'd been using log shipping for quite a while now and after the initial stumbling it seems pretty solid. The fact that all necessary jobs show success, the only thing I can think of is rather off the wall: are the loginhistory updates being committed?
-
Been there done that. Main thing you need to do if you aren't seeing the "In Sync" on the monitor, but you can see the logs have been applied, you need to check your rights on the servers. make sure that you are running both the service and the agent with an id that has rights on the other box.
You would then make sure you didn't set any delay load thresholds ( although I wouldn't expect a 1 week delay)
I spent 3 months in a testing environment with LogShipping. Finally got all of the bugs out. Just a heads up, there's a bug when you try to run the failover per the documentation.
Give me a shout if you need some more input. Be glad to help.
-
Try querying restorehistory table on your secondary server and this should give you an idea about the last log file restored.
-
Thanks for all the replies. Here is what I have done to this point.
I would have bet money that the secondary server sql agent service was using the same domain account as the primary server but it was not. This has been corrected. Both servers use the same domain account, I have verified this.
I removed the log shipping and any jobs that were running in relation to the log shipping. I then deleted the secondary server database that I am trying to ship logs to. This is to start with a clean slate.
I created a Blank database on the secondary sever. I then made a fresh complete backup of the primary database and applied to the sercondary database leaving the database in read only mode and allowing transaction logs to be applied.
I then setup log shipping to ship the logs to the secondary server.
All went fine with no errors.
When the time for the log shipping came I saw the log show up in the shared folder on the secondary server. I then checked the copy and restore history in the log shipping monitor and all was successful.
I then checked the jobs in the sql agent section and they too had succeeded.
I then queried the restorehistory table in the msdb database (per suggestion) and it shows the restore of the complete backup but not the restore of the logs.
I then queried the loginhistory table and bam the new data is not there.
So any ideas where I am screwing up?
Thanks in advance.
Gary
-
Hi Gary,
LogShipping can be frustrating sometimes so hang in there. I have LogShipping running successfully, so maybe I can help. I have a couple of questions before I can address your issue.
1. What version of SQL are you running on both the Primary and Secondary servers? i.e. SQL 2000 EE with SP3, or SQL 7 with SP3.
2. What OS is your SQL installed on? i.e. Windows 2000 Advance Server with SP3.
-
Primary Server is Windows 2000 Latest Service pak running on a cluster.
Secondary server is Windows 2003 latest service pack on a quad processor.
Both SQL's are SQL 2000 sp3
igig of ram on each server.
-
Microsoft States that in order to achieve successful logshipping that the O/S and the SQL should be at the same levels. That isn't to say it's not possible to ship, but Microsoft won't support shipping to different O/S configs.
One thing you may want to check is your load delays. I had the same issue for the longest time. Initially, ensure you are shipping say every 15 mins and you are loading every 15 mins. If you set a load delay (not to load the logs for 4 hours) you will see exactly that issue.
If this isn't the problem, check the sql logs and see what the problem is. It may have some error codes that you will be able to research. If you haven't already checked the Load job history, that would be a good place to start.
Good Luck,
I implement in production today!! (keep your fingers crossed)
-
Microsoft States that in order to achieve successful logshipping that the O/S and the SQL should be at the same levels. That isn't to say it's not possible to ship, but Microsoft won't support shipping to different O/S configs.
One thing you may want to check is your load delays. I had the same issue for the longest time. Initially, ensure you are shipping say every 15 mins and you are loading every 15 mins. If you set a load delay (not to load the logs for 4 hours) you will see exactly that issue.
If this isn't the problem, check the sql logs and see what the problem is. It may have some error codes that you will be able to research. If you haven't already checked the Load job history, that would be a good place to start.
Good Luck,
I implement in production today!! (keep your fingers crossed)
-
Around SP2 (I think), M$ discovered that SQL Standard could still participate as a target in Log Shipping. They put the brakes on that real quick. So, the point: What version of SQL Server 2000 are you running. If memory serves me right, both ends need to be Enterprise.
It sounds to me like you're on the right track though. If everything is dumping and copying but not loading, you've set up correctly but might have inadvertently tweaked the load delay like Lisa mentioned.
Stick to it though. Log Shipping is pretty handy to have around.
Cheers,
Ken
-
Hi Gary,
Your OS is fine. But make sure you are running SQL 2000 Enterise Edition on both servers.
Next, make sure you have shared both the Transaction Log folders on the Primary and Secondary servers. Login to the Primary server, open enterprise manager and register both servers. Use an admin account to login to the primary server. The account login should also be in the admin group of the secondary server.
Try configuring LogShipping from the Primary server, and let us know if it works.
Xpress.
-
Giving this a little more thought...
If the dump and copy work but you're not seeing error messages on the load - or, in fact, no messages about a load at all - I'll bet it's the load delay.
BTW: Good luck on that production rollout, Lisa... Remember: only rookies panic. <grin>
-
It's all good! LogShipping is working perfectly. I had to do the transfer logins manually, but have found that our script works better than the one provided by M$. There is a bug in the SP_ResolveLogins script, so we worked around it and don't use it. I originally fixed it, but then determined that when trying to run the jobs to fail over ( all scripted ) O-SQL wouldn't connect due to our server naming conventions. -S in a server name is read in O-SQL as a command switch.
We now have a single click failover. This is great when the Stress level is high and the sleep level is low.
Take it easy!
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply