The code and slides from my presentation from today (Service Broker - Asynchronous processing in SQL )are now online.

The code is on codplex http://www.codeplex.com/MultiThreadedSQL,

The slides are on http://sqlblogcasts.com/files/folders/servicebrokerpresentations/entry1885.aspx

and a video of the service broker restaring after I stopped SQL Server is here http://sqlblogcasts.com/files/folders/servicebrokerdemos/entry1884.aspx

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/Service Broker - Asynchronous/Multi threaded File Upload

I have just finished my presentation on asynchronous process/parallel processing using Service Broker at the Microsoft Technology Conference in Ireland. I was a bit tentative after deciding last night, after a few guinesses and whisky (thanks Barry), to add a new feature to my presentation, failure.

Yep I thought it would be great if I did a demo of how service broker handled failure and performed reliably. How can I simulate failure I thought, well the best I could come up with at short notice was to shutdown Sql Server with no wait whilst service broker was processing.

Anyone that has done presentations will realise that effectively killing SQL Server mid demo is probably not the wisest idea., any way the presentation was going well and so I went for it.

In my demo Service Broker is configure with activation to run an SP process the messages and upload the file.

So I kicked of my process that results in ~180 messages(files) sitting in the queue to be uploaded. I then showed that Service Broker was processing some messages and then shutdown SQL.

Once shutdown, my app dies because it can't access the DB.

I then restart SQL and pray.

Eventually the databases recover and service brokder starts processing the messages again without any intervention.

Wow it worked. When I tried it last night I was quite shocked that it worked.

A video of this will is available here http://sqlblogcasts.com/files/folders/servicebrokerdemos/entry1884.aspx

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/Service Broker - Reliable processing

I will be over in Ireland on Thursday presenting on Service Broker .

It appears few people are actually using it for two way messaging (conversations). 

More are actually using it as a distribution mechanism for getting data from one place to another, asynchronously and reliably.

The other implementation is to perform parallel processing. We are told over and over that we should use set based processing in databases, whilst these are great at obtaining a result set of data in an effiecient manner, any calculations within the set are generally performed sequentially on a set of data. If this calculation is expensive this has a huge impact on performance. This can be seen clearly with a TSQL udf, these are very expensive when compared with system functions (http://sqlblogcasts.com/blogs/simons/archive/2005/01/17/User-Defined-Function-performance-comparison.aspx and http://sqlblogcasts.com/blogs/simons/archive/2006/03/13/Performance-of-formatDateTime-SQLCLR-function.aspx)

So if we want to get ultimate performance we need to do things in parallel, developers for a long time have been writing multi threaded apps, especially with .Net where its really easy. However in TSQL its not very easy, until now. Service broker provides the ability to perform processing in parallel. If you're interest in how then come along next week and see my presention on Service Broker

Also presenting is Tim Sneath former UK resident now over in Redmond working on funky UI things like XAML, and Silverlight.  http://blogs.msdn.com/tims/archive/2007/06/04/silverlight-in-the-uk-and-ireland.aspx

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/Parallel processing in TSQL

I remember seeing a very early CTP of Yukon and the new DTS. I was very exceited to see a parallel property on the loop container which would allow the contents of the container to be executed in parallel.

If you've used the RTM on Yukon you will know that there is no such property. Unfortunately it was too complex to be implement.

However you can still get parallel execution by using multiple tasks or by having multiple paths in a data flow.

For more details read this post

http://blogs.msdn.com/sqlperf/archive/2007/05/11/implement-parallel-execution-in-ssis.aspx

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SSIS - Parallel processing

If you've worked in a large AD shop you will have come across Group Policy. A means by which you can make sure each component in a domain has the same settings, behaves the same way, has the same security. This is essential when managing a large server estate.

Unfortunately Group Policy doesn't extend to SQL Server. So you can't say I want all servers backed up,don't use auto shrink, you must rename the sa account etc. (unless you use a third party system)

Katmai brings us Policy Based Management, if you manage multiple servers you'll love it.

The information currently available is here

http://download.microsoft.com/download/B/F/2/BF24C54E-5635-4C79-AFB4-0C3F840E79F4/Katmai_datasheet_Final.pdf

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/Katmai - Group Policy for SQL Server

A press release went out today about Katmai the next version of SQL Server.

There are some really exciting developments. Spatial indexing being one of them.

Yes spatial is going first class, no more need for writing your own CLR code, this stuff will be in the engine.

There will be CTPs coming out for you to get your hands on, so get those location based applications geared up for a better experience.

http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx

http://download.microsoft.com/download/B/F/2/BF24C54E-5635-4C79-AFB4-0C3F840E79F4/Katmai_datasheet_Final.pdf

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/Katmai new features - Spatial indexing

One of the things I love about this industry is that there is always more you can learn.

Running x64 is meaning I am hitting a few hurdles which result me having to figure out whats gone wrong and thus I end up learning something new.

Todays lesson is debugging, t86he tool I am writing this blog post is an extension I've written for Omea. Omea is an information application, it does RSS, NNTP and with my extension blogging. It will also index your files, outlook etc a bit like google desktop.

For some reason when I try and attach to Omea to debug my component it would attach but not do anything. not even load any symbols. Today I tried again and found that the "Attach to Process" dialog has a "Attach to" option this is the type of code you want to debug, x86, managed, TSQL or script. It was set to automatic, which means it detects what to debug. Oddly for OmeaPro.exe the type is Script, Managed x86, which seems to mean that the automatic debug type is script. Obviously not good as I want debug my managed code. So I manually selected Managed by clicking on the "Select ..." button and heh presto all the symbols are loaded and my breakpoints get hit.

I don't why the automatic doesn't detect the correct type, just another x64 feature :)

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/Debugging on x64 machine

For those that downloaded the enhanced error component I forgot to make it clear that the new version requires you to have service pack 1 installed. The reason being is that we are using an interface that allows us to look at the whol package in the component UI.

If you were around at a SSIS presentation in 2005 you will have seen the statements on the extensibility. The statement from MS was that they have used exactly the interfaces we do to create their components. However on investigation we couldn't figure out how they had written the Slowly Changing Dimension to add components using the public interfaces. Well what was missing was the pipelineService that was made public in Service Pack 1.

So you can now write components just like Microsoft. If you are interested in the code for the error component let me know.

In the meantime feel free to download the component from here

http://sqlblogcasts.com/files/folders/transforms/entry2.aspx

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SSIS - Enhanced Error Component

I've just been tearing my hair out trying to get variables and locking working. It was only after one of those eureka moments that I realised that the variable names are case sensitive.

So the lesson learnt is make sure that you have a naming convention for your variables so you don't have to remember if your variables are all lower case (unreadable) , title case (my preference) or camel case.

I've also decided that I prefer to use the lockForWrite over the LockOneForWrite and even over specifying the names in as a delimited list. The reason being is that you have the better control in doing it that way.

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SSIS - Variable lists for the script task are case sensitive

I am starting to regret installing 64 bit vista on my laptop, my woes are continuing as I found out today I can't debug script tasks.

I do hope this is fixed in Katmai :)

The full limitations, and there are a few, can be found here

http://msdn2.microsoft.com/en-us/library/ms141766.aspx

This is cross posted from my SQLBlogcasts blog which can be found here, http://sqlblogcasts.com/blogs/simons/SSIS - 64 Bit woes