Disclaimer While doing a research for a SCADA integration project, I found this document on Google cache as the main site was unavailable. It has some good points and may be useful for anyone looking for this comparison.
Many people question the concept of using SOAP or REST based services to deliver data from a database when they could potentially use SQL/ODBC. In general terms, if your requirement is for complex SQL/ODBC queries, SQL/ODBC is the way to go. However, when the requirement is less about complex queries and more about accessing and/or updating the data, SOAP and REST provides many advantages in today’s' networks.
| Feature | SQL/ODBC | SOAP/REST |
|---|---|---|
| Standards | While an SQL standard exists, there are different flavors implemented by different databases. | Fully standards based using WSDL, SOAP, REST, XML, HTTP and TCP/IP |
| Compatibility | All ‘client’ products (such as Excel, Word, InfoPath etc.) will require a product specific driver installed locally to the client software to talk to a database using SQL/ODBC. | All ‘client’ products in the market today have built in capability to issue REST requests and most have the ability to call a SOAP based Web Service. This means that all such products can access data using REST and/or SOAP without any driver installed locally to them. |
| Encryption | Generally implemented using internal, product specific mechanisms that require knowledge on the client and server sides. | Uses SSL which is the de facto standard for encryption on the Internet today. Most if not all technologies can understand SSL communication out of the box. |
| Installation | Software must be installed on the platform where the database server runs and on each client system that must access the database. | Single installation on server platform where the database runs. No installation is required on the client systems. |
| Configuration | Required on both the client and server sides. | Only on the server side. URLs can then be published to standard UDDI Servers which can then be discovered by the client. |
| Firewall support | Firewalls must be reconfigured to enable SQL/ODBC Connectivity behind them. This makes it difficult to configure access to data across firewalls. | HTTP and HTTPS are protocols that firewalls are normally configured to allow through. This makes SOAP and REST ideal for queries that must cross organizational boundaries as occurs more frequently. |
| Coupling | SQL/ODBC applications are generally quite tightly coupled to the database servers they are using. | The SOAP And REST based protocols are very loosely coupled which leads to more options for load balancing, scalability and failover protection. |
| Security | Proprietary | SSL |
| Debugging | May require multiple, proprietary tools to debug queries. | Standard tools on the market can be used to debug issues such as ‘sniffers’ or proxy technologies available in products like Eclipse. |
| Licensing | Many adopt usage based models based on the number of clients using a database. | The database sees one individual client (i.e. the SOA server) and the server has, in theory, no limits on the number of clients that can connect to it. |
| Data Types | SQL/ODBC databases have specific types that don’t always translate correctly across different systems. | The SOA server uses UTF8 and UTF16 based XML which can handle any data type or code page. |
| Flexibility | Once the SQL/ODBC interface is opened up and made available to a programmer, they can create complex queries which are very flexible. This is as designed but has the potential to overload the database server. | (...) a very finely controlled services can be defined to ensure that queries cannot overload the system. |
| Data Integrity | Programmers need to understand the specific transactional semantic of the database(s) they are accessing. | The programmer tells the SOA server what it wants through SOAP headers and the SOA server deals with the semantic for each different database on behalf of the client. |