Exploring my Options


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.

One thought on “Exploring my Options

  1. This is so true– the solution needs to fit the situation and be maintainable.

    This solution has a great bonus, too: you’re automatically testing each of your backups!

Leave a Reply

Your email address will not be published. Required fields are marked *