Share via

FAQ - Configuring Log Shipping in SQL Server

  • Article

Through this blog, we will try to address common customer questions on log shipping in SQL 2005.

  • Can log shipping be configured between SQL 2000 and SQL 2005?

    You cannot configure log shipping between SQL 2005 (as primary/secondary) and SQL 2000 (secondary/primary).|

    To demonstrate this, let’s try to configure log shipping as below:

o SQL Server 2000 as the source/primary server and SQL Server 2005 as the secondary/destination server.

Use the Database Maintenance Plan Wizard to configure Log Shipping. During this wizard, you will reach the “Specify the Log Shipping Destinations” where you can click on ADD button to add the servers.

Under the Server Name drop down, you will ONLY see those servers that are already registered in your Enterprise Manager. Since you cannot register a SQL 2005 server under Enterprise Manager, it will never pop-up in the drop down menu, thereby not giving you any option to setup log shipping in this scenario.

Now, if you try using SQL Server 2005 Management Studio, you still cannot configure log shipping. Register a SQL 2000 server under Management Studio and right click on the SQL 2000 database.

Here you won’t find the option “Ship Transaction Logs” under TASKS. This is by design.

o SQL Server 2005 as the source server and SQL 2000 as the standby destination server.

In your SQL Server 2005 Management Studio, right click on a SQL 2005 database and go to TASKSà Ship Transaction Logs. When you enable the database for log shipping, you can add Secondary servers. If you try to add a secondary server that is SQL 2000, it will throw the following error message:

“servername” is not a valid secondary server instance because it is not the correct version. Secondary server instances must be servers running SQL Server 2005 or later.

By design, both these scenarios are not possible out of the box in SQL Server.

However, you can always implement your own custom solutions.

  • How do I upgrade my existing SQL 2000 log shipping configuration to SQL 2005?

    You cannot directly upgrade a SQL Server 2000 log shipping configuration to SQL Server 2005. The Database Maintenance Plan Wizard, which was integral to log shipping in SQL Server 2000, is not used as part of the log shipping configuration in SQL Server 2005. As a result, log shipping stops functioning when you upgrade a server to SQL Server 2005.

    However, you can migrate a SQL Server 2000 log shipping configuration while maintaining synchronization between your primary and secondary databases. This can be done by the methods described in the BOL topic Upgrading a SQL Server 2000 Log Shipping Configuration.

  • Can I run queries against the secondary/destination server so as to reduce the load on my primary server?

    Yes, you can reduce the load on your primary server by using a secondary server for read-only query processing. To do this, the secondary database must be in STANDBY mode. You will not be able to run queries if the database is in NORECOVERY mode.

    For more information on this, refer to the BOL topic Using Secondary Servers for Query Processing.

Hope this helps.

 

UPDATE on 9th Oct, 2006

Log shipping between the SQL 2005 x86 editions and corresponding 64bit editions (x64 and IA64) is supported.

 

POSTED BY : Sanchan Saxena

Comments

  • Anonymous
    October 30, 2006
    Do you have comments on the following. I have set up log shipping between two servers with a third serving as a monitoring server. Recently the monitoring for the eight databases that I am log shipping began to indicate that everything was failing. Upon further inventigation of the log_shipping_monitor_history_detail tables on the prinary and secondary everthing appears to working properly but I have been unable to resyncronize the monitoring. I have attempted to use the stored procedure sp_refresh_log_shipping_monitor to no avail, it does not appear to do anything with respect to the data in the dbo.log_shipping_primary_databases and log_shipping_monitor_primary tables on the primary server or the log_shipping_monitor_secondary and dbo.log_shipping_secondary_databases tables on the secondary. I have also manually updated the records in these tables in an attempt to syncronize but after the next sucessful backup, copy and restore this monitoring data is not updating. Does anyone have any ideas as to what I am doing wrong or how I might rectify this situation.

  • Anonymous
    January 19, 2007
    It's a very good article.. useful.

  • Anonymous
    July 24, 2007
    Well erick , i feel you should check the security or share acess to your source Folder .

  • Anonymous
    July 26, 2007
    Okae ....the procedure will appear to "work" but the .bak files will i feel  have the wrong names. I sugesst you to kindly check the backup file name convention !! or u check the users rights like does it copying to your Net share folder !!

  • Anonymous
    May 12, 2015
    if i want do log shipping in cross main then what precaution taken ?