February 27, 2004 at 7:34 am
Hello All,
I have a slight issue that I've been trying to find information on without much success, so I thought I'd get with the experts here. I've configured log shipping between 2 servers, primary (A) and secondary (B), with Log Shipping Monitor on (C). I'm shipping logs for 15 db's and all is going well with the copy and load sequences for all of them. My issue is when I view the list of plans on server (C), all listed have "the big red X", indicating a problem. On further review of the properties and results, the Status tab/sheet, under Status: the note reads - "Backup not occurring". As I stated, when viewing the history of backups and/or the copy and load history, all are successful. If I Disable the Backup Alert, the red X goes away and the status then displays "Normal".
Q: What backup is it reporting on, that supposedly isn't occurring? T-logs are being backed up on A, copied to B and then loaded (restored) on B, with success, according to the results listed in the backup and copy/load histories for each plan.
Notes:
1. Aside from the T-Log backups that are taking place every hour as part of the log shipping scheme, I am backing up the server A db's as part of a separate maintenance plan nightly. So it seems the backups are occurring successfully there.
2. As you know, once log shipping is configured the plans shows up on server B as well as on server A where they were created, with slightly different config tabs. When viewing the server B property sheets for the plans, there is a tab for Complete Backup. I've tried configuring a backup schedule there, but the results are that these backup jobs can't run because the db's are in "Warm Standby" mode.... So that won't fix it.
Anyway, that's it. Any help out there would be cooler than ice. Thanks much for any assistance anyone can lend.
Thank You SQL Community!
March 1, 2004 at 8:00 am
This was removed by the editor as SPAM
March 1, 2004 at 8:26 am
I would check and confirm that the sql server agent account on each of your servers has sufficient permissions on the others. It could be that it cannot retrieve or post information about the log shipping due to authentication issues. What does it show as the current delta for the 2 servers? If this is a massive amount and yet the trans log files are being dumped and copied on a regular basis this would indicate that being the problem.
March 3, 2004 at 6:40 am
Hi Stacenic,
Thanks very much for the reply. The copy and loads are happening regularly but the delta's are in the 9500 range, so you might have hit the nail on the head. I will grant those permissions for the SQLAGENT and see if that changes anything.
Thank you very much for your suggestion.
Ren
March 3, 2004 at 6:43 am
What I've done in the past is to make the destination server also the monitor server and use the same domain service accounts on both boxes so that I eliminate the problem.
March 3, 2004 at 6:51 am
OK, that's great. Just so happens that my monitor is my destination server. I originally had the monitor on a different machine, then I recofigured the entire thing, and put the monitor on my destination machine. I will make sure that I am using a domain account with proper access. Is there anywhere in particular you would suggest giving this domain account permissions that might help clear this up?
Thank you!
March 3, 2004 at 6:57 am
In the past when I've done this the service account on both machines was the same and was an admin on the box, with the builtin/administrators group in sql this made it a sysadmin, so I did not have to assign any extra permissions.
March 3, 2004 at 8:14 am
I've done so here now as well. The services, MSSQLSERVER and SQLSERVERAGENT services on both boxes are now using the same domain account, which have been added to the BUILTIN\Administrators group on each server, except for the MSSQLSERVICE service on the primary (source) server. It has been using a different, local machine account and my changing it won't take effect until I stop and restart the service. I can't do this on the production (source) server during production hours so I will have to wait to apply this change, to see if it helps. I have a feeling it will fix my problem. I'll keep you posted on my results, once I'm able to restart this service.
Thanks again and have a great day!
Ren
March 3, 2004 at 8:16 am
I believe that it's the SQL Agent service account that is critical, as this is what does all the work (ie dumping, copying and restoring files). So long as you have this account as an admin I don't see that you should experience any problems.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply