0

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.

kcode
  • 1,825
  • 4
  • 19
  • 21

2 Answers2

0

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.

lg.
  • 4,649
  • 3
  • 21
  • 20
  • This should work daily and should be limited to certain tables. The replication would require an open firewall as well. – kcode Sep 28 '10 at 09:00
  • You could export the selected tables, copy (by FTP as Chris W suggested) and import into remote server. – lg. Sep 28 '10 at 10:25
0

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.

Chris W
  • 2,670
  • 1
  • 23
  • 32
  • An Enterprise edition is available. A VPN is not possible in this scenario. The data volume is relatively high, so that the web sync merge replication is not suitable as well. A one way transfer is just fine. The data should be transfered on a regular basis. – kcode Sep 28 '10 at 09:14
  • Based on what you're saying I suspect a custom IS job to bulk dump data from the required tables and FTP it may be good option. You can then have a job on the target server pick up the data from the FTP folder and do whatever processing it needs to do to import it at the target. If you're using secure FTP and ideally also IP restricted access to the FTP server then it should be a pretty secure set-up. – Chris W Sep 28 '10 at 09:43