Delphi: Internet Based Data Source

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开发者_JS百科 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?

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.

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.

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.

DataSnap

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.