Hyper-V: Clone a machine with SQL Server installed

The first part of this post will describe the cloning process,
the second - how to get SQL server up again after the clone machine is ready.

Part 0 : Before all

If you use Reporting services, backup service key and store separately, preferably on another machine. Remeber the password, ofc.

Part 1 : Hyper-V - clone your virtual machine

Have to say at once - I did have a possibility to stop the original machine I was cloning. If you do not have such an optyion, this is not exactly post for you. 
Disclaimer: The described method does not pretend to be the best, it just worked for me. I have tried different ways, and that one worked all the time.

I didn't turn on the original machine untill i was completely done with cloning and fixing SQL Server.

1. Shut down the original machine and export it to some location.

 

2. Copy exported machine where you want to have your clone and rename the folder accordingly. (it is absolutely safe to do that, your original machine is not influenced)

3. In your exported folder delete all files except for hard drives, which have extension .hvdx

4. Create new virtual machine at your host, giving it a new name and attch existing hard drive - the one which is exported. IMPORTANT: do not turn on the original machine yet!

5. Start your new machine. Start command line tool and run the following:
c:\windows\system32\sysprep\sysprep
in the window check Generalize.

Wait until the process is done and machine rebooted. This takes time, so fetch coffeee or smth :)

6. When the machine is restarted it asks for windows settings, since they were nuked. Provide them. Details of what sysprep does with machine is here - very nice post, helped me to understand a lot!

7. Change machine name to something meaningfull, reboot. Add it to domain if needed, reboot.

8. If you have static ip configuration, do not forget to change that as well.

9. That's it , you have a clone up and running!

And now, lets get SQL server up.

Part 2 : Reconfigure SQL Server on a cloned machine
 
The problem is that the cloned machine after renaming has old SQL server instance name (from the original machine), so we need to change that. Analysis services doesn't look happy and, naturally, Reporting services also needs to be reconfigured.

1. Start SQL Server Management Studio, try to connect to the instance with the new machine name. If it works - lucky, the process was taken care off during machine rename.

1a. If connecting with machine name as SQL Server name didn't work - provide . or localhost as a server name and connect. Create new query as:

sp_dropserver <old_machine_name>;
GO
sp_addserver <new_machine_name>, local;
GO

Restart SQL server. 

Now - Reporting services.

2. Start Reporting Services Configuration Manager. Navigate to Database tab and click Change Database. As you can see it has the name of the old machine, put the new name in, and on Next page select correct Report Server Database (ReportServer). Finish.

3. Restore Encryption Key. Go to Encryption Keys tab and click Restore. Find the key file you saved before (see Part 0 : Before all) and restore the key from there.

 

 

4. That should be it. Navigate to Report Manager URL to make sure it works.

Now - Analysis services.

1. After all that, my analysis service still was not happy. The only thing that helped was to start installation again and repair (just next next next - repair). If somebody knows how to do it the correct\better way - drop me a note, I will be really thankfull!

2. When repairing done, restart the machine. Make sure you can connect to Analysis services, SQL Server instance and Reporting Services.

3. Start your original image.

Backlog board TF400898: An Internal Error Occurred.

After upgrading to TFS 2012 Update2 our customer start getting "TF400898: An Internal Error Occurred" when trying to view a product backlog board.

Event log at tfs application tier said:
Exception Message: The given key was not present in the dictionary. (type KeyNotFoundException)
Exception Stack Trace:    at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
   at Microsoft.TeamFoundation.Server.WebAccess.Agile.Models.WorkItemSource.<>c__DisplayClassa.<GetCompleteWorkItemData>b__6(IDataRecord dataRecord)
   at Microsoft.TeamFoundation.Server.WebAccess.Agile.Utility.WorkItemServiceUtils.<GetWorkItems>d__c.MoveNext()...
...etc ..etc

As this is a known issue, solution is described here:
http://blogs.msdn.com/b/visualstudioalm/archive/2013/03/19/how-to-fix-tf400898-an-internal-error-occurred-on-your-kanban-board-in-tfs-2012-update-2.aspx

But... it didn't help. Yes, there was inconsistency and 2 possible work items states mapped to Complete board state, but after fixing CommonProcessConfig.xml and leaving only User Story work item in Requirements categories - error still kept coming.

It looks like if there are work items which have the "invalid" state, the board is not getting rebuilt. So, in this unlucky case the fastest fix is to create a new area, and make it the only and default area for backlog.Which practically makes the backlog (and the board) empty.

So, this time the board gets rebuilt successfully. Go to the board page and verify it. After that, just set default area(s) for the backlog the way it was before. Most likely you will need to correct columns for the board, but the annoying TF400898 will be gone.

Just click on Correct this now, and assign relevant states for the board columns.

Neat!

MSSQL: Shrink transaction log

Common issue, right. Let's do it.

1. Go to your database in Management Studio.
2. Right click on the database and select Tasks -> Backup. Choose Transaction Log as Backup type.

 

3. When backup is taken, right click on database, select Tasks -> Shrink -> Files. Choose File type Log.

In 95% of cases that will do the trick.

In 5% of unlucky events, log file is not getting less, but there is no error displayed. If you have that, click Script on the previous screenshot (Shrinking log file). And then run script to do the job, and click on Messages at Output window.

You will see smth like this: 
Cannot shrink log file 2 because the logical log file located at the end of the file is in use.

Then you need some extra steps.
2a. Right-click your database and select Properties. At Options tab set Recovery model to Simple.
Do Step 3.
3a. Set recovery model back to Full. 

Good luck!

Some features of Team Web Access are not visible to you

Installing TFS, going to Team Web Access and observing something lilke this?

And there is no Backlog and Sprint Planning Tools and neither can you see Request Feedback feature.

You need to enable all features. Somehow by default they are set to "Standard" level, not "Full".
Enter administration page and go to Control Panel:

 

Select Full and make it default.

That will do!

Configure SSL in IIS :The process cannot access the file because it is being used by another process.

Tja... annoying! Obviously someone else is listening on 443, let's find out who.

Command prompt: netstat -ano
Looking for port 443 and memorizing PID.

Troublesome PID = 8952. Open Task manager -> Processes - sort by PID.
Ah, here it is. Skype! Kill it, and you will be able to successfully configure your web site to work over SSL.

TFS 2012 Velocity chart

Here I will explain what is it the velocity chart in TFS 2012 shows. Generally - velocity shows how much a team can do during a sprint.

So it comes to - how do you estimate effort. IN TFS 2012 we use User Stories, and for estimates - Story Points. This is essential to remember. Look here, an example of velocity chart:

First of all, on X-axis - iteration ordered by time. This means you have to set timeframe for your iterations. For this go to admin page and Iterations tab.

Y-axis. See numbers? Those are story points. If your work items do not have story points - they will not appear on this chart.

Now about colours. Green shows work items in Resolved\Completed\Done state. Blue - Active. Other states are not shown.

Hope that clarifies some things.

CodeMetrics and VS 2012 workaround

As it is for now, running metrics.exe on a machine which doesn't have VS 2010 gives you an error:

Could not locate C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools\Static Analysis Tools\FxCop\metrics.exe. Please download Visual Studio Code Metrics PowerTool 10.0 at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=edd1dfb0-b9fe-4e90-b6a6-5ed6f6f6e615

Downloading and installing as it says - doesn't really help, it gives yet another error about missing dll or one of its dependencies.

So, to be able to run code metrics tool on a machine without VS 2010 installed, do the following:

1. Create folder C:\Program Files (x86)\Microsoft Visual Studio 10.0\Team Tools
2. Copy over the whole Static Analysis Tools folder there from the machine with VS2010.
3. Copy Microsoft.VisualC.dll version 10.0.0.0 to the FxCop subfolder of Static Analysis Tools folder.

This made the trick.

UPDATE: It's finally there! See CodeMetrics for VisualStudio 2012.