SQL Server Performance Forum – Threads Archive

Log Shipping Configuration

Hello. I’m having difficulty getting log shipping to work using two servers running Windows 2003 Server Sp1 w Sql Server 2000 SP3. Here’s what I’m looking at: Server A is configured as the primary source. I have a folder named R:LogShip_Source to deposit the transaction log backups and it is shared as \ServerALogShip_Source with share permissions for Everyone/Full Control. Server B is configured as the destination and the monitor. I have a folder named C:LogShip_Destination which is where ServerB will place the TRN files after a copy and restore operation. On ServerB, I performed a standby restore of the database that I want to use for log shipping. The databases are named the same. I ran the Database Maintenance Plan on ServerA’s Enterprise Manager, which has ServerB registered as well. I configured the log backups and the copy/restore job to occur every 5 minutes. The destination node was configured using the folder listed above and I chose to use the existing database that I previously restored in standby mode. I also checked the option to have ServerB assume the primary role in the future. The Wizard completes successfully. However, after the initial log copy, I do not see any more copy/restores done. I get Out of Sync errors after 20 minutes. What am I missing here? I followed the step by step instructions according to Microsoft and this thing doesn’t work. I noticed that I don’t see a job on ServerB to perform the copy/restore operation. I see only the transaction log backup job on ServerA. Please help. Thanks.

I’ve moved to Log Shipping forum. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Hi, it’s tough to say where things have gone wrong. It could have been one of the steps you performed with the Wizard or it could just be the Wizard that has messed up for you (has been known to happen). On the secondary server, do a select * from msdb.dbo.log_shipping_plans. If the table is empty or you don’t see the plan you just created then something went wrong along the way. I would suggest deleting the log shipping plan you set up and try again – that sometimes works. If it doesn’t work, delete the plan and try and set up log shipping using the stored procedures – works all the time <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Here’s a step by step for using the sprocs. All of these sprocs are documented in BOL so you might want to check them there. Hope this helps. FIRST PART – TAKES PLACE ON PRIMARY SERVER 1)Set up a transaction log backup job on the primary server. This can be done through Enterprise Manager without any problems. 2)Change the owner of the tran log backup job (created in step 1) to sa. 3)Obtain the plan_id for the tran log backup maintenance plan from msdb..sysdbmaintplans. 4)Specify that the database is going to be log shipped, using the plan_id obtained in step 3, above: EXEC msdb..sp_add_log_shipping_database @db_name = ‘<db name>’, @maintenance_plan_id = ‘&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />lan id obtained in step 3>' 5)Define the log shipping monitor by executing the following procedure: EXEC msdb..sp_define_log_shipping_monitor @monitor_name = ‘<name of log shipping monitor server’, @logon_type = 1, — 1=NT Logon, 2=SQL Logon @delete_existing = 1 THE SECONDARY COMPONENT: (all of this takes place on the secondary server) 6)Create a log shipping plan with the following procedure: EXEC msdb..sp_add_log_shipping_plan @plan_name = ‘<enter the name you want to call this plan>’, @description = null, @source_server = ‘<name of source server>’, @source_dir = ‘<the UNC path of the source directory>’, –enter UNC path @destination_dir = ‘<destination path>’, @history_retention_period = 2880, –the number of minutes to retain history (2880 = 48 hours) @file_retention_period = 2880, — the number of minutes to retain files (2880 = 48 hours) @copy_frequency = 5, @restore_frequency = 5 7)Obtain the plan_id for the above log shipping plan from msdb..log_shipping_plans. <img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />Define a log shipping plan database by executing the following procedure, using the plan_id obtained in step 7, above: EXEC msdb..sp_add_log_shipping_plan_database @plan_id = ‘&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />lan id obtained in step 7 above>’, @plan_name = ‘<the name of the plan as entered in step 6>’, @source_database = ‘<name of source database>’, @destination_database = ‘<name of destination database>’, @load_delay = 1, @load_all = 1, @copy_enabled = 1, @load_enabled = 1, @recover_db = 0, @terminate_users = 1 9)Change the owner of the copy and restore jobs (created by the above step) to sa. 10)Define the log shipping monitor by executing the following procedure: EXEC msdb..sp_define_log_shipping_monitor @monitor_name = ‘<name of monitoring server>’, — 1must be the same monitoring server as defined on the primary server, in step 4 @logon_type = 1, — 1=NT Logon, 2=SQL Logon @delete_existing = 1 THE MONITORING COMPONENT: (all of this takes place on the monitoring server as defined in step 4 and 8, above) 11)Define the log_shipping_primary server using the following procedure: EXEC msdb..sp_add_log_shipping_primary @primary_server_name = ‘<name of the primary server>’, @primary_database_name = ‘<name of primary database name>’, @maintenance_plan_id = ‘&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />lan id>’, — plan_id for the tran log maintenance plan obtained in step 3 @backup_threshold = 30, –minutes @threshold_alert = <error number of an alert that you have create>, –error number for RAISERROR @threshold_alert_enabled = 1 12)Obtain the primary_id, created above, from msdb..log_shipping_primaries. 13)Define the log_shipping_secondary using the following procedure, using the primary_id obtained from step 12: EXEC msdb..sp_add_log_shipping_secondary @primary_id = 576, — primary_id obtained from step 12 @secondary_server_name = ‘<secondary server name>’, @secondary_database_name = ‘<secondary database name’, @secondary_plan_id = ‘&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />lan id>’, –the plan_id from msdb..log_shipping_plans on the secondary server obtained in step 8, above @copy_enabled = 1, @load_enabled = 1, @out_of_sync_threshold = 45, –minutes @threshold_alert = <error number of an alert that you have created>, –error number for RAISERROR @threshold_alert_enabled = 1 Karl Grambow www.sqldbcontrol.com

ENsure SQLAgent is started on both the machines and their startup account has required privileges.
Referhttp://www.sql-server-performance.com/sql_server_log_shipping.asp for more information and check anything is missed. Satya SKJ
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 saw that the SQL Agent is disabled on the secondary server, but I thought this was the effect of the Wizard? Thanks.

Karl, I will try configuring the log shipping using the SP’s. let’s see if that works. Thanks.

SQLAgent must be in START state in order to perform the log shipping tasks, SQL will never attempt to stop the services until unless it is accompanied by any statement from application. So you must check the SQL error log or SQLAgent log for any reference. It is better to enable ‘AUTO_START if stopped unexpectedly’for SQLagent by all means. Satya SKJ
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, the SQLAgents are running on both servers. I believe i’ve narrowed down the problem to the SQL Job that performs the log restore on the secondary server. This job is part of the maintenance plan and it fails everytime it executes. I run the SQL Agent under a special NT domain account. Does this domain account need full access to all databases on the secondary server? Thanks.

Change the job so that it runs as the SA account. I can’t really think of a reason why you’d want to it to run under another account. You have to remember though, that once the jobs are running under the SA account, the copy job will assume the permissions of the service account for SQL Server agent so you have to make sure that the service account is a domain account with directory permissions to the source folder on the primary server. Otherwise the copy job will fail. On another note, check out the msdb..log_shipping_plan_history table on the secondary server info for details on the error. Hope that helps, Karl Grambow www.sqldbcontrol.com

One detail that i forgot to add to my setup is that the source node is actually a 2 node SQL cluster, so it has a shared computer name. The other node is just a single server that I’d like to send logs to. Do you guys think this would have anything to do with the problem I’m having? Thanks

quote:Originally posted by carlitosway One detail that i forgot to add to my setup is that the source node is actually a 2 node SQL cluster, so it has a shared computer name. The other node is just a single server that I’d like to send logs to. Do you guys think this would have anything to do with the problem I’m having? Thanks

That shouldn’t make a difference. Can you do a select * from msdb..log_shipping_history table on the secondary server and post the results of any error rows in that table? Karl Grambow www.sqldbcontrol.com

I run the SQL Agent under a special NT domain account. Does this domain account need full access to all databases on the secondary server?
Establish security to all the servers. The Windows account you use to set up log shipping must have SQL Server systems administrator (sa) privileges on all the servers.
Satya SKJ
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.

I ran the query on the history table and for some reason, the only TRN file that’s being listed is the first_file_000000000000.trn. It looks like the copies from the primary to the secondary just aren’t happening. I don’t see any of the copied logs on the secondary server folder that I specified to receive the logs from the primary.

Ok, sounds like it could be one of a couple of problems. My guess is that it’s either a permissions problem or the source directory is not correct. On the seconday server, run the following query: select * from msdb..log_shipping_plans Find the log shipping plan and then look at the source_dir column. This should be a UNC path (e.g. "\primary_server…path") If it is not a UNC path then you can either update the column directly and set it the correct UNC path or better still, setup log shipping from scratch. The reason I suggest setting up log shipping from scratch is that I cannot honestly remember if there are other references to the source directory in other tables. When you set up log shipping, on the screen after you specify the transaction log backups, you will be asked to specify the primary source path – this should be a UNC path, not a local path. If all of the above is already setup correctly and you’re still getting errors then it’s possibly a permissions problem. Check the account that is running the SQL Server services on the secondary server. The services on the seconday server should be a domain account that has permissions to read from the above specified UNC path on the primary server. Typically though, it helps if both machines are running under the same domain account. Let me know if this helps, Karl Grambow www.sqldbcontrol.com

I changed the account to a domain account and now the copies are working fine but the restore jobs fail. The database on the secondary server is in read only standby mode and I cannot enable the domain account to have access to it. I changed the restore job to run under the SA account and it still fails. Thanks, looks like we’re getting close.

I’m looking at the copy /restore history on the secondary server and i see failures for the restore. it is still trying to restore the first file and it reports and error 4305.

Ok, well i made more user account changes. The restore job now works, but now the backup job fails. By default the Maintenance wizard creates 5 jobs on the secondary server. It creates two restore jobs a copy alert job and a backup log alert job. The backup log alert job was successful after the first few job runs, but then started to fail. Perhaps this job needs to coordinate with the time settings and thresholds? This is so frustrating <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

looking at the windows event viewer and i get this error: Error: 14420, Severity: 16, State: 1
The log shipping source (SERVERNAME) has not backed up for 26 minutes. But i’m looking at the source log directory and i see new log backups coming in.

Ok so let me get this right. The backup, copy and restore jobs are all working now but the log shipping monitoring is incorrectly reporting that the backup jobs are failing? Is that right? It sounds again like it could be a permissions problem. Basically, every time the backup job runs on the primary server it will attempt to update the log_shipping_primaries table on the secondary server. We’ve gone through this already but just check that the account that is running SQL Server on both machines is the same and a sysadmin on both machines. Also, make sure that the backup job on the primary is running as sa – this is to ensure that it runs under the security context of the service account. If none of that works, run the following on the secondary server: select * from msdb..log_shipping_primaries Check the last column, source_directory and make sure that it’s a UNC path pointing to the primary server’s source log backup path. As for the 4 jobs on the secondary server. Have you deleted log shipping at any time? If so, sometimes it fails to delete the corresponding log shipping jobs on the secondary server and this is perhaps why you’re seeing 2 copy jobs and 2 restore jobs. That’s the only thing I can think of. If you look in the msdb..log_shipping_plans table, there’s a column for the copy_job_id and a column for the load_job_id. These point to the job_id in msdb..sysjobs. I’m assuming there’s only one plan in the log_shipping_plans table: Just find the 2 jobs for the corresponding copy_job_id and load_job_id and then delete (through Enterprise Manager) the two OTHER jobs. Looks like we’re getting there – almost. Karl Grambow www.sqldbcontrol.com

Karl, Just to clarify, after running the wizard it creates the following jobs on the secondary server: 1. Log Shipping Alert Job – Backup
2. Log Shipping Alert Job – Restore
3. Log Shipping copy for (PRIMARY SERVER).DB_logshipping
4. Log Shipping restore for (PRIMARY SERVER).DB_logshipping Here you can fill in PRIMARY SERVER with the server name of the source. and DB is the name of the database participating in log shipping. Once I fixed the security logins, I was getting the logs to finally copy over to the secondary system and it would do its restore with no problems. The jobs all run well without failing for about 10 minutes. Then all of a sudden the Log Shipping Alert Job – Backup fails. I believe it does so after the Backup Alert threshold kicks off. It would keep reporting the error: Error: 14420, Severity: 16, State: 1
The log shipping source (SERVERNAME) has not backed up for X minutes. However, I do see that the logs are being copied over to the second machine. I’m running the first two jobs under the local SA account for the secondary server, and the other two jobs I run them under the domain account since I believe it needs to connect to the other system. So, that’s where I’m at now. I appreciate your help -C

Hi, ok so we’ve got log shipping working it seems but we’re getting alerts. The "Log Shipping Alert Job – Backup" job just checks the local msdb..log_shipping_primaries table and checks the last_updated column so the problem is unlikely to be with this job itself. Do a select * from msdb..log_shipping_primaries on the secondary server and check to see if the table is getting updated . A new record should be created in this table for every log backup that runs on the primary server. If there isn’t, then you know the problem lies with the primary server and it’s inability to write to this table. On the Primary server, make sure the backup job is running under sa. I know that it would seem that running it under the domain account should work but I remember there been some kind of problem with this. If you run it under sa then it will use whatever account the SQL Server services have been set up with. The two log shipping alert jobs you mention don’t actually connect to the primary server so it shouldn’t matter what account they are running under but for completness I would set them to run under the sa account anyway. Let’s see if that helps. Finger crossed <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by carlitosway Karl, Just to clarify, after running the wizard it creates the following jobs on the secondary server: 1. Log Shipping Alert Job – Backup 2. Log Shipping Alert Job – Restore 3. Log Shipping copy for (PRIMARY SERVER).DB_logshipping 4. Log Shipping restore for (PRIMARY SERVER).DB_logshipping Here you can fill in PRIMARY SERVER with the server name of the source. and DB is the name of the database participating in log shipping. Once I fixed the security logins, I was getting the logs to finally copy over to the secondary system and it would do its restore with no problems. The jobs all run well without failing for about 10 minutes. Then all of a sudden the Log Shipping Alert Job – Backup fails. I believe it does so after the Backup Alert threshold kicks off. It would keep reporting the error: Error: 14420, Severity: 16, State: 1 The log shipping source (SERVERNAME) has not backed up for X minutes. However, I do see that the logs are being copied over to the second machine. I’m running the first two jobs under the local SA account for the secondary server, and the other two jobs I run them under the domain account since I believe it needs to connect to the other system. So, that’s where I’m at now. I appreciate your help -C <hr height="1" noshade id="quote"></blockquote id="quote"> Karl Grambow www.sqldbcontrol.com

Karl, I made the changes and the backup job is now running under the sa account. However, it is still not updating the msdb table on the secondary server. On the primary and secondary servers I run the SQL Server service under the domain account. On the secondary server, I’ve granted this domain account SA priveleges as well as access to the msdb database. Also, the log shipping monitor status does not update. It’s stuck on the first transaction file copy. I’m assuming it’s due to the same problem?

Not really sure what to suggest next. It definately seems to be a problem with the primary server not being able to talk to the monitoring server and typically that is a permissions thing. The only thing I can think of is to double-check that the primary server can connect to the secondary server using the service account. To do this try doing the following from your QA window on the primary server: exec master..xp_cmdshell ‘osql -E -S <enter name of monitoring server> -Q "select IS_SRVROLEMEMBER(”sysadmin”,suser_sname()) IsSysadmin"’ If it works you will get a return value of 1. If you don’t then you’ll know that the service account running SQL Server on the primary server does not have access to the monitoring server with sysadmin privileges. The only other thing you might want to check is the contents of the msdb..log_shipping_monitor table on the primary server. Make sure that the name of the monitor server is correct and that it is using NT Authentication (logon_type=1). In fact, it probably would not hurt to re-specify the monitoring server. On the primary server run the following: EXEC msdb..sp_define_log_shipping_monitor
@monitor_name = ‘<name of monitoring server>’,
@logon_type = 1, — 1=NT Logon, 2=SQL Logon
@delete_existing = 1 If that still does not help your situation then you could try running the above command again but use a logon_type of 2 instead of 1, in which case you’ll need to specify the @password parameter for the log_shipping_monitor_probe login. The above sproc is fully-documented in BOL. The log_shipping_monitor_probe login should have been created as part of log shipping (assuming you’re running a mixed authentication environment). I’ve never really used it because I’ve mostly worked in NT authentication environments but I assume you might want to set its password on both the primary and secondary servers. I’m sure there’s some documentation on it in msdn. If none if this works I’m afraid I’m pretty much out of all other ideas. Let me know what you find. Karl Grambow www.sqldbcontrol.com

Karl, I did not configure the linked servers, can this be the cause? Is it required for log shipping? -C

Hi, no, linked servers are not required for log shipping. Karl Grambow www.sqldbcontrol.com

Karl, On the monitor server, I granted select and update priveleges to the domain account explicitly on the msdb log shipping primaries and secondary tables. This fixed the problem, however now the Log Shipping (PRIMARY SERVER) Restore job fails. Recall that the wizard creates two restore jobs, one on the secondary and another pointing to the primary. It’s running under the domain account and it still failed. Fixed one problem, but introduced another <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> I tried running the Job’s T-SQL code manually from QA and tt fails with this message. (12 row(s) affected) Server: Msg 22029, Level 16, State 1, Line 0 sqlmaint.exe failed. -C

Hi, I’m confused. The wizard should only create one restore job, not two – and the restore job should be on the secondary server, not the primary. Either way, I just picked up on something you mentioned. The domain account should be a member of the sysadmin server role on both servers, which will then remove the need to explicitly specify permissions. Karl Grambow www.sqldbcontrol.com

Karl, Sorry yes the wizard creates a restore job and a restore alert job, both on the secondary. The restore job is the one that’s failing now. The domain account has the sysadmin role on both servers. Still no luck :0( This is a nightmare.

Indeed, this is proving more troublesome than it should be. Check the msdb..log_shipping_plan_history table on the secondary server and see if you can find a reason for the restore job failing. Karl Grambow www.sqldbcontrol.com

Karl, I’ve decided to just start the configuration from scratch, and configure a new domain account to run the log shipping jobs under. I added the SA roles to this new account and now log shipping works! No errors, finally. Thanks so much for your help, great job. -C

That’s great news! I’m glad you got it fixed <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Karl Grambow www.sqldbcontrol.com

Hi I am getting similar problem My full back up on primary is running once a day at 3 AM and log runs every hour.
on the log shipping monitor
the source bacup failure alert is set to 180 min (is it correct). the copy and restore job works fine and also log gets restore on the db. what could be wrong? please help Thanks Thanks!!

]]>