2

I have a database client application that runs on a LAN connected to a SQL Server database on the network. I now want to move the database online to a hosted internet server, however, continuing a normal SQL ADO connection to it over the web is full of problems.

What to you guys use to expose the data over the web and preferably still be able to use database controls client side?

Improve this question

5 Answers 5

Reset to default
4

Web Services (SOAP and WSDL). Delphi recent versions are capable of creating and consuming web services.

DataSnap is also valuable, but I think of it as a "middle tier" in your database architecture, and your middle tier probably talks to your web and desktop clients over soap, although you could go directly to datasnap, and there are benefits to either one. Direct Custom Soap implementations (as opposed to using DataSnap architecture over SOAP) is preferred by many because of the sheer number of languages and platforms that communicate using it. While it is capable of working "over the web", there is a valid complaint among some, that it doesn't support SSL, at least not very well (you have to do a lot of work to add SSL). There are also competing third party frameworks from RemObjects, which many people find worthwhile, which are commercial, and while not cheap, are a good deal.

Update: It would obviously be foolish to directly make your SQL database directly connected to the internet. But that's almost exactly what you're asking for. You either build a middle tier, and expose only what you want to expose via an interface that only allows exactly what you want into your database, from exactly those you want to allow in, or you fall back to 100% "attack my database directly, you unfriendly internet, you". I really doubt even Microsoft themselves would recommend you directly expose SQL connectivity over the internet. So it's middle tier, or direct, your choice. If you want to use data aware controls with your solution, you can do it, and I would recommend DATASnap using SOAP.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks, though I've used SOAP and WSDL before on delphi, I've not come accross a control set that will allow me to use DB aware components on my application. You mention "Delphi recent versions" - I'm using 2007. Is this why I don't see it?
Web services are a middle tier - you have a web server and some translation code between your application and the database.
It sounds like Hein wants a middle tier without a middle tier.
Why not use a SQL-server that can only be reached through an SSH-tunnel, that would be secure.
2

If you need/wish to use standard DB-aware controls you need to use a framework that is able to map data to a TCustomDataset descendant client side. This framework in Delphi is Datasnap, although there are others (i.e. RemObjects). Pure web services via SOAP will let you ask from data from the server, but the whole interface is up to you, SOAP let you invoke remote functions, nothing more, it does not support a "db oriented interface" (sending queries and returning resultsets), while Datasnap & C. do.

The only issue is Datasnap in Delphi 2007 use older techonologies that have been superseeded by a new implementation from 2009 onwards. In Delphi 2007 Datasnap supports both an HTTP proxy to its DCOM implementation (through an ISAPI dll, but IIRC it can have issues with recent IIS versions), and a SOAP implementation (using TSOAPConnection). They both take care to encapsulate request and response in a special format to allow a standard delphi db access to data. From 2009 there is a new implementation that can use different transports (including HTTP), but which IMHO became somewhat usable only starting from XE (especially because of security and proxy issues). You would have to change all your data access components, and review the application design.

Because, whatever technology you use to access a remote database, when data go across a slower connection, application design has a paramount importance. If the application is designed to request too many data from the server, to access it very often and so on, it could have dreadful performance. Usually is better an approach that requires the proper subset, elaborates it locally only when needed (and even better, asks the remote server to perform any activity that can be easily performed remotely, i.e. using stored procedures or queries), and then send changes to the server.

Improve this answer

4 Comments

Thanks Idsandon, I realise web connected apps are slower vs Lan, but we're talking less than 20-30 rows per query per screen, I've tested with a direct sql connection over the internet, response is quite good. TO get back to your recommendation, should I forget about 2007 and upgrade to XE? Even then you don't sound convinced (somewhat usable).
I would not use a direct connection due to security issues, at least I would protect tje connection with a VPN. I only used D2007 datasnap via SOAP for experiments, I use it extensively via DCOM. DCOM is not firewall friendly (it uses random ports), and although it can be configured to use a given port range is not the easiest protocol to configure to work on the Internet, works best in a LAN domain, I've found issues in the socket proxy (scktsrv.exe) and AFAIK the HTTP proxy (httpsrvr.dll) hasn't been updated for a long time, before using it I would test if it works as expected
In XE Datasnap was improved, but IMHO there are still flaws in its data protection scheme, but it was designed to work from the ground up via HTTP (and without DCOM) If your data are not very sensitive you could give it a try, my advice is to download the trial and see if the upgrade is worth the price. Be aware that upgrading will mean to go Unicode fully, you have to update your application in this regard as well, and it may offset many advantages, depending on the port effort.
Thanks Idsandon. Nothing is easy hey?
2

You can configure a VPN between the server and clients, and you'll be able to use the same application to connect to a cloud-based SQL server over a secure channel.

Performance can be an issue, it depends on how the application is designed. SQL Server is IMHO a situable database to run over a VPN, because it is very well optimized to minimize round-trips between client and server.

Windows server comes with VPN components, you have free/open source options also, like OpenVPN.

Improve this answer

7 Comments

I guess the whole cloud concept is a little different than connection two site using a VPN. The database design is less important than the application design. Also you may have to tune the database client to carry more data in each "packet" it uses. There is also the need to send "keep alive" packets to avoid sessions to be rolled back thinking the client is dead.
@ldsandon, I know the whole cloud concept is different than the connection of two machines or networks using a VPN, but I don't see how the cloud concept excludes the VPN connection between a client network or machine and a remote server.
@jachguate, the problem with VPNs are I have to grant all my users access to my server. Locking down is a pain. Helping them setting up the VPN is more of a pain, especially if I plan to distribute this app to 100s of users.
@Hein, from your comments to all posts it looks like you do want to pay any cost to be over the Internet, unfortunately you have to pay for it. If a VPN is not suitable to you, you can provide a middle tier, like the already mentioned DataSnap (as I do), or, if you're not concerned about security and hackers, open the database to remote connections in the server, even in the 80 or 443 ports to pass trough firewalls (but no proxies).
Thanks jachguate, but Datasnap seems to be XE only, I can't find any reference in D2007 and even if there is, the concensus is not to use it. I'm not opening up the sql database on port 80 :) Besides the risk, I have IIS running on it.
|
2

DataSnap

Improve this answer

3 Comments

Thanks, I've heard of datasnap, but I'm trying to avoid creating a middle tier. I was hopinh I could somehow expose SQL Server's data through XML and have my application's datasets use the XML data as if it's a native sql connection, after all, XML data sets are many years old. This not an option?
What would be the benefit of your approach? It would just slow down a lot your application (native data -> XML -> your application), having to encode and decode the data. You'd better explain what issue you have connecting directly to the database (unreliable connection? too slow? other?) and what the requirements are.
Sure there will be a performance sacrifice, but I get to connect over port 80, which is always open, and I'm using a technology which is internet-tolorant. This is remote data connections. If XML has too much overhead, there are other long standing standards. Just strange that MS SQL has been advertising XML datasets since 2003 and Delphi has XML components since before then, yet it's not implemented.
1

Since you are using SQL Server, you should consider Azure platform, as it contains cloud bases SQL Server platform.

Self hosted Windows + SQL server require licenses for both, and your own administration.

Azure is supported via MS client libs, and Azure web services are in standard control set of newer Delphi versions.

Improve this answer

4 Comments

Thanks, but sorry Azure is incredibly expensive, especially for smaller applications. Clients rarely agree to a monthly fee for a prodct. They would rather pay a once-off fee.
@Warren P - I can't have SQL ports open on the hosted server.
If you can use a VPN you can access the server in a protected way
SQL Azure is nice, but only provides a subset of regular SQL server. For instance, SQL Azure cannot run scheduled jobs like regular SQL Server can.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.