TFS 2010 to TFS 2012 Express

Last weekend I did a full upgrade on my computers to Windows 8.  Along with that, came the upgrade of Visual Studio 2012 and SQL Server 2012, and Team Foundation Server 2012.  My plan was to have a completely fresh development environment, with no legacy 2010, 2008, 2005 versions.

This was a good plan, but I had one reservation.  In this year, I converted from Visual Source Safe to TFS 2010, and now I was going to have to upgrade again to TFS 2012.  Everyone understands how to manage a big file system structure like VSS, but TFS might be a mystery.  Actually, it’s much easier.  It’s just two SQL databases.  So I backed up those databases and did my full, fresh install.

Now I’m ready to install TFS 2012, but what about my data?  I’d seen many blogs and articles describing the upgrade process.  Every one said, uninstall TFS 2010, then install TFS 2012.  But I didn’t want to have any TFS 2010 bits on the new install.  So in order to get the database instance to restore my TFS 2010 data, I installed TFS 2012, then uninstalled it.  Then I restored my TFS 2010 data into the SQLEXPRESS instance TFS created.  Finally, I reinstalled TFS 2012, selecting the “Upgrade” option, which converted my TFS 2010 data to the new schema. 

Sounds like a roundabout solution but it worked without any problems and without resorting to older product installs.

My, How Big You’ve Grown

image

There used to be a time when SQL Server was a database engine.  Setup of the product was easy.  You told it where to install, then you set up the service accounts.  Over time, the feature set of SQL Server grew and it became more of a suite of products.  This involved some more installation steps with more information to be gathered.

Somewhere around 2008, a significant change occurred with the SQL Server setup.  It involved pre-install checks, rule checks, confirmations, and other stages that significantly slowed down the install process.  What used to be a two minute wizard is now a process that can easily consume 15 minutes.  Add to the mix the growth of the feature set of SQL Server and the full install could take hours.

Following along the left of this post is a screenshot of the setup process.  We have three rule checks, one before you start, one after choosing the features to install, and one after you configure the features you want to install.  For my install, I chose “All features with defaults” thinking it would be the fastest and easiest.  Nope, I still had to run through all the steps.

The most annoying change to the install process is the rule checks.  Even if everything checks out, you still have to click Next on the wizard.  If it’s all good, why do I need to review it?  As mentioned before, this happens three times during installation.  It’s almost as if the setup program is saying, “Look at how hard I’m working.  See, I made you a big list of everything I did.”  It’s like an employee that isn’t confident of his work and has to document everything he does to justify what he’s done.

The feature growth of SQL Server has become outrageous as well:  Analysis Services, Reporting Services, Distributed Relay, Failover Clusters.  I would be very interested in seeing if SQL Server Express is selected more often just because the feature set is more realistic for most projects.

I am also curious to see if this post’s text is longer than the installation steps for SQL Server.  And I even tried to be more verbose so I could fill space.

And as it turns out, I didn’t write enough and the next post continued right next to the image.  I guess that’s a slight issue with the WordPress theme template.

Now This is Something Worth Looking Into

When you see something like this in the call stack, you have to know more:

[InvalidOperationException: This SqlTransaction has completed; it is no longer usable.]
   System.Data.SqlClient.SqlTransaction.ZombieCheck() +1623536
   System.Data.SqlClient.SqlTransaction.Rollback() +172
   Framework.Common.Database.RollbackTransaction() in C:\Framework\Common\Database.vb:413 
   Test.uxCreate_Click(Object sender, EventArgs e) in C:\Test.aspx.vb:47
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +154
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3691

And the error message is interesting as well: “This SqlTransaction has completed; it is no longer usable”.

A little background on what is going on: this is a database class that is managing a SQL transaction to be used by multiple objects.  The database object instance gets passed along to the different objects and they use it, participating in the transaction.  Somewhere along the way, maybe a commit is occurring and then the transaction becomes invalid.  That’s how it seems.

Tossing in a bunch of debug.writelines to see what what happening inside the methods popped up a message about a SQLException being raised because of a non-existent column name.  Fixing the schema problem fixed the zombie problem.  But what was the reason for the original error?

Let’s say you bring a sandwich into work.  You give that sandwich to someone and tell them to put it in the refrigerator for you.  This person (the fridgemaster) puts it in the fridge and comes back to the refrigerator a little later to find your sandwich moldy and spoiled, so he throws the sandwich out.  Later on yet, you go to the fridgemaster and ask for your sandwich.  He says “this sandwich has spoiled; it is no longer usable.”  In response to your puzzled look, the fridgemaster says “I did a ZombieCheck and it was moldy.”

In coding terms, the order of events was: TX started, SQL error occurred, TX rolled back (by SQL Server), SQLException raised and caught, TX rolled back in catch block (by user code) and unable to because the TX already was rolled back by SQL Server.  This rollback behavior is dependent on the severity of the error. In the case of the schema error, which presumably was interpreted as “this will NEVER work”, this equated to being severe enough to roll back the transaction.

Small Changes Make Big Changes

I read couple small tips from a SQL Server MVP that I had to try out.  I was surprised this is the first I’d ever heard of them.  The tips were concerning optional parameters – basically, when you pass a default to match everything or a specific value to match.  Like all things programming, there’s plenty of ways to accomplish the same results.  The difference is how well they perform.

Take an example where you are optionally filtering a table by VendorID, NULL means match everything.  The two ways I’d done this previously are:

and (@vendorid is null or vendorid=@Vendorid)

and

and vendorid=isnull(@Vendorid,vendorid)

Only subtly different.  I had always worried about the second option because I’d read that using functions ruins chances of optimization using indexes, but it turns out to not be the case.  The new suggestion by the MVP was to use BETWEEN and COALESCE the NULL default value into an upper and lower bound.  So, storing a high value number like 999999999 in a variable called @MaxInt, the clause looks like

and vendorid between coalesce(@VendorID,0) and coalesce(@VendorID,@MaxInt)

But how does it perform?  Interestingly well. When using an ID the query plans were:

57% of batch using the OR construct

image

vs. 53% of batch using ISNULL

image

vs. 26% of batch using COALESCE

image

When using NULL as the value, the query plans were the same and the batch percent was different but relative: 42%, 38%, and 16%.

The I/O cost was significantly lower when using the BETWEEN and, as shown, there’s less complexity and it results in a lower cost to the overall batch.

The other tip was another use of COALESCE.  Instead of:

and (@streetnumber is null or streetnumber like '%'+@StreetNumber+'%')

use:

and streetnumber like coalesce('%'+@StreetNumber+'%','%')

In my testing, that single change took the statement from 68% of the batch to 60% of the batch.  After changing all the criteria, the whole query went from an unusable 25 seconds to a manageable 4 seconds.

Finding Solutions In Problems

I’m always looking for novel ways to accomplish something, even it’s totally inefficient… This sounds familiar.

Interestingly, the solution I was experimenting with in the last post is closely related to a problem I took up for resolution today.  In the inefficiency of the code, I found a very clever and unique way of splitting a string on a delimiter like a comma.  Now in this particular case, there was a much more efficient way to accomplish the end result, but I can appreciate the creativity in this query.

This is the code that was focused on:

declare @SortExpression VARCHAR(100) = 'state,zip',@SortOrder VARCHAR(10) = 'ASC'

SELECT STUFF(
(SELECT ', ' + Parsed 
FROM (
    SELECT SUBSTRING(@SortExpression + ',', Number,
        CHARINDEX(',', @SortExpression + ',',Number) - Number) + ' ' + @SortOrder AS Parsed
    FROM Common.dbo.Numbers
    WHERE Number <= LEN(@SortExpression)
    AND SUBSTRING(',' + @SortExpression, Number, 1) = ','
    ) t2
FOR XML PATH(''),TYPE).value('.[1]', 'varchar(MAX)'),1,2,'')

Whenever you see three SELECT keywords and XML together to result in a single value, something seems out of place.  This code takes two strings, “state,zip” and “ASC” and turns it into a single string “state ASC, zip ASC”.  That’s a lot of code for just that.

The interesting part of that query is in the middle, where the query accesses the a database with a table called Numbers which just has a list of numbers from 1 to 10000.  In another post, I created a list of numbers, the exact same thing, in memory using UNPIVOT and a CTE.  I could’ve done the exact same thing to fix this, but the eventual solution I came up with does the same with no recordsets at all to deal with.

Drilling into the inner query,

SELECT SUBSTRING(@SortExpression + ',', Number,
    CHARINDEX(',', @SortExpression + ',',Number) - Number) + ' ' + @SortOrder AS Parsed
FROM Common.dbo.Numbers
WHERE Number <= LEN(@SortExpression)
AND SUBSTRING(',' + @SortExpression, Number, 1) = ','

the thing I find so clever about this query, despite its inefficiency, is the approach it takes.  It uses a sliding window of characters, breaking on the comma, and looking for a trailing comma (that was manually added as a terminator).  When you see the results without the SUBSTRING criteria in the WHERE clause, it looks like:

Parsed

state ASC

state ASC

tate ASC

ate ASC

te ASC

e ASC

ASC

zip ASC

ip ASC

p ASC

And with the SUBSTRING check, the only rows returned are the ones that have a trailing comma.  Code like this makes your head hurt.  In fact, it’s so complex, you almost have to accept it at face value, thinking it’s just so complex, you’d better not touch it.  That is when you isolate and experiment, get the same results, prove it out with different values, and improve it.

So what’s the best replacement I could come up with?

select replace(@SortExpression,',',' ' +@Sortorder + ',') +
case when @SortExpression<>'' then ' ' + @Sortorder else '' end

One complete statement.  Whee!

Finding Solutions Before Problems

I’m always looking for novel ways to accomplish something.  Even if it’s totally inefficient, it still is a good exercise in problem-solving.  Sometimes you can find a good challenge by taking something simple, then making it complex.  For example, there is a simple way to get the first missing value within a non-contiguous range.

declare @i int=1
create table #t(value int)

-- Add numbers in multiples of 4
while @i<100
begin
    if (@i % 4)=0 
    insert #t(value)values(@i)
    set @i=@i+1
end

-- Get first available value
select top 1 t1.value+1
from #t t1
where not exists (
    select 1 
    from #t t2 
    where t2.value=t1.value+1
    )
order by 1

drop table #t

You can get all the next available numbers by removing the “top 1”, but what if the gap was more than 1 value wide?  You’d have to get the first missing values, then fill them or track them in a temp table/variable, then call the statement again.  In this example,  we do have gaps larger than one value.  We want to return 1,2,3,5,6,7,9,10,11, etc. This means we need to check for every value that is not in the #t table.  How would you be able to do that in a single statement?  In pseudo-code, you want to do something like:

select x where not exists (select 1 from #t)

but you have to have a FROM clause.  And that means you have to have the potential values somewhere to pull from.  You could make a temp table/variable and populate it with a range of values you want to compare against, but again, we want this to be done in one statement.

To solve this, you can make a small lookup table using UNPIVOT

select v
from (
    select 0 v0,1 v1,2 v2,3 v3,4 v4,5 v5,6 v6,7 v7, 8 v8, 9 v9
    ) n
unpivot(v for value in (v0,v1,v2,v3,v4,v5,v6,v7,v8,v9)) n

This gets you a resultset of 0 through 9.  Not a lot of values to compare against.  But structuring it as a CTE, you can make this 0-99.  You can join it again to get 0-999, and keep joining until you get the maximum values you need.

with Numbers(num) as
    (
    select v
    from (
        select 0 v0,1 v1,2 v2,3 v3,4 v4,5 v5,6 v6,7 v7, 8 v8, 9 v9
        ) n
    unpivot(v for value in (v0,v1,v2,v3,v4,v5,v6,v7,v8,v9)) n
    ) 
select (N1.num*10)+N2.num 
from Numbers N1
full join Numbers N2 on 1=1

Now that we have a table of lookup values, it’s trivial to find the missing values from our original table.

with Numbers(num) as
    (
    select v
    from (
        select 0 v0,1 v1,2 v2,3 v3,4 v4,5 v5,6 v6,7 v7, 8 v8, 9 v9
        ) n
    unpivot(v for value in (v0,v1,v2,v3,v4,v5,v6,v7,v8,v9)) n
    ) 
select lookupNum
from (
    select (N1.num*10)+N2.num lookupNum
    from Numbers N1
    full join Numbers N2 on 1=1
    ) n
where not exists(    
    select 1
    from #t t
    where t.value=n.lookupnum
    )
order by 1

One complete statement.  Whee!

In CASE you were wondering…

Here’s a thought I had a couple of days ago. I actually was almost done with this blog entry yesterday, but ended up closing the window and not saving any of it. Argh.

My thought was on sql sorting. There are times when you want to have your results sorted at the server based on a parameter. Those of you who like dynamic sql would be all over that. It’s so simple (in Northwind):

declare @SortType int
declare @sql varchar(2000)
-- SortTypes
-- 1- Lastname
-- 2- Firstname
-- 3- Employeeed
-- 4- Extension
-- 5- postalcode

set @sorttype=4

set @sql='select * from employees'
if @sorttype=1 set @sql=@sql + ' order by lastname'
if @sorttype=2 set @sql=@sql + ' order by firstname'
if @sorttype=3 set @sql=@sql + ' order by employeeid'
if @sorttype=4 set @sql=@sql + ' order by extension'
if @sorttype=5 set @sql=@sql + ' order by postalcode'

exec(@sql)

Ok. But as I’ve said before, I am not a fan of dynamic sql. And my environment doesn’t allow it because all security is done through the stored procedures. So, how do we do the same thing without dynamic sql? It’s a great use of the CASE function.

Let’s start with the first two sort types: lastname and firstname. you can put the CASE in the ORDER BY section:

select *
from employees
order by case @sorttype
    when 1 then lastname
    when 2 then firstname
    end 

That was simple. So let’s add the third type, employeeid.

order by case @sorttype
    when 1 then lastname
    when 2 then firstname
    when 3 then employeeid
    end

Works well. Wait, no it doesn’t. Sorting by type 3 is fine, but sorting bu type 1 or 2 gives:

Msg 245, Level 16, State 1, Line 12
Conversion failed when converting the nvarchar value 'Davolio' to data type int.

Ok, that’s not a problem, we’ll just convert everything to varchar. So now it’s like:

order by case @sorttype
    when 1 then lastname
    when 2 then firstname
    when 3 then cast(employeeid as varchar)
    end 

That works, kind of. You won’t notice the bug on Northwind, but you will when we do the next type, extension. We need to convert it to varchar also.

order by case @sorttype
    when 1 then lastname
    when 2 then firstname
    when 3 then cast(employeeid as varchar)
    when 4 then cast(extension as varchar)
    end 

Wow, that’s not right. 2344 doesn’t come before 428. But it does in the string world, which you forced your way into. There is a solution. we just need to pad the numbers with zeros. But how many zeros? All the values have to be the same length to be considered equivalent. so now we have:

order by case @sorttype
    when 1 then lastname
    when 2 then firstname
    when 3 then cast(employeeid as varchar)
    when 4 then replicate('0',10-len(extension))+ extension
    end 

That works fine. And we’ll have the same problem with postalcode, which is numeric and character, so we need to pad the beginning of those to be equal-length strings. The final version:

select *
from employees
order by case @sorttype
    when 1 then lastname
    when 2 then firstname
    when 3 then cast(employeeid as varchar)
    when 4 then replicate('0',10-len(extension))+ extension
    when 5 then replicate('0',20-len(postalcode))+ postalcode
    end 

You’d want to make the length of the postalcode be the max length of the field, in case it’s blank. But the point is CASE is a very powerful tool. It can be used anywhere a single value can be used. In ORDER BY, in JOIN, in SELECT, it’s universal. Now I’m going to save this before I lose it again.