I’ve been presented with a rather unique problem. And the right solution had left me a little perplexed.

My company has a SQL server at a business partner’s manufacturing site. The server is in what our network guys would consider a less than secure environment. They’re in the middle of setting up a permanent VPN tunnel, but they’re being incredibly tight with what traffic they let through.

The parameters in which I have to work are pretty specific:

• Users need to run reports against data that’s on the remote server.
• The data does not need to be real-time. Reports only run once a day
• Any connections must originate on our side of the tunnel, not the remote
• The solution needs to be fairly bullet-proof because we may not have a full-time SQL DBA in the future
• The remote database is small, less than 2GB
• The remote server is running SQL Server 2005 Express
• The remote server is not on our AD domain
• The remote site’s server will most likely be eliminated when we migrate to our new ERP system

I’ve looked at several options.

• Mirroring is out because both need to be online at the same time.
• Log shipping is out because of the complexity and potential lack of DBA.
• Clustering won’t work without shared disk, nor will it work over a VPN

It finally hit me that I was making this much harder than it needed to be. a simple backup and restore will completely fit the bill. Once a day, we will back up the remote server (assuming it’s not being backed up now) and then have the server on my side initiate the connection for a restore. Hopefully we can get the remote server on the domain to simplify authentication.

It’s potentially the simplest solution and the easiest one for a non-DBA to fix when something goes bump in the night.