| Author |
Topic |
|
holland07
Starting Member
7 Posts |
 Posted - 2003-04-23 : 14:00:32
|
| I am trying to copy data from one server to another, the servers are not on the same network, and it needs to be a secure transfer due to data sensitivity. I don't want to transfer all the tables, just a subset, so I thought maybe there would be someway to progamatticly do it using Export/Import run through stored procedures, but I don't know where to start with that.What would be th best way to do this? |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-04-23 : 14:09:43
|
| without being facetous.....(or maybe just a litle bit...)you need to clarify how often you want to perform this operation....and whether or not it is to be done one/two directions.will the subset be used to generate updates back to the main database, etc...or just for offline reporting.i'm sure there are other questions you need to resolve....but i know you'll get some good (other) advice here....and going back to my tounge-in-cheek starter....if the answer to question #1 is once only...or once a year...would a diskette carried from one machine to another not be the most secure solution of all??sometimes simple solutions are all that is required.I hope you get a decent solution to your problem....and smile when you get it! Good evening! |
 |
|
|
holland07
Starting Member
7 Posts |
Posted - 2003-04-23 : 14:40:28
|
| I'm sorry, I should have been specific. I would need to perform this transfer once a day, evening.The data is only being transferred one direction. This "other" database is for read-only purposes. And this needs to be anautomatic function, no human intervention, with it running everyday.Let me know if I need to give any other information.Thanks. |
|
|
|
PiecesOfEight
Posting Yak Master
200 Posts |
Posted - 2003-04-23 : 15:00:47
|
| You could use snapshot replication if you want to completely refresh the data at the destination database. If you want incremental changes (i.e. what has changed today) then you could use transactional replication and configure it so that you synchronize the databases once a day. Let me know if you need more info. |
|
|
|
holland07
Starting Member
7 Posts |
Posted - 2003-04-23 : 15:09:09
|
| Yes, I was planning on using Snapshot Replication. My primaryconcern is security. If the data is sensitive and I needto transfer it between servers, the transfer method has to be secure. I am just not sure if Replication is secure or not. Andif it isn't, what would be another way to copy data between servers?Is this as uncommon a thing as I am beginning to think it is? |
|
|
|
PiecesOfEight
Posting Yak Master
200 Posts |
Posted - 2003-04-23 : 17:38:01
|
| Replication does not provide any encryption directly, so you will have to use SSL or a VPN. I think you will be in the same boat with any solution though -- I don't think any other SQL Server export/copy methods have built in encryption. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-04-23 : 17:43:36
|
| I'm not a replication expert, but here's a shot.Could you do log shipping, and just transport the logs over an SSL connection?Create an app that reads the "log ship" files and does an HTTPS POST of the files to the destination PC which then imports the logs into the destination sql server?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-23 : 22:44:40
|
| If you just want some tables why not transfer via dmoseewww.nigelrivett.comDMO transfer==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
darinh
Yak Posting Veteran
58 Posts |
Posted - 2003-04-23 : 23:01:29
|
| We do merge replication between 2 geographically independent sites and have no problems with security. Assuming both your sites are protected by a decent firewall then it should be easy enough to set up a secure tunnel between your 2 servers. Whoever administers your firewalls should be able to do this. It tends to be reliable although occasionally has problems due to rain fade on a digital microwave link. Usually it is clever enough to recover when the link comes back although we have modified the SQL Agent job to email alerts if it doesn't. |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-04-24 : 02:45:08
|
| You can use log shipping and SQLLitespeed to encrypt + compress your log files. This should both secure and speed up your transfer.[url]http://www.sqllitespeed.com/slsdefault.asp[/url]HTHJasper Smith0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27 |
|
|
|
|
Long Distance Replication