Setting up Log Shipping was a very eask task for me till few weeks back, when I was asked to setup a DR server. We zeroed in on Log Shipping as the ideal method of data transfer for DR. It was when I really started to implement it, I found the hard truth! The servers are in two different domains. The production server is maintained by a third party company and hence in their domain and the DR is at our data centre which is a seperate domain.
The idea was to have the DR in our data centre so even if there is an issue at the third party site we could manage the business using the DR at our end.
Now can we setup Log Shipping between different domains?
Yes, as per this KB Article. The two different methods which is suggested in the KB article couldnt be implemented in my situation. Hence I decided to play around with it.
As you would setup any normal Log Shipping scenario, I also setup Log Shipping. Now as the SQL Server Agent Service account at the secondary site doesnt have enough permissions to access the network share in the Primary site it failed with the below error.
Now to get over the error, I need to give SQL Server Agent account permissions to the share which was not possible. Another option was to use proxy but we have the same problem with the proxy as well.
However, I had access the share in the Primary server from secondary server using the network path \\PrimaryServer\logshipping folder. I have a local windows account through which I am able to access the shared folder.
Now I copied the job step which does the copy operation at the secondary server, and then created a batch file in the secondary server like this below
net use j: /Delete & net use j: \\PrimaryServer\log_shipping PASSWORD /user:Primaryserver\Username && "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe" -Copy 0A923E9A-7345-4FFB-BB1F-B909FEDD9247 -server SecondaryServer\Instance
So what I have done in the above script is to first make sure that the drive J: is not used up and if then delete the share, then assign J drive to the logshipping shared path using the windows account and password, and then the copy job step ("C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe" -Copy 0A923E9A-7345-4FFB-BB1F-B909FEDD9247 -server SecondaryServer\Instance)
Now I added another step in the Log Shipping Copy Job and then just gave the path and name of the batch so that it executes the batch. Now I changed the job to start at this new step and hence the old step (original) will not be used.
This way I was able to give the sqllogship.exe access the network path and copy over the files.
Happy Log Shipping !!