So, like all TFS upgrade projects I work on, I got a last minute request that added a major wrinkle to our neat little TFS upgrade plan. “Can we just use SQL 2012 SP1 instead of SQL 2008 R2 SP1 for TFS 2012? It shouldn’t change anything right?” FAMOUS. LAST. WORDS. Notice they were not MY words. I had the foresight to say that no, it absolutely WOULD change things because I hadn’t based any of my estimates or my plan of attack on upgrading the DT software to a new major release. And I also stated that while it was a supported configuration for TFS 2012, since no one here had validated that SQL Server 2012 SP1 would work on their custom VMWare implementation, anything could happen and so my estimate and plan was out the window. It was supposed to be a quick, neat, in-place upgrade that required almost no patching or updating OTHER than TFS itself. And then they decided they wanted to be on the latest and greatest everything all at once. Awesome. That always goes well.
So as I expected, everything went smoothly UNTIL we got to the part where I upgraded SQL Server 2012. So let me back up in case you are wondering how I got to that point… I pinged some colleagues on the TFS product team to verify that I could more or less follow my original plan, but work in an upgrade of the SQL Backend to SQL 2012 along the way. We came to the conclusion that to minimize risk and isolate sources of potential issues, that I should follow my original plan and upgrade to TFS 2012 on SQL 2008 R2 *first*. Then after I verified that configuration was working properly, I would upgrade the database to SQL Server 2012. I had a plan, and lots of caffeine. I also had this awesome blog post to reference from Martin Hinshewood with some helpful nuggets in it too. This might even work…
The upgrade to TFS 2012 on SQL Server 2008 R2 went without a hitch. In case you are curious, they are on SQL Standard x64. I was able to hit the server, fire up the collections, connect to Team projects, SharePoint and reporting. I followed the advice of many blog posts and started with the SQL 2012 Upgrade Advisor. The only issue I ran into there was that I had to install .NET 4.0 and a specific prerequisite. I love, LOVE when error dialogs give you links that you cannot click on or copy and paste into a browser too. So helpful SQL dudes! So here you go:
Once I thought I had all my prerequisites in order (wait for it), I ran the upgrade advisor tool, counted my green check marks, and started the upgrade to SQL Server 2012. Somehow the Upgrade Advisor DIDN’T make sure that SQL 2008 R2 SP1 was installed before it let me waste 30 minutes walking through dialogs
Once I got past that installation, the SQL Upgrade finished without another hitch.You will need to restart the server again, but since TFS has been down the whole time anyway it’s not like it matters at this point. Then I started the SQL 2012 SP1 install and it got 99% of the way through the install and ::insert sad trombone:: “The NT service ‘MsDtsServer110’ could not be started”. Who did what again? I searched on it exactly as stated, and SHOCKINGLY got nothing useful back. Again, AWESOME.
After a bit more digging I found some telling information in the event log under System Events:
The service account does not have the required user right “Log on as a service.” So the NT Service\MsDtsServer110, which I have no knowledge of through past experiences, is missing a permission and so SSIS keeps failing. I was unfamiliar with the Service account “NT Service\MsDtsServer110” so did some digging around to see what popped up in regards to SQL 2012 installs. Finally hit a TechNet post that described my exact issue. For whatever reason, most of the SQL Services run as Network Service, (or some other known service account), but the SSIS service runs as this new guy in SQL 2012, and due to local domain security policy here at this client (just like the article warned), my Setup account was not allowed to provision that account properly. So we followed the article’s advice for a workaround, reset the logon account to a known service account, started up all the services for SQL Server, and was able to complete the TFS 2012 DT upgrade. WHEW!
So, lots of potential gotchas, none of which were TFS or SQL’s fault, but since most of my friends work for large corporations with complicated rules about access and domain policies coming out of their ears, I thought this might be helpful. Hope it was!