96

I'm looking for a "top ten" list of reasons why we should be connecting to remote databases via web service instead of directly connecting to the db. This is an internal debate right now and I'm pro-web service but losing the argument. I have a basic grasp of WCF / web services, no one else does. We can do whatever we want moving forward but we need to stick with whatever we choose now.

Here is what I've come up with. Any more?

  1. WCF web services can, if configured correctly, be more secure.
  2. Changes to the DB only need to be made at the service level (config file or recompile service).
  3. Once setup and hosted, web services are easier to consume.
xpda
  • 15,585
  • 8
  • 51
  • 82
DenaliHardtail
  • 27,362
  • 56
  • 154
  • 233

6 Answers6

123

  1. Security. You're not granting DB access to anyone but web server/app user.

    This is extra important when you have tons of users. You avoid the pain of user/role maintenance on DB side.

  2. DB load reduction. Web service can cache the data it retrieved from DB.

  3. Database connection pooling (hat/tip @Dogs).

    A web service can use a small pool of permanently opened DB connections. The helps in a variety of ways:

    • DB connection pool is limited on database server side.

    • opening a new DB connection is VERY costly (especially to database server).

  4. Ability for fault tolerance - the service can switch between primary/DR data sources without having details of fail-over be implemented by service consumers.

  5. Scalability - the service can spread requests between several parallel data sources without having details of the resource picking be implemented by service consumers.

  6. Encapsulation. You can change underlying DB implementation without impacting service users.

  7. Data enrichment (this includes anything from client customization to localization to internalization). Basically any of these might be useful but any of them is a major load on database and often very hard to implement inside a DB.

  8. May or may not apply to you - certain architecture decisions are not DB acces friendly. E.g. Java Servers running on Unix have an easy access to a database, whereas a java client running on a Windows PC is not database aware nor do you possibly want it to be.

  9. Portability. Your clients may not all be on the same platform/architecture/language. Re-creating a good data access layer in each one of those is harder (since it must take into account such issues as above-mentioned failovers/etc...) than building a consumer layer for a web service.

  10. Performance tuning. Assuming the alternative is clients running their own queries (and not pre-canned stored procedures), you can be 100% sure that they will start using less than optimal queries. Also, if the web service bounds the set of allowable queries, it can help with your database tuning significantly. I must add that this logic is equally applicable to stored procedures, not unique to web services.

A good list can also be found on this page: 'Encapsulating Database Access: An Agile "Best" Practice'

Just to be crystal clear - some of these issues may not be applicable to ALL situations. Some people don't care about portability. Some people don't need to worry about DB security. Some people don't need to worry about DB scalability.

DVK
  • 126,886
  • 32
  • 213
  • 327
  • 27
    Sorry, disagree. 1. So you grant DB access to a group instead of a single principal - no difference. 2. Any app can cache data. The kind of data that can be cached across multiple users will usually be low volume data in any case. 3. FT should be handled by the database in any case. 4. This is not an out of the box feature, and has to be programmed. 5. Your data access layer should be doing the encapsulation. 6. Same thing. 7. Really? JDBC doesn't run in a client? 8. Good point, when it matters, which is rare. 9. query vs. SP was not part of the question. – John Saunders Jan 26 '10 at 23:45
  • 7
    1. Try managing that across 5000 users with hundrends of roles. Does not scale so well anymore. 2. Depends entirely on an app. Our current case has an instance of caching results of a query which in uber-optimized case takes 20 minutes to run and which we need to run 100s of times a day at least from different apps. 3. FT is handled on a bunch of levels. What do you mean "should be handled by a database"? – DVK Jan 27 '10 at 13:43
  • 4
    4. Of course it has to be programmed. But it can be programmed into the service once, or into a zillion client apps on various platforms with varying capabilities. There's a major difference. Never mind the issues of configuration management for load balancing. 5. Same reasoning. You don't need to re-implement DAL. Matter of fact, you can just think of the web service as a portable DAL to ease your mind. 7. We don't want every client opening DB connections. Is that such a big thing to ask for? Again, you're forgetting points 1-5. – DVK Jan 27 '10 at 13:47
  • 2
    8. >1 client architecture happens VERY often. Matter of fact I never worked on a project without such a situation in my life, but I'm centered in financial world. 9. It was not. I was basically playing devil's advocate. – DVK Jan 27 '10 at 13:48
  • 2
    I like this answer, but I think you skipped one of the most important points: Connection pooling. If you have 1,000,000 clients, you're going to either have 1,000,000 open connections, or millions of connections constantly being opened and closed. Basic intuition about computer organization tells us a well tuned pool of a couple hundred long-lived connections is astronomically more efficient than having a million long-lived or millions of short lived connections. The HikariCP docs to a great job of elaborating on this idea: https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing – Dogs Oct 20 '17 at 15:38
  • @Dogs - yep, I alluded to it in one of the comments above but neglected to add to the answer itself. Feel free to edit in please :) – DVK Oct 20 '17 at 15:45
16

In my opinion, you should not automatically be exposing your database as a web service. If it turns out you need a service to expose your data, then write one, but not all database access should be done through web services.

  1. There is no reason why a database connection should not be secure
  2. You can encapsulate the database in a data access layer (possibly Entity Framework)
  3. Web services are no easier to consume than a well-written data access layer.
John Saunders
  • 160,644
  • 26
  • 247
  • 397
  • Why necessarily XML? There's also much lighter to parse JSON, CSV for simple flat data, etc... – DVK Jan 26 '10 at 20:02
  • It's not "for no good reason". As noted about, depending on your requirements and desires for future development, it might be necessary for your project. – Chris Stewart Jan 26 '10 at 20:20
  • I write my WS to consume a DAL. What port would you suggest exposing a DAL on? – samus Mar 27 '17 at 18:20
  • @samus it doesn't matter. – John Saunders Mar 27 '17 at 20:32
  • "There is no reason why a database connection should not be secure", well, it is difficult for example to secure the connection between a Windows client and a database. Really difficult to implement a secure connection! – NoChance Jun 01 '18 at 09:28
12
  • Web Services form an API, defining the allowed interactions between external systems and the application's data.
  • Web Services decouple the database from external interactions and enabling the data layer to be managed independently of those outside influences.
  • Allowing access only by Web Services ensures that application logic gets the chance to execute, protecting data integrity.
  • Web Services allow the most appropriate authentication/authorization measures to be taken, as opposed to a database requiring a username and password/table level privileges.
  • Web Services provide an opportunity for automatic service discovery and configuration to be used.
  • Web Services traffic can be encrypted for transit over unsecured networks. Don't know of any direct DB connection solutions that enable that...?

Most of these points go for any formal API, not specifically Web Services.

brabster
  • 42,504
  • 27
  • 146
  • 186
  • 1
    Yeah, that was what I was going to say, if you have a single application accessing a database all your points are available for normal APIs as well. – Ignacio Soler Garcia Jul 10 '15 at 12:36
  • "Web Services form an API, defining the allowed interactions between external systems and the application's data." You can do that with a database too. – Shoe Oct 27 '15 at 15:08
  • "Allowing access only by Web Services ensures that application logic gets the chance to execute, protecting data integrity." -- I would argue that data integrity should be part of the DBMS only. – Shoe Oct 27 '15 at 15:09
  • @Shoe It's nice to enforce as much integrity as possible by the DB, but as data starts populating and exposing shortcomings, like the need for some input validation, it's nice to enforce this at the application level (though I for one prefer enforcing on the client side, it sometimes needs to be dealt with on the server side if the validation rules are dependent on data known only to the server (kept from the client app)). Is it a big deal to change a DB's integrity rule set, I'd imagine it not being a trivial matter? – samus Mar 27 '17 at 18:15
2

Writing a web service that simply wraps calls to stored procedures seems to be a misguided approach to designing a good DAL. More than likely, your stored procedures are legacy code left over from a older client-server systems i.e. business rules are buried in the SPs. If that's the case, you're really attempting is creating a silk purse from a sow's ear.

Moreover, your adding a SOAP message protocol layer that adds a performance hit to web apps that have been 'coerced' to dating this 'pig'. I'm working a project right now where our new MVC-4 app has been instructed to use such a DAL. We have the burden of having to change both the WebMethod signature and the SP signature whenever a new user story comes along necessitating such changes; which for us, is every single sprint. Inherent in such a passthru approach is two tightly coupled tiers.

Sevasanakid
  • 21
  • 1
  • 1
    Web API addressed the WCF/SOAP bloat issue. It's now like a light, fit, agile feline; just what it needs to RESTfully serve. – samus Mar 27 '17 at 18:28
  • In theory, it is not wrong to call stored procs using web services. – NoChance Jun 01 '18 at 09:31
2

1)Headache of maintaining database is reduced from developers side so that they can focus only on developement.

2)Web service supports communication of different platforms (operating systems like windows,ios,android etc) in a very easy and effective method. for example consider a situation when android application & ios application wants to communicate to a website which is java based so for communication of all the three things webservice is the best solution instead of maintaing three different databases.

Rohan
  • 21
  • 3
1

In general

  1. Web Service level promotes reuse of common data requests for multiple applications
  2. Web Service can be set up with version management which deflects many issues arising from application level development. For example if I am new to a project which existing application do I use as a good model for configuring my application to use existing database sources.
  3. Web Service has evolved to allow flexible options for sending requests and getting response results back in a common format such as JSON by using a simple URI which means that client applications can be developed using a more common standard that encourages dependable uniform interfaces.

I am just getting stared with ASP.NET Web Api and plan on making data services first.

I have recently been focusing on .NET MVC web applications with the use of the entity framework.

  1. If you already use MVC the Web Api also uses MVC with the Api controller so the learning curve to build the services are fairly painless.

I recently found myself in a frustrating predicament with an MVC web app that I was building originally based on Oracle stored procedures. The original version as Oracle 9 or even earlier which presented another problem with Visual Studio 2012 pushing a more modern connection factory approach with load time assemblies finding the right dll files to use based on web config connections and TNS names.

Attempts to connect to the database failed with 'no longer supported' error messages. Out of curiosity I downloaded Oracle 12c and made some application level connections that worked nicely with my TNS names and the load assembly dll and I was able to work with Oracle with no problem.

There were some web services built that were working with connections to the older Oracle version. They were built with methods that were specifically mapped to selected tables however to my disappointment. I would have to write my own.

I was told that the group that was responsible for maintaining the Oracle databases that they would be writing new stored procedures to replace the older ones that I was using to abstract the client interface and business logic layers.

So my first thoughts were that all of the common data requests such as filling up drop down list or auto completes with enterprise wide data be done through data services that would call the Oracle stored procedures. Why repeat that process over each application and have each developer struggle with configuration and version/load assembly, TNS issues?

so....

  1. For multiple database server issues such as using Oracle stored procedures in a .NET MVC application that might usually be using EF for the SQL Server data usage why not push those headaches up to Web Api service methods where those configuration issues can be isolated.
  2. Again the client interfacing can be done using JavaScript, JQuery and JSON which you are already using if you are doing this using Web Api to make SQL Server data requests.

I am an Application Developer/Analyst and not a DBA so my perspective is one from experience with the never ending frustration of having to constantly modify applications when database tools evolve.

Brian Quinn
  • 57
  • 1
  • 4