| Author |
Topic |
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
 Posted - 2008-06-10 : 12:21:16
|
| I know this is a rather open-ended question, but I am looking for a way to transfer data from a database on one server to another server via HTTP. Can I do this using SQL 2005 Web Services? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-06-10 : 12:49:24
|
| no. since you can't call a webservice from sql server without doing some heavy duty CLR integration.your other options are either creating a linked server or setting up a service broker coversation between servers._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
 |
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2008-06-10 : 13:16:04
|
| The other 2 options, wouldn't that require a VPN connection. The client server is in Texas, and the master server is in Chicago. I plan on having 300+ client servers all transferring data back to Chicago, but I don't want to have any VPN connections. I want to transfer the data over standard HTTP port 80. Any ideas?TIA,Ken |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-06-10 : 13:44:58
|
| service broker doesn't require a vpn connection. you just have to open the port that you will use it.as far as i can remember the port has to be between has to be between 1024 and 32767.but you might want to try it over 80. however this wont be HTTP protocol.if you really really really need it to be http then create a small app that will do this._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2008-06-10 : 15:01:44
|
| Can SQL 2005 call a third-party web service? If I were to create a SOAP web service that would accept an encrypted database and import it, how can SQL call it? |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2008-06-10 : 15:04:38
|
| What exactly are you trying to transmit via the web.And entire database, a few strings? You could use your firewall to map port 80 directly to your sql servers internal port. Making the web part of your lan. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-06-10 : 15:15:45
|
| you have to develop a solution for calling 3rd party web service by yourself in CLR and inport this in sql server.i remember there was a quite complex sample for doing this on codeproject a while back.but i 'd really look into other options for this._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2008-06-10 : 15:48:34
|
| On a daily basis we transfer all updates/inserts (no deletions allowed) from one "on-line productivity" database (WorkTrack) to another "update database" (WorkTrack_SDP) on the same client server. We then need to send the WorkTrack_SDP database to Chicago, and import it into our WorkTrack_Master database which contains data of over 300 client WorkTrack databases. These are on our client's networks, not ours and are not under our control and as I am sure you can understand it is very difficult to get any IT department to open any ports or allow any sort of VPN connection. I could write a SOAP Web Service in Chicago that would accept the Update Database (WorkTrack_SDP) which could be placed into an encrypted format and converted to XML. The web service would then extract the database and import it into our Chicago WorkTrack Master database. This could all be done over HTTP port 80 and that is something that we can demand of our clients. I wanted the SQL Server on the clients to create a single file of the database and send it over the web service, but I guess that cannot be done in SQL 2005. So, I guess I will create an app that will be called by SQL to perform the transfer of the database "file" over the web service.What's the most compact and reliable single file solution of exporting an entire database? Backup?Thanks guys. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2008-06-10 : 17:26:44
|
| Doesn't the Transport Route in the Service Broker require a port to be open on the client side? That's a problem. I want the data transport to go over standard HTTP port 80. That way only have to demand that our clients allow internet access to our web site.I'm thinking of creating a SQL backup of the Update Database (WorkTrack_SDP) and transforming it into an 256 bit encrypted zip file, and putting that into a blob field in a Visual Foxpro table and turning that into an XML file which could be transmitted via a standard web service. Since it's encrypted, any sniffer of the xml web service transport would not be able to decipher the data. The Web Service on the server would then reverse the process, restore the backup to a WorkTrack_SDP database on the Chicago server, and execute a SPROC that would Insert/Update the data from WorkTrack_SDP into WorkTrack_Master.Does that sound like a good plan to you?Thanks for your valuable time, insight and assistance! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-06-10 : 17:33:59
|
| honestly put... i don't know. backups might be quite big...you'll have to manage network hiccups etc...good luck with it._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2008-06-10 : 17:52:07
|
| Actually the database backups should be relatively small since they will only contain one days worth of data. The WorkTrack_SDP database tables on the client server are truncated before insertion of updated data from WorkTrack. Also it will be compressed during transport due to using WinZip Encryption.Network hiccups are planned for. I will actually record when an SDP was sent to the server in a "SDP Control" table on each client. Also a method on the Web Service will be called to verify the previous SDP transferred was actually imported. I'm even thinking of possibly having the Web Service on the Server do the whole import shebang and return an OK/Failure message. We'll see how long it takes.Hi Tara. No I don't want to use FTP or Replication. We already use FTP to transport Visual Foxpro databases and that has other implications (need port 20/21 open, CERN proxy server issues, etc). Plus you have to have a program running on the server to check for FTP files and import them. Also I don't like replication because it's too static. Thanks for the thought though! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-10 : 17:53:50
|
| Replication is certainly not static. We use it for a mission critical process that requires the data to replicate in near real-time.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-10 : 19:17:02
|
| You would probably want to transfer data with FTP in Replication.Thats basically faster than TCP/IP or other means. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-10 : 19:38:05
|
| Yes Tara, I see that. |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2008-06-11 : 07:47:39
|
| Sorry me bad word. By Static I meant that when using replication it's difficult to manage database schema changes because you have to change all of the subscribers/publishers to match the new schema. I foresee some of these 300 clients having additional tables/columns where others may not. As long as the master database contains a schema for all clients I can import the data via scripts based on the client schema. I can't do that when using replication - the publisher db schema must match the subscriber db schema - correct? |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2008-06-11 : 10:33:34
|
| You could set the client servers to run a second nightly backup, then schedule a windows task to nightly ftp the file to your main location.Out going 1 way connection should not have any issue with the vast majority of firewalls. If that was still an issue, you coudl use a webserver hosted on yourside and post the file through it. I believe all of that could be easily eneough done with a simple batch file. YOu could even configure it to telnet an email to you with the results daily. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-11 : 11:17:22
|
quote:
Originally posted by Ken Blum
Sorry me bad word. By Static I meant that when using replication it's difficult to manage database schema changes because you have to change all of the subscribers/publishers to match the new schema. I foresee some of these 300 clients having additional tables/columns where others may not. As long as the master database contains a schema for all clients I can import the data via scripts based on the client schema. I can't do that when using replication - the publisher db schema must match the subscriber db schema - correct?
That isn't true in SQL Server 2005. Schema changes now get replicated.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Ken Blum
Constraint Violating Yak Guru
383 Posts |
Posted - 2008-06-11 : 11:37:06
|
| Thanks again Tara. I will definitely consider Replication in the future. If there was a way to perform replication via HTTP port 80 I would consider it. I just can't demand that our clients open ports on their firewall for VPN/FTP. For now I think I will handle it by transferring the data I need via a web service. Tripodal: Good idea, but as I stated before we already use FTP to transfer VFP databases. There are also firewall/proxy server issues when using FTP, and I am trying to avoid that. |
|
|
|
Next Page
|
Can I use Web Services to transfer data?