Log Shipping across two non-domain related networks - restore error

High Availability
1

Hi,

I have a need to setup log shipping across two networks that have no domain relationship. They are two separate work groups. I have been through every tutorial online to get this working but I am having an issue with what I believe is the permissions across the two network/servers.

The actual setting up of the log shipping is fairly simple, I understand the logic. I can connect between the two SQL servers using IP address port 1433 (I know this port is not secure I am just using it to get the log shipping working), so I can open databases from both sides no issues. So I don’t think there is any firewall / connection related issues.

I have setup new window accounts on each server called SQLSVC with the same passwords and set the SQL service Agents on both machines with these new accounts (restarted the services also). I have given full read and write access to the two directories on each server that will hold the SQL logs to these new accounts. On the primary server ‘A’ I have set the log folded I created as shared with full permissions given to the local SQL service agent. I can allocate any additional permissions I need but I have even added (the everyone) account and given that full permission just to test.

Where it is falling over is when I do the ‘Save Log Shipping Configuration’, it backs up the database on server ‘A’ ready to restore on server ‘B’ and is at the restore step I get this error.

The SQL on both servers is exactly the same version. SQL 2008 R2

What am I missing? Any help would be really great thanks for looking.

Restore backup to secondary database ….. ERROR….

TITLE: Microsoft SQL Server Management Studio

SQL Server Management Studio restore database ' testdb '

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (SqlManagerUI)

Cannot open backup device '\WIN-UC7TSJO3UEM\Log shipping\testdb.bak'. Operating system error 53(failed to retrieve text for this error. Reason: 15100).
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3201)

2

Just to be clear.
You have shares set on the log backup folders with the permission on/for both sides.
The files are created on A.
Then the files are copied from A to B.
The failure is when B tries to restore?

3

Hi,

On Server A the source database server I have a folder called Log shipping and this is a share and has permissions for the SQLSVC user I created on both server A and Server B.

The initial .bak and then .trn files are stored on Server A in the Log shipping folder.

This is where it fails... The files are not copied (I think the SQL Agent should be doing this) I believe the files should be copied to server B so that the initial .bak can be restored and then every 15 mins after that the .trn file will be copied and restored keeping Server B almost in sync with Server A.

Or is the initial .bak meant to be restored across the network from Server A to Server B.

Does that make sense?

4

even though you have the same visually identical user SQLSVC behind the scenes they each have their own UID and hence are not the same.

you should use server2\SQLSVC is that is even possible. you need to tack on the "domain " of the second server.
Are these two server even in the same ip range? Can you ping one from the other?

5

Hi yosiasz,

Thanks for your message.

The two servers are on different ip ranges but I can ping them both, server A can Ping Server B and Server B can ping server A.