Log Shipping - Monitoring Server not Updating

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Log Shipping - Monitoring Server not Updating

Author

Topic

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-07-29 : 15:21:03

Hello,

I am trying to implement Log Shipping. I have different Primary, Secondary and Monitoring Server. All the translogs are being backed up and copied onto Secondary Server and getting restored.

But the problem is with the Monitoring Server. My Primary is updating the Monitoring Server Properly but not my Secondary Server. On the Monitoring Server if I view the Backup History and Copy/restore History there are no failures.

Admin Account has the same user name and password on all these machines and SQL Server Agaent and SQL Server are running under this account.

On Monitoring Server's msdb database Administrator has select and update permisssions on log_shipping_primaries and log_shipping_secondaries tables.

On Monitoring Server Backup information is getting updated but not copy and restore information.

Any ideas?

Thanks
maximus

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-29 : 15:27:24

On the secondary server in the log_shipping_monitor table in the msdb database, does it show the monitoring server's name in monitor_server_name column?

If you can't figure it out, I would start up SQL Profiler on the monitoring server to see if there are any errors. You might find that the secondary server's account isn't able to get to the monitoring server or maybe doesn't have enough permissions. SQL Profiler will tell you exactly what is or isn't occurring.

Tara

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-07-29 : 15:39:27

Thanks Tara,

Yes in the Secondary Server for log_shipping_monitor table there is monitoring Server Name. (Actually its IP address)

I will start a Profiler and see whats going on.

Thanks
maximus

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-07-29 : 15:58:59

Hi Tara,

Can u let me know what events to watch for in the SQL profiler to get the info you mentioned in your reply.

I am running profiler for Errors and all that it says is Secondary Server Out Of Sync.

maximus

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-29 : 16:05:28

Ahhh! Secondary server is out of sync. So the copies and restores aren't in fact happening.

Tara

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-07-29 : 16:12:10

Hi Tara,

They are infact happening. On Monitor Serevr if I right click Log Shipping Monitor and see "View Copy and Backup History". I see that it is doing copy and restoring the transaction log files. the files are also correct.

My DB on Secondary Server is in READ ONLY MODE so I checked teh tables that I changed after starting teh log shipping and they are changed.

Only thing is it is not getting updated on the Monitoring Server

maximus

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-29 : 16:19:10

I don't understand your problem then. If View Copy and Backup History is showing it is working, then what part of the monitor isn't showing this? It's the secondary server that reports to the monitoring server about copies and restores. The primary doesn't do this. So the secondary server is updating it.

Tara

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-07-29 : 16:29:57

Hi Tara,

In Monitor Server, in Log Shipping Monitor if I right Click and go to properties for Last Backup File I have this : test_db_log_200407291515.TRN

and for Last File Copied and Last File restored I have :
first_file_000000000000.TRN

On the same pair If I right Click and select "View Copy/restore History" I see this:( I am showing first 4 entries and only Activity and files)
Copy - test_db_log_200407291500.TRN
Restore - test_db_log_200407291445.TRN
Copy - test_db_log_200407291445.TRN
Restore - test_db_log_200407291430.TRN

As you can clearly see that it shows as if it copied and restored files on the secondary but on the main properties it shows the worng file.

Does this make sense?

maximus

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-29 : 16:37:17

Ok, I see what you are talking about.

There are two jobs on the monitoring server that updates those values (first_file_000000000000.TRN). They are named "Log Shipping Alert Job - Backup" and "Log Shipping Alert Job - Restore". Those jobs must not be running or those values would have been updated. Check the SQL Agent on the monitoring server for those jobs.

Tara

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-07-29 : 16:41:57

Yes there are 2 jobs running on the monitoring Server

Log Shipping Alert Job - Backup - This is running fine.
But this job is failing all teh time.
Log Shipping Alert Job - Restore

maximus

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-29 : 16:45:53

quote:

But this job is failing all teh time.

That's the problem. That jobs updates the copy and restore values that is showing first_file_000000000000.TRN for you. It should get updated with the TRN names.

You will need to figure out why that job is failing and correct it in order for these values to get updated.

Tara

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-07-29 : 16:52:22

Hmmm...

Do u think I have this problem:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;292586

As mentioned in this article they told to Add the SQL Server Service startup accounts to the monitoring Server and this account should have permission to select and update these tables(log_shipping_primaries, log_shipping_secondaries) in msdb.

I have the same user name and password for all three servers and I specifically gave Select and Update on theese tables in the msdb database. Is this not enough?

Thanks
maximus

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-29 : 16:58:49

Why are you restricting permissions so much? It obviously needs more permissions as the article mentions to use db_owner.

Jobs run using the SQLSERVERAGENT logon account. This account should have local admin or at the very least sysadmin in SQL Server.

It is highly recommended that MSSQLSERVER and SQLSERVERAGENT use a domain account that has local admin privileges on the database server. The same account should be used for all of the SQL Servers.

Tara

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-07-29 : 17:23:32

Hi tara,

I gave the local Admin account on the monitoring Server db_owner for msdb database and am running it again to see if it updates or not.

And before this ... this account has "System Administrators" Server role.

quote:
Originally posted by tduggan

It is highly recommended that MSSQLSERVER and SQLSERVERAGENT use a domain account that has local admin privileges on the database server. The same account should be used for all of the SQL Servers.

If all of them are in the same Domain..doesn't this defeat the purpose of having a warm standby Server? Sorry for all these questions.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-29 : 17:26:44

How would it defeat it? You should have backup domain controllers at the other site so that the domain is still up in case you need to move to the other site. So the servers would be in the same domain.

Tara

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-07-29 : 17:46:41

Thanks Tara. Even giving db_owner to the local Admin acount on Monitoring Server did not work.
I think I should move all these into the same domain and then try it.

I'll keep you posted.

Thanks
maximus

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-29 : 17:50:16

What is the error on that job that is failing? Job history, show details, should give you information about how to troubleshoot it.

Tara

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-07-29 : 19:18:56

Hi tara,

It is working now

.. Thanks for all your help.You will not beleive why it wasn't working before.

Primary and Monitor Server are behind a firewall and Secondary is outside teh firewall. Primary is updating it but not secondary so I saw the firewall logs and saw that packets are being dropped from Secondary to Monitor on port 1433. So I opened the port for Secondary.And I did teh maintainence plan again and it worked.

But still all the Servers are not in teh same domain. Can you point me to sources where I can find links on why to run SQLSERVERAGENT and MSSQLSERVER services on domain admin accounts with local admin privilages.

Thanks again for all your help,
maximus

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-29 : 19:24:33

Not domain admin accounts, just domain users but the user has local admin on the database servers. I don't have any links handy, but SQL Server Books Online certainly mentions it.

Tara

maximus_vj
Yak Posting Veteran

88 Posts

Posted - 2004-07-30 : 10:32:18

Thanks Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources