Best Practice: Direct SQL Access vs. Web Service [closed]

Question

With respect to an application that has both a web and desktop client version:

1. What is the best practice for the desktop client which needs access to a SQL Server?
2. What are the benefits of connecting to the database from the application vs using a web service?
3. Which one provides better security?
4. What type of scope would call for one vs the other (enterprise intranet vs. web app, etc)
5. Are there any other considerations that are necessary when choosing on platform?

Answer 1

The general rule of thumb is the following:

1. Write an independent data access assembly that will talk to the database.
2. If you are looking for interoperability between different platforms/clients then expose this assembly as a SOAP web service.
3. If you are looking for performance use the assembly directly in your client .NET applications.

Answer 2

What is the best practice for the desktop client which needs access to a SQL Server?

If you're using a local SQL Server then access the database directly. If the client has to use an SQL database on another system, the use of a web service is preferred for an additional protection and the added advantage of having a business layer that should be able to handle multiple users.

What are the benefits of connecting to the database from the application vs using a web service?

Connecting through a web service will always be a bit slower and modifications to the database will be a bit more difficult to add to the whole system. (Basically, that would mean that you need to create a newer version of the web service while maintaining the older web service for backwards compatibility.)

Which one provides better security?

The use of web services tends to be safer, although security is often more a people issue than software issue. But with the web service between the user and the database, the connection to the database is more secure since the user cannot directly access it. (Except for the functionality you provide through the web service.) This point is moot when client and database are on the same system because then the user can get full access.

What type of scope would call for one vs the other (enterprise intranet vs. web app, etc)

Web services are better for client-server applications, where users should not have direct access to the database. Otherwise, a direct database connection would just improve performance. When creating a web service, start by writing a generic (class) library which will provide the functionality for the web service. Create a web service around this (business) library, exposing the important methods to the outside world. Any web site could call this library directly without using the web service, although you can always opt to even let the web site code access the data through the web service. Even if you create just a desktop application with a local database, writing a business library with logic to access the database is just a very good thing to do. Your client could call this business library directly or through a web service, depending on your needs.

Are there any other considerations that are necessary when choosing on platform?

Mostly just the amount of hardware that you're willing to use to set things up. If you can afford to set up a database server, a separate web service for the services and a third for your web site, with a dozen or so client systems, then you can opt for the most layered version, where both client and web site call upon the web service, which calls the database. But if everything needs to run on a single system then just stick to the application and the business layer/library instead.

Adding layers will reduce performance from the view of a single user, though. However, working with multiple layers can improve the overall performance because resources get divided better amongst multiple users.

Answer 3

I'd keep it simple and minimize the amount of layers. Layers cost performance, introduce complexity, and require changes to be made in more locations.

So, if the netwerk connection between the application and Sql Server is open (typically tcp port 1433), I'd use Sql connectivity.

Answer 4

Given the context, there can be a big security concern with client access to databases. It requires either giving users access to the db, or creating a service account. Giving users direct access to the db poses risks. Both approaches open the door to exploiting desktop dll's to connect to db outside of application context (Multiple times I've seen cases where there is a common data access class that all functional operations use. And of course, this components initializes all the connection information. Reflection based access makes it is easy to get to protected or private methods, unless you assert Security Privileges).

Web services expose functional operations that don't expose any sql based operations. Not only is this more secure, it abstracts your client away from your data storage implementation.

Again, it depends on your context. In the Enterprise/ISV realm though, it is generally a big no-no.