Home / server / Questions / 185310
In Process
kcode
Asked: 2010-09-29 00:24:25 +0800 CST

Copy SQL Server data from one location to another over the internet

  • 772

How can I copy certain SQL server data from one server to another without opening direct access to SQL server in the firewall?

I thought of things like integration services in combination with an receiver webservice etc. But I don't know how practical this is.

firewall sql-server-2008 security internet

2 Answers

  1. If you have to to one time copy, you can do it With backup and restore of database.

    If you want a mirror of database, I think you have to use replication.

    • 0

  2. Depending on the edition you've got there's a few things you can look at.

    If you can use a VPN connection between the two servers then replication or log shipping are good options. Or maybe just some manually scripted backup/restore jobs or IS packages as you've suggested.

    You say you're on 2008 so Web Sync Merge Replication could be another option for you if you can't use VPN. In the past I've had an IS job sending data to another server via SFTP which was clunky but worked.

    It really depends exactly what you want to do with the data and what latency you're after as to which is the best option.

    • 0