.net web service question

S

sithdrake

Limp Gawd
Joined
Mar 28, 2002
Messages
212
I'm working on a new project where i'll select certain portions of data from a sql database on one system that I need to transmit over the web to a sql database on another system. I was thinking of using a web service on the receiving end. The frontend on the sending in will be a windows form application.

Is this the best way to pull this off? Send the data via a web service? It could be a large amount of data.
 
How often does the data need to be moved & what are your requirements for data currency on the destination database?

An IPSEC VPN tunnel + publication/subscription would have a lot less overhead.

Or, in the absence of a VPN tunnel, you could use port forwarding to allow the SQL servers to directly communicate and use a SQL ETL script to move the data (and schedule it with the a SQL agent).

SQL 2005 and later supports integrated SOAP endpoints - and there are great advantages to using a webservice one way or another - but for large amounts of data, it may not be the fasted method.

Adding an additional application & webservice adds additional, possibly unnecessary, points of failure to your data flow architecture -

Also, what about data anomalies? Is there any chance of this occurring? Do you have a plan to resolve them?
 
SSIS should cover most of the ETL processes as well as dealing in the data anomolies its just a case of the servers being able to see each other which i suppose is the issue
im very interested in this so i have subscribed
 
I think I did a sucky job explaining this. What we're looking to do is not to sync 2 databases. We're going to be hosting certain pieces from a client's database on our site and give a nice frontend to it. The databases will have different layouts and structures. Via a windows forms program or a service or something like that on the client's side it will select certain records from their database and upload those records to our server, which will do the appropriate inserts/updates into the database on our end. This is not for a redundant solution or anything like that where we need vpn's. We'll be a hosting service and front-end for parts of the data in their database.
 
Any reason you couldn't just use a socket? Maybe I'm over simplifying this but doing a web service for this seems like a lot of extra work.
 
sithdrake: what you are describing is an SSIS package that does ETL stuff.

SSIS = sql server intergration services

ETL = extract, tranform, load.


It's what designed to do what you described.
 
maxedoutcc said:
sithdrake: what you are describing is an SSIS package that does ETL stuff.

SSIS = sql server intergration services

ETL = extract, tranform, load.


It's what designed to do what you described.
Click to expand...

Or replication :)

Your database that is driving the front-end application definitely does not need to subscribe to the entireparent database.

http://msdn.microsoft.com/en-us/library/ms165713(SQL.90).aspx

So far, it sounds like a simple SQL script (whether you write it manually or use SSIS to write it - doesn't matter) and port forwarding in your firewall (i.e., open port 1433 in your firewall, but only from their IP address) and setup the SQL job on their SQL server to push the data to you regularly. (of course, the data is not encrypted and this is definitely not as secure as an encrypted VPN tunnel between your sites... but in all reality, you're very unlikely to experience a security issue if this is setup properly and your firewall is secure/reliable)
 
You must log in or register to reply here.
Back
Top