Agile Driven Development

Effect Mapping to manage products

2011-09-20T21:59:00.001+01:00

Just been to this talk by Gojko on Product Management using Effect Mapping. It is a technique which is useful for high level project visualisation. It is very similar to mind mapping technique where stakeholders, users and teams colloborate on project scope.

It helps reduce scope of wish lists and helps teams focus on business goals by asking the questions Why?, Who?, How? and What? in this orders

Why? Allows you to narrow down to the business goal. This is the centre piece of your effect map from where all other discussions should start and reason upon

Who? It is not the user but it is who can cause the desired effect to achieve the business goal. In most cases these are project/ product stakeholders.

How? For each stakeholder , identify how the target group can achieve or obstruct the desired effect in real life and not in terms of software, these should effectively be stakeholder needs

What? For each stakeholder identify what business activities or software capabilities would support the needs of the stakeholder. These become your epics in the product backlog

At the end of the effect map both the stakeholders and team should be able to see the synergy of the business goals and what needs to be achieved.

For more see, Gojko’s white paper on this see http://gojko.net/effect-map

Some advice from people who have used this are..

Getting the right number of people can be a challenge
Staying focussed and at the right level of detail is important
Ensuring enough focus on the how is important
Keeping everyone away from solutionising is a real big challenge when technical people are involved
Ideal group size of 5-8 when working for a time box of 2-3 hours
Will be of immense value to the business

This technique is not necessarily something for agile projects you could use it even for waterfall projects

A useful tool I have found which you can use for this is at www.mindmeister.com. Check it out its pretty handy. Even if you are a developer striving to do something on your own , if you put your ideas on a mind map it will help you visualize the idea.

Running SOAP UI Tests in Teamcity using MSBuild

2011-09-16T09:41:00.001+01:00

One of the web services we have has a bunch of SOAP UI Tests. I wanted to make sure when this was run on the build server we have good feedback for each test case rather than a whole build running a pack of tests and telling you if it failed or passed. Kind of a reason i was moving this build away to Teamcity from cruise control

The MSBuild script is as below. I had to define the variables I need and the Directory where all project files for Soap UI can be found.

   1: <Import Project="$(MSBuildExtensionsPath)\MSBuildCommunityTasks\MSBuild.Community.Tasks.Targets" />

   2: <PropertyGroup>

   3:     <SoapUIDir>$(MSBuildProjectDirectory)\SoapUI</SoapUIDir>

   4:     <SoapTestTool>C:\Program Files\eviware\soapUI-4.0.0\bin\testrunner.bat</SoapTestTool>

   5: </PropertyGroup>

The next thing I had to do was run each project file using the SOAP Ui testrunner.bat file.

The options I have used –j will ensure JUnit style reports are pushed out of the Soap UI test runner, for local builds you could just make it push out Html reports

-h allows you to specify the host header to use for your Urls, The host header you supply here will override what is stored in the SOAP UI projects , so you run these tests on different sites based on your environment.

I have done a FileUpdate because i wanted to change Urls of the service from the old server to the ones on the new servers, you wont need this and it can be ignored

1:

   2: <Target Name="ConfigureSoapUITests">

   3:    <MakeDir Directories="$(SoapUIDir)\Report"/>

   4:    <ItemGroup>

   5:      <SoapUIProjectFiles Include="$(SoapUIDir)\*-soapui-project.xml"/>

   6:    </ItemGroup>

   7:    <!-- Replace old server urls with new ones -->

   8:    <FileUpdate Files="@(SoapUIProjectFiles)" Regex="$(someurl)" ReplacementText="$(newurl)" IgnoreCase="true"/>

   9: </Target>

10:

  11: <Target Name="RunFunctionalSoapUITests" DependsOnTargets="ConfigureSoapUITests">

  12:    <!--Run all the soap ui functional tests-->

  13:    <Exec Command="&quot;$(SoapTestTool)&quot; &quot;%(SoapUIProjectFiles.Identity)&quot; -h &quot;$(HostHeader)&quot; -I -r -a -j -f &quot;$(SoapUIDir)\Report&quot;" />

  14: </Target>

At this point I hooked up the MSBuild target to run on Teamcity using the MSBuild runner, for the target “RunFunctionalSoapUITests”. The feedback wasn't good

The final bit you need to do is configure Teamcity to read the xml styled junit reports that the test runner is spitting out. You can do this using the Build feature option for XmlReport processing as shown in the screen shot.

Now trigger the build if you have sorted out all the other variables required you can run the build and see the output on Teamcity, pretty good really on test case by test case basis.

For failed tests I added a configuration for artifacts as follows, Teamcity will show you the response but to see the entire request response file , you need to setup artifacts as follows. All failed test cases create a .txt file which ends with FAILED.txt, so I push these as artifacts and i can see this for failed tests,

SoapUI/Report/*FAILED.txt => FailedTests

I find this useful that with little effort so much could be done and feedback is really good

User Story - A Promise to have a conversation

2011-09-06T23:39:00.001+01:00

Time and again it is important to constantly remind your team that a User story is not just a required but is actually the premise on which you promise to have conversations in the team and with the user. It is by no means a finalised description of what the system should be doing. The first time it is written the analyst or product owner only has as much information as the user gives them. This information is pretty much raw most often a wish list off some post-it notes on the edge of the users monitor. It will have information of what the user wants to achieve or the grand plan of how something could be done brilliantly to save money or achieve a business goal. It will not tell you what the user wants the system to do.

This is where collaboration is fundamental to the idea of agile development ( or should i say ADD – Agile Driven Development). The user story is to be evolved by having conversations between various functional experts. By functional experts I mean a QA, a developer, an analyst even a UI designer for that matter. The question is why? I guess it’s because these functional experts can think of the software that is to be built with a view of what the system should do, A user story should convey both what the user wants to do and what the system will do to be complete, clearly the initial draft of these stories didn’t do this.

If asked why collaborate, why don’t we just let each of these functional. The risk is probably as we can all guess

When a developer evolves a story on his own he is going to make sure it is technically brilliant (may be not always) and eventually forget what the user wants , in most cases this conversation ends up in the developer trying to define what the system should do and what the user wants.. yes funny how many over engineered systems have we not seen and been part of in the past

When a UI designer is going to evolve it on his own he is going to make sure it is pretty software and most likely to make it usable but with lack of clarity of the functionality that the user really needs, he just has his wireframes which show the user the dream he wants to have

When a QA is going to evolves this on his own he is going to make sure it is very testable, infact so testable that they start defining behaviour of the application and the implementation of the software even though they may or may not match what the user wants.. oh well make it testable but so testable that the stuff you build is not usable.

The analyst on the other hand is so caught up with making sure he conveys what the user wants he forgets most often how to test the functionality or in some case forgets to tell the team what the system should do , well don’t blame him they are not the functional experts on the technical implementation of a system

We have seen these things happening all the time, any form of methodology without collaboration kind of summarises the situation in which these things happen. Alright then, so we can’t do without collaboration so what now and how far do we go with collaboration? How do we know where to stop, well I am going to have to be vague and say well it’s for the team to figure it out in the context of the system they are working but then, I guess some of the answer lies in the ability of the team and the user to work towards coming up with stories which adhere to INSPECT and the story itself becoming the documentation of the system. As Gojko says in his book “Specification by Example” stories evolve into living documentation of the system. When you can actually read a story and express in simple English the aspects of what the user wants to do and what the system is doing to achieve the users need you could say you have reached that point where you can stop and move on to the next bit of functionality. Again living documentation is not written once; it evolves over a period of time by refactoring constantly; similar to how a domain model is constantly built in code by constantly refactoring and entities in the model. Teams which collaborate constantly recognise this need to bring the stories used to build the system in line with the domain concepts in the system, and vice versa, it is a constant cycle of refactor and improve.. Oh should I say iterate and continuously improve... Rings a bell... Agile?. In the real life, collaboration is underrated by teams and it is something teams think they should do because it needs to be done. Most agile teams do this once for every story (I am laughing already) while estimating the story not so much while actually implementing it. That said there are also teams which constantly collaborate. I guess question is which team are you working in and what are you going to do about it ?

Screw Unit – Teamcity Integration

2011-06-27T09:12:00.001+01:00

I had to setup up client side tests to run for my team on Teamcity. I initially thought I should use rake to do this, but then I had to leverage the fact that my team is comfortable with the .Net stack and not so much with Ruby. At this point I just thought i should use a unit test to run my screw unit test via Watin in a browser. This idea is available in a lot of other blogs for QUnit tests. The unit test opens the suite.html , parses the file and reports if the test failed or passed.This works fine. But then when a test failed I had to either look at the logs of the build or had to navigate to the Url, this feedback was ok but not great

I tried to write a teamcity test runner for screw unit which will send messages to TeamCity , but this was hard work and the effort involved was simply too much

If not real time feedback from a test runner, at least seeing the suite.html as a tab on my build would be good.. so I just pushed the artifacts for the build to include the Screw Unit test pack and set up a new tab in TeamCity server config file (main.config file) called Screw Unit Report. This tab would open the html file for the tests from the artifacts. So now I have TeamCity showing the Screw Unit suite as a tab, that's better, the only thing is when you click on the tab it runs the tests every time, but that's not such a big deal really . The effort involved in setting this up was 30 minutes. (I already knew how to setup tabs in TeamCity )

So to summarize

1. Write a unit test runner which will use Watin to open the Screw Unit test suite.html file.

   1: using System;   2: using System.Collections.Generic;   3: using System.Diagnostics;   4: using System.IO;   5: using System.Linq;   6: using System.Threading;   7: using MbUnit.Framework;   8: using NHamcrest.Core;   9: using WatiN.Core;  10:    11: namespace Tests  12: {  13:     [TestFixture]  14:     [Timeout(600)]  15:     public class TestRunner  16:     {  17:         private FireFox browser;  18:    19:         [SetUp]  20:         public void SetupBrowser()  21:         {  22:             browser = new FireFox();  23:         }  24:         /// <summary>  25:         /// Tests that ScrewUnit tests pass  26:         /// </summary>  27:         [Test]  28:         [Category("ScrewUnitTests")]  29:         public void RunAllTestsFromSuite()  30:         {  31:             var screwUnitTestFile = Path.Combine(Environment.CurrentDirectory, @"Javascript\ScrewUnit\tests\spec\suite.html");  32:             browser.GoTo(@"file:///" + screwUnitTestFile);  33:             browser.WaitForComplete(5000);  34:    35:             var resultsDiv = browser.ElementWithTag("h3", Find.ByClass("status"));  36:             resultsDiv.WaitUntil(() => resultsDiv.Exists && !resultsDiv.Text.ToLower().Contains("Running"), 30000);  37:    38:             AssertThatTestsHavePassed(resultsDiv);  39:         }  40:    41:         private static void AssertThatTestsHavePassed(Element resultsDiv)  42:         {  43:             var resultsArray = resultsDiv.Text.Split(new[] { ' ' });  44:    45:             var numberOfFailures = Int32.Parse(resultsArray.ElementAt(2));  46:    47:             Assert.That(numberOfFailures, Is.EqualTo(0), string.Format("{0}. Click on the Screw Unit Report Tab to see the details", resultsDiv.Text));  48:         }  49:    50:         [TearDown]  51:         public void TearDownTestRunner()  52:         {  53:             browser.Dispose();  54:             Thread.Sleep(2000);  55:             var browserProcesses = Process.GetProcesses()  56:                     .Where(process => process.ProcessName.ToLower().Contains("firefox") && process.StartInfo.UserName.ToLower().Contains("build"));  57:                     browserProcesses.Each(p => p.Kill());  58:         }  59:          60:    61:     }  62:     public static class Extensions  63:     {  64:         public static void Each<T>(this IEnumerable<T> collection, Action<T> action)  65:         {  66:             foreach (var item in collection)  67:             {  68:                 action(item);  69:             }  70:         }  71:         72:         public static void WaitUntil(this Element element, Func<bool> predicate, int timeout)  73:         {  74:             var startTime = DateTime.Now;  75:    76:             while (!predicate())  77:             {  78:                 Thread.Sleep(1000);  79:                 var now = DateTime.Now;  80:    81:                 if ((now - startTime).TotalMilliseconds > timeout) throw new TimeoutException("Timed out waiting for condition to become true");  82:             }  83:         }  84:     }  85: }

2. Push the Screw Unit test suite into the artifacts of your build in the team city configuration of your build

3. Configure the main.config file located at <TeamCity Install Folder>\.BuildServer\configuration\confg to create a new tab.

Run your build and you should be able to see the screwunit report on the build server now

   1: <server>   2:     3: <report-tab title="Screw Unit Report" basePath="ScrewUnit.zip" startPage="tests/spec/suite.html" />   4:     5: </server>   6:  

You could use the screwunit test sample i took from git hub to test this Screw Unit Tests sample

Step by Step - Cucumber, WatiR and Ruby installation tips

2011-06-16T17:37:00.001+01:00

There are few road blocks you hit when you go about the process of installing Cucumber, Watir and Ruby the first time, you have to search all the information and then as you install there are some things that work while some dont , I just thought it may be a good idea to consolidate the information in one place for myself if i do run into this situation of having to install this again. I have tried and tested this thrice and use the same process to install our test agents.

Installing Ruby

#Tip – Choosing the version of Ruby installer

Watir is stable with Ruby 1.8.7 so dont carried away and install 1.9.x of ruby , you learn the hard way that it is not going to work properly.

See Http://watir.com/installation for updates on when 1.9.x support will be provided. Go to http://rubyforge.org/frs/download.php/74293/rubyinstaller-1.8.7-p334.exe download the exe and run the installer. I chose the installation folder to be called just ruby as I want to avoid installing multiple versions for now.

Ruby Path

Check if "c:\ruby\bin" is included in the path (else run PATH=%PATH%;c:\ruby\bin at the command prompt)

Installing the Dev Kit for Ruby

Download http://github.com/downloads/oneclick/rubyinstaller/DevKit-tdm-32-4.5.1-20101214-1400-sfx.exe .

Click on it to extract files to a folder <DEV-KIT-FOLDER>.
Open a command prompt for the <DEV-KIT-FOLDER>.
Run the command “ruby dk.rb init”
Run the command “ruby dk.rb install”

Not sure if you need this but run a “gem update system” and it should say Nothing to update :).

Installing gems

Now at the command prompt

Run “gem install cucumber”.
Run “gem install watir”
Run “gem install “win32console”
Run “gem install rspec”

Installing ANSI con – if you are unable to see colours on your console window when you run a cucumber feature, you may need to install ANSICON

Go to http://adoxa.110mb.com/ansicon . Download AnsiCon 140.
Extract the files.
Open a command prompt for the folder you have extracted the files in
cd to x64 folder if you use a 64 bit machine or x86 folder if you use a 32 bit machine
type "ansicon.exe -i"
Close the command prompt , open a new one

This should be sufficient to run cucumber features now. In a weeks time I will post a project framework with some useful stuff for ruby / selenium / cucumber which can be downloaded so you can go about building tests quickly

ScrewTurn Wiki

2011-06-15T22:55:00.001+01:00

I was looking for a some kind of ASP.Net sample site purely to demo some BDD scenarios at work, but then I wanted to do it on a site which is more complex than the usual ASP.Net sample site made of Customer/Order.

I found a couple of Wikis, but the one that caught my eye was ScrewTurn Wiki. First things first it is free under the GPLv2 license (for more details on commercial licenses see Commercial License Help)

The installation took less than a few minutes using the Microsoft Web Platform installer, You install the Wiki in one of two modes file system storage mode or SqlServer storage mode (just use SqlExpress). To choose which mode you want to install. See Installation Help for more details. Apparently you can go with file storage mode and then switch to the SqlServer data storage mode later (Data Migration)

The fact that you can manage the ScrewTurn Wiki using Microsoft WebMatrix is simply brilliant. The ease of use and ability to be able to publish the Wiki is simply useful. You can pretty much configure your hosting details if you wanted to host something on the internet and keep pushing your changes.

Now for Plugins, quite a lot of them seem to be available. There are vast number of navigational, text editing and data provider plugins. In addition to this you can customise different portions of the Wiki using your own providers , this seems like one of those things that was given a great deal of thought. See Custom Providers

I guess I am very impressed by what the Wiki offers, but looking at the features I am actually wondering if a product which was a Wiki is evolving into a CMS? Not sure, cant say I am bothered either, the only reason I raised that concern is the Wiki as is, is pretty simplistic and this is what appealed to me, building too much into could make it bulky and complex. I am just a developer so I cant give an accurate view of what users of the Wiki would want. On the bright side there are some really new features that are coming and that can be leveraged. V4 CTP offers native Azure support which should be good if you wanted to use Cloud based services I guess. See Roadmap for more details

DDD eXchange 2011 Podcasts

2011-06-13T11:21:00.001+01:00

Attended this conference on Friday (10/06/2011) and was consolidating the links for the podcasts

Some of my favourites are

· Greg Young on Assert.That(We.Understand) – related to TDD
· Udi Dahan on Domain Models and Composite Applications
· Jim Webber on REST and DDD - REST based APIs
· Matthew Wall on REST & APIs in the Guardian's DDD Processes

Podcast Links

The N+1 Iteration syndrome

2010-08-26T23:12:00.001+01:00

I constantly ask myself if i know what is next at the end of a sprint or iteration and that I should make an effort to know what is coming up next, I observe that just like me the members of the team are only focussing only on the card that there magnet is on in the current iteration. Adapting to a constant flow of user stories and requirement is not easy for any team and is as important as focussing on the stories in the current iteration. We as a team focus on the user stories in the board, but it may be worthwhile asking ourselves how many people in the team are really aware of what is coming up in the next iteration. If members in the team were asked to answer to this question honestly you will find that a vast majority probably don’t have much information or are totally ignorant. I prefer the term N+1 for the next iteration. In most teams I have worked this is a problem that is evident in one form or the other and some common symptoms I find are the ones mentioned below.

Symptoms

Analysts find it frustrating that they have to repeatedly read the story out and explain the same story more than a few times.
Team velocity sways massively and the standard deviation to average velocity is quite high
Requirements workshops are almost absent and it seems like analysts are in a different time zone on the user requirements on most occasions when compared to the team.
Team members are not sure about the size of the story and try to come up to a size as close as possible to the rest of the team rather than putting any effort involved in understanding the size of the story.
Constructive discussions, debate and any implementation concerns are almost absent
The team seems to easily agree on the size of the story and gets swayed into a conclusion by anyone who can speak the team into a conclusion
Large stories seem to be finished earlier than they ought to be and some of the smaller stories seem to take more time and some times end up looking like large stories.

This syndrome manifests itself in different ways and consequences range to varying degrees of severity on the functioning of an agile team. The team should address this situation if they do find these symptoms, the effects of not addressing this problem could result in false velocities, skewed metrics, increase in cost of the project and finally manifests itself in a loss of trust from the users for whom we actually work on the project. I wonder if I am making a big deal out of this, but this may be because I perceive the consequences of this syndrome to grow exponentially into bigger problems and can be quite damaging for the future of the team and the project.

We can mitigate some of these symptoms, a few ideas that allow you to improve and move in the right direction are below

Introduce a N+1 sprint section on the left and side of your Kanban or sprint board and line up stories that will flow into the next sprint.
Encourage analysts who are working on N+1 Q to speak about there analysis during your stand ups, this helps spread awareness of the N+1 iteration on a daily basis. Truth is in an iteration the analyst is probably working 50% of their time on the N+1 sprint and the other current sprint.
Encourage your team members to pair with analysts and discuss and learn what they are working on, if you can allow your developers and QAs to pair for 5% of the iteration on a rotational basis with the analyst. These pairing sessions really helps non technical analysts to learn a few tricks and understand why you would think the story is complex or simple
Have mini 15 minute sessions every day after the stand up to pick up one story from the N+1 board and discuss with the analyst, testability and implementation details. This will mitigate the loss of requirements workshop they are long and can be boring anyway , small cycles of these sessions will get the team to be constantly involved in requirements.. the term cross functional teams was not coined just for developers and QAs , it did mean all functions in the project.
Have some ground rules for your planning session,
- Team comes attend the planning session with an awareness of the stories flowing into the Kanban,
- You really don't want estimating to eat up all your planning time, clearly planning is not only to estimate it is also about discussing priorities and setting goals for your iteration, spend some time planning how you would action retrospectives as well.

You will see that the team will at least loose the perplexed “I don't know what you are talking about look “ and the “I cant be bothered” attitude , this could be a good starting point to address the problem. This will allow your team to be more involved in planning as much as they are involved in the progress of the sprints.

Authoring and Automating - User Stories

2010-08-23T23:18:00.000+01:00

In most agile development teams the responsibility of writing user stories falls into the hands of the analyst. The analyst not in all cases may be well versed with the idea of writing stories. This is not because he does not know what to write but sometimes because he does not know the best way to express the story in the chosen story writing platform. This doesn't warrant a developer to pair with an analyst to author a story, In my opinion developers are not welcome to pair with the analyst to author user stories. Allowing this will allow implementation detail to find its way into the stories and sometimes they dictate the users intention.

Authoring stories

The best person for your analyst to pair with should be your QA, this proves to be the most useful.

The QA looks at a story early in the life cycle and ensures all aspects of the story are testable.
The ownership of the story is with the QA and he/she is able to identify any automation concerns of the story..
Any scenarios that have not been through in a story due to data related anomalies are identified.
The QA is involved in this process early on before the iteration in which the story is picked up , this will allow the QA to bring in some valuable information on the size of the story to the planning session.
Since the QA gets an understanding of the story before a developer is involved his view of the story is as close as possible to the users requirement in the story. This important to make sure the intention of the user is not skewed by the understanding of a developer.
The QA is able to identify any smoke tests that may be required to be run when a release is deployed to UAT or Live.

Automating stories

In our current project our QA starts automating user stories when he runs out of stories to test. In most cases the QA to dev ratio is 1:2 or 1:3 and so the QA gets bogged down with implementing acceptance criteria so the team has enough stories to dev on. It helps for devs to pair with QAs to automate acceptance tests and my observation has been the following

On a normal day we developers are more in sync with writing better code than QA’s, developers can always help in writing better test code.
When developers implement the acceptance criteria in the form of Given When Then, they actually are implementing the story itself.
Developers will get an idea of how to implement the story and tests required when they actually develop the story.
Where the story is looking for new elements on the UI, developers can aid in mocking the UI for the story else automating all the steps of the user story could be a night mare for the QA all on his own.

PS : My Selfish reason - Developers learn a new language .. I learnt ruby this way :)

In effect when three different people with different skills are involved in the authoring and automation of the story, this will ensure a lot more analysis happens and more often than not edge cases are discovered ahead of development. Any edge cases which will increase the cost of the story can be identified and a decision made taken if they have any real value in development.

Pair Usefully and Code Effectively

2010-08-20T21:15:00.001+01:00

Pairing is of real value when two developers work with each other in a such a manner that they allow the other person to teach and learn new ways of working. This mutual learning process will ensure that the competency of the team will grow uniformly. In my current job we almost never work alone we always pair. Recent retrospectives have been focussing on how we get the most out of our pairing sessions. Some developers said we don’t swap pairs enough, there were questions about how and when do we determine it is a good time to swap pairs.

Well there is probably no hard and fast rule on pairing but some of the things we seem to recognise in a team are as follows.

1. Story Champs - A story owner or champion should be present on the story who will see the story go through the board

2. Story Progress - Having a story champion will allow you to swap pairs across a story reasonably well without affecting the progress of the story.

3. Keyboard Policy – More often than not this is a reason for frustration among developers in a pairing session. Use TDD to pair effectively.

Eg. Pair made of Dev A and Dev B.

Dev A writes test to fail – Red
Dev B implements code to pass the test – Green
Dev A refactors the code

Alternate between the devs in this cycle will ensure pairing sessions are not driven by just one dev

4.Pairing Overdrive - If at any point during a pairing session one of the two seem to be dictating code for more than a few minutes ( I call this overdrive mode). You should really stop pairing and determine if the pairing session is useful and how it can be made useful , the dev who is on overdrive mode should really take the initiative to bring his/her pair up to speed on the story they are working on and discuss any gaps in each others understanding.. get away from the computer go for coffee break.. really helps.

5. Check-in frequency – When we pair due to reasons beyond our control one of the two dev may have to leave at any time before the other, frequent check-ins will allow you to make sure your CI is effective and also that you can switch machines quickly and effectively.

6. Computer Policy - if a pair is working on machine and dev who owns the computer has to leave, the other dev should be able to continue working on the story ..In such a situation it helps to leave the computer unlocked till your pair can make a check-in and then pull the changes on his computer and continue work, locking your computer and leaving could really be annoying to some one who really wants to continue work.

7. Early Birds –If you come in earlier than your pair, instead of just picking up the story and continuing work it helps to look at your CI server and fix any broker builds. If you do work on the story do make sure you explain what has been done and how far you have progressed so your pair gets up to speed, else you end up with Pairing Overdrive.

8. Privacy Policy – If you are working on your pairs machine and when your pair is not around do not snoop around there mail or respond to IM that they receive even for fun, what seems to be fun may turn out to be an annoyance. Finally there is absolutely no need to get personal during a pairing session, every developer has his own style and strengths it is important to respect that , for all you know they might be really skilled in areas you are not so good yourself..

After every stand up it makes sense to discuss who can swap pairs. Infact before a stand up it helps to be in a checked in state if you want to make sure your team can swap pairs every day

For now this is all.. code carefully and pair usefully :)

Acceptance Testing

2010-08-10T09:21:00.002+01:00

After playing around with Cucumber/ Cuke4Nuke and Specflow.. turns out cucumber is the winner for me.. Cuke4Nuke took me a while to get going and seems considerably slow and is behind cucumber by some distance..

Specflow is alright however the idea of using attributes to match my step definitions is what i didn't like.. integration with VS and being able to use the NUnit test runner are clear winners.. the entry barrier to this is pretty much minimal.

Well as for Cucumber/ Ruby / WATIR, the idea of using RubyMine to write my tests and debug them in the same doesn't make me miss VS integration and debugging.. I don't think i particularly miss writing my tests in different languages.. well i never do so why bother… The choice of gems available for Ruby and dynamic nature of ruby is something that makes the effort rewarding .. Build integration using a Rake runner is easy not much effort either..One other reason is may be because i use this at work .. there is yet one more to be looked at which is the WebDriver API for Selenium 2.0. http://google-opensource.blogspot.com/2009/05/introducing-webdriver.html ..

I took some time to play around with these since I don’t like skimming through the surface ..

SqLite Manager plugin for firefox

2010-04-28T23:44:00.001+01:00

There is a good chance some of you use this or know about this already. However this is kind of new stuff for me.. In my current project we use SqLite to run our integration tests for the repository tests and the some of the rhino ETL process classes. We use SqLite as a file based database rather than an in memory instance and while i was debugging through the code I hit a point where i wanted to see how the schema was created and the data stored, so i can be sure the foreign keys where stored with the right values

SqLite Manager is a useful plugin for Firefox 3.6. This is browser based and allows you to work on the file based database just like Sql Server Enterprise Manager allows you to work with SQL 2000. This is a really useful tool when you are writing integration tests with SqLite.

You can get the plugin at , you may need firefox 3.6 or above

https://addons.mozilla.org/en-US/firefox/addon/5817

NUnit - Teamcity - TestCaseSource tip

2010-04-25T22:48:00.010+01:00

If you are using features from NUnit 2.5 or above TestCaseSource attribute may sound familiar. This is a very useful feature in NUnit now which helps us reduce the clutter of repetitive code in our test class. I am sure everyone can see how this can be done on the NUnit website as well , but the point of this post is in relation to a problem I ran into when I used the TestCaseSource attribute. To elaborate on this let me use a simple example of a class which calculates the square of an integer.

public class SquareOfNumbers
{
   public static int Square(int x)
{
   return x * x;
}
}

When i first started writing tests i used the TestCaseSource attribute , so my test class looked like the one below

[TestFixture]

public class SquareOfNumberFixture
{
private static readonly Dictionary<int, int> _testData = new Dictionary<int, int>();

public static Dictionary<int, int> TestData
{
   get
   {
       _testData.Add(2, 4);
       _testData.Add(0, 0);
       return _testData;
   }
}

[Test, TestCaseSource("TestData")]
public void Test(KeyValuePair<int, int> keyValuePair)
{
   Assert.That(SquareOfNumbers.Square(keyValuePair.Key ), Is.EqualTo(keyValuePair.Value));
}
}

These tests run successfully, but when run as part of my teamcity build there is no detail about what the tests where as in there was no reference to the name of my test method. I did spend sometime trying to fix this and found a couple of issues.

1. Team city was not using the same nunit version that I was using to write tests, so the plugin had to be updated on team city, this way i made sure the build uses the same nunit framework as my source code does

2. I changed my tests to use TestCaseData class in conjunction with the TestCaseSource attribute, this allowed me to specify a name for each test scenario , so after refactoring the test class , I can now see the test names on the nunit test report generated by Team city, the code for the new test class is shown below

In addition to fixing the problem , TestCaseData class made the test code more readable.

[TestFixture]
public class SquareOfNumberFixture
{
  public static IEnumerable TestData
  {
      get
      {
          TestCaseData squareOfZeroTest = new TestCaseData(0).Returns(0);
          squareOfZeroTest.SetName("SquareOfZeroReturnsZero");
          yield return squareOfZeroTest;

          TestCaseData squareOfIntegerTest = new TestCaseData(2).Returns(4);
          squareOfIntegerTest.SetName("SquareOfTwoReturnsFour");
          yield return squareOfIntegerTest;
      }
  }

  [Test, TestCaseSource("TestData")]
  public int TestSquareOfNumbers(int x)
  {
      return SquareOfNumbers.Square(x);
  }
}

On second thoughts further refactoring the class makes it more readable if i had done the following…

[TestFixture]

public class SquareOfNumberFixtureNew
{
  [TestCase(0, Description = "SquareOfZero", Result = 0)]
  [TestCase(2, Description = "SquareOfTwo", Result = 4)]
  [Test]
  public int TestSquareOfNumbers(int x)
  {
           return SquareOfNumbers.Square(x);
  }
}

Acceptance Testing – Cucumber? Specflow? Cuke4Nuke

2010-04-22T01:38:00.005+01:00

I have been pretty much dormant on this blog due to my part time MBA coupled with a some tight deadlines at work. And to get me back into the blog bandwagon I just thought I will do this small pet project of mine outside work for which I have been exploring the acceptance testing tool to use infact functional test tool which will allow me to translate my acceptance criteria on stories to tests from plain english.

I found something about Specflow on a few blogs,it is a set of libraries to write specifications using Gherkin. You can check the screencast here or read this post from Ryan Lanciaux. SpecFlow can use either NUnit or MsTest as the engine. You could use specflow and drive WATIN.

Being a .Net developer I am quite inclined to use Specflow and Watin after having had a look at it mainly because i dont need to learn a new language and i can use my c# knowledge to write tests, However previous experience with WATIN a couple of years was not great and it always seemed be behind some of the things you could do with WATIR, hopefully this has changed now, In my current job we use Cucumber + Watir to write acceptance tests and we use Ruby, Although I found it a bit steep on the learning curve initially and mainly because i havent worked in anything but .Net. Now after a couple of weeks into it I am beginning to think if this is may be the right choice, for e.g I am not even a novice in Ruby but RubyMine from JetBrains makes it easy when you are a new bee.

I still didnt want to give up exploring a .Net + cucumber combination and my search led me to this article by Richard Lawrence in his blog which compares the pros and cons of Watir and Watin.. go to . Interestingly Richard and a group of other people started driving this project called Cuke4Nuke sometime ago now which should allow us to write tests in C# for cucumber, That sounds great to me, because I am not looking at any advanced usage of cucumber, i guess my needs are pretty basic for now, I have not used it yet but all i can say at the moment is having looked at the screen cast and the background the creators of the project have given it quite a bit of thought on performance (if you read the blog you will find that they did explore using Iron Python over WATIN which was horribly slow).

A couple of months ago they released 0.3 which supports almost everything you can do with Cucumber in Ruby or Java, making C# a first class language for Cucumber. (The only missing features are small things like tags on Before and After hooks and a richer Table object.)

To install and have a play with this go to the Cuke4Nuke Wiki at http://wiki.github.com/richardlawrence/Cuke4Nuke/

Screen Cast on Cuke4Nuke and WATIN

http://www.richardlawrence.info/2009/12/03/screencast-testing-web-applications-in-net-with-cuke4nuke-and-watin/

I am going to give this a go for a spike on my new project so lets see how this goes :).

Nant Task for Sandcastle

2008-09-19T11:56:00.001+01:00

I have been thinking of writing a Nant task for Sandcastle for quite some time so I decided to knock something up . I have been fairly busy at work and havent been able to spend time on this. As i find some time on the train I just finished this piece of code.

I am quite a fan of Nant and the Nant contrib project has a task for NDoc which kind of inspired me to do one for Sandcastle. Sandcastle supports the new frameworks in .Net quite well and most people who were using NDoc would have migrated to Sandcastle due to its capabilities for build purposes.

This task is intended to allow users who do not want to use sandcastle command line builder in there nant scripts as an external process. The logging of this task and the sandcastle output is also streamed into the nant log.

I have based my task schema on the bare minimal that will require direct configuration in nant. In any case you should be able to do all the configuration in the sandcastle project. What’s shown below is purely a because i am a developer and I need these features and so i think everyone is going to be happy with it :). '

The following readme is placed in a text file along side the installer zip file.

The Sandcastle for task is built using the following components
Nant 0.86
Sandcastle Help File Builder
Sandcastle UI Builder

The core components required by the task are installed by the installer.

The installer allows you to install the task into a folder that you choose but does not check to see if Nant is installed in the same directory
Generally C:\Program Files\Nant\bin
Installing the task in the same folder as Nant is the only scenario that has been tested.

The path of nant should be added to the path variable as instructed in the Nant installation instructions

In addition to installing the task the following configuration for the Nant.exe.config file needs to be added.

Under the elements
<configuration>
...<nant>
......<frameworks>
...........<platform>
................<task-assemblies>

<include name="NAnt.Contrib.Tasks.Sandcastle.dll"/>
If you wish to add the Sandcastle task into another folder, the Nant probing paths need to be set to look at this folder. It is not recommended however

Source code is also provided and feel free to customise it or edit it , The source code is present at Install folder\sandcastle task\Src

I have tested this on my system by installing sandcastle, nant and this task and it works fine. Obviously i am saying “Works on my machine”, if you see any problems using it please let me know about it.

The Sandcastle task schema for the Nant script should be as below, project and output are the only two required attributes,

<sandcastle 
    project="${Sandcastle project file path}" 
    output="Output location for Sandcastle files">

    <showMissing 
        remarks="false" 
        params="false" 
        returns="false" 
        values="false" 
        namespaces="false" 
        summaries="false">
    </showMissing>
    
    <document 
        internals="false" 
        privates="false" 
        protected"="false" 
        attributes="false"
        copyrightText="" 
        feedbackemail="" 
        footer="">
    </document>
</sandcastle>

e.g Nant build file

<?xml version="1.0"?>
<project name="Hello World" default="build">
    <property name="projfile" value="C:\Documentation.shfb"/>
     <target name="build">
        <sandcastle project="${projfile}" output="C:\Documentation\Help">
<document copyrightText="Copyright@ TSQLDOTNET Limited" feedbackemail="srinivas.s@tsqldotnet.com"/>
 </sandcastle>
    </target>
 </project>

Link: Nant Sandcastle Installer

The download consists of the prerequisites hence it is bulky at 40MB, The prerequistes include Sandcastle installer , Win 3.1 installer and DotnetFx

Google Chrome

2008-09-02T22:49:00.001+01:00

Now as is all over the news i went about downloading the browser to get a feel of it. The download of the click once installer was about 474 KB so how big is that :) really neat start, then the download of the installer and install takes about 1 minute (4 MB line) and in another minute it imports all settings from IE. Now that was really impressive , two and half minutes all set and up and running.

Features that clearly stand out are the Search history, Dynamic Tabs and the simplicity of book marking pages, there is more but these make be happy already :).

A useful thing is the Search your history box, I have wished for something like this for ages and its nice to see this feature. Hope Google does this for favourites as well.. I having accumulating favourites for the last seven years and i some times wish i could search through surely useful to me :)

The UI is definitely better than the heavy IE7, tabs are not new in a browser these days just checkout the dynamic tabs feature in Google Chrome, it is definitely cool , drag the tab out and see how it works

Bookmarking is easier and the download feature is different to other browsers.

Seems like Google has just started , I am already thinking of using this browser , but i am not sure about any bottle necks .. gotta wait and see

I am quite happy with what the beta offers, if Google manages to hold off IE , I bet we could see this being used more widely..

If you want to download go to http://tools.google.com/chrome/?hl=en-GB

Determine a Leap Year?

2008-08-13T13:27:00.001+01:00

Armando Prato Armando Prato had written a SQL tip on how to determine a leap year the trick used is pretty neat, His example is in TSQL as below , but i guess we could use the idea in any language

        1: create function dbo.fn_IsLeapYear

       2:  (@year int)

       3: returns bit

       4: as

       5: begin    

       6:    return

       7: (select 

       8:     case datepart(mm, dateadd(dd, 1, cast((cast(@year as varchar(4)) + '0228') as datetime)))     

       9:     when 2 then 1     else 0     

      10:     end

      11: )

      12: end

      13: go

I like the idea of appending 0228 to the year and finding out if it is a leap year, The function takes in the year, appends '0228' to it (for February 28th) and adds a day. If the month of the next day is a 2, then we're still in February so it must be a leap year! If not, it is not a leap year.

In C# this could be something like , I just thought its worth mentioning this on the blog for my record at the least

       1: bool isLeapYear = ((new DateTime(<int year value>, 02, 28)).AddDays(1).Date.Month.ToString() == "2")
  

Tracking v/s Capturing changes

2008-08-08T23:40:00.001+01:00

Change Data Capture has by far been my favourite feature everytime i think about SQL Server 2008. The really neat bit is something I missed during my learning process, There are two flavours to capturing changes, the change itself and the data that has changed, this is what distinguishes Change Data Tracking and Change Data Capture in SQL Server 2008

Change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system.. When you want to stage data in logical blocks such as a website publishing engine, or a clearing system this feature could prove very useful. This is mainly because of the granularity of the changes that are captured and the nature in which they are stored providing no coupling to the object whose changes are captured.

Change tracking on the other hand captures the rows in a table that changed, but does not capture the data itself. This allows applications to determine rows that have changed only with the latest row data being available in the user tables. Therefore, change tracking is more limited in the historical questions it can answer compared to change data capture. However, for applications that do not require historical information, there is far less storage overhead because of the changed data not being captured. It is the data captured which causes the database to grow. A synchronous tracking mechanism is used to track the changes and has been designed to have minimal overhead to the DML operations.

Either of these features can be used to synchronize applications or there database engines. Synchronization can be implemented in applications in two directions, one-way and two way.

In One-way synchronization applications, such as a client or mid-tier caching application, can be built that use change tracking. e.g, a caching application requires data to be stored in the database and to be cached in other data stores. In this scenario the application must be able to keep the cache up-to-date with any changes that have been made to the database. There are no changes to pass back to the Database Engine.

In two way synchronization, the data in the Database Engine is synchronized with one or more data stores. The data in those stores can be updated and the changes must be synchronized back to the database. A good example of two-way synchronization is an application which is occasionally connected such as a mobile application. In this type of application, a client application queries and updates a local store. When a connection is available between a client and server, the application will synchronize with a server, and changed data flows in both directions. In two-way synchronization applications must be able to detect conflicts. A conflict would occur if the same data was changed in both data stores in the time between synchronization's. With the ability to detect conflicts, an application can make sure that changes are not lost.

So my misinterpretation that change capture and change tracking meant the same proved to be wrong. This really useful feature could be put to use effectively in a scalable manner by choosing the right flavour of change capture which is based on the needs or nature of your application. There is no denial that applications on SQL Server versions prior to 2005 will need a major overhaul if there is an existing mechanism in place, that said it is best not to underestimate the implementation of Change Data Capture for a existing application. However new applications could base there designs around this feature and seek to benefit rapidly.

On this note a quick note to people who use Log shipping, The feature is useful when batch processing of transactions is to be done on a regular frequency, however it is still limited in not being able to identify each transaction individually, there is no denial however that is the best choice for Disaster Recovery options.

Concurrency Control in .Net Data Tiers

2008-07-28T18:06:00.001+01:00

Most of our data in applications is stored in relational databases, although there are other ways of storing data I guess about 75% of applications use RDBMS. While using these databases we write .Net code which functions correctly but may incorrectly write, read or handle data in the run time. e.g. I have found a common case where developers tend not to use some of the features built into the RDBMS system under the covers, and concentrate so much on getting the application tier right, that they fall into pitfalls that arise out of data handling , One such issue is concurrency of data, It is one of those things developers don’t look at when they hit a data handling issue. a consequence is a lack of transaction capabilities , sometimes they don’t analyse that the root cause could be data handling. Agree or deny I at the least cannot think of applications used for business not to have transactions. More often than not business processes in a application translate into a transaction block or unit.

Concurrency is one such issue which can be controlled and exercised. It can be in an optimistic or pessimistic mechanism, whatever be the mode exercised it is important to understand the concept and how it can be exercised.

Optimistic concurrency lets the last update succeed and can make data viewed by the end user dirty, if a update happened since data was displayed to the user. It is the applications responsibility to determine stale data and then decide to perform an update based on the decision it takes. Optimistic concurrency is used extensively in .NET to address the needs of mobile
and disconnected applications, where locking data rows for prolonged periods of time would be infeasible. Also, maintaining record locks requires a persistent connection to the database server, which is not possible in disconnected applications

In the pessimistic scenario, read locks are obtained by the consumer of the data and any updates to the same data are prevented, Pessimistic concurrency requires a persistent connection to the database and is not a scalable option when users are interacting with data, because records might be
locked for relatively large periods of time.

These are the mechanisms you adopt. To implement this in a data tier of a .Net application developers use transactions

Most of today’s applications need to support transactions for maintaining the integrity of a system’s data. There are several approaches to transaction management; however, each approach fits into one of two basic programming models:

Manual transactions. You write code that uses the transaction support features of either ADO.NET or Transact-SQL directly in your component code or stored procedures, respectively.

Automatic transactions. Using Enterprise Services (COM+), you add declarative attributes to your .NET classes to specify the transactional requirements of your objects at run time. You can use this model to easily configure multiple components to perform work within the same transaction.

To decide if u want to use transactions in SQL code, ADO.Net or Automatic transaction I found a simple decision tree which allows you to make a decision on which method to use to implement transactions, of course you make the decision if you need to use transactions in .Net

Astoria Data Services – IIS + SQL Server ?

2008-07-25T21:35:00.001+01:00

A few months ago I had the opportunity to preview Astoria Data Services in a NextGen User Group meeting and I was thinking I have seen this before, but i failed to recollect where at that time, read the following you will see why i am pointing this out. At the time of preview MS promised to add security to Astoria, Basically all I could see was SQL queries on the browser URL / request URL

When you install IIS on a machine , in management console of windows you have an option to configure SQL XML support for IIS. Do this and then configure a Virtual directory in IIS, which offers quite a list of decent security settings. Configure these and browse to the URL with a SQL query in the URL and see what happens, there ain’t much difference?

I am not cynical but i want to point out this is not new, we will however have to wait and see what additional features are offered by Astoria before we can comment

Oracle 9i and 11g Data Access Components for .Net

2008-07-23T19:21:00.001+01:00

I am currently working with SQL Servers and Oracle servers and products communicate with both databases, In my previous job where we used only SQL Server everything was easy as it was native to Microsoft’s products, however the world of .Net with Oracle has its pain points. A particular scenario i came across today was having a web server with Oracle 9i Data Access Components and 11g Data Access Components on the same server and getting them to work for two different ASP.Net sites. I initially installed the ODAC components without the client thinking it was enough for the ASP.Net site to work, however as it turns out there is more to it than i thought it would be

When i started Oracle 9i client components where already installed on the server and the Products using 9i components where working fine. However the moment I installed the 11g Data Access Components the Products using 9i stopped working throwing exceptions and neither where products using 11g components working. So i had to uninstall 11g Data Access components and leave Product A working. So sort this problem i googled’ a lot and found information scattered all over the place, In my need to have everything in one place i thought i jot down a few notes. So to fix the issue I had to do the following

Install a Oracle 11i client home.

Install Oracle 11i Data Access components into the home created in the previous step

The we have to make sure the odp.net folder which resides under the home directory has permissions for ASP.Net and Network Service accounts for read and execute on all sub folders and files.

It doesn’t end there if your connection strings use TNS names configure tnsnames.ora in the network\admin folder of the 11i client folder. In our case the 11i Components where used to access 9i databases so i just copied the 9i tnsnames.ora file into the 11i client.

Once all this is done you should be good to go with your applications. Most provider related exceptions should be resolved when you follow this procedure.

Through with the 2nd year, 1 more to go

2008-07-23T11:28:00.002+01:00

La la lalalalalala !!! Just checked my 2nd year MBA results, quite a relief to have passed with some good scores.. I was a bit worried I wouldnt get through... considering the power naps between assignment work ...

Processes, Engineering and Organisations

2008-07-22T22:24:00.001+01:00

I reflect on my miniscule career of eight years and some management studies and I find myself thinking about how i would want to do business or run an organisation, I understand profit is the basis for any business being brutally factual about it , but my point is as profit increases and businesses grow rate of returns decrease due to scalability and efficiency issues. As I start my rant I have to mention there is a clear difference in engineering process and quality processes. Processes which are used to engineer a product are different from processes using to perform business. Trying to smudge one with the other in the name of organisational vision is in itself a game most service providers play and the IT industry is full of such innuendos , does any organisation care to mention what engineering processes are going to be used in there project tenders, I guess not.. some how the most important things for engineering which is the task in hand fail to make the list of priorities. We are too busy to pay attention to detail and want the bare bones in place.

I have been working on improving engineering processes for sometime now and have been at both ends, creating and consuming processes. Some organisations use the fixed cost project model and would probably say I will pass as the stakeholders are only interested in there projects and not the engineering process itself, however what they do like is a accreditation such as ISO etc. Even though the PMO as a functional body exists, it doesn't really work as a unified body other than trying to sort out dependencies and integration issues and they wonder why most projects are over budgeted and running longer they should be , If these organisations join the bandwagon of adopting a process like Scrum, Prince or CMM they are not going to find much mileage unless they have there fundamentals sorted out , simple things like infrastructure, configuration management and engineering processes some how manage to get to the list of requisites of a project only at the end of a disaster, or in some cases where you have outsourced your development in a Root Cause Analysis, the funny thing about the RCA is in my experience no one bothers or cares to look at it and there are no lessons learnt either, funny the person who has to come up with it also has mask the most obvious factors which increase cost..

To run an organisation with innovative engineering and quality processes takes more than training a bunch of individuals on some process and asking them to get on with it, Organisational leaders such as CTO’s and CEO’s need to understand that unless it is pipelined form the top in the form of actions, they are not going to maximise return on investment. Unless they do so , PMO' bodies will not action anything because at the moment every organisation is so concerned about short term goals and profits that they have laid the rules of sustainability in business to rest, It is not that organisations don’t do enough , it is just that they try to do something only when the eggs are rotten..

Oh i work in IT so the references to CMM/ Scrum/ Prince you can take anything for an example Six Sigma, ISO, TQM , I am on a train at 10 in the night and this is so not what i should be thinking about

Data Modelling Jazz

2008-07-09T13:01:00.001+01:00

When we often think of data modelling it is a pretty picture created in Visio and for the some one more serious about data modelling it is representing entities, attributes and relationships in a meaningful manner. I didn’t realise modelling languages are different and a tool such as Visio supports/ works on such languages. e.g IDEFIX(Integration Definition for Information Modelling) is modelling language .. Woah that definition really woke me up .. and as usual this was developed in the US Airforce in 1985

The primary tool of a database designer is the data model. It’s such a great tool because it can show the details not only of single tables at a time, but the relationships between several
entities at a time. Of course it is not the only way to document a database;

• Often a product that features a database as the central focus will include a document that lists all tables, data types, and relationships. (developers think can’t be bothered)
• Every good DBA has a script of the database saved somewhere for re-creating the database. (developers think am still not bothered)
• SQL Server’s metadata includes ways to add properties to the database to describe the objects. (developers by now would think oh get a life will you).

Some common terms you would come across are Entities which are synonymous to tables in database, attributes which are synonymous to column definitions in a table and relationships represent how two entities relate to each other. We represent these pictorially or grammatically in written text Anyway my idea of blogging about Data Modelling was to drop a few notes on some practices we could adopt while modelling data.

Entity names There are two ways you can go about these: plural or singular. Some argue tables names should be singular , but many feel that the table name refers to the set of rows and should be plural. Whatever convention you choose be consistent with it, mixing and matching could end up confusing the person reading the data model.
Attribute names: It’s generally not necessary to repeat the entity name in the attribute name, except for the primary key. The entity name is implied by the attribute’s inclusion in the entity. The chosen attribute name should reflect precisely what is contained in the attribute and how it relates to the entity.
Relationships: Name relationships with verb phrases, which make the relationship between a parent and child entity a readable sentence. The sentence expresses the
relationship using the entity names and the relationship cardinality. The relationship sentence is a very powerful tool for communicating the purpose of the relationships with non technical members of the project team (e.g., customer representatives.
Domains: Define domains for your attributes, implementing type inheritance wherever possible to take advantage of domains that are similar. Using domains gives you a set of standard templates to use when building databases that ensures consistency across your database.
Objects: Define every object so it is clear what you had in mind when you created a given object. This is a tremendously valuable practice to get into, as it will pay off later when questions are asked about the objects, and it will serve as documentation to provide to other programmers and/or users.

Implementing Scrum

2008-06-26T01:17:00.001+01:00

8 Questions to Answer Before You Begin

Meet the Antipreneurs

2008-06-26T01:02:00.001+01:00

They profit from the free market, but they criticize it, too. So how do they get away with it?

http://www.businessweek.com/magazine/content/08_66/s0806032873701.htm?chan=magazine+channel_bwsmallbiz+--+top+stories

Iteration Length?

2008-06-25T00:09:00.001+01:00

Our organisation has always used two week iterations and sometimes we do wonder if two weeks is optimum, Well the first thought was for enhancement projects two weeks fits the bill, these could be projects where the solution to implement the features is available to the team, but for a brand new project which includes a technical learning curve and innovation, three weeks might be be apt. This is subject to discussion with the teams. Although Scum recommends a 30 day iteration, 2 week iterations yield results and 3 week iterations will yield innovative results. At the end of the day both iteration lengths are within the realm of Scrum and it is a variable available to teams and the product owner, how it is varied or used as long as the goals of the release are met, is not important.

Based on previous experience with two week iterations I have made some observations which kind of remind us the strength of the two week iteration

Two weeks is just about enough time to get some amount of meaningful development done.
Two week iterations indicate and provide more opportunities to succeed or fail.e.g, within a 90 day release plan, 5 - 2-week iterations of development and 1 stabilisation iteration at the end make it possible to have checkpoints on the way to the release.
The 2-week rhythm is a natural calendar cycle that is easy for all participants to remember and lines up well with typical 1 or 2 week vacation plans, simplifying capacity estimates for the team.
Velocity can be measured and scope can be adjusted more quickly.
The overhead in planning and closing an iteration is proportioned to the amount of work that can be accomplished in a two week sprint.
A two week iteration allows the team to break down work into small chunks where the define/build/test cycle is concurrent. With longer iterations, there is a tendency for teams to build a more waterfall-like process.
Margin of error for capacity planned and available is lesser in two week iterations.

Well the above is on the basis of what I have observed and may be different in your organisation.

Five Levels of Planning in Scrum

2008-06-24T02:18:00.001+01:00

In agile methods, a team gets work through iteration planning. Due to the shortness of the iteration a planning gains importance than an actual plan. The disadvantage of iteration planning when applied to projects that run for more then a few iterations or with multiple teams is that view of long term implications of iteration activities can be lost. In other words: the view of ‘the project as a whole’ is lost.

Planning activities for large-scale development efforts should rely on five levels

• Product Vision
• Product Roadmap
• Release Plan
• Sprint Plan
• Daily Commitment

Each of the five levels of planning help us address fundamental planning principles of priorities, estimates and commitments.

Principles of SOA

2008-06-20T07:35:00.001+01:00

Although there is no official standard for SOA, the community seems to agree that there are four guiding principles to achieve SOA.

Service boundaries are explicit.

Services expose business functionality by using well defined contracts, and these contracts fully describe a set of concrete operations and messages. Implementation details of the service are unknown, and the consumer of the service is agnostic to this, so the technology platform of the service is irrelevant to the client. What is relevant though is where the service is present so that the client can reach it to consume it.

Remoting, WCF and Web Services all support this principle. But what distinguishes WCF is its ability to answer some of the limitations in the other technologies. e.g for Remoting to be used, the underlying CLR types have to be shared between the client and the service, not so in WCF. Web Services with WSE allows a service to address this principle as well.

Services are autonomous.

As previously mentioned services expose business functionality, to achieve this they encapsulate the business functionality, what this means is that the service should encapsulate all tiers in the functionality from database access to business tier and the contract itself. At the end of the day the service should be replaceable and movable without affecting the consumer. As a rule of thumb to achieve this any external dependencies should be eliminated during the design process. Any component changes in the service should change the version of the service as a logical unit. This is otherwise called atomicity.

Clients and Services share contracts, not code.

Given that we said service boundaries are explicit, the contract enforces the boundary between the client and service, and this leads us to conclude that a service once published cannot be changed and all future versions should be backward compatible. There is an argument that contracts may or may not be tied to a particular technology or platform. I am unable to comment on this but seems to be only fair is as long as the service can be consumed by a client and the client can remain agnostic to the technology used to implement the service, this principle is achieved.

Compatibility is based on policy.

Contracts define functionality offered by a service, while a policy defines the constraints imposed by the service on the consumer for attributes like security, communication protocol and reliability requirements. Prior to WCF , Web Services with WSE 3.0 helped developers achieve this, I have had the opportunity to do this on .Net 2.0 using WSE and i can say that this is pretty crucial to how your service behaves with the consumer :). At the end of the day the policy is also exposed in WSDL to the client , so that client knows the constraints imposed by the service.

As a architect/developer we may try to meet the principles listed above in our applications, but due to the influence of technology in implementation and deployment we may not be able to abide by these principles strictly, but that is probably alright. For e.g if we had three Services which encapsulate functionality for order processing, accounts and sales, we cant really have three databases for each service to work individually, we may have a reporting service which may need access to the three databases, in which case we may consolidate everything into a single database, and use schemas in the database to isolate functionality at the database level, then build a database access layer which servers the functionality required by the service. Relational data seems to be quite limiting in this respect, but would the Entity Framework allow us to address, May be may be not it is yet to be seen

WCF Fundamentals

2008-06-19T13:00:00.000+01:00

I have been trying to read some stuff on some WCF concepts and thought it would be useful to jot down some of the concepts outlined by Michele Leroux Bustamante in the book "Learning WCF" for reference purposes. Although it is pure theory it is useful to get an understanding of WCF. If you have used WSE before these may seem familiar ..

Messaging, Serialization and RPC

Enterprise applications communicate with each other using remote calls across process and machine boundaries. The format of the message is what determines the application's ability to communicate with other applications. RPC and XML are two common ways of achieving this. RPC calls are used to communication with objects or components across boundaries (process and machine implied). A RPC call is marshaled by converting it into a message and sent over a transport protocol such as TCP. At the destination the message is unmarshaled into a stack frame that invokes the object. Generally a proxy is used at the client and stub at the destination to marshal and unmarshal calls at either end points. This process is serialization and deserialization. Now the proxy and the stub can use only. Remoting in .Net works with this principle of messaging. Now RPC comes in many flavours, but each flavour is not compatible with another , this means it is not interoperable.. so have this applied in a interoperable way we use Web Services or WCF. WCF however wins over the two, RPC and Web Services because of its ability to operate in both RPC style messaging and Web Service messaging. In both cases the service type is used and the life time of the service type can be controlled using configuration settings defined for the service model

Services

In WCF all services use CLR types to encapsulate business functionality and for a CLR type to be qualified as a service it must implement a service contract. Marking a class or interface with an attribute ServiceContractAttribute makes a class a service type or the class implementing the interface a service type. To mark a method as a service operation (in a contract definition) , we use the attrubute OperationContractAttribute.

Hosting

WCF Services can be self hosted or on IIS like ASP.Net applications. The host is important for the service, and therefore a ServiceHost instance is associated with a Service type. We construct a ServiceHost and provide it with a service type to activate incoming messages.The ServiceHost manages the lifetime of the communication channels for the service.

Metadata

The client to understand how it should communicate with a WCF service needs data about the address, binding and contract. This is the part of the metadata of the service. Clients rely on this metadata to invoke the service. It can be exposed by the service in two ways, the Service host can expose metadata exchange endpoint to access metadata at runtime or using a WSDL. In either case clients use tools to generate proxies .

Proxies

Clients use proxies to communicate with the WCF service. A proxy is a type which represents the service contract and hides serialization details from the client. Like Web Services, in WCF if you have access to the service contract you can create a proxy using a tool. The proxy only gives information about the service and its operations, the client will still need metadata to generate endpoint configuration and there are tools available to do this.

Endpoints

When a service host opens a communication channel for a service it must expose one or more endpoints for the client to invoke. An endpoint is made of three parts Address (a URI), Binding (protocols supported), and Contract (Operations). A service host is generally provided with one or more endpoints before it opens a communication channel.

Addresses

Each endpoint for the service is represented by an Address. The Address is in the format scheme://domain:port/path.

Scheme represents the transport protocol. Http, TCP/IP, named pipes or MSMQ are some of them, scheme for MSMQ is net.msmq, for TCP it is net.tcp and named pipes is net.pipe.
Port is the communication port to be used for the scheme other than the default port if required, for default ports it is not required to be specified.
Domain represents the machine name or the web domain.
Path is usually provided as part of the address to disambiguate service endpoints. E.g net.tcp://localhost:9000 or net.msmq://localhost/QueuedServices/ServiceA

Bindings

A binding describes the protocols supported by a particular endpoint, specifically

Transport protocol, TCP, MSMQ, HTTP or named pipes.
Message Encoding format , XML or binary.
Other protocols for messaging, security and reliability, plus anything else that is custom. Several predefined bindings are available as standard bindings in WCF, however these only cover some common communication scenarios.

Channels

Channels facilitate communication between the client and the WCF service. The ServiceHost creates a channel listener for each end point which generates a communication channel. The proxy on the client creates a channel factory which generates a communication channel for the client. Both these channels should be compatible for communication to be successful between the client and the service.

Behaviors

Behaviors are local to the service or the client and are used to control features such as exposing metadata, authorisation, authentication or transactions etc.

SQL Server 2008 RC0 is out

2008-06-18T20:56:00.001+01:00

RC0 SQL Server 2008 for some reason seems to be quietly done, didn't realise it was out, it is available to download at SQL Server 2008 RC

PS: SQL Server 2008 RC0 will automatically expire after 180 days

Enterprise Application Integration

2008-06-15T03:33:00.001+01:00

I have been recently thinking about integration scenarios for different applications and how to take decisions on the way two applications should be integrated.. Infact my question is what are the factors i would consider to make a decision on how two applications will integrate. On the same note as much as there is a benefit that comes out of integration of two applications more often than not there is a resulting consequence based on how the applications integrated

Should the two applications be loosely coupled? This is first one that comes to my mind ,on most occasions the answer is yes it should be loosely coupled, the more loosely coupled the two applications are the more opportunities they have to extend there functionality without affecting each other and the integration itself. If tightly coupled its obvious that the integration breaks when applications change.

Simplicity, if we as developers minimize code involved in the integration of the applications, it becomes easily maintainable and provides better integration.

Integration technology Different integration techniques require varying amounts of specialized software and hardware. These special tools can be expensive, can lead to vendor lock-in, and increase the burden on developers to understand how to use the tools to integrate applications.

Data format , The format in which data is exchanged is important, and it should be borne in mind that the format should be compatible with an independent translator other than the integrating applications itself so that at some point these applications are also able to talk to other applications should there be a need. A related issue is data format evolution and extensibility and how the format can change over time and how that will affect the applications.

Data timeliness , we need to minimize the length of time between when one application decides to share some data and other applications have that data. Data should be exchanged in small chunks, rather than large sets. Latency in data sharing has to be factored into the integration design; the longer, the more opportunity for shared data to become stale, and the more complex integration becomes.

Data or functionality, Integrated applications may also wish to share functionality such that each application can invoke the functionality in the others. But this may have significant consequences for how well the integration works.

Asynchronicity, This is a aspect developers start realising after implementation and performance tests fail, By default we think and code synchronously, This is especially true of integrated applications, where the remote application may not be running or the network may be unavailable, the source application may wish to simply make shared data available or log a request.

ASP.Net and COM Interop

2008-06-13T18:03:00.001+01:00

At some point in time we have had to use a COM component in .Net applications as part of migrating legacy code to new technologies, but kind of puts me off is doing it without knowing why we are doing it or at the least how the legacy component works. Visual Studio makes it so easy to use COM components by doing all the work for you under the covers. In this article i would like to see how COM is used and get into some of the aspects of dealing with COM.. in the .Net world. A COM component can be consumed in .Net either by Early binding or Late binding.

Early binding

This is where type information about the COM component is available to the consumer at design time to the consumer, e.g most common thing we do is reference it in VS.Net and the studio runs tlbimp.exe to generate the Interop assembly for consumption by .Net code, this is because .Net needs meta data information about the assembly before hand. Another reason this is most prefered way of consuming COM objects is that Early binding is much faster in its access than Late bindinig, In addition to this developers are able to use the COM object as if it was another .Net object by creating an instance using the new keyword.

Late Binding

Information of the COM component is not known until code is executed or in the runtime.A classic example of this is using HttpServerUtility.CreateObject used in ASP pages You will need to pass the component program ID of the COM component to this method.. Now it is important to consider how COM components are instantiated. In windows XP and 2000 how the COM component is instantiated depends on how the threading model of the COM compoenent is marked in the registry as Free, Apartment, Neutral or Both.

Components marked Free

When we call a COM component marked as free in ASP.Net the instance is on the same threadpool thread that the ASP.Net page started running. The ASP.Net thread pool is initialised as a Multi Threaded Apartment (MTA) and since the COM component is marked as Free, no thread switch is necessary and performance penalty is minimal

Components marked Apartment

Traditionally, business COM components that are called from either ASP have been marked Apartment. The single-threaded apartment (STA) threading model is not compatible with the default threading model for the ASP.NET thread pool, which is MTA. As a result, calling a native COM component marked Apartment from an ASP.NET page results in a thread switch and COM cross-apartment marshalling.

Under stress, this presents a severe bottleneck. To work around this issue, a new directive called ASPCompat was introduced to the System.Web.UI.Page object.

How ASPCompat Works

The ASPCompat attribute minimizes thread switching due to incompatible COM threading models. More specifically, if a COM component is marked Apartment, the ASPCompat = "true" directive on an ASP.NET page runs the component marked Apartment on one of the COM+ STA worker threads. Assume that you are requesting a page called UnexpectedCompat.aspx that contains the directive ASPCompat ="true". When the page is compiled, the page compiler checks to see if the page requires ASPCompat mode. Because this value is present, the page compiler modifies the generated page class to implement the IHttpAsyncHandler interface, adds methods to implement this interface, and modifies the page class constructor to reflect that ASPCompatMode will be used.

The two methods that are required to implement the IHttpAsyncHandler interface are BeginProcessRequest and EndProcessRequest. The implementation of these methods contains calls to this.ASPCompatBeginProcessRequest and this.ASPCompatEndProcessRequest, respectively.

You can view the code that the page compiler creates by setting Debug="true" in the <compilation> section of the web.config or machine.config files.

The Page.ASPCompatBeginProcessRequest() method determines if the page is already running on a COM+ STA worker thread. If it is, the call can continue to execute synchronously. A more common scenario is a page running on a .NET MTA thread-pool thread. ASPCompatBeginProcessRequest() makes an asynchronous call to the native function ASPCompatProcessRequest() within Aspnet_isapi.dll.

The following describes what happens when invoking COM+ in the latter scenario:
1. The native ASPCompatProcessRequest() function constructs an ASPCompatAsyncCall class that contains the callback to the ASP.NET page and a context object created by ASP.NET. The native ASPCompatProcessRequest() function then calls a method that creates a COM+ activity and posts the activity to COM+.

2. COM+ receives the request and binds the activity to an STA worker thread.

3. Once the thread is bound to an activity, it calls the ASPCompatAsyncCall::OnCall() method, which initializes the intrinsics so they can be called from the ASP.NET (this is similar to classic ASP code). This function calls back into managed code so that the ProcessRequest() method of the page can continue executing.

4. The page callback is invoked and the ProcessRequest() function on that page continues to run on that STA thread. This reduces the number of thread switches required to run native COM components marked Apartment.

5. Once the ASP.NET page finishes executing, it calls Page.ASPCompat EndProcessRequest() to complete the request

RESEED

2008-05-22T13:22:00.001+01:00

Just learnt a new TSQL term , although i did know this functionality is achievable in other ways, i hadn't come across this one before. RESEED resets the seed value of the IDENTITY. However, SQL Server 2000 works differently on RESEED for virgin tables when compared with 2005/2008.

For the table_name, to reset the seed value of the identity column to a new_value DBCC CHECKIDENT(table_name, RESEED, new_value) does the trick.

In SQL Server 2000 RESEED always increments the seed value but on 2005/2008, it doesn't increment but starts with the new_value.

Cruise Control .Net farms

2008-05-20T17:33:00.001+01:00

In the present organisation that i work for , we work as four teams continuously developing code and checking it into TFS and as a consequence of this we have adopted the process of continuous integration using Cruise Control.Net . Well using Cruise Control.Net along side Nant to have automated build processes is nothing new, but what we have achieved and are planning to achieve is hopefully a unique implementation of a automated build platform. We have about nine different projects having there own automated builds and since Nant is not able to scale across multiple CPUs, the resources on the server are underutilized and wait times have increased considerably. So as a solution to this we have not installed two instances of Cruise Control on the main build server one called the current build which developers use to build binaries and test , while the nightly build runs on a scheduled basis generating the end product installers. Sometimes people may still need a setup and nightly build allows forcing builds manually using the CC Tray application.

So to achieve this there were a few bottle necks we had to work around.

Cruise Control does not install two instances completely, for starters it does install the windows service for itself when you install a second instance. So you have to manually install the service using the installutil.exe in the .Net framework.
Since you will have two instances building simultaneously you will need to isolate the identities under which these build run and the physical locations of these builds.
The cruise control server web dashboard also has to be configured manually as separate virtual directories.
The cruise control manager for each instance has to be configured to use a different port, the default install of CC.Net using 21234, we could use something like 21244 as a series . In case you have a firewall make sure your network admin allows requests to this port (CC Tray uses these ports to communicate with the server)

Now that said we have a single server which has two instances of Cruise Control, The build outputs are copied on to a network share. We now have another build server where we are going to replicate the set up of the first server and then split the CC projects across the two physical servers to share the load of the builds. It all looks simple, but thanks to the guy who authored a Templated build where I have been able to enhance it to achieve the following, We have been constantly updating the build or the last one year and we are close to achieving end to end automation for the product, so when we check-in code to TFS , we are able to dish out a CD ISO image for the products to the network share in the nightly build. We also have switches as properties in the build scripts which can allow the current builds to create setups incase the nightly build is overloaded. Due to constant development work and support issues we have to squeeze work like this as part of our non functional sprint work. But then scrum treats engineering work as Non functional and is rightly justified in doing so.

Storing Hierarchical Data - HeirarchyID

2008-05-20T00:05:00.001+01:00

In SQL Server 2000 we were limited by the 32 level recursion limit for TSQL, and storing and querying hierarchical data in the form of trees was really difficult and inefficient, We used Cursors or temporary tables to write these queries. But simplicity, maintenance or performances were sacrificed. Ofcourse we could bundle a bit of code in the data layer of your application to share the load however this didn't the solve the problem in reporting scenarios where the data processing was to be done on the database server

As we moved on to SQL Server 2005 it improved because of the introduction of CTE's, CTE's where beautiful solutions to solving querying hierarchical data, I use the word beautiful because it looked nicer on the outset when you used it without knowing the limitations it worked well on development environments For e.g using the AdventureWorks database we could use a CTE to query employee manager data as shown below

WITH UpperHierarchy(EmployeeId, LastName, Manager, HierarchyOrder)
 AS
 (
    SELECT emp.EmployeeId, emp.LoginId, emp.LoginId, 1 AS HierarchyOrder
    FROM HumanResources.Employee AS emp
      WHERE emp.ManagerId isNull
    UNION ALL
    SELECT emp.EmployeeId, emp.LoginId, Parent.LastName, HierarchyOrder + 1
    FROM HumanResources.Employee AS emp
           INNER JOIN UpperHierarchy AS Parent
                 ON emp.ManagerId = parent.EmployeeId
 )
 SELECT *
 From UpperHierarchy

Although this decreased the complexity of writing queries, performance of these queries was still challenged on large databases, the optimisation of the CTE execution plans did improve things but as in any database situation the optimizer is handicapped without indexing capabilities. Indexes reduce the load on the query increasing performance and scalability. In addition in SQL 2005 the underlying storage structure was still something users had to design to suit there requirements. This just got better with the introduction of a new managed SQL CLR data type called HeirarchyID in SQL Server 2008., It is available ready to use in the databarse server now... If you now look back and remember the introduction of the CLR into SQL Server in Yukon you will appreciate this feature even more and how it has been panned out..

This data type does not store the identifier of the parent element but a set of information to locate the element in the hierarchy. This type represents a node in the tree structure. If you look at values contained in a column of HeirarchyID type, you realize that they are binary values.It is extremely compact and supports arbitrary inserts and deletions. As per MS a node in an organizational hierarchy of 100,000 people with an average fanout of 6 levels takes about 38 bits. This is rounded up to 40 bits, or 5 bytes, for storage. Because it stores the elements hierarchy in its entirety it is also indexable now..

As always there are a few limitations

It can hold upto 892 bytes but to be honest that should allow you to span out into a really big tree structure under a single node.
A query with the FOR XML clause will fail on a table with HeirarchyID unless the column is first converted to a character data type. Use the ToString() method to convert the HeirarchyID value to the logical representation as a nvarchar(4000) data type

We can represent the HeirarchyID type in a string format. This format shows clearly information carried by this type. Indeed, string representation is formatted as is:/<index level 1>/<index level 2>/…/<index level N>. This representation corresponds to atree structure . Note that first child of a node does not have a value of 1 all the time but can have the /1.2/ value. So to play around a bit we first need a table with a column of HeirarchyID type and an index on the hierarchy ID column

CREATE TABLE Organization
(
    EmployeeID heirarchyid NOT NULL,
    EmployeeName nvarchar(50) NOT NULL
)

ALTER TABLE dbo.Organization
ADD HierarchyLevel As EmployeeID.GetLevel()

CREATE INDEX IX_Employee
ON Organization(HierarchyLevel,EmployeeID);

To populate the data table we use the CTE we mentioned earlier to just modify the SELECT statement like the following

Insert Into dbo.Organization(EmployeeId, EmployeeName)
Select Node, LastName 
 From UpperHierarchy

Now Hierarchical data can be queried using the functions

HeirarchyID data type can be manipulated through a set of functions.· GetAncestor, GetDescendant, GetLevel, GetRoot, ToString, IsDescendant, Parse, Read, Reparent, Write, for details on the functions please refer to the CTP documentation of Katmai but most of them are self explanatory #

For e.g to see how we use these functions, let us insert a node as the last child of an existing node.To do this we first retrieve the sibling node.

--finding sibling node
SELECT @sibling = Max(EmployeeID)
FROM dbo.Organization
WHERE EmployeeId.GetAncestor(1)= @Parent;
--inserting node
INSERT dbo.Organization(EmployeeId, EmployeeName)
VALUES(@Parent.GetDescendant(@sibling,NULL), @Name)

We do not always want to (or can) recover the sibling node to perform insertion. There is perhaps an implied policy to determine node position. For example, let’s say we have an [order] column which position nodes among its siblings. We can compute node path as string: In this example, since the node @Parent is the root, that will give/<order>/. Thanks to the Parse() function, we can use this value to create the new node.

Declare @Parent As HeirarchyID = HeirarchyID::GetRoot() 
Declare @NewPath As varchar(10)= @Parent.ToString()+ CAST([Order] AS varchar(3))+ '/'
INSERT dbo.Organization(EmployeeId, EmployeeName) VALUES(HierarchyId::Parse(@NewPath),'aChild')

You will have note the new syntax of SQL Server 2008 to declare and assign variables in only one line. :: denotes a static method on a SQL CLR type in TSQL. So what am i getting to finally the CTE is not so much of a beauty anymore, just run this query to see what it returns

Select *
From dbo.Organization
Where @BossNode.IsDescendant(EmployeeId)

If you run this query along side the CTE query and compare the execution plan of these queries you would see why this new feature is being talked about :)

SQL Profiler for 2005 Express Edition

2008-05-19T10:22:00.001+01:00

I was trying to see how i can profile SQL 2005 Express Edition as the management studio does not have a profiler. I found one at the following location though , it is very useful and quite a light weight tool http://code.google.com/p/sqlexpressprofiler/downloads/list

NEW INSERT STATEMENT

2008-05-16T09:17:00.001+01:00

In SQL 2008 some new row value constructors have been added, we are familiar with the INSERT statement which has been around for ages the ANSI way

INSERT INTO Table1 (column1 ,column2 ,... columnN) VALUES (value1,value2,....valueN)

Another way of inserting a single row of data is as follows

INSERT INTO Table1 SELECT value1,value2,....valueN

Similarly for multiple rows of data

INSERT INTO Table1 
SELECT value1,value2,....valueN
UNION SELECT value1,value2,....valueN
UNION SELECT value1,value2,....valueN

Now the new ROW VALUE CONSTRUCTOR allows the following to add multiple rows of data

INSERT INTO Table1(column1 ,column2 ,... columnN) VALUES
(value1 , value2 , ... valueN),
(value1 , value2 , ... valueN),
(value1 , value2 , ... valueN),
(value1 , value2 , ... valueN),
(value1 , value2 , ... valueN),

We normally use INSERT statements in stored procedures using parameters to the stored procedure, so the above row value constructor is not very useful , it seems to be use of a table valued parameter to insert multiple rows of data is a better option in SQL Server 2008

Databases and Software Development

2008-05-16T00:07:00.001+01:00

Database is a form of software development and yet all too often the database is thought of as a secondary entity when development teams discuss architecture and test plans—many developers do not seem to believe, understand or leave alone feel the need to understand that standard software development best practices apply to database development. Virtually every application imaginable requires some form of data store. And many in the development community go beyond simply persisting data, creating applications that are data driven. Given this dependency upon data and databases, Data is the central factor that dictates the value any application can bring to its users. Without the data, there is no need for the application.

The very reason we use the word legacy to refer to an application as old as 3 years is more often than not because of the database. As applications grow in size with new features , the amount of thought put into refactoring front end code or developing new code by developers is not put into the database development, ,so what happens essentially is a situation where your front end is two years ahead of the database , and as time progresses your applications capabilities and features get pulled back by the limitations of database quality and standard. We can all deny this but in reality it results either in a limitation or extra cost on creating work arounds.

The central argument on many a database forum is what to do with that ever-present required logic. Sadly, try as we might, developers have still not figured out how to develop an application without the need to implement business requirements. And so the debate rages on. Does “business logic” belong in the database? In the application tier? What about the user interface? And what impact do newer application architectures have on this age-old question?In recent times I have been able to have a look at technologies like Astoria, Linq and the Entity Model framework and Katmai. I was amazed at how little or no database code needs to be written by a developer who is doing UI or business layer development in a software. At the same time being a SQL Server fan myself.. I was worried that my database skills will slowly vaporise into thin air. Hmm that's not as bad as it sounds. All these new technologies such as Astoria, Linq or the Entity Framework are abstractions of the database and allow developing a logical data layer which maps to a physical database , so all developers who work primarily in the UI level or business layer level will slowly stop doing any SQL code at all, instead churning code that interests them against a logical data layer, but what contradicts this is the need to learn a new syntax in the form Linq, . On the other hand, database development will shift to being the specialist job...the design development and management activities of the database developer and Administrator will begin emerging as specialist skills as opposed to generalist skills in the near future. The future of the database specialist seems to be bright.. but what is to be seen is how organisations look at this shift in software development ideology.. To be fair this model of specialist and generalists is not new..

How to consume a .Net ArrayList in classic ASP

2008-05-15T22:53:00.001+01:00

I first followed the procedure to register a .Net assembly using regasm on the web server as below (http://weblogs.asp.net/dneimke/archive/2004/01/31/65330.aspx)

Make sure your assembly has a strong name to install it in the GAC
Run regasm /tlb “<path of your assembly>”
Add the assembly to the GAC

This allows your assembly to be accessible in your ASP code to create server side objects

In my example I have a .Net assembly which has a class as below

using System; 
using System.Collections; 
namespace API 
{ 
      /// <summary> 
      /// Summary description for Versions. 
      /// </summary> 
      public class Versions 
      { 
            ArrayList versions = new ArrayList();
            public Versions() 
            { 
                  versions.Add(“1”); 
                  versions.Add(“2”); 
                  versions.Add(“3”); 
            } 

            public ArrayList List
            { 
                  get 
                  { 
                        return versions; 
                  } 
            } 
      } 
}

I initially tried to have a method in my .Net class return an ArrayList, Then I tried to create an object of type “System.Collections.ArrayList” in ASP and iterate the object in ASP, as the array list type definition was not supported when the asp code executed. So I created the class above which had a public property having a type of System.Collections.ArrayList

So now in ASP I did the following and it printed out the arrray list on my asp page

<%@ codePage="65001" %> 
<html> 
      <head> 
            <title>Test Page</title> 
            <%   1:  
   2:                   set versions = Server.CreateObject("Versions") 
   3:                   For Each v in versions.List 
   4:                         Response.Write(v & "</br>") 
   5:                   Next 
   6:             
%> 
      </head> 
      <body></body> 
</html>

That can be further enhanced in your ASP code to achieve the functionality you need to

TSQL - GROUP BY and ALL

2008-05-15T22:52:00.001+01:00

We sure have used Group By in our SQL queries to group data and get result sets with aggregates from SQL Server, But before I explain lets create a temporary table with order details table using the Northwind database in SQL 2000

I will just join orders and [order detail] table in Northwind to get the data i need into a temporary table as shown below

SELECT 
  O.OrderID, 
  ProductID, 
  UnitPrice, 
  Quantity, 
  (UnitPrice*Quantity) AS Amount,
  CustomerID
INTO 
  #tempOrders
FROM 
  Orders O 
INNER JOIN
  [order details] D 
ON
  O.[orderid] = D.[orderid]
ORDER BY
  ProductID

So now i have a table called #tempOrders with the order details i need.

Now suppose I'd like to see the customers that were sold Product #1 along with the total amount that they spent. I will usea query with a GROUP BY clause as below with a WHERE condition to filter records

SELECT 
   CustomerID,
   SUM(Amount) AS TotalAmount
 FROM
   #tempOrders
 WHERE 
   ProductID = 1
 GROUP BY 
   CustomerID

Now, let's say that I'd like to see all customers that have been sold any products, but we still just want to see the "TotalAmount" for ProductID #1. For customers that have never ordered ProductID #1, it should output a "TotalAmount" value of 0. One way to do this is with a CASE expression as shown below

SELECT 
  CustomerID,
  SUM(CASE WHEN ProductID = 1 THEN Amount ELSE 0 END) AS TotalAmount
FROM
  #tempOrders
GROUP BY 
  CustomerID

Now this would return customers who haven't purchased Product #1 with a total of 0. In situations like these the SUM(CASE...) expression can be replaced with a GROUP BY ALL.

SELECT 
    CustomerID,
    ISNULL(SUM(Amount), 0) AS TotalAmount
  FROM
    #tempOrders
  WHERE 
    ProductID = 1
  GROUP BY ALL CustomerID

Values that are excluded from the aggregation according to the WHERE clause have NULL values returned, the ISNULL function makes sure all customers who haven't ordered Product #1 have a total of 0 instead of NULL. The ALL option basically says "ignore the WHERE clause when doing the GROUPING, but still apply it for any aggregate functions". So, in this case, the WHERE clause is not considered when generating the population of CustomerID values, but it is applied when calculating the SUM. This is very much like our first solution, where we removed the WHERE clause completely, and used a SUM(CASE...) expression to conditionally calculate the aggregate.

GROUP BY ALL is kind of obscure and neat to know, but not really useful in most situations since there are usually easier or better ways to get this result. This won't work if we want all Customers to be displayed, since a customer must have at least one order to show up in the result.Another limitation is we can not use GROUP BY ALL if we want to return a grand total for all orders

How to consume a .Net ArrayList in classic ASP

2008-05-14T08:52:00.001+01:00

I first followed the procedure to register a .Net assembly using regasm on the web server as below (http://weblogs.asp.net/dneimke/archive/2004/01/31/65330.aspx)

Make sure your assembly has a strong name to install it in the GAC
Run regasm /tlb “<path of your assembly>”
Add the assembly to the GAC

This allows your assembly to be accessible in your ASP code to create server side objects

In my example I have a .Net assembly which has a class as below

using System;                                                     
using System.Collections; 

namespace API 
{ 
      /// <summary> 
      /// Summary description for Versions. 
      /// </summary> 
      public class Versions 
      { 
            ArrayList versions = new ArrayList(); 

            public Versions() 
            { 
                  versions.Add(“1”); 
                  versions.Add(“2”); 
                  versions.Add(“3”); 
            }   

            public ArrayList List 
            { 
                  get { return versions; } 
            } 
      } 
}

So now in ASP I did the following and it printed out the arrray list on my asp page

<%@ codePage="65001" %> 
<html> 
      <head> 
            <title>Test Page</title>
            <%   1:     set versions = Server.CreateObject("Versions") 
   2:                   For Each v in versions.List 
   3:                         Response.Write(v & "</br>")
   4:                   Next
   5:             
%> 
      </head> 
      <body></body> 
</html>

That can be further enhanced in your ASP code to achieve the functionality you need to

PIVOT and UNPIVOT

2008-05-13T11:06:00.001+01:00

There may be situations while writing SQL code where we would like to have totals or aggregates of columns alongside a column, e.g number of points scored by a sales person with their sales, as well as separate sales by month or year.

So lets first create a table which represents sales data as below

CREATE TABLE #tempTest
(
empName VARCHAR(25),
noOfSales INT,
[monthName] VARCHAR(3),
Points INT
)

Just add a few rows of data to this table

INSERT INTO #tempTest VALUES('James',2,'Jan', 2);
INSERT INTO #tempTest VALUES('Jason',1,'Jan', 1);
INSERT INTO #tempTest VALUES('Mark',3,'Jan', 3);
INSERT INTO #tempTest VALUES('Mark',1,'Jan', 1);
INSERT INTO #tempTest VALUES('James',1,'Feb', 1);
INSERT INTO #tempTest VALUES('James',2,'Feb', 2);
INSERT INTO #tempTest VALUES('Jason',3,'Feb', 3);
INSERT INTO #tempTest VALUES('Jason',2,'Feb', 2);
INSERT INTO #tempTest VALUES('Mark',1,'Mar', 1);
INSERT INTO #tempTest VALUES('James',2,'Mar', 2);
INSERT INTO #tempTest VALUES('Jason',2,'Mar', 2);

Now to show how many sales each sales person gets per month we could use PIVOT to achieve this easily

SELECT
empName,
[Jan] AS Total_Sales_Jan,
[Feb] AS Total_Sales_Feb,
[Mar] AS Total_Sales_Mar
FROM
(
    SELECT
      empName, noOfSales, [monthName]
    FROM
      #tempTest
) AS Source PIVOT (SUM(noOfSales) for [monthName] IN ([Jan],[Feb],[Mar]) )AS PivotTable

This query results in the following result

Results Pivot 1

Please note that PIVOT can have only one non pivoted column, Example below shows how PIVOT can be used incorrectly

SELECT
empName,
SUM([Jan]) AS Jan,
SUM([Feb]) AS Feb,
SUM([Mar]) AS Mar,
SUM(Points) AS Points
FROM
(
    SELECT
      empName,
      noOfSales,
      [monthName],
      Points
    FROM
      #tempTest
) AS Source PIVOT (SUM(noOfSales) for [monthName] IN ([Jan],[Feb],[Mar]) )AS PivotTable
GROUP BY
empName

Results Pivot 2

Note that the results are incorrect for Points, they dont sum up correctly for each sales person as expected, this is because you cannot use more than one unpivoted column

UNPIVOT is the exact opposite of PIVOT although the usage of it is likely to be minimal there are chances this may be used in some occassions

Deleting Duplicates using CTE's

2008-05-09T10:34:00.001+01:00

While writing stored procedures and dealing with temporary data within the scope of a stored procedure we come across a situation where our temporary data has duplicate records and we want to get rid of duplicated. What we do normally in SQL 2000 would be to create a new temporary table and select distinct records from the temporary table which has duplicates. Then we select the data from the new cleaned up table.

This is not very performant as is evident, SQL 2005 onwards we have been able to do this with CTE's efficiently as below

Deleting from the CTE actually changes the underlying table. Be careful how you setup your CTE. You could have some unintended deletes without the right logic.

CREATE TABLE #itemtable(
Item_Code varchar(10),
Item_Name varchar(100)
)

INSERT INTO #itemtable(Item_Code, Item_Name)
VALUES ('1','Item 1');

INSERT INTO #itemtable(Item_Code, Item_Name)
VALUES ('2','Item 2');
INSERT INTO #itemtable(Item_Code, Item_Name)
VALUES ('3','Item 3');

INSERT INTO #itemtable(Item_Code, Item_Name)
VALUES ('3','Item 3');

INSERT INTO #itemtable(Item_Code, Item_Name)
VALUES ('4','Item 4');

INSERT INTO #itemtable(Item_Code, Item_Name)
VALUES ('5','Item 5');

INSERT INTO #itemtable(Item_Code, Item_Name)
VALUES ('6','Item 6');

INSERT INTO #itemtable(Item_Code, Item_Name)
VALUES ('7','Item 7');

SELECT * FROM #itemtable;

WITH Duplicates AS
(
    SELECT
      *,
      row_number() OVER(PARTITION BY Item_Code ORDER BY Item_Code) as RowNum
    FROM
      #itemtable
)

DELETE FROM Duplicates WHERE RowNum > 1; --

SELECT * FROM #itemtable;

Table Valued Parameters

2008-05-07T16:07:00.002+01:00

In recent times I haven’t written any complex TSQL code which needed to scale, possible a scenario you may come across while working on a reporting application where most of the processing is done on the database server using TSQL. In SQL 2005 user defined types were available but in Katmai this has been furthered to allow table valued parameters to be passed to stored procedures. Well we can also think of them as instances of user defined types. At the end of the day it boils down to being able to efficient and more performant code , but then this feature seems to have a limitation at the moment.

The table data type allows variables that are, in effect, tables. They also allow you to return tabular data from functions without doing a SELECT at the end of the function. That's great, but don't temporary tables do the same? Yes and No …. While table variables and temporary tables are similar, there are several key differences that determine which option you use.
E.g. Consider the sample below in SQL 2005 to add orders to the database table using TSQL, I will need a stored procedure which takes each column in a row as a parameter, and more over each row needs a round trip to the database server..

CREATE PROC sp_add_order(@order_id int, @customer_id int, @order_total money )
AS
INSERT INTO Order
(
order_id,
customer_id,
order_total
)
VALUES
(
@order_id,
@customer_id,
@order_total
)
GO

Now with SQL 2008 we are able to insert several rows of data with just one round trip to the server by using a user defined type called OrderType which has a table structure. That is efficient and quite performant…

CREATE TYPE OrderType AS TABLE
(
[order_id] int NOT NULL,
customer_id int NOT NULL,
[OrderTotal] [money] NULL
)
GO

ALTER PROC sp_new_order (@order OrderType READONLY)
AS
INSERT INTO [OrderTest]
(
order_id,
customer_id,
[OrderTotal]
)
SELECT * FROM @order

RETURN
GO

The one limitation to note here is the READONLY option on the parameter, At the moment the CTP only allows us to pass read-only table valued parameters, We are unable to use OUT parameters of Table Value Type, It would be great to be able to do this, as we don’t have to make nested calls to stored procedures in reporting scenarios… It is to be seen how this pans out in the final release, although the feature doesn’t look big, the sheer benefits of being able to use Table Valued Parameters comes as blessing in disguise for many TSQL developers.

Spatial Data - Delivering Location based Intelligence

2008-05-06T23:45:00.002+01:00

I have been on and off blogging recently due to my part time course at the Uni which has kept me busy.. By the way I did look through most of the new features on Katmai and actually did a small lunch time session at work on Katmai, one of the topics I did speak in details was spatial data support on Katmai.

SQL Server 2008 provides the geography (for geodetic spatial data) and the geometry(for planar spatial data) data types. Both are .NET CLR types, just like your Hierarchy data type which I will post about in another blog soon. The presence of the CLR in SQL Server provides an avenue for some really new and exciting features in SQL Server. When the CLR was introduced on the Server in Yukon I did think it is a bit too much to be present in a database server, but then now that some of the new features in Katmai have panned out as a result of that it does all seem to make sense now.

The geography data type provides a storage structure for spatial data which is ellipsoidal in nature, and is represented by latitude and longitude coordinates. Typical uses of this kind of data include defining roads, buildings, or geographical features as vector data that can be overlaid onto a raster-based map, basically any round earth data.

The geometry data type provides a storage structure for spatial data which is planar or in other words Euclidean in nature. This kind of data is commonly used for maps and interior floor plans where the curvature of the Earth does not need to be taken into account.

To create data that is of the type geometry or geography we could create instances using input in the format called Well Known Text (WKT) or Well Known Binary(WKB). Well WKB is the format specified by the Open Geospatial Consortium that permits the geometry data to be exchanged between the client and the SQL server. The geometry data type provides properties and methods that are aligned with the Open Geospatial Consortium (OGC) Simple Features Specification for SQL and enable you to perform operations on geometric data that produce industry-standard behavior.

SQL Server 2008 increases the maximum size for CLR types in the database from the 8000 bytes limit that was imposed in SQL Server 2005 to two gigabytes (2 GB), which makes it possible to store extremely complex spatial data elements, such as polygons, which are defined by a large number of points. By storing spatial data in relational tables, SQL Server 2008 makes it possible to combine spatial data with any other kind of business data; this removes the need to maintain a separate, dedicated spatial data store and enables high performance queries that do not need to combine data from multiple external sources. Performance of queries against spatial data is further enhanced by the inclusion of spatial index support in SQL Server 2008.

Both spatial data types in SQL Server 2008 provide a comprehensive set of instance and static methods that you can use to perform queries and operations on spatial data. If we explore these two data types we find that Point, MultiPoint, LineString, MultiLineString, Polygon, MultiPolygon, GeometryCollection are all instances of spatial data types recognized by both geometry and geography data types.

To construct instances of a point, Multipoint etc. from WKT format Katmai provides functions as following
Point instance from WKT input use STPointFromText
MultiPoint instance from WKT input use STMPointFromText
LineString instance from WKT input use STLineFromText
MultiLineString instance from WKT input use STMLineFromText
Polygon instance from WKT input use STPolyFromText
MultiPolygon instance from WKT input use STMPolyFromText
GeometryCollection instance from WKT input STGeomCollFromText

To construct instances from WKB format Katmai provides the following functions

Point instance from WKB input use STPointFromWKB
MultiPoint instance from WKB input use STMPointFromWKB.
LineString instance from WKB input use STLineFromWKB.
MultiLineString instance from WKB input use STMLineFromWKB
Polygon instance from WKB input use STPolyFromWKB
MultiPolygon instance from WKB input use STMPolyFromWKB.
GeometryCollection instance from WKB input use STGeomCollFromWKB

There are also methods which allow developers to compare or determine the relationship between geometrical instances such as the ones listed below

STEquals determines if two instances comprise the same point set
STDisjoint determines if two instances are disjoint
STIntersects determines if two instances intersect.
STTouches determines if two instances touch.
STOverlaps determines if two instances overlap.
STCrosses determines if two instances cross .
STWithin determines if one instance is within another .
STContains determines if one instance contains another.
STRelate determines if two instances are spatially related
STDistance determines the shortest distance between points in two geometries

Equivalent set of functions and methods are available for Geographical types as well .

To Create and add Spatial Data

USE Adventureworks
GO

IF OBJECT_ID ( 'dbo.SpatialTable', 'U' ) IS NOT NULL DROP TABLE dbo.SpatialTable;
GO

CREATE TABLE SpatialTable
( id int IDENTITY (1,1),
GeomCol1 geometry,
GeomCol2 AS GeomCol1.STAsText() );
GO

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0));
GO

To create a Geography instance from WKT format

DECLARE @g geography;
SET @g = geography::STGeomFromText('LINESTRING(47.656 -122.360, 47.656 -122.343)', 4326);
SELECT @g.ToString();

To create a Geometry instance from WKB format

DECLARE @g geometry;
SET @g = geometry::STGeomFromWKB(0x010200000003000000000000000000594000000000000059400000000000003440000000000080664000000000008066400000000000806640, 0);
SELECT @g.STAsText();

-- Point from WKB
SET @g = geometry::STPointFromWKB(0x010100000000000000000059400000000000005940, 0);
SELECT @g.STAsText();

To create a Geometry instance from WKT format

-- Creating an instance with WKT
DECLARE @g geometry;
SET @g = geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0);
SELECT @g.ToString();

-- Parse is also useful to convert data from WKT
SET @g = geometry::Parse('LINESTRING (100 100, 20 180, 180 180)');
SELECT @g.ToString();

-- Point instance from WKT
SET @g = geometry::STPointFromText('POINT (100 100)', 0);
SELECT @g.ToString();

Determining if the instances intersect

USE AdventureWorks
GO

DECLARE @geom1 geometry;
DECLARE @geom2 geometry;
DECLARE @result geometry;

SELECT @geom1 = GeomCol1 FROM SpatialTable WHERE id = 1;
SELECT @geom2 = GeomCol1 FROM SpatialTable WHERE id = 2;
SELECT @result = @geom1.STIntersection(@geom2);
SELECT @result.STAsText();

Sample code

As the integration of geospatial information into applications becomes more prevalent, we will require database systems that can store and manipulate spatial data. With the introduction of the spatial types, SQL Server 2008 provides a data storage solution for spatial data, and enables organizations of any scale to integrate geospatial features into their applications and services.

Building Time Zone awareness - Date Time types

2008-02-15T00:36:00.000Z

I have been working for the last two years on projects which involve synchronising data between web applications and many a the date and time on web application used as the source of the replication is not on the same time zone as the destination web application. Some times it is necessary that some portion of the date time values moved across these applications be time zone aware. In other words some date time values need to be moved into the local time where the server is present, while some needed to be moved at absolute times(same as specified on source).

For eg.

Consider a table with four columns on a web application in UK on SQL 2000 server
ID - identity
Name - VARCHAR(100)
sellingDate - datetime -- The date and time on which the product should start selling
ModifiedDate - datetime -- The date and time on which the product record or data was last modified

Now when I move this data across to a web application in India the selling date should still remain on absolute date time specified, but the last modified time should be moved across as relative time to the time zone where this is present.

This is quite a constraint since you would end up having a business rule for every datetime column of your database and this rule is only a adhoc one determined by functionality rather than any real type based information. It would be great to distinguish these time zone aware columns in the database level.

Another problem when a .Net datetime value is serialised is it has an extra time offset appended at the end. This when passed to SQL 2000 parameters was not usable in its raw string form (say when passed as a XML fragment parameter containing this value)

In SQL 2008 we have new date time data types as below
DATE. - Allows only dates, accuracy is a day
TIME - Allows only times to the accuracy of nano seconds
SMALLDATETIME - Allows dates and times , accuracy is one second
DATETIME - Allows date and time as before with 3ms accuracy, but the range of dates that can be specified has been increased to 01-01-1753 to 31-12-9999
DATETIME2 - Allows specifying date and time like the DATETIME type but to a accuracy of nano seconds , YYYY-MM-DD hh:mm:ss:[nnnnnnn]
DATETIMEOFFSET - Allows users to specify a date and time with a time zone offset.

With these 6 new date time types in SQL server 2008, time zone aware applications can be more efficiently and easily designed/ developed.

As we have seen the date time types, lets see what functions are available, its great to see an introduction of the datetimeoffset but it would even better to do some playing around with it at the database level

Functions which return DATETIME values, These are as present in SQL 2000, 2005
GETDATE(), CURRENT_TIMESTAMP and GETUTCDATE() are still available.

Functions which return DATETIME2 values, other wise refered to as HIGH PRECISION date time functions, SYSDATETIME(), SYSUTCDATETIME(),SYSDATETIMEOFFSET() (returns DATETIMEOFFSET type value).

Other functions as in previous versions such as DATEPART, DATEDIFF, DATEADD are available , but two new useful functions which complement the DATETIMEOFFSET datatype hav been introduced.

SWITCHOFFSET() and TODATETIMEOFFSET()

e.g.

SELECT SWITCHOFFSET(sysdatetimeoffset(), '+08:00'

The SWITCHOFFSET function converts a value of datetimeoffset type from stored time zone to specified time zone

SELECT TODATETIMEOFFSET ( GETDATE() , '+08:00' )

The TODATETIMEOFFSET function converts a datetime type value into a DATETIMEOFFSET type with the offset specified

ISDATE()

Another useful function for datetime is the ISDATE() function, which validates that the specified value in the parameter is a valid date time value. There are several different scenarios to use it but in general for something as below

SELECT ISDATE(NULL); --returns 0

USE AdventureWorks;
GO
SELECT ISDATE(s.OrderDate) FROM Sales.SalesOrderHeader s;
GO
--Returns ones

All the information above is based on the November CTP for Katmai. Looking at all the above it does show good prospects for handling date and times for developers.

Change Data Capture (CDC) - I

2008-02-12T00:21:00.000Z

Oracle 9i has this feature since 2003 as I can recollect, but being a SQL Server developer I am quite pleased its available in Katmai. To give a bit of background as to why CDC is a crucial feature , we will try to understand ETL in Data warehousing. A crucial function of data warehousing is extracting data on a regular basis applying some kind of transform if necessary to the data and storing it in the warehouse. So if i have a database replication setup I would run DTS packages which replicate and transform all data between the transactional database and the warehouse database. If you are a DBA or a developer involved in a data integration project that involves the regular extraction and transportation of a large amount of data from one system to another system or systems, you could consider CDC to help reduce your ETL time. The point is its more efficient than our previous replication methodologies for sure and it is particularly effective as we scale up on the database size.
In SQL Server 2000, 2005 you would have written triggers to update data into another table where the metadata on the change is stored and then further right more plumbing code to put it all together ... I work on a product which has a similar concept.. Believe me it’s not a easy on 2000 and 2005. We where able to track data even though it was complicated but now in Katmai we will be able to capture the details of the event which caused data change on the relevant database table.

In Katmai, when we use Change Data Capture feature on a database table, a mirror of the tracked table schema is created with additional columns that include metadata. The metadata holds all the information about the change.

To know if CDC is enabled for a database, we could run the following query

SELECT * FROM sys.databases WHERE is_cdc_enabled = 1

Enabling the database for CDC

USE AdventureWorks

GO

EXEC sys.sp_cdc_enable_db_change_data_capture

GO

When you execute this command five tables in a schema called cdc are created. In addition to this there are two table valued functions added on to the cdc schema of the database. These functions behave like stubs for table based CDC functions

The five tables created are as below

cdc.captured_columns

cdc.ddl_history

cdc.index_columns

cdc.lsn_time_mapping

cdc.change_tables

In Katmai, when we use Change Data Capture feature on a database table, a mirror of the tracked table schema is created with additional columns that include metadata. The metadata holds all the information about the change.

In addition to this you will notice there are two Jobs one for Capture and one for cleanup added under the SQL Server Agent jobs for the database CDC functionality

CDC Schema

Enabling the table for CDC

On the AdventureWorks database to enable CDC on HumanResources.Employee table, we should use the following functions

USE Adventureworks

GO

EXEC sys.sp_cdc_enable_table_change_data_capture

@source_schema = 'HumanResources',

@source_name = 'Employee',

@role_name = 'cdc_AdventureWorks',

@capture_instance='HumanResources_Employee_CT'

GO

When we execute this command a new capture instance is created a table with this name of the capture instance is created with the schema as shown in the diagram

Note that columns as in the Employee table are created, In addition to which there are five extra columns which store metadata about the data.

Now we update a row on the HumanResources.Employee table as below

UPDATE HumanResources.Employee

SET LoginID = 'adventureworks\guy2'

WHERE employeeid = 1

Now select the data in the HumanResources_Employee_CT, we will find two rows inserted in this table, One representing the data before the update and the other after the data was inserted

Disabling CDC on a table/capture instance

USE Adventureworks

GO

EXEC sys.sp_cdc_disable_table_change_data_capture

@source_schema = 'HumanResources',

@source_name = 'Employee',

@capture_instance = 'HumanResources_Employee_CT'

GO

I guess I will pause here and continue on the rest in my next blog…. There are several cdc related system functions which could be used for different purposes, I will detail each of these in my next blog.. and i will also detail the schema a bit when i get my head around it hopefully

Download - Scripts CDC

Resource Governor

2008-02-11T21:13:00.000Z

Before we understand the Resource Governor let us consider an example where we have hosted two databases App1 and App2 on a single SQL Server 2005 with a query execution time out of 600 seconds. Lets say App1 database should not allow queries to execute for more than 5 minutes which is 300 seconds and App2 should allow 600 seconds. This was not easy to achieve on SQL 2005. (We may argue that we could specify connection timeout on the client connection string in ADO.Net, but that’s just a work around), Alright, what if I want to give connections of App1 database more priority than App2 database connections when server resources are limited?

One of the new features in SQL 2008 (Katmai) is the Resource Governer. It is a component that allows administrators to configure pools of resources. Consider this an analogy to the Application Pool in IIS 6.

The 2 main components in the Resource Governer that matter are the Resource Pool and the Workload Group.

Resource Pool

The Resource Pool allows you set server (as in the physical server on which SQL server is installed) level resource usage such as

MAX_CPU_PERCENT, MIN_CPU_PERCENT and MAX_MEMORY_PERCENT, MIN_MEMORY_PERCENT

Using TSQL

CREATE RESOURCE POOL poolNorthwind
WITH
(
MAX_CPU_PERCENT = 30,
MAX_MEMORY_PERCENT = 50
);

Workload Group.

The Workload Group allows you to control some specific SQL Server related conditions, as well as apply Resource Pools. It allows you to control settings such as the connection priority, query time out, max parallel requests and other parameters as mentioned below

IMPORTANCE = MEDIUM , REQUEST_MAX_MEMORY_GRANT_PERCENT, REQUEST_MAX_CPU_TIME_SEC , REQUEST_MEMORY_GRANT_TIMEOUT_SEC , MAX_DOP , GROUP_MAX_REQUESTS

Using TSQL

CREATE WORKLOAD GROUP workGroupNorthwind
WITH
(
IMPORTANCE = MEDIUM , -- default is medium, its like OS thread priority REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 300,-- max query timeout to 5 min
MAX_DOP = 8, -- maximum degree of parallelism to 8
GROUP_MAX_REQUESTS = 30
)
USING poolNorthwind;

In order for SQL Server to be able to use the Workload Groups that you've created, you need to classify each connection to determine which group that specific connections falls into. This is done through the use of a Classifier Function. Classifier functions are new in SQL Server 2008 and execute each time a new connection is made to the server. Classifier functions are scalar user-defined functions and are basically used to return the name of the target Workload Group for each user connection.

Code Example

CREATE FUNCTION ufnNorthwindRsGovMgr()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
DECLARE @grpName SYSNAME

IF (APP_NAME() = 'Microsoft SQL Server Management Studio')
SET @grpName = 'workGroupNorthwind'
ELSE
SET @grpName = 'default'
RETURN @grpNameEND;

The final step in the configuration of the Resource Governor is to assign the classifier function and activate the configuration.

ALTER RESOURCE GOVERNOR
WITH
(
CLASSIFIER_FUNCTION=dbo.ufnNorthwindRsGovMgr
);

ALTER RESOURCE GOVERNOR RECONFIGURE;

Using the Management Studio

If we use the Management Studio which comes with the Developer versrion of SQL 2008 CTP allows you to create the Resource Pool using the Resource Governor properties dialog, shown in the picture on this entry

I have created a solution with the above code . Download

Features on SQL Server 2008 - Katmai

2008-02-11T20:00:00.000Z

For the last three weeks i have been playing around with SQL Server 2008 (Katmai) CTP. I managed to install it on Windows 2003 server without any problems. Although i would advise people not to install alongside SQL Server 2005. If you want to use management studio of SQL 2008 CTP be sure to use the developer edition CTP, the expression edition only comes with some client tools. Since its a CTP and you only are trying to get your head around new features it is useful to have management studio installedAs with any CTP i didnt know where to start and i found that Chad Boyd ihas listed the new features for Katmai quite extensively on his blog. Since I am just a developer and i filtered the list of features that may be more useful for a developer to look at.. I m planning to plough through one feature at a time and put up some information on this site for people who are interested.

Performance

Data compression (easy to enable/disable online, more efficient data.
Backup stream compression (server level control or backup statement control, all backup types).
Resource Governor (create pools and groups to govern, define classifications based on built-in functions, segment resource utilization among groups).

Management

Policy based management framework (manage via policies vs. scripts, enterprise wide support, automated monitoring/enforcement, etc.).
Integrate with Microsoft System Centre.
Extended Events (high perf lightweight tracing infrastructure, NOT sql trace, integrated with ETW).

Development Enhancements

Improved datetime data types (100th nanosecond precision (7 digits past second), time zone datetime offset, date only, time only).
HierarchyID datatype (hierarchical aware data type, ORDPath values, built-in functions, methods, etc.).
Entity Data Model support (develop 'business entities' vs. tables, model complex relationships, retrieve entities vs. rows/columns).
LINQ.
Sql Server Change Tracking (Change Data Capture, get 'diff' data changes WITHOUT a comparible value (i.e. datetime, timestamp, etc.)).
Table Valued Parameters.
MERGE statement ('upsert' data, also includes deletion functionality).
Large UDT's (no more 8000 byte limit on CLR based UDTs, no more 8000 byte limit for UDA's).
Spatial data (GEOMETRY and GEOGRAPHY data types, built-in spatial function support, spatial indexes).
XML enhancements (support for lax validation, office 12 support, xs:dateTime support, lists/union types, LET FLOWR support, etc.).
Inline initialization and compound assignment

Service Broker

New UI and Tools for working with (add/drop/edit functionality within SSMS, Diag tools).
Conversation Priority (set message ordering, send/receive impact, 110 levels).

Data Storage

Data compression (see above).
FILESTREAM attribute (get the 'best of both' functionality from BLOBs in the DB vs. BLOBs on filesystem, no more "to blob or not to blob").
Integrated Full Text Search (FTS fully integrated into DB engine, no external storage, no external service, more efficient and reliable costing).
Sparse columns (more efficient storage for 'wide' tables with many columns that repeat and don't contain data).
New index types (spatial indexes, hierarchical indexes, FILTERED indexes (indexes on filtered values within columns), etc.).

Reporting

IIS Agnostic Reporting Services Deployment (no IIS required to run RS any longer).Richtext support.
Enhanced visualiztion (graphing).
New Word rendering (render reports to Microsoft Word).