Direct SQL Access vs. Web Service
In this post I would like to analyze the two ways of how applications can exchange data (especially when it comes to the database access).
I am mainly a big advocate of SOA (Service Oriented Architecture), but on the other side, you have developers who are more oriented toward direct database connections and sharing the data in this way.
I would like to share with you some findings that hopefully will help you if you find yourself in a situation where you need to decide either to go with one or other way.
Let’s illustrate two of many possible scenarios:
As it is shown in the image above, there is the possibility to connect directly to the database, or simply connect to the Web Service instead.
Before continuing let’s illustrate some of the Web Service advantages and disadvantages (Content fully taken from link, and therefore all credits to the author.)
- Interoperability– This is the most important benefit of Web Services. Web Services typically work outside of private networks, offering developers a non-proprietary route to their solutions. Services developed are likely, therefore, to have a longer life-span, offering better return on investment of the developed service. Web Services also let developers use their preferred programming languages. In addition, thanks to the use of standards-based communications methods, Web Services are virtually platform-independent.
- Usability – Web Services allow the business logic of many different systems to be exposed over the Web. This gives your applications the freedom to chose the Web Services that they need. Instead of re-inventing the wheel for each client, you need only include additional application-specific business logic on the client-side. This allows you to develop services and/or client-side code using the languages and tools that you want.
- Reusability – Web Services provide not a component-based model of application development, but the closest thing possible to zero-coding deployment of such services. This makes it easy to reuse Web Service components as appropriate in other services. It also makes it easy to deploy legacy code as a Web Service.
- Deployability – Web Services are deployed over standard Internet technologies. This makes it possible to deploy Web Services even over the fire wall to servers running on the Internet on the other side of the globe. Also thanks to the use of proven community standards, underlying security (such as SSL) is already built-in.
- Although the simplicity of Web services is an advantage in some respects, it can also be a hindrance. Web services use plain text protocols that use a fairly verbose method to identify data. This means that Web service requests are larger than requests encoded with a binary protocol. The extra size is really only an issue over low-speed connections, or over extremely busy connections.
- Although HTTP and HTTPS (the core Web protocols) are simple, they weren’t really meant for long-term sessions. Typically, a browser makes an HTTP connection, requests a Web page and maybe some images, and then disconnects. In a typical CORBA or RMI environment, a client connects to the server and might stay connected for an extended period of time. The server may periodically send data back to the client. This kind of interaction is difficult with Web services, and you need to do a little extra work to make up for what HTTP doesn’t do for you.
- The problem with HTTP and HTTPS when it comes to Web services is that these protocols are “stateless”—the interaction between the server and client is typically brief and when there is no data being exchanged, the server and client have no knowledge of each other. More specifically, if a client makes a request to the server, receives some information, and then immediately crashes due to a power outage, the server never knows that the client is no longer active. The server needs a way to keep track of what a client is doing and also to determine when a client is no longer active.
- Typically, a server sends some kind of session identification to the client when the client first accesses the server. The client then uses this identification when it makes further requests to the server. This enables the server to recall any information it has about the client. A server must usually rely on a timeout mechanism to determine that a client is no longer active. If a server doesn’t receive a request from a client after a predetermined amount of time, it assumes that the client is inactive and removes any client information it was keeping. This extra overhead means more work for Web service developers
One of the greatest advantages of Web Services is that is possible with quite low investment to properly scale the application infrastructure if needed and offer much greater processing power, as illustrated in the picture below:
If you need more power, just add a new server and configure the load-balancer (In order to get most of the load-balancing my advice is to create Stateless Web Services. That’s the real nature of the Web Service, isn’t it?), otherwise you will find yourself into scalability problems, once again.
What I do like about this solution:
- It is very simple to maintain
- I may code the Web Service in a multitude of languages or frameworks (Java, .NET, …)
- One very important thing: The Business Logic is only in one place.
- There is a possibility to create some caching mechanism so that the performances would be even more improved and there will be less database calls
- The database is a simple repository (or potentially is so)…
What I don’t like about this solution:
If you need extreme speed you would need to start tweaking the connection to the web service and for instance not using SOAP or JSON serialization but directly using the binary exchange format that could lead to some limitations (firewalls, less interopearbility, etc..)
Many clients one database
On the other side, let’s consider the following scenario where we have multiple clients connecting to the same database.
You may already see it that the same business logic most probably would be implemented in already two places, which can lead to a non full implementation or simply a different implementation of the same rule, which is potentially very error prone. Unless the two teams share the libraries, they would need to create everything from scratch.
This is pretty much the setup of the project I am working on, and I don’t particularly like exactly what described above. The simple rules are really scattered all across applications and therefore very difficult to change.
Database scaling out
Obviously, there are ways of scaling out the database as well, and this involves concepts like: shared databases, replication, linked servers, distributed partitioned views, Data dependent routing, etc.
Data dependent routing
Data-dependent routing is a very interesting pattern. In its most elementary form allows software architects to partition their application’s data across multiple databases in a way known only to the underlying application. So, the application receives database requests and appropriately routes them to the right databases for processing. Although this approach is a bit more complex to implement and maintain than other scaling-out options, it allows for unprecedented flexibility and unmatched performance gains.
So, in other words, multiple databases will be serving different instances, locations, etc.
As you may noticed, I only scratched the surface of the problem and there are probably many different views and opinions about what to use.
In my projects I really tend to use Web Services as a mean of communication as the interface is very well defined, there could be different versions of the same service, while on the database we are somehow limited to what the Stored Procedure, a View or the schema offers. Some fine grained security needs to be applied directly in the database.
Scaling out Web Services is much easier than working with databases.