SQL Server Performance

Large Number of databases and log shipping

Discussion in 'SQL Server Log Shipping' started by stevem123, Feb 11, 2004.

  1. stevem123 New Member

    We currently have several hundred SQL database for several hundred customers on a clustered pair of SQL 2000 Enterprise servers.

    We currently do full backups nightly and transactional backups every hour and move these backups offsite.

    We would like to setup a warm standby server as well. From what I can see from the log shipping docs available on this site and info from MS, it appears that the log shipping feature built into the Enterprise Edition of SQL needs to have each database configured for log shipping individually. This would not work for us as we continuously add new customers and databases and need a solution that will automatically take on new databases.

    So our basic plan is to:

    1. Everytime backups are done generate the necessary SQL scripts to restore the backups

    I use a modified version of the script at


    [their original one had one flaw in regards to restores transactional backups...not sure if they fixed it yet]

    2. Send backups and restore script(s) to the warm standby server

    3. Restore the backups on the standby server with the script

    We would need to generate some checks along the way to be sure that all the steps are completeing properly and to be able to monitor that the all the data is making it over.

    There may be some third party solutions out there as well, but having looked at some in the past prior to going with a cluster solution I am not sure that they are a good choice for so many databases and continuous adding of them that we do.

    Anyone have any thoughts or suggestions for using some sort of log shipping in this scenario?

    Thank you,
  2. satya Moderator

    Here is a KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;314515 which enlights the FAQs about Log shipping.

    And I think SQLLiteSpeed does have a log shipping tool and easy way to monitor, as same as log shipping monitor available with SQL server. SQL Server 2000 log shipping installs a monitoring utility on a monitor server, preferably a third server.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page