April 20, 2011 by Mike Hillwig
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.