Re: Copying SQL Server 2000 data to remote database - best option?

#1

I have this same need, except I need to copy data from our local
SqlServer to our public internet site hosted by our web hosting
company. I've used both #1 and #3 to do this. They both work quite
nicely, but I wonder about how secure DTS is over the internet with #1
and ran into size issues with #3.

I have some of the same questions...

#1) Is DTS secure over the internet without a VPN setup or does it pass
the SqlServer credentials in clear text?

#3) I wrote a webservice that takes a dataset as a parameter and
updates the tables. It works great, but I ran into size issues when I
went from sending 1,000 records from one table up to 10,000 records.

My next plan was to compress the data before sending it up to a
webservice. Can I pass a byte array to a webservice... or is there a
better way to do all of this?

Any suggestions would be appreciated.

Thanks,

Mike

lsriram7 wrote:

Quote:

I have the following situation:

I need to transfer data from a set of tables in SQL Server A in Country A to
a common table in SQl Server B in Country B over the internet. Both SQL
Server instances are of version SQL Server 2000. The number of records that
need to be transferred are in the range of 15000-20000 intitally and may
later increase regularly. What would be the best way to do this securely? I
have thought about the following options:

1. Create a DTS package in SQL Server A in country A which will read the
data from the tables in SQL Server A and insert it into the common table in
SQL Server B directly.However I am not sure about two things in this option
a. Can a DTS package on one server insert data to a database table on
another sql server on a remote server machine through internet?
b. Even if a. was possible how to ensure that it happens in a secure way?

2. Create a DTS package that exports the data from SQL Server A in coutnry A
to a text or XML file. Use a FTP task to move this file to a common FTP
server folder location. Write another DTS package at SQL Server B in country
B to read data from the text or XML file and insert it into the local table
on SQL Server B

3. Write a ASP.NET web service application. however I think this puts a
limitation on the amount of data that be transferred in a single request.

4. Change to SQL Server 2005 and expose SQL Server as a web service

Which of the above would be the best option.Are there any other secure
options? Please let me know.

Thank you in advance.

Regards,
SL

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Copying SQL Server 2000 data to remote database - best option?

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Copying SQL Server 2000 data to remote database - best option? - 12-17-2006 , 01:41 PM