September 22, 2008 at 6:44 am
Our sql server database is remotely hosted by an ISP. We want to be able to pull the data into a local database on a scheduled basis.
We could write programs on both ends to extract, encrypt, transmit, load... but we'd rather use tools built into SQL Server.
Here's the requirements we're trying to meet:
* 30 tables / 300 mb data
* Can either drop local tables and create new each time or append - which ever is easier
* Must be secure - don't want to open ourselves up to hackers obtaining the credentials or the data.
* scheduled
I think an ssis package may be the best bet but would love to hear from those with experience in this matter. Is there a better way to go? Also, how secure is ssis across the internet?
Thanks,
Mike
Mike Kleiman
September 22, 2008 at 8:33 am
As far as security, to use SSIS for this, I would recommend a VPN tunnel. If you cannot do that, to connect to your ISP's SQL Server directly from your location you need to allow a port access from the internet that can query your data directly - this is never going to be all that secure. You could spend the time to create web services (if the ISP would allow it so you could access the data through secured web pages, but it will get a bit complicated.
If you can get the hosting company to send you backup files, log shipping is probably a better route. You do not have a lot of data, so even full backups could really be managed, but log backups sent to you regularly could work pretty well.
September 22, 2008 at 8:40 am
If you can establish a VPN to your ISP that would be ideal. It is secure.
You could use transactional or scheduled snapshot replication or SSIS.
I have used replication over a VPN and it worked well.
September 22, 2008 at 8:46 am
Michael,
We're on a shared database server so server configuration is not an option - including vpn.
We do have direct access to the database though - we currently use SQL Server Management studio to access it. My thought was to set up an SSIS job on the local server to pull down the desired tables but not sure how secure server 2 server dialog is; For instance, everytime I use sql server management studio - it's transmitting my credentials; How safe/unsafe is that? Same question regarding setting up a Pull job from local database server.
By the way - both database servers are sql server 2005.
Thanks
Mike
Mike Kleiman
September 24, 2008 at 4:36 am
SQL Server 2005 soes not have data encryption on the wire. Passwords are only weakly protected and I assume you would be using SqlServer authentication and therefore transmitting a password.
If you dont use a VPN, then you may be vulnerable to packet sniffing attacks.
See the following articles:
http://www.sqlservercentral.com/articles/Security/sqlserversecurityloginweaknesses/1095/
http://www.sqlservercentral.com/Forums/Topic253723-5-1.aspx
Apart from that you are right, SSIS is probably the simplest method of data transfer for a few tables if you dont mind a fairly high latency.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply