SMO issues with SQL 2012 SP2

After rolling out SQL 2012 SP2 (CU1) across all our environments, we noticed all our TeamCity builds were going a lot slower, or never completing. I didn't make the connection at first, but I eventually tracked it down to a SMO call in our install script that was taking a long time to return (actually I think it either deadlocks or never returns on our busy build server).

Firing up profiler I was able to confirm that the SMO call in question (querying a database's DatabaseSnapshotBaseName property) was resulting in multiple SQL queries hitting the server, one of which in particular was fairly massive. We were doing this in an inner loop, so the performance was fairly rubbish - we had a classic ORM-lazy-load issue.

Clearly we should have looked at SetDefaultInitFields before, but the performance was always good enough we didn't have to bother. Which makes me suspect strongly that either the defaults have changed with SP2 (CU1), or the 'get all properties for the database' query has changed to include additional data (probably something cloud related), which reduces the performance substantially in the 'get everything' case.

What's a bit nasty here is that you also have this query executed if you reference the smo JobServer object, since (through ILSpy) one of the first things that class's constructor does is access the StringComparer property for the MSDB database object:

this.m_comparer = parentsrv.Databases["msdb"].StringComparer;

This object depends on a couple of server properties, but I think 'Collation' is the key one here - if that's not loaded then the the newer 'all properties' query kicks in, which is again much slower than it was before.

Moral: be sure to add 'Collation' to your SetDefaultInitFields() list for Databases.

Nuget Package Restore with internet-restricted build servers

The first time I enabled nuget package restore I had a nasty shock: the TeamCity build server didn't have internet access (and wasn't getting any).

I initially went back to storing-packages-in-source-control, but when I created a spike ASP.Net MVC 4 project, which wanted to add some 30 packages, I decided I needed to address the root problem.

A few people suggested I should setup a local nuget repository, the simplest version of which is just to dump all the packages you need into a file share. But how to point the build server at the share, rather than the standard nuget feed URL? Short of logging in as the build server user and changing the settings through Visual Studio (lame), there appear to be a couple of ways:

Editing %appdata%\nuget\nuget.config (for the build server user)

This achieves the same as the above, but you don't have to log in as that user. It's reliable, but because it doesn't 'carry' with the solution in source control it's brittle (needs doing again if the build server user changes etc...) and you can't vary it on a project by project basis, which is a bit limiting.

Editing the generated nuget.targets file that package restore adds to your solution

This looks like the go, and even has comments in there to show you what to change. But I didn't find this reliable. On my local machine (under a no-internet user) this worked just fine, but on my build server I kept having package resolution errors (from a Silverlight 4 project).

Setting the PackageSources build parameter

What the above did show is what MSBuild properties are involved, so I was able to specify that property on the MSBuild command line and that seems to work consistently:

MSBuild mysolution /p:PackageSources=\\server\fileshare

...now finally I have a green light on TeamCity and can go and do something actually productive instead.

Problems Running Tests When Preserving Output Directory Structure with Team Build

Previously I’ve posted about how to override the Team Build default output directory scheme and produce something a bit more sane.

Unfortunately if you do implement this it can break the built-in test run task, and most of the recipes related to it. You’ll get the following error in your build logs:

MSBUILD : warning MSB6003: The specified task executable "MSTest.exe" could not be run. The directory name is invalid

If you run the build with /verbosity:detailed to see the actual args passed to MSTest.exe, and then run MSTest yourself interactively, you’ll see the real underlying error:

Directory "(my build path)\Binaries\Debug" not found.
For switch syntax, type "MSTest /help"

The problem here is that (as detailed on the TestToolsTask doco) the team foundation build targets sets up MSTest.exe with SearchPathRoot="$(OutDir)", ie $(BinariesRoot)\$(Configuration). But if you overrode CustomizableOutDir and never actually copied the binaries out to the output folder that directory will never get created.

Fix 1:

If you’re not really using CustomizableOutDir, remove it. Reverting to the default Team Build directory structure is the simplest way of getting the tests to be located and executed and everything to ‘play nice’.

Fix 2:

Make sure that if your TFBuild.proj says CustomizableOutDir you do actually have the corresponding custom tasks in the individual projects to copy the binaries (see my previous post), otherwise you end up with no output whatsoever, and the test task will fail.

Fix 3:

If you want CustomizableOutDir but want to be robust to the possibility that your project builds may not populate the output directory structures properly, you can hack your build to run the tests out of the source \bin\debug folders.

My first pass was just to add the following to my BeforeTestConfiguration target (that I’d added from the Running Unit Tests without a Test List recipie):

    <!--because this is what the TestTask gets its SearchPath set to, it must exist-->

    <MakeDir Directories="$(OutDir)"/>

But that wasn’t good enough on its own, because now the error was:

CoreTestConfiguration:
File "..\..\(blah)\bin\Debug\Assembly.UnitTests.dll" not found

The relative paths to the test assemblies were correct relative to the $(SolutionDir), but not relative to the $(OutDir). So, for want of a better answer, I just overwrite OutDir for the duration of the test task:

   <!—defined elsewhere-->

   <TestsToRun Include="$(SolutionRoot)\%2a%2a\bin\$(Configuration)\%2a.UnitTests.dll" />

 

  <Target Name="BeforeTestConfiguration">

    <!-- normal bits as per the recipe-->

    <Message Text="Using tests from @(TestsToRun)" Condition=" '$(IsDesktopBuild)'=='true' " />

 

    <CreateItem Include="@(TestsToRun)">

      <Output TaskParameter="Include" ItemName="LocalTestContainer"/>

      <Output TaskParameter="Include" ItemName="TestContainer"/>

    </CreateItem>

 

    <Message Text="LocalTestContainer: @(LocalTestContainer)" Condition=" '$(IsDesktopBuild)'=='true' " />

 

    <!--Fix to allow use of CustomizableOutDir -->

    <MakeDir Directories="$(OutDir)"/>

    <PropertyGroup>

      <OldOutDir>$(OutDir)</OldOutDir>

      <OutDir>$(SolutionDir)</OutDir>

    </PropertyGroup>

  </Target>

 

  <Target Name="AfterTestConfiguration">

    <PropertyGroup>

     <OutDir>$(OldOutDir)</OutDir>

    </PropertyGroup>

  </Target>

Whether this is a good idea or not I’m not sure, but it does seem to work. Note that I put it back the way it was afterwards (using AfterTestConfiguration).

Moral

I think the story here is that using CustomizableOutDir is a complete pain in the arse, which ends up requiring considerable customisation of the rest of the build workflow. I don’t mind a prescriptive process per-se, but I do have a real issue with the ‘flat’ output directory structure that Team Build kicks out. But attempting to change it just seems to cause a heap more trouble than it’s worth.

Actually - as Martin Fowler said years ago - using XML as a build language is a really dumb idea in retrospect. Everyone says TeamCity’s pretty cool: might be time to take a look at that…

 

PS: If you’re trying to get your head around what happens where in Team Build (aren’t we all) there’s a great Team Build Target Map over at the Accentient blog

PS: I notice on Aaron Hallberg’s blog there’s a much simpler approach if you just want to separate per-solution output directory structures, which may not suffer the same problems.

Stupid Naming Themes [WiX]

This is WiX:

candle.exe
dark.exe
heat.exe
light.exe
lit.exe
melt.exe
pyro.exe
smoke.exe
torch.exe

Jesus wept. Even the ‘fresh ground .jar of Java beans’ crowd outgrew this childishness…

Preserving Output Directory Structure With Team Build 2008

The default TFS drop folder scheme is a train-wreck where all the binaries and output files from the entire solution are summarily dumped into a single folder (albeit with some special-case handling for websites). Its just gross.

What I wanted, of course, was a series of folders by project containing only the build artefacts relevant for that project: ‘xcopy ready’ as I like to say. Pretty sensible yes? Quite a popular request if one Googles around: one wonders why the train-wreck scheme was implemented at all. But I digress.

Contrary to what you (and I) may have read, you actually have to do this using AfterBuild, not AfterCompile (which fires way too early in the build process[1]). So the canned solution is:

Put this in your TFSBuild.proj:

  <PropertyGroup>

    <CustomizableOutDir>true</CustomizableOutDir>

 

    <!--  TEAM PROJECT

(this turns off the ‘train-wreck’ scheme, and goes back to each project’s output going to their bin\debug or whatever folder)

Put this in any project file that you want to capture the output from:

  <Target Name="AfterBuild" Condition=" '$(IsDesktopBuild)'!='true' ">

    <Message Text="Copying output files from $(OutDir) to $(TeamBuildOutDir)" />

    <ItemGroup>

      <FilesToCopy Include="$(OutDir)\**\*.*" />

    </ItemGroup>

    <Copy SourceFiles="@(FilesToCopy)" DestinationFiles="@(FilesToCopy ->'$(TeamBuildOutDir)\$(AssemblyName)\%(RecursiveDir)%(Filename)%(Extension)')" />

  </Target>

And hey presto:

A series of output folders, by assembly, containing only the artefacts relevant to that assembly, as opposed to the

Note however that with this scheme you get all files marked ‘Copy to Output Directory’ but not files marked as Content, which makes this currently unusable for deploying websites and means it’s not strictly speaking xcopy-ready[2]. Hopefully there is an easy fix to this, otherwise I’ll be diving back into the SNAK codebase where I’ve solved this previously.

 

[1] before serialization assemblies are generated, and before the obj folder has been copied to the bin folder. A good diagnostic is to put <Exec Command="dir" WorkingDirectory="$(OutDir)"/> into an AfterCompile target, and take a look at what you get back.
[2] I have a big thing about this, which is why I dislike the ClickOnce build output so much. Build and deploy need to be considered separately.

Finally: PowerShell as build language

I've never really got into MSBuild, which surprised some people given how much time in the last four years I've spend mucking about with CCNet / NAnt. It was partly that we did a bit of investigation when MSBuild came out, and saw a couple of things we didn't really like about it and decided to wait for v2 (ie Team Build in TFS 2008). Partly.

More fundamentally however the problem is that MSBuild is just too similar to NAnt, and my considered opinion after years of usage is that NAnt sucks, or to be more specific, XML is a terrible 'language' for writing executable code. Fowler puts it pretty well:

"After all until we tried it I thought XML would be a good syntax for build files"
http://www.martinfowler.com/articles/rake.html

Sure it's fine for the IDE to write that stuff out (though even then you have to look at it and wince, right), but for humans who want to customise their build process? Jesus wept. Variable scope: gone. Explicit parameters for subroutines (targets): gone. Its fine when it's simple, but once you start looping and branching and using temporary variables it's just a great big mess of angle brackets that even it's mother pities. And debugging? Now there's a story...

There's a time and a place for the angle bracket tax, and this isn't it. Square peg, round hole.

So given how amenable for DSLs PowerShell has proven to be, I've been holding my breath for some kinda PowerShell rake-alike.

And here it is: Introducing PSake

(Also Microsoft themselves are thinking about it, and canvassing for opinions about whether it's a good idea or not.)

Sadly (actually quite the opposite) I'm not actually having to deal with the build process on my current project, so I don't really have much excuse to play with it. But I dream of a future in which the TFS Team Build project wizard kicks out a PS1 file instead. It'd certainly make fixing some of it's shortcomings a whole heap easier (that's a subject for a future post)


Edit [14/7/08]: Most requested feature for MSBuild? Debugging. Obviously this'll be interpreted by the MSBuild team as a need for a debugger, but maybe they should have used a language that already had one.