Log Shipping Stops Working When You Failover

Add a Second Node to a SQL Cluster Installs Tools to C Drive

This was the most annoying problem.  I created a SQL 2008 cluster which was all working fine including failovers until I configured log shipping.  Everything else was ok but when I failed over the log shipping jobs all failed.  It turns out that the Log Shipping files are stored in the same location as the shared tools.  If you install your shared tools to a drive other than the C drive (as most folk do) then this causes a problem because the log shipping jobs use a hard coded path in the job step.  So if you have installed the shared tools to say the D drive on your main cluster install; when you add the second node you don't get the opportunity to select a drive to install the tools to, the SQL install just chooses the default location which is the C drive.  So you end up with the following configuration:

Node 1 - D:\Program Files\Microsoft SQL Server\120\Tools\Binn\sqllogship.exe
Node 2 - C:\Program Files\Microsoft SQL Server\120\Tools\Binn\sqllogship.exe

So if Node 1 was the active node when you setup log shipping the path will be pointing to D, if it was Node 2 it will be pointing to C.

So if I setup log shipping while Node 1 is active my log ship jobs will have the path pointing to D.  I test the jobs and they succeed because the log shipping files on this node reside on the D drive.  Now I failover to Node 2 and test the jobs again and they fail because on Node 2 the log shipping files reside on the C drive and the job step is pointing to the D drive.  Fail back over to Node 1 and test the jobs again and they succeed.

To stop your jobs from failing you can simply remove the path from the log ship jobs.  If no path is entered SQL Server will uses the Windows environment variables to find out where the file is:

If you choose to do this don't forget that if you ever need to reinstall log shipping you will have to remember to go back into the log shipping jobs and remove the path again. 

For a better and more resilient solution you need to redo the secondary node as follows:

1. Make sure everything has been failed over to the primary node (ie the one that has the shared tools installed to a drive other than C eg D)
2. Log onto the secondary node
3. Using management studio remove the secondary node from the cluster
4. Uninstall SQL server
5. Reboot the node
6. At this point I'd do a tidy up - uninstall often leaves empty folders behind
7. If I've had to remove any left over folders I always do a reboot
8. From management studio do a Tools Only install and make sure you select the same drive to install the tools to that you did on the primary node eg D
9. Once you have your shared tools installed you can now use the Add a Node to a Cluster to install the secondary node
10. Since the shared tools have already been installed to the drive of your choice the log shipping files will be put in this location when the secondary node install is done.
11. Setup log shipping and if you check the log ship jobs from both nodes they should have the path mapped to the same drive.
12. Test the log ship jobs on both nodes and they should succeed.