I am currently on the SQL Server 2005 Application Development Ascend Training course this week hosted by the extremely knowledgeable Bob Beauchemin. To be honest, although I had been keeping abreast of the developments of SQL Server 2005 (formerly Yukon) from some of the resources on the internet and our talented SQL guys at Conchango, I had not had much time to devote on the subject with all the BizTalk work.
So it has been a satisfying break this week to be able to get some in depth knowledge from someone as experienced as Bob. I have only just finished the second day and have already noticed the degree of changes that have gone into this revolutionary release. SQL Server has been making revolutionary releases every other circulation, with evolutionary releases in-between. SQL 2005 is most certainly revolutionary.
The majority of the last two days have consisted of the integration of SQL Server 2005 with the CLR. I must admit that the first time I had read about this functionality a while back, I had my reservations as to its use and performance. I could see its place when creating user defined functions perhaps, but wondered as to the performance overhead.
So far in the course I have been converted, or rather my eyes have been opened to the possibilities and been satisfied with the performance metrics that have been presented. Obviously, this is far from saying that typical SQL Server data interfaces are now obsolete. T-SQL is still the preferred method for heavy data access, as of its set based nature. However, .NET certainly provides other avenues for the more heavy calculations and exposing functionality available in .NET that has not been available in SQL Server before.
When you have functionality that requires a mixture of both intense mathematical calculations and data access then some more experimentation is necessary. As with most things you come across when designing solutions to problems in computing, the best solution is dictated by the unique requirements of the problem you are trying to solve. A good article on the cost/benefit of .NET in SQL Server 2005 is located here.
Although I have been informed that the next release of BizTalk (Pathfinder) will not be based upon the SQL Server 2005 engine (I am not so sure now after seeing contradicting reports), I was interested to notice that cataloguing assemblies in SQL Server 2005 does actually insert the assembly in binary format within the database, not a reference. I am sure that this is something that may possibly be harnesses in the future with BizTalk and provide a few possibilities. However, the actual assemblies containing the applications would still require deployment to the individual processing servers I'm sure.
A final point in this quickly put together post, SQL Server 2005 only works with versions of the .NET Framework that have been tested with it. This is due to the nature of the database maintaining a certain amount of control to provide secure and stable access modes to the assemblies that are deployed on the platform. This does expose a small caveat in that caution must be taken when deploying any .NET Service Packs/Upgrades. If these have not been certified to work with SQL Server then things may stop working! Probably more of an issue with development machines than in the more structured production environment.