SQL Server Performance Forum – Threads Archive
log_shipping_primaries not being updated
Greetings, I am running SQL Server 2000, SP4 on Windows 2003 server. I have configured log shipping and it is working. I have verified that by seeing that changes are being propagated to the standby database. The problem is that the msdb.log_shipping_primaries table on the standby is not being updated. I have verified this by looking at the table and the log shipping monitor, which is on the standby server. The monitor shows the last backup file as being first_file_000000000000.trn. The msdb.log_shipping_secondaries table on the standby server is being updated and the monitor displays the correct files for last file copied and last file loaded. KB article 292586 speaks of incorrect permissions on the msdb.log_shipping_primaries table causing this problem, saying the SQL Server Startup account on the primary server needs Update and Select privileges on this table in order for this to work correctly. This appears to be the problem but I don’t know how to fix it. The account which starts the SQLSERVERAGENT is the account I believe that is being referred to. It is the same account on each server but they are local accounts rather than domain accounts. This account has select & update privileges on both the msdb.log_shipping_primaries and msdb.log_shipping_secondaries table on the standby so I don’t know what I am missing. If anyone can help me out I would be most appreciative. Thank you.Hi, The problem, as you pointed out, is permissions related. You say you are using the same account on each server but then mention that they are local accounts. By using local accounts the accounts cannot be the same and this is where the problem is. You need to give the account on the primary server access to the monitoring server and to do this you need to use a domain account. A local account on the secondary (even if it has the same name as the local account on the primary) is still a different account. Hope that helps,
Karl Grambow www.sqldbcontrol.com
Karl, yes it does help but adds to the confusion a bit. I am under the impression that both the log_shipping_primaries and the log_shipping_secondaries tables are updated by the user running the job on the primary server. The log_shipping_secondaries table is being updated correctly on the standby server. Am I not understanding this correctly? Thanks.
This differs when you use different (same name) account between primary & secondary servers, but in case of a domain account that is assigned to SQL SErver services then it should have proper privileges to carryout the mentioned tasks. It works for me when 2 accounts used between the servers with same privileges on both sides. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
Satya, As I mentioned these accounts are local accounts, not domwain accounts. At the operating system level both accounts are set up the same way, all properties are the same. On the standby server the account has been grantes select and update on both the log_shipping_primaries and the log_shipping_secondaries tables but on the primary it has not been granted these privileges on either of these tables. Do have have suggestions as to what additional privileges I could look at? Thank you.
REfer this KBAhttp://support.microsoft.com/kb/292586/ for relevant information and one of the Technet article refers:
quote:
Log Shipping
You should use Windows Authentication when you are using log shipping. If Mixed Mode must be used, however, the installation of log shipping creates the user log_shipping_monitor_probe. The Primary and Secondary servers use this account to update the log_shipping_primaries and log_shipping_secondaries tables when a transaction log backup, copy, or restore operation occurs. Changing the role of a Secondary server to be a Primary server requires that the login accounts that existed on the original Primary server be in place at the Secondary server when it assumes Primary server role responsibilities. The Log Shipping Process does not automatically ship the login accounts (however, it does ship the users in a database). Clients attempting to access the new Primary server will be denied access unless the login accounts have been copied over and their SIDs resolved to the UIDs of the database users.
Log Shipping
You should use Windows Authentication when you are using log shipping. If Mixed Mode must be used, however, the installation of log shipping creates the user log_shipping_monitor_probe. The Primary and Secondary servers use this account to update the log_shipping_primaries and log_shipping_secondaries tables when a transaction log backup, copy, or restore operation occurs. Changing the role of a Secondary server to be a Primary server requires that the login accounts that existed on the original Primary server be in place at the Secondary server when it assumes Primary server role responsibilities. The Log Shipping Process does not automatically ship the login accounts (however, it does ship the users in a database). Clients attempting to access the new Primary server will be denied access unless the login accounts have been copied over and their SIDs resolved to the UIDs of the database users.
Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
Satya, I had found the KB article you refer to and it was in part from that I learned that the two tables in question were not being updated. When I configured log shipping I specified windows authentication in the maintenance plan. This is where I begin to get lost and am not clear about what to look for. One thing I find is that when I invoke EM on the standby server and go to management -> Log Shipping Monitor, right click on the log shipping pair and choose View Copy Restoer history I am presented with a dialogu box which says the server <name> must be registered, Register it now? I am however able to see the primary database through EM while logged in to the standby server. However, on the Primary server I can see the log shipping monitor, it is on the standby server, and view all the pieces. I’m not sure what that indicated, can you help? Thank you.
Make sure that your primary or source server name is correctly set at monitor on msdb — logshipping_primaries. I got the same problem some time ago .. that was my solution. Denis Denis Garcia
IT
Cell:+55 11 9423 7053
quote:Originally posted by wjwagman
Greetings, I am running SQL Server 2000, SP4 on Windows 2003 server. I have configured log shipping and it is working. I have verified that by seeing that changes are being propagated to the standby database. The problem is that the msdb.log_shipping_primaries table on the standby is not being updated. I have verified this by looking at the table and the log shipping monitor, which is on the standby server. The monitor shows the last backup file as being first_file_000000000000.trn. The msdb.log_shipping_secondaries table on the standby server is being updated and the monitor displays the correct files for last file copied and last file loaded. KB article 292586 speaks of incorrect permissions on the msdb.log_shipping_primaries table causing this problem, saying the SQL Server Startup account on the primary server needs Update and Select privileges on this table in order for this to work correctly. This appears to be the problem but I don’t know how to fix it. The account which starts the SQLSERVERAGENT is the account I believe that is being referred to. It is the same account on each server but they are local accounts rather than domain accounts. This account has select & update privileges on both the msdb.log_shipping_primaries and msdb.log_shipping_secondaries table on the standby so I don’t know what I am missing. If anyone can help me out I would be most appreciative. Thank you.
Anita
I would suggest to open a new thread in this case for clear solution rather than hijacking a thread. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>