[ACCEPTED]-Best Practice: Direct SQL Access vs. Web Service-web-services

Accepted answer
Score: 17

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.


Score: 16

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

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

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

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

Which 50 one provides better security?

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

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

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

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

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

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

Score: 7

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

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

Score: 5

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

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

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

Score: 3

If you can acces the DB from the desktop 7 then you should do that.

You have multiple 6 kinds of clients. That means your application 5 should have mulitple layers. It does not 4 mean you need multiple tiers.

Multiple tiers 3 can be necessary if your layers must transfer 2 data over firewalls or if you have diverse 1 technolgies.

Score: 0

I do a hybrid. Direct database access with 8 limited user who can perform read only from 7 the tables. Webservice with a high privileged 6 database user who can perform write functions. The 5 business rules are built in the webservice 4 (audit trials, permission checks etc)

The 3 direct db access makes it easier for me 2 to develop reports, access lookup values 1 from the client app.

More Related questions