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.