Post reply

Log Shipping Hell

  • September 27, 2004 at 10:58 am

    #524549

    More information. SQL is Enterprise Edition on both the primary and secondary server.

    I have though about this all weekend and perhaps this is the problem.

    The folder I ship the logs to is on the secondary server. I really don't think this would be a issue but who knows with Microsoft.

  • September 27, 2004 at 11:27 am

    #524553

    Ok, After re-reading all of the posts I have a few more questions.

    1. You stated that you created the 2ndary db and then set up logshipping.

    Did you allow the wizard to create the DB? or did you manually create it with a restore?

    2. Are the files making it to the 2ndary server? If yes, then from what I can tell, they aren't loading correctly (This is the main issue of course). Have you checked your server event log or the SQL logs?

    Sometimes it's better to blow it all away than to try and reverse engineer it. I have removed logshipping in the past, cleaned up all reference to it in the master.mdb and deleted the dbf on the 2ndary server. Then create the maintenance plan, allowing the Wizard to create the DB and as a Standby server. Make sure your log files are created on the primary and shipped to the secondary. Then put your monitor server on another server, ( could be 2ndary or a tertiary box) in order to view the updates. Key here is to ensure you have the servers registered on both boxes.. particularily primary with 2ndary registered.

    3. What is the last file showing as loaded?

     

    When you recreated logshipping for the 2nd time, and blew away the first instance, did you then clean up some of the tables that have the logshipping history?

    Last but not least, if you think it's a rights issue and it's a development environment, then open it wide up, then start closing things down once you have the initial config working.

    Sorry that was long winded. If that doesn't help, pick up a copy of

    Microsoft SQL SERVER 2000 HIGHAVAILABILITY by Allen Hirt with Cathan Cook, Kimberly L. Tripp and Frank McBath

    It is a deep well of information and if you look around it won't cost more than 10 bucks with all the scripts on cd.

  • September 28, 2004 at 3:35 pm

    #524710

    Okay, here is an update. Log shipping from my primary server to my secondary server is now working and apperars to be working correctly.

    what I did to get it to work.

    After doing a lot of research I found I had registered the secondary sql server incorrectly. I registered it using the name of the server like this <ServerName>. The correct way for log shipping is <ServerName.DomainName>.

    I created a folder on the primary server to hold the logs. I had created this folder on the secondary server in my other setups.

    I shared this folder and then granted permissions to the domain account, that sql is using, to this folder.

    I made the registration change, performed the clean up(according to this site) http://support.microsoft.com/default.aspx?scid=kb;en-us;298743

    I then re-ran the wizard allowing it to create the database for me(I had created it myself through a restore and left it in read only mode in my other setups).

    After the wizard completed successfully, I then waited for the time when the load would take place. I monitored through the msdb.restorehistory table.

    After the load took place, I then queried the login table in the standby database and it had the approriate data.

    I have since queried several times and all seems to be good.

    Now I need to migrate all the database logins, more joy.

    Thanks to all for the suggestions and directions.

    Gary

  • September 29, 2004 at 5:00 am

    #524762

    migrating the logins isn't difficult. Let me know if you have any problems.

  • October 1, 2004 at 10:15 am

    #525057

    It looks like I spoke too soon. I am not sure what is happening. When I look at the properties for the log shipping monitor the last backup file is correct, the last file copied is "first_file_000000000000.trn", the last file uploaded is "first_file_000000000000.trn".

    When I query the restorehistory table I see the correct information, when I query the login table in the secondary database it looks correct.

    Any Ideas?

    Thanks

    Gary

  • October 1, 2004 at 10:49 am

    #525061

    Is SQL Agent running?

  • October 1, 2004 at 11:19 am

    #525066

    Yes, SQl Agent is running on both the primary and the secondary servers.

  • October 1, 2004 at 11:51 am

    #525070

    I have a "rest-of-the-day-plus-a-half-hour" meeting in about 15 minutes so I'm sure I won't see your reply until Monday, but check the Database Maintenance Plan History for your job.  Is there any history?  I might be rehashing old stuff, but make sure the recovery model for the source database is set to simple too.

  • October 1, 2004 at 12:02 pm

    #525074

    the primary database is set to full recovery mode

     

  • October 1, 2004 at 12:09 pm

    #525077

    Sorry about that.  I meant Full.

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply