SQL Server Performance

LogShipping SQL 2005

Discussion in 'SQL Server 2005 Log Shipping' started by rayala.thulasi, Jan 5, 2009.

  1. rayala.thulasi New Member

    Dear Team,
    I configured LogShipping in SQL server 2000/2005.
    1. In SQL server 2000 the transaction files are copying to secondary server and its working fine.the secondary server canbe able use for querying purpose.
    2.In SQL 2005 after restoring the backup files from Primary to secondary the existing user getting disconnected. is there any possibilty to overcome this issue.
    Error Message: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
    This is problem for reporting server users ,could anyone help me on this.
    Thanks in Advance,
  2. Elisabeth Redei New Member

    The secondary cannot restore transaction logs if there are any users connected at the time of the restore. When you set up Log Shipping, you choose whether you want to forcibly disconnect any users that are connected when the restore job is about to run. So on your 2005 system, it sounds like you chose that option.
    So you need to schedule your restore jobs to accommodate for any reporting activities - or vice versa.
    Makes sense?
  3. rayala.thulasi New Member

    hi Elisabeth,
    Thanks for your Quick Reply..
    .while configuring secondary i choose the option disconnect to existing users. Thats why its disconnwcting the users.if i am not checking that option i am facing the problem while restoring the transactional files. The same configuration is working in sql 2000 without disconnecting the users.
    is there any possibilty without disconnceting users we can restore this files...
  4. Elisabeth Redei New Member

    But then I don't think you have yet had a a "collision" between restoring logs and users being connected.
    The only way around it is to find a balance between how often your restore job is running vs. how up-to-date data your users need. Maybe it is enough to restore at some set times every day (like early morning, lunchtime and evening)?
  5. satya Moderator

    Did you setup the SECONDARY server's database as STANDBY in SQL 2000 and 2005 servers?
    The issue about Transport-level error occurs due to a connectivity problem having hardware issues, or the network is overloaded, or the server is overloaded.
  6. rayala.thulasi New Member

    Hi Satya,
    i setup secondary server database as standby mode in both SQL 2000/2005.
  7. gurucb New Member

    This issue is at the network level."A transport-level error has occurred when sending the request to the server". When database log file is being restored on secondary all existing users are disconnected (may be internally server session for that client is removed from memory).
    On Reconnecting client still assumes it is connected to Server (which obviously is not) so it gets that error. But this comes only first time and on retrying it should connect.
    If you have any application that connects to Secodary standby database then you should catch the exception and retry connection and it would be transparent to end users.
    This did not occur in SqL Server 2000
  8. rayala.thulasi New Member

    Thanks for reply,,
    Yes the disconnecting the users is not happening in the SQL 2000. Its happeing in the SQL 2005 only.I am going to use secondaty server for Reporting purpose.
  9. Elisabeth Redei New Member

    Hi again Thulasi,
    Bottomline is; users cannot be connected to the database while the restore job is running (or vice versa, the restore job cannot run if users are connected). This is true for all versions of SQL Server and it is quite well-documeted, e.g here:
    http://msdn.microsoft.com/en-us/library/ms189572(SQL.90).aspx, "Using Secondary Servers for Query Processing":
    "You can choose to have database users disconnected when transaction log backups are being restored. If you choose this option, users will be disconnected from the database each time the log shipping restore job attempts to restore a transaction log to the secondary database. Disconnection will happen on the schedule you set for the restore job.
    You can choose not to disconnect users. In this case, the restore job cannot restore transaction log backups to the secondary database if there are users connected to that database. Transaction log backups will accumulate until there are no user connections to the database."
    You probably configured 2000 NOT to forcibly close connections so the second paragraph applies to your 2000 log shipping servers, and the first to your 2005 log shipping servers (or you have yet to have a collision on your 2000 servers.
    I have never run with the second option and personally I don't think it - in general - is a very good idea because you have no control over the latency between your primary database and your secondary database.
  10. rayala.thulasi New Member

    Hi Elisabeth,
    I hope your doing good ...
    I have one more doubt in my mind related to log shipping...
    1.Due to some Disaster recovery i restored the full backup in Primary log shipping server.after restoring the full backup in primary server the transactional files are not restoring in to the secondary server. the schedule is getting failed.do i need to reconfigure the log shipping for the particular database?
    could you please suggest me on this...
  11. satya Moderator

    You have to restore the subsequent log files backup that was performed after that full database backup was occurred. This happens due to the mismatch of LSN from the last full backp to existing in the database.

Share This Page