Exploring my Options

1

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.

  • 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!