SQL Server Performance

log shipping on SQL2K standard edition

Discussion in 'SQL Server Log Shipping' started by jrichardson, Oct 21, 2005.

  1. jrichardson New Member

    I would like to implement Log Shipping (I think) but when I started to read up on it, I found that this is available only with Enterprise Edition? Is that True?

    If that is the case, Here is my situation.. I have one production SQL Server (2000 Standard Edition) with 20 Production DBs (about 6 gb total) Most of our APPS are 3rd party apps so I maintain a Test SQL Server Instance where I can test/QA any scripts the vendor may send for fixes/enhancements/new versions.

    I use Maintenance Plans to do Full Backups nightly and transaction log backups once an hour during business hours (basically 8am - 7pm) of my User Databases and Full weekly Backups of my System Databases.

    I recently discovered that the Production DB server has not been backed up in awhile.. YIKES! I immediately changed my Backup Scheme and in addition to my local SQL Backups (above) I now do a FULL backup of ALL databases to a network Share on another Server - and I will continue to do so until I see that our tape backups of the DB Server are reliable again (maybe never)

    I guess my question is ... If I can't do log shipping with SQL Server 2000 Standard Edition.. Can anyone tell me how they would handle this situation? Ideally I want to move/restore backups from my production instance to a standby instance for Disaster Recovery
  2. satya Moderator

    You can take advantage of log shipping using Standard Edition, only the differnece is you don't have wizard to play with.

    The following links for your information:
    http://www.sql-server-performance.com/sql_server_log_shipping.asp
    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship2.mspx

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. jrichardson New Member


    Thank you for the reply. I'll definitely read the articles! I was hoping to avoid have to script out all of my Backups on the Production Server.. but it looks like I don't have a choice if I want to be able to use log shipping for DR.
  4. satya Moderator

    You can be able to simulate log shipping using scripts from Primary to secondary servers using above links.

    ALso using MS Backoffice Resource Kit you can download log shipping scripts for standard edition of SQL 2000.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. itsonlyme4 New Member


    how do i get a hold of MS Backoffice Resource kit?
  6. WingSzeto Member

    I followed the steps in the log shipping steps for SQL standard version using the link you listed and the backup and restore work on a small testing database (1 mbyte). However, when I use the same log shipping setup but change the database name from the testing one to the real one (12 Gbytes) in the stored procedure, I got the following message during the restore operation.

    Msg 7221, Sev 18: Could not relay results of procedure 'stored procedure name' from remote server 'serverA'.

    I am using SQL 2K std with sp4. How do adddress this problem?

    Any help on this is very much appreciated.

    Wing

  7. ryalli New Member

    I receive the same error relating to log shipping...has this one been figured out yet?
    Executed as user: [DOMAINADMINUSED]. Could not relay results of procedure STOREDPROCEDURENAME' from remote server 'SERVERNAME'. [SQLSTATE 42000] (Error 7221) [SQLSTATE 01000] (Error 7312). The step failed.
    I get this error when I try to run restoreDB StoredP from linked server. The servers are linked via sa account and are both sql2k sp4.

    If I run the LOG restore StoredP portion everything works fine.

    DB and LOG restores both work locally when running the StoredP locally.

    Any ideas?
  8. ryalli New Member

    Anyone???
  9. veereshnashi New Member

    Yes,
    This error occures , if u r trying to execute a stored procedure which is there in another sql server.
    I have implmented my own logshipping for multiple databases, so when doing so if you try to execute a stored procedure which is there in another server i recieved the same error. So we have to add linked server to the primary server for back up server.


    You Store, I Manage
  10. dooncomputer New Member

    I also get same error:

    Could not relay results of procedure 'dba_compress_db_backup_pccvs2' from remote server 'PCCMGMT'. [SQLSTATE 42000] (Error 7221) [SQLSTATE 01000] (Error 7312). The step failed.

    is it possible that rpc call timing out?

    Any help will be greatly appreciated.
  11. satya Moderator

    It might occur if the SQL servers on primary and secondary do not have similar level of service pack and hotfixes including on the operating system.

    If the RPC is timing out then check on windows event viewer for further information on network related issues between 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.
  12. Visgor New Member

    Not true I have both servers on the same version 8.00.2039 I start getting the same error after upgrading to SP4
  13. ryalli New Member

    I changed the timeout interval to somethign really high and it works, but why would setting the timeout to 0 casue a timeout...I wasunder the impression 0 would wait forever?
  14. satya Moderator

  15. Visgor New Member

    I ran sp_configure 'remote query time-out (s)' set interval for RPC time-out twice as default and problem went away.
    Satya, The article you are referencing to is correctly describing the cause of error. However it allegedly should be fixed in SP4
  16. satya Moderator

    Glad to hear the problem is disappeared and few times KBAs are referring it has been fixed in latest service pack, I think that for information only./

    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.
  17. Delvester New Member

    There is a 3rd party tool available from Quest Software which will allow you backup and restore you databaseslogs etc and take advantage of Log shipping setup and configuration via a wizard driven approach even if you are using STD edition of SQL Server.

    Worth checking it out. Tool is called Litespeed.

    Cheers,
  18. satya Moderator

    WHen you can perform the log shipping with BackOffice Resource Kit then why to go for third party tools. If not you can design your own log shipping solution with a scheduled backup-copy-restore procedures between the servers.

    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.
  19. ssivaprasad New Member


    Received the same error:

    Could not relay results of procedure 'db_restore' from remote server 'RMTSRVR'. [SQLSTATE 42000] (Error 7221) [SQLSTATE 01000] (Error 7312). The step failed.

    When checked on the
    sp_configure 'remote query timeout (s)' it value was 600 seconds that 10 Minutes not the default value of Zero.

    Do I need to reset this to some higher value ?

    I thought that the value of 0 (Zero) for unlimited?

    HTH
    Sivaprasad S

    ------------------
    S SIVAPRASAD
    #2970;#3007; #2970;#3007;#2997;#2986;#3007;#2992;#2970;#3006;#2980;#3021;
  20. satya Moderator

    Check the service pack level on both the SQL server and ensure both are on same level including hte hotfix.

    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.
  21. icata New Member

    I am getting the same error

    Could not relay results of procedure 'sp_executesql' from remote server 'TOSQLDMSCLS'. [SQLSTATE 42000] (Error 7221) [SQLSTATE 01000] (Error 7312).

    Both servers are

    SQL Server SP4, 8.00.2040

    Win 5.0.2195 SP4 build 2195

Share This Page