Databases as Services

Databases as Services typically are well encapsulated and contain business rules

Without discussing if the kind of database design Barry is suggesting is "Service Oriented" or not, I think the idea of a well encapsulated database with business logic is interesting. It follows Jim Gray's Put the computation near the data principle. It goes against the idea of the 'middle tier' where the business logic is performed to avoid loading the database server.
However, most 'modern' features of relational databases (referential integrity, joins) are usually performance intensive. People who need to get the most of them, don't use those features, but most of us use them because the performance impact is not that bad in our scenarios, even if it increases the load of the database. It looks then that moving the logic to the database could be acceptable in some cases.
Going back to the database as services matter, the main difficult that we have with SQL Server 2000 to make this work is that services need to deal with hierarchical data, and T-SQL stored procedures suck at it. I cannot have a stored procedure that receives an Order. I need one for the Order header and another for the Order lines (I know, I could send the header and lines in a string and parse them, but that's what 'sucks' mean). That is not encapsulation.
In SQL Server 2005 we have could handle in two ways. One could be using UDTs, but I'm not sure if I can have an Order UDT. The other option is to send it in an XML.
When you want to update an Order, and you are sending an XML, you need a way to tell the stored procedure which changes you made to the order (for example, if you added and deleted a row line), so sending a simple XML with the order information won't be enough. You need to send out of band information that describes the operations done in the Order, and probably the previous values for the modified rows. This starts to look like a diffgram ;).
Having a stored procedure that receives a diffgram with the Order and applies business logic and updates the database looks like a good solution, and it seems it could fit Barrys' requirements. The only issue is that you need a diffgram-friendly platform (or another XML standard that can be used to exchange diffgrams that is supported in multiple platforms) if you want to have an easy way to work with those stored procedures from a different platform.


  • Actually in SOA diffgrams wouldn't work since you do not know the state of the data once the change request comes back (the order may have shipped by the time you get the change line item request). The best the caller can do is suggest what they would like to do with the data. This is why you have to do the diffing on the server side. Since you have to load the current state of the data out to compare it to the data sent in anyway it might actaully make a lot of sense to do this closer to the database (although i've always done this in a middle tier sans sql server 2005).

  • Hi Ray,

    That's exactly why diffgrams will work in SOA. I won't apply the diffgram 'as it is', but I will use it to know what the user did with the data and compare it with the current state. Without a diffgram (or something with the same information) there's no way do know it, and thus you cannot make the right decision...

    Following your example, I get the diffgram with the order with the new line, I read the order from the database, I see the 'IsShipped' is different than the original value that is in the diffgram, and I throw an OptimisticConcurrencyException..

  • Yeah i guess my point was that since you have to "read the order from the database" anyway to validate the request- keeping it closer might make a lot of sense with the right tools.

    I guess I favor more granularity in the service methods to determine intent as opposed to using client side difgrams. Plus, I mostly let the last request in win in terms of updates so I usually dont care about getting the state of the data I sent the guy back.

    Needless to say, the toolsets coming out are going to give us some interesting options in terms of architecting these solutions.

  • In the order scenario I would have UpdateOrder which would contain the line items that have been modified. However you still dont need a difgram since each line item that existed before (a non-add) would have an an immutable ID attached to it. And for deleted items would have an "IsDeleted" flag since you wouldnt actually be deleting it anyway... you'd simply be flagging it... so I still think you can manage without a difgram.. right?

    (granted the isdeleted flag is sending intent...but it would most likely exist in the schema anyway)

  • So, new rows will have a null ID, deleted rows will have an IsDeleted field = true, and the rest will of the rows will be updated (unless you want the service to receive all the rows, including the non-updated ones, in which case you'll need another flag for the modified rows).

    Yes, that would work without requiring a diffgram (and it will be duplicating part of its functionality).

    Thanks for your comments!

  • UDT's won't work, they are just for scalars. But HTTP hosted web services inside SQL Server 2005 will work - and will/can map to a stored proc that returns multiple result sets - and you guessed it - they use the dataset serialization format by default.

  • Hi Bryan, thanks for the feedback.

    Why would I use a SQL Server web service instead of a stored proc?

    I think that for queries I can just return the result sets and load them in a dataset in the client.

    For updating, I still need the diffgram. I can probably do it as a SQL Server web service or as stored proc. I guess a stored proc would be better...

Comments have been disabled for this content.