The Old Joel on Software Forum: Part 4 (of 5)

What's in the middle?

What are the possible alternatives to use for distributed, thin clients to connect to a remote database? Like for example, a software such as this has a Windows client, without a local database. The data resides on MS SQL server, which could be installed anywhere. Could they possibly be using a DSN-less ODBC connection? In that case, one would have to provide a static IP for their server then? Thoughts please.

Textbox
Tuesday, May 11, 2004

It doesn't have to be a static IP, it can be negotiated using Dynamic DNS.

What's in the middle can be anything from ODBC, JDBC, ADO, SOAP or any other agreed protocol.

There's a few rules of thumb though. If you have a disconnected client running through a public network then try not to have SQL flying between the two. Instead create services on the server either in a middleware wrapper or as stored procedures.

Use serialised connections.

Enable authentication at every step.

Use a different service to receive results than you do to initiate stuff on the server. This has good scalability and makes it harder for intercepts.

Simon Lucy
Tuesday, May 11, 2004

I forgot to post the link.

http://www.accpac.com/images/products/Pro/GL_Fiscal.gif

Textbox
Tuesday, May 11, 2004

So, passing parameters to procedures would be safer, you mean?

>Use a different service to receive results than you do to initiate stuff on the server.

Would that not be so overly tedious?

Textbox
Tuesday, May 11, 2004

Only if you do it manually.

You set up a RequestID, along with a ConnectionID kick off the request and either have the return service kick you, or poll the reply service with the RequestID.

Simon Lucy
Tuesday, May 11, 2004

>If you have a disconnected client running through a public network then try not to have SQL flying between the two. Instead create services on the server either in a middleware wrapper or as stored procedures.

Good advice indeed. One approach, which works quite well, is this: (I'll refer to .NET, but you can do similar things with other technologies of course)

- Write a middle tier. It basically recieves requests from your client, extracts data and sends it to the client. The data may be sent in the form of serialised "dataset" objects or in the form of serialised custom objects. (See http://www.benchmarklearning.com/events/EventData/DevCare/BusinessEntities.ppt )

- Use HTTP as your transport protocol. Why? Because if your users are behind corporate firewalls (not your firewall, but _their_ firewall) they probably won't be able to reach your server if you don't use HTTP.

- You can host your middle tier in IIS (using either Remoting or Web Services). That way IIS can do authentication and encyption (https) if required. Obviously IIS also ties in well with the previous bullet point about using HTTP.

While this may sound complicated if you haven't done it before, in theory you can keep it fairly simple. In .NET you may have little more than some DataAdapters in your middle tier, if your app is just a simple CRUD (create, read, update, delete) system. (Although for more complex apps you'll also want to put business logic there).

>Enable authentication at every step.

In particular, do not trust the client app. If your system runs over the internet then there is nothing to stop someone from building their own client-side app, by-passing any client-side security measures you may have used, to try to hack into your server. So your server app needs to do authentication. If you host your middle tier in IIS, then you can use IIS's authentication features.

>Use a different service to receive results than you do to initiate stuff on the server. This has good scalability and makes it harder for intercepts.

I haven't seen this done. Another approach, which I have seen, is just to use HTTPS. That make intercepts impossible, at the expense of a little extra work for client and server (to do the encyption).

John Rusk
Saturday, May 15, 2004