Log Shipping Monitor Shows Problems when there aren't any

I Have setup a pair of server with log shipping, it works perfectly, logs are
backed up every 2 mins and restored succesfully to the destination server. My
only problem is that the monitor (running on the backup server) thinks that
things are not working correctly. It shows the correct times for the copy and
load jobs but the backup job time never gets updated. I have read the MS KB
article 292586 which states that this could be a permissions issue that the
source server does not have permissions to update the msdb database on the
destination server, however I have tried changing things in this area by
having the sql account running with full admin privs (both domain admin and
sql admin) this didnt work, I also tried running sql in mixed auth mode and
then configurung log shipping to use SQL authentication, it correctly created
the new log_shipping_monitor account, but still the log shipping monitor
refuses to work. Can somebody help me before I pull all my hair out.
Brett
Hello Brett,
My suggestion is that you reset password for log_shipping_monitor_probe
account on all shipping databases (both secondary and primary) and removed
and
reapplied permissions to the log shipping tables on both primary and
secondary servers. If log_shipping_monitor_prob does not exist, create the
login, set the default
database to msdb, and grant it access to msdb.
On both Primary / Secondary server, right click the log shipping database,
select properties. In the general tab, click the details button for log
shipping. Select "Use SQL Server Authentication" and change the password
to log_shipping_monitor_prob account.
More related information:
321247 How To Configure Security for Log Shipping
http://support.microsoft.com/?id=321247
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Log Shipping Monitor Shows Problems when there aren't any
>thread-index: AcX4xD/DRKnfVG7nSZmLj/7UPPi8Vg==
>X-WBNR-Posting-Host: 194.109.239.116
>From: "=?Utf-8?B?QnJldHQgQ2Fycg==?=" <brettcarr@.news.postalias>
>Subject: Log Shipping Monitor Shows Problems when there aren't any
>Date: Sun, 4 Dec 2005 03:17:01 -0800
>Lines: 15
>Message-ID: <0E563B60-3095-4635-BC0A-3153BEB87CDB@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.replication:67206
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>I Have setup a pair of server with log shipping, it works perfectly, logs
are
>backed up every 2 mins and restored succesfully to the destination server.
My
>only problem is that the monitor (running on the backup server) thinks
that
>things are not working correctly. It shows the correct times for the copy
and
>load jobs but the backup job time never gets updated. I have read the MS
KB
>article 292586 which states that this could be a permissions issue that
the
>source server does not have permissions to update the msdb database on the
>destination server, however I have tried changing things in this area by
>having the sql account running with full admin privs (both domain admin
and
>sql admin) this didnt work, I also tried running sql in mixed auth mode
and
>then configurung log shipping to use SQL authentication, it correctly
created
>the new log_shipping_monitor account, but still the log shipping monitor
>refuses to work. Can somebody help me before I pull all my hair out.
>Brett
>
|||"Peter Yang [MSFT]" wrote:

> Hello Brett,
> My suggestion is that you reset password for log_shipping_monitor_probe
> account on all shipping databases (both secondary and primary) and removed
> and
> reapplied permissions to the log shipping tables on both primary and
> secondary servers. If log_shipping_monitor_prob does not exist, create the
> login, set the default
> database to msdb, and grant it access to msdb.
> On both Primary / Secondary server, right click the log shipping database,
> select properties. In the general tab, click the details button for log
> shipping. Select "Use SQL Server Authentication" and change the password
> to log_shipping_monitor_prob account.
>
Ok. I've done the following:
1. log_shipping_monitor_prob did not exist on the primary server so I
created it.
2. I reset the password for log_shipping_monitor_prob on both primary and
secondary.
3. On both primary and secondary servers I have set
log_shipping_monitor_prob to have select,insert,update and delete permissions
on all tables starting with log_shipping in the msdb database.
However I'm afraid the 'Last Backup' date and time listed in the monitor is
still the date and time I set it up originally. The Last copy and Last
restore dates are still being updated correctly.
Brett..
|||Hello Brett,
Going forward, I'd like to confirm if Windows Authentication option is
selected on the Specify The Log Shipping Monitor Server Information screen
during log shipping setup.
If so, you may want to check if the SQL Server Service startup account on
the primary server of a log shipping pair does not have sufficient
privileges to update table msdb..log_shipping_primaries on the monitor
server.
Also, please check if the value in primary_server_name column in
log_shipping_primaries does not match the <@.@.servername> of the primary
server.
If the issue persists, please use SQL profiler on the monitor server to see
if the update operation on log_shipping_primaries occurs or if there is any
related errors in error log.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>Thread-Topic: Log Shipping Monitor Shows Problems when there aren't any
>thread-index: AcX5iaV+Rf+MfqKZTvqVdLuPrgGVlw==
>X-WBNR-Posting-Host: 193.0.2.180
>From: "=?Utf-8?B?QnJldHQgQ2Fycg==?=" <brettcarr@.news.postalias>
>References: <0E563B60-3095-4635-BC0A-3153BEB87CDB@.microsoft.com>
<VAQE#gX#FHA.1236@.TK2MSFTNGXA02.phx.gbl>[vbcol=seagreen]
>Subject: RE: Log Shipping Monitor Shows Problems when there aren't any
>Date: Mon, 5 Dec 2005 02:50:03 -0800
>Lines: 35
>Message-ID: <33C3B088-9CA3-4C3E-98B1-86E784833DDC@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.replication:67231
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>"Peter Yang [MSFT]" wrote:
removed[vbcol=seagreen]
the[vbcol=seagreen]
database,[vbcol=seagreen]
password
>Ok. I've done the following:
>1. log_shipping_monitor_prob did not exist on the primary server so I
>created it.
>2. I reset the password for log_shipping_monitor_prob on both primary and
>secondary.
>3. On both primary and secondary servers I have set
>log_shipping_monitor_prob to have select,insert,update and delete
permissions
>on all tables starting with log_shipping in the msdb database.
>However I'm afraid the 'Last Backup' date and time listed in the monitor
is
>still the date and time I set it up originally. The Last copy and Last
>restore dates are still being updated correctly.
>Brett..
>
>

Log Shipping & Backups

I have a Win2K3 SQL Cluster that is currently log shipping to my DR SQL
Server and a DEV SQL Server. Everything is working perfectly. For piece of
mind, I would still like to do a backup of the production server everynight
but that is not possible when log shipping is implemented.
What is the best way I can do this on the DEV Server? As you know, the
database is in a read only state. Can I take the database offline and copy
the *.mdf file and attempt to do a restore to another database name? I am
not a SQL expert by any means but there has to be a way to restore the
database so I can test it and back it up. Any suggestions?
Thanks!!
I'm not too sure about this: "I would still like to do a backup of the
production server everynight
but that is not possible when log shipping is implemented" - why not? It's a
while since I've done this, but AFAIR a full backup on the prod server
shouldn't interrupt the log shipping.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the response. I had a post about a month ago (See Log Shiping
8/7/2005 - 8/10/2005) that said you can not backup the database when log
shipping is active.
Here was the response:
"Logshipping bases on incremental backups. The regular backup every four
hours sets a new starting point for the TA-Log which will not be understood
by the standby server - it will need to be reinitialized with the new full
database backup ... I never found a method to backup my databases with
active logshipping - the standby server seems to be the only backup you get!
I gave up logshipping because of that and use a rather complex transactional
replication based scenario instead ..."
If you can backup the production database when log shipping is active I
would love to know how. What do you mean "AFAIR" a full backup? Thanks!!
"Paul Ibison" wrote:

> I'm not too sure about this: "I would still like to do a backup of the
> production server everynight
> but that is not possible when log shipping is implemented" - why not? It's a
> while since I've done this, but AFAIR a full backup on the prod server
> shouldn't interrupt the log shipping.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||My experience is that this is incorrect. I just tested this - I did backups
in this order:
'c:\backups\backupdb1.bak'
'c:\backups\backuplog2.bak'
'c:\backups\backupdb3.bak'
'c:\backups\backuplog4.bak'
And the following restore path was successful:
restore database northwind from disk = 'c:\backups\backupdb1.bak' with
norecovery
restore log northwind from disk = 'c:\backups\backuplog2.bak' with
norecovery
restore log northwind from disk = 'c:\backups\backuplog4.bak' with
norecovery
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
So you had log shipping enabled on the Nortwind DB then you performed a
backup? Was there any special way you did the backup? What about the comment
"The regular backup every four hours sets a new starting point for the TA-Log
which will not be understood by the standby server" Is this comment not
true? Before I attempt a backup tonight I need to make sure. Thanks!!
"Paul Ibison" wrote:

> My experience is that this is incorrect. I just tested this - I did backups
> in this order:
> 'c:\backups\backupdb1.bak'
> 'c:\backups\backuplog2.bak'
> 'c:\backups\backupdb3.bak'
> 'c:\backups\backuplog4.bak'
> And the following restore path was successful:
> restore database northwind from disk = 'c:\backups\backupdb1.bak' with
> norecovery
> restore log northwind from disk = 'c:\backups\backuplog2.bak' with
> norecovery
> restore log northwind from disk = 'c:\backups\backuplog4.bak' with
> norecovery
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Now I'm confused - are you saying I made the comment? and if so, please
can you point me to the source. Anyway, I believe this comment is mistaken,
and certainly in my test that I related to you what you propose is indeed
possible - please try it oput for yourself. If not, everyone who implemented
log-shipping would be prevented from backing up their database using a main
backup.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Sorry for the confusion. I didn't say you made the comment it was posted to
me in another Post. (Please see Log Shipping 8/7/2005) by Christian Donner.
He indicated you could not do a backup on the database that is log shipping
to a DR or Hot Stand By server. He says once you do the backup the on the
production server it "sets a new starting point for the TA-Log which will
not be understood
by the standby server - it will need to be reinitialized with the new full
database backup ..."
I hope this clears it up!!
"Paul Ibison" wrote:

> Now I'm confused - are you saying I made the comment? and if so, please
> can you point me to the source. Anyway, I believe this comment is mistaken,
> and certainly in my test that I related to you what you propose is indeed
> possible - please try it oput for yourself. If not, everyone who implemented
> log-shipping would be prevented from backing up their database using a main
> backup.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
>
|||OK - thanks for the clarification, and this definitely contradicts my test
when doing the backups manually. I'll test this with a log shipping setup
when I get access to a network, but perhaps you could test it on yours
meanwhile.
Cheers,
Paul Ibison
|||Paul,
You were correct!! I was able to backup my production databases even though
log shipping was active on this server. I tested this in my DEV environment
first and had no issues. Thanks again for working through this.
I guess sometimes you get bad info on these boards.
"Paul Ibison" wrote:

> OK - thanks for the clarification, and this definitely contradicts my test
> when doing the backups manually. I'll test this with a log shipping setup
> when I get access to a network, but perhaps you could test it on yours
> meanwhile.
> Cheers,
> Paul Ibison
>
>
|||Thanks for the followup - glad it works.
Must admit that I was beginning to doubt myself and even got some other MVPs
to verify what I was saying
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)