Architecture question - call a remote SQL Server directly or thru a service?

Question

In my new WPF/silverlight app, is it better to directly connect to my remote SQL Server (I'm using linq to sql), or is it better to call a WCF service and have the service connect to the database?

The SQL Server and a Win2k8 web server are both leased and at the same location. If creating a WCF service, I would run it on the web server and connect to the database next door. I'm not concerned with the ability to re-use this service, but what I'm concerned with is the performance. Is it better to make the SQL call remotely from my clients directly, or to call a service and have the service do the calls.

Answer 1

Mostly for abstraction and security purposes, if this application is running on a client's computer offsite (which I have to assume it is) you should expose a WCF service. That way you can handle security, and never let the client see anything dealing with connecting to your SQL server. This way, also, if something changes on your SQL server, you don't have to update your application.

As for performance, I would say it will be slower to a degree, but it should be very unnoticeable, within a measure of a few milliseconds more time.

Answer 2

If you're going to use Silverlight at all (and want to share a codebase), you're far better off using a WCF service to access your data.

Answer 3

You'd normally need a web service, because the SQL Server port (tcp 1433) is closed in most firewalls. Even if you can bribe your own firewall admin to open it, it would still be blocked at client sites.

Answer 4

I always prefer to implement to interfaces because then you are abstracted from the implementation of the data store. Right now my own application is talking to a number of databases and functionality is being transferred to new systems, by coding to the interface the new systems can code their own data access layers without my system being affected.