Lately I suffered some headache trying to set up log shipping on a couple of Sql Server 2008 machines.
In my farm, the “second” server arrived when the first was already running, responding to a high traffic website. And the two servers are not in the same domain – actually they are not in any domain.
I won’t enter in detail on how to set up log shipping, follow the wizard, it’s quite easy (to reach the wizard: right click on a db, choose Tasks, then “Ship transaction logs”).
When you set up log shipping, and the servers are in the same domain, you have little problems: you need to create a couple of file share on the two servers, and give read permissions to accounts running the Sql Server Agent service “on the other” server. But that is quite easy, follow instructions and it’s done.
A different story is when the shipping server don’t know anything of the “shipped” one. I tried different configuration, but I always end in “Access denied” errors.
At last I found this answer on Serverfalult.com, and that was the path to follow. The answer is about Sql 2005, but the same works on sql 2008.
In brief, here’s what I did:
- created an account on the two servers, with exactly the SAME NAME and the SAME PASSWORD, and put the user in Administrators group
- changed identity (“log on” tab) of both Sql Server Agent AND Sql Server services (only Agent did not suffice) on both servers
- gave read permission on the share used in the log shipping configuration
Done all that, when I ran the log shipping job they started working immediately.
Now I’m not a windows authentication guru, but all this looks a little crazy to me… but hey, who cares! Now log shipping works… :-)
