Notes from when i set up log shipping over the internet.. to be padded out with real deatil later.

Create vpn between servers (use remote access and routing, and create the other server as a peristant dial on demand route)

Make user say “sqlserver_agent” on both servers, with same username and same password.
Make directory on primary server eg c:\database_logs
grant access to both “sqlserver_agent” and “network service”
share path on primary server.

create log destination folder on secondary server eg “c:\database_restore_logs” make sure both “network service” and “sqlserver_agent” can read/write to it

make sql server agent on both run servers with the same account as above.

make sure database on primary server is in full mode.
back up database to share, and manually copy it across. manually restore it on the other site, but leave it in standby mode (had to do the manual copy since sqlserver.exe isnt running as the service account, and therefor couldnt access the network share where it was living on. only the agent could)

use the wizard to create the jobs on both servers that do the restore.

my wizard for some reason created the job with the wrong hostname and the sqllogship.exe command couldnt talk tot he server, so had to edit it from the wierd windows hostname that it had and change it to the ip of the host. using the correct hostname probably would have worked as we..

the user “network service” has no access over the network to do fuck all. dont use it for trying to communicate to other hosts.. i tried enabling guest, adding network service to all the shares, enabling everyone, and a whole lot of other things.. just create a service account on both servers with the same details and that will work just fine!!

used these sites for info

http://www.mssqltips.com/sqlservertip/2562/sql-server-log-shipping-to-a-different-domain-or-workgroup/

http://omaralzabir.com/how_to_setup_sql_server_2005_transaction_log_ship_on_large_database_that_really_works/