I mentioned on my recent blog that I was involved in the Visual Studio / SQL Server / BizTalk Server launch. As part of that, Mike Taulty and I (along with some other folk, including MVPs and RDs, such as Benjamin Mitchell) ran a Chalk & Talk session entitled “.NET, SQL Server & BizTalk Server : Application Design Considerations”. 

At the Birmingham and London launches, we kept this session free format, which was great fun, and ensured some good discussions, but some of it went off topic a little, so for the Harrogate and Edinburgh launches, we put up a list of the five areas that we though worth of discussion. This worked better I think, and was indeed reflected in the feedback scores for the event.

I did promise to put the notes up from those sessions, so here they. Many thanks to all who attended

1. SQL Server trigger written to process the order

  • Why did we do this? What were the benefits, etc?
  • Issues: thread blocks on web service call! (timeout, lost order, etc)
  • Need to decouple trigger for call, could use SQL Server Broker, message queue, etc
  • One way web service call, but how do you track errors, failure, etc
  • Not addressed in sample: Security (context of call, Encryption, etc) and Reliability / guaranteed delivery.

2. Choice of .NET CLR vs T-SQL in SQL Server

  • Generally when would we choose CLR vs TSQL
  • Developer skills, Things you can/can't do, such as e.g. encryption
  • Proximity to data
  • Sensitivity of data
  • Scale out in database versus scale out on middle tier
  • Perf - CLR does some stuff faster, such as string manipulation, etc, TSQL does CRUD better
  • Database abstraction

3. Exposed Stored Procedure as web service endpoint

  • Why did we do this? What were the benefits, etc?
  • Discssion around CRUD Stored Procs versus SOA/Services
  • More generally when would could use for web services
  • WSE, ASMX, Indigo, etc
  • Any questions of speed / latency with web services? (All those angled brackets must be slow!)

4. XML in the Database

  • Is this a good idea? When would you do it?
  • How does it work under the covers
  • Associating schema with the data, eg Versioning issues
  • What about performance?

5. Contoso uses BizTalk Server to process order

  • Why did we do this? What were the benefits, etc?
  • When would we not use BizTalk Server 
  • Low latency 
  • Why wasn't Business Rules Engine used?
  • What about Windows Workflow Foundation? - When does this fit in