Webinar on Windows 8 App Development

I recently did a webinar on some of the features that Windows 8 Store App developers can easily add to their apps to enhance the user experience.  You can view the webinar here.  I specifically covered Semantic Zoom, Live Tiles, Snap, and Search integration with the Windows 8 charms bar.

SQL Server Database Design Gotchas

This is a long over-due post about some SQL Server database design gotchas that I have run across as I’ve worked with SQL Server over the years. A couple of these are already “best practices” and the others are simply practices I’ve learned that improve the database design.

1. Don’t use UniqueIdentifier (GUID) as the clustered key of a table.

This results in massive table fragmentation, as the order of the data in the table will be based on this column. Because GUIDs are by nature random and not sequential, this means that inserted data will always be randomly inserted all over the place. It is best to choose a sequential column or set of columns as the clustered key of a table.

2. Index your foreign keys.

SQL Server does not index foreign keys and thus you must do this manually. This is a good practice because you will be joining tables on the primary/foreign key relationships and therefore having an index on the foreign keys will allow SQL Server to use the index on those joins. If you follow point #3 on your database design, you can use the following SQL to generate your foreign keys (SQL 2008+):

SELECT 'CREATE NONCLUSTERED INDEX IDXFK_'+SCHEMA_NAME([d].schema_id)+'_'+[d].[name] +'_'+ OBJECT_NAME(a.referenced_object_id) + '_' + [c].[name] + ' ON '+ SCHEMA_NAME([d].schema_id)+'.'+[d].[name] + '('+[c].[name]+');'

FROM sys.foreign_keys a

INNER JOIN sys.foreign_key_columns b ON a.parent_object_id = b.parent_object_id AND a.referenced_object_id = b.referenced_object_id AND a.object_id = b.constraint_object_id

INNER JOIN sys.columns c ON a.parent_object_id = c.object_id AND b.parent_column_id = c.column_id

INNER JOIN sys.tables d ON c.object_id = d.object_id

ORDER BY [d].[name], [c].[name]

3. Don’t create nullable BIT columns.

This is a logical error anyway. A bit (or boolean) by definition is either true or false. If there is a third option, use another data type like tinyint. And create a default constraint on the bit column to save yourself on inserts.

4. Use a single column for your primary key (avoid composite primary keys).

The benefits to this one are multiple. One column is the identifier of the row, any foreign keys back to the table are also one column, the join in simpler, the index is smaller, etc. With composite keys, you can quickly end up in a situation where the great-grandchild table ends up having 4 columns as its primary key, and who likes to type that much code for joins?

5. Use unique constraints to specify the business keys (corollary to the previous point).

While the primary key is a single column, use a unique constraint to specify the business key instead of defining the primary key as the business key.

6. Be consistent in your naming.

Self-explanatory – it is difficult to maintain a database that is inconsistent. Even when making changes to an existing database, stick with the convention already defined instead of doing your own, even if you disagree with its convention.

7. Avoid making the primary key column of a lookup table an identity column if the table is one where the primary key value will have meaning.

For system lookup tables (typically those you would generate as enums in code), I’d recommend not applying an identity specification on the key column (thus requiring an explicit set of the value on insert) and I’d change the column name to end in “Cd” instead of “Id” to denote that it is an explicit value that can be coded against.

That’s it for now. Enjoy!

Installing Windows 8 Enterprise and Activation Error

After installing Windows 8 Enterprise edition, I received the following error:

Windows can’t activate right now. Please try again later.

Trying later results in the same message.  The problem turns out to be that the system need a product key.  To do this, simply run an elevated command prompt and type:

slmgr.vbs –ipk “ENTER PRODUCT KEY”

Once this is done, you will be able to activate Windows (and on mine, it was already activated when I went to the activation center).

Delimited List of Columns

I frequently need to get a list of columns of a database table in a delimited format, and have found myself rediscovering the following query:

select '['+ [name] + '],'

from sys.columns

where object_name(object_id) = 'table-name'

order by column_id

for xml path('')

For a table with three columns, the return from the above would be:

[Column1],[Column2],[Column3],

Enjoy!

Creating Scripts to Automate a Local Rebuild

As a project grows larger, it tends to take a longer time to pull down the latest from source code, recompile all the code, and if required, redeploy the database to your local machine so you have the latest of everything.  A few weeks ago a coworker remarked that he would like to run a script that would clean, get latest, rebuild everything, and reset his database to the latest from source control, while he goes and gets coffee (our project/local ‘reset’ is a multi-step process that takes about 15 minutes to run).  It was a great idea, and so I went off and created something that is now in use by the team, and something you may find useful in yours.  Let’s walk through it.

Using an MSBuild build file, I created targets for each action: Clean, GetLatest, Compile, BuildDatabase, and DeployDatabase.   At the top of the file there are properties (PropertyGroups and ItemGroups) defined that provide some “configuration” information for what will be run.  Notice that the DeployDatabase target explicitly depends on the BuildDatabase target; the other targets do not have dependencies because I want them to be able to run separately.

 
  
    
    Debug
    Any CPU
    false
    true
    YourDBName
    "if exists(select top 1 1 from sys.databases where [name] = '$(DatabaseName)') BEGIN ALTER DATABASE $(DatabaseName) SET SINGLE_USER WITH ROLLBACK IMMEDIATE;drop database $(DatabaseName);END"
    .\sqlexpress
    sqlcmd -E -S $(DatabaseServer) -Q $(DropDatabaseSQL)
  
 
  
    
    
    
  
 
  
    
    RunCodeAnalysis=$(RunCodeAnalysis);SkipInvalidConfigurations=true;RestorePackages=false;
  
 
  
    
    
  
 
  
    
    
  
 
  
    
  
 
  
    
  
 
  
    
  
 
  
    
  
 
  
    
    
  
 

The items you will need to configure are the DatabaseName, GetPath, SolutionProjectsToBuild, and DatabaseProjectsToBuild values.  Once this is done, you should place this file near or at the root of your project (the location of the file will be the MSBuildProjectDirectory value).  You can then execute MSBuild, calling the targets, or chaining them together.  You should run this from a Visual Studio Command Prompt, as it uses TF.exe.  This command is part of the TFS Power Tools, so ensure that you have it installed.

For our project, I created a .bat file that contains the following (note my build file is called master.build):

call “C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\vcvarsall.bat” x86
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:GetLatest

I have defined additional targets in my build file to run multiple steps; here is an example of one:

If I want to execute two steps at the same time, in my bat file, I do the following (deploy database pops up in a second command window):

cmd /c start cmd /k “C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:DeployDatabase /m:2″
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:Compile /m:2

And since the team wanted options, I did the following in my bat file:

call "C:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\vcvarsall.bat" x86
cls
echo Please select one of the following options:
echo 1. Get Latest, Compile, and Deploy DB (default in 10 seconds)
echo 2. Get Latest and Compile only
echo 3. Compile only
echo 4. Deploy Database Only
echo -
 
choice /C:1234 /N /D:1 /T:10 /M:"Your selection >> "
 
if %ERRORLEVEL% == 1 GOTO FULL
if %ERRORLEVEL% == 2 GOTO GETCOMPILE
if %ERRORLEVEL% == 3 GOTO COMPILEONLY
if %ERRORLEVEL% == 4 GOTO DATABASEONLY
 
GOTO END
 
:FULL
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:CleanGetLatest
cmd /c start cmd /k "C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:DeployDatabase /m:2"
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:Compile /m:2
GOTO END
 
:GETCOMPILE
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:CleanGetLatest
cmd /c start cmd /k "C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:BuildDatabase /m:2"
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:Compile /m:2
GOTO END
 
:COMPILEONLY
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:Clean /m:2
cmd /c start cmd /k "C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:BuildDatabase /m:2"
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:Compile /m:2
GOTO END
 
:DATABASEONLY
C:\Windows\Microsoft.NET\Framework\v4.0.30319\Msbuild.exe master.build /t:DeployDatabase /m:2
GOTO END
 
:END
echo on
Pause

Runas /netonly

I discovered the runas /netonly gem today and it is wonderful! Yes, wonderful!

The runas command allows you to run a program on a machine as a different user than what you are logged in as. This is great when you are doing same domain activities, but what about crossing domains? This is the issue the /netonly switch solves. If you include this switch, it will run the program as your logged in user, but any network calls will be sent as if they came from the user you specified! Thus, if I execute something like:

runas /netonly /user:AnotherDomain\AnotherUser devenv

This runs Visual Studio as my user, but any network calls (TFS, Database, etc.) will use AnotherDomain\AnotherUser. A SQL server with Windows Authentication only can now be connected to via this command on your machine not on the same domain as the SQL server. You can run code locally in Visual Studio and debug, connecting via Windows Authentication to a server on a different domain.

Yes, this is wonderful!

Config Transforms for Elements

I would have thought this was obvious, but it took me a bit to figure it out.  The config file transforms that are available for web.config files and for all other files can be used to replace sections of a config file, based on a project configuration.  Most of the examples show changing attributes.  I wanted to change the entire element, in this instance, connectionStrings.  To do this, you simply put the Replace value in the Transform attribute on the element:

Troubleshooting Build Failures

We are having random build failures which have been a huge pain to troubleshoot (although we know that it has something to do with multi-core builds), so I wrote a quick Powershell script today that will run the build against the solution repeatedly until the build fails.

while($true)
{
    C:\Windows\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe /nologo /target:Rebuild YourSolutionFile.sln /p:SkipInvalidConfigurations=true /p:DeployOnBuild=False /p:Configuration=Debug /p:RestorePackages=false /m:2 /p:OutDir="E:\play\Binaries\\"  | Out-Host
 
    if ($LastExitCode -ne 0)
    {
        break;
    }
}

Crossing Domains without Password Pain

I work in consulting, and as such, my machine is never on the client domain.  This causes some headache because every time I want to connect to a client resource, I am prompted for my client username and password.  By default, the client’s resources are in the Internet zone, so nothing is trusted, nor are my saved passwords used to authenticate – I am asked to reenter my password each time I connect to any resource.  Fortunately, there is an easy solution – simply add the domain as a Local Intranet site in your Internet settings.  Under Internet Options, Security, Local Intranet, Sites, Advanced, you can enter the paths to the client resource that you commonly access.  Once this is done, after you check the “Save Password” option when you access a resource again, you won’t have to reenter the password again.

If the client has a TFS instance, you can save your username and password used to access it by going to the team web access site in Internet Explorer and saving your credentials when prompted.  When you launch Visual Studio and connect to that TFS instance, it will then use your saved credentials.

Setting up a new IIS Server for ASP.NET or MVC

When setting up a new Windows Server for hosting ASP.NET or MVC applications, I have several Powershell scripts that I run to modify some of the default IIS settings.  You can also modify the IIS settings manually, but don’t fear the command line – it is your friend.

Powershell has an IIS module that you will need to import to run most of these commands – WebAdministration.  Now for the first set of scripts:

Import-Module WebAdministration
 
#expire web content after 30 days
Set-WebConfigurationProperty -filter "/system.webServer/staticContent/clientCache" -name cacheControlMode -value "UseMaxAge"
Set-WebConfigurationProperty -filter "/system.webServer/staticContent/clientCache" -name cacheControlMaxAge -value "30.00:00:00"
 
# change logging to include two more properties
Set-WebConfigurationProperty -filter "/system.applicationHost/sites/siteDefaults/logFile" -name logExtFileFlags -value "Date, Time, ClientIP, UserName, ServerIP, Method, UriStem, UriQuery, HttpStatus, Win32Status, BytesSent, BytesRecv, TimeTaken, ServerPort, UserAgent, HttpSubStatus"
 
# change the IIS server's header value to from value -- applies to ENTIRE SERVER
$computer = gc env:computername
Set-WebConfiguration  -filter "/system.webServer/httpProtocol/customHeaders/add[@value='ASP.NET']/@name" -value "From"
Set-WebConfiguration  -filter "/system.webServer/httpProtocol/customHeaders/add[@name='From']/@value" -value $computer

The above scripts are mostly self-explanatory – adjusting logging, static caching, and making sure the HTTP header of the sites on the box will include the box name.  This is especially useful in load-balanced scenarios, when you need to troubleshoot an errant server.

The next script modifies IIS to allow anonymous and windows authentication to be set in the web.config of child applications.

# change the master IIS config file to allow override of anonymous and windows auth
[xml]$config = Get-Content C:\Windows\System32\inetsrv\config\applicationHost.config
$config.selectSingleNode("/configuration/configSections/sectionGroup[@name='system.webServer']/sectionGroup[@name='security']/sectionGroup[@name='authentication']/section[@name='anonymousAuthentication']").SetAttribute("overrideModeDefault", "Allow")
$config.selectSingleNode("/configuration/configSections/sectionGroup[@name='system.webServer']/sectionGroup[@name='security']/sectionGroup[@name='authentication']/section[@name='windowsAuthentication']").SetAttribute("overrideModeDefault", "Allow")
$config.Save("C:\Windows\System32\inetsrv\config\applicationHost.config")

By default IIS does not allow child applications to define their own authentication.  You can change a site’s security policy in the IIS manager, but this modifies the security settings in the applicationHost.config file instead of the web.config of the application.  You can allow the local site’s web.config to define this with the script below:

And finally, I prefer IIS to be clear of any default sites and application pools before I start adding my own, so I remove them (Warning: this will clear all sites and application pools from a server):

# RESET IIS environment
Remove-Item 'IIS:\AppPools\*' -Recurse
Remove-Item 'IIS:\Sites\*' -Recurse

Assembly Implicit References, Installations, and the MSBuild Impact

I spent quite a bit of time trying to figure out why two of our Silverlight XAP files did not include the System.Windows.Controls.Toolkit.Internals.dll file when packaged on a build server.  Of course, when I built these locally, the XAP files were packaged correctly with this assembly being included in the XAP package.  I finally ran the exact same MSBuild command on both servers and then compared the output to determine why it was not being included.  When the Silverlight Toolkit is installed, it puts the reference assemblies under C:\Program Files (x86)\Microsoft SDKs\Silverlight\v4.0\Toolkit\Apr10\Bin.  We have not installed the toolkit on the build server; we have just added the assemblies to source control under a shared path.  What I found is that if the assemblies are not in their “default” location, when MSBuild compiles the code, it won’t copy implied assemblies to the bin directory of the project, which will then prevent these from being included in the XAP file.  In this example, System.Windows.Controls.Toolkit.dll references System.Windows.Controls.Toolkit.Internals.dll, but the latter is not referenced in the project.  If I compile on a machine that has the Toolkit installed, the latter assembly will be copied to the bin.  On a machine that does not have the toolkit installed, the Internals assembly will not be copied to the bin.

Interesting compile behavior.  Lesson learned: make your references explicit, especially for Silverlight projects.

Creating and Modifying MSMQ’s Using Powershell

There are a few Powershell functions that I have written and use to create and delete queues in MSMQ and to modify permissions on these queues.  At the end of the script there are a few examples on how to use the functions.

Clear-Host
[Reflection.Assembly]::LoadWithPartialName("System.Messaging")
  
function Create-Queue
{
    param
    (
        [Parameter(Mandatory=$true)]
        [string]$queueName,
        [switch]$isPrivate = $true,     
        [switch]$isTransactional = $false
    )
      
    $innerQueueName = $queueName
      
    if ($isPrivate)
    {
        $innerQueueName = ".\private$\"+$queueName
    }
    else
    {
        $innerQueueName = ".\"+$queueName
    }
      
    if (![System.Messaging.MessageQueue]::Exists($innerQueueName))
    {
        [System.Messaging.MessageQueue]::Create($innerQueueName, $isTransactional) 
        Write-Host "Created queue " $innerQueueName
    }
    return $innerQueueName
}
  
function Add-QueueUserPermission
{
    param
    (
        [Parameter(Mandatory=$true)]
        [string]$queuePath,
        [Parameter(Mandatory=$true)]
        [string]$user,
        [string]$fullControl = $false
    )
      
    $q = new-object System.Messaging.MessageQueue($queuePath)
      
        if ($fullControl -eq $true)
        {
            Write-Host "FullControl granted to "  $user
            $q.SetPermissions($User, [System.Messaging.MessageQueueAccessRights]::FullControl, [System.Messaging.AccessControlEntryType]::Allow) 
        }
        else
        {
            Write-Host "Restricted access granted to "  $user      
            $q.SetPermissions($User, [System.Messaging.MessageQueueAccessRights]::DeleteMessage, [System.Messaging.AccessControlEntryType]::Set) 
            $q.SetPermissions($User, [System.Messaging.MessageQueueAccessRights]::GenericWrite, [System.Messaging.AccessControlEntryType]::Allow) 
            $q.SetPermissions($User, [System.Messaging.MessageQueueAccessRights]::PeekMessage, [System.Messaging.AccessControlEntryType]::Allow) 
            $q.SetPermissions($User, [System.Messaging.MessageQueueAccessRights]::ReceiveJournalMessage, [System.Messaging.AccessControlEntryType]::Allow)
        }
      
      
}
  
function Delete-Queue
{
    param
    (
        [Parameter(Mandatory=$true)]
        [string]$queuePath
    )
      
    [System.Messaging.MessageQueue]::Delete($queuePath)
}
  
function Delete-Queues
{
    [System.Messaging.MessageQueue]::GetPrivateQueuesByMachine($Env:COMPUTERNAME) | ForEach-Object { Delete-Queue($_.Path) }
    [System.Messaging.MessageQueue]::GetPublicQueuesByMachine($Env:COMPUTERNAME) | ForEach-Object { Delete-Queue($_.Path) }
}
  
#########################################################################################################################
  
#examples
# creates a queue that is private but not transactional
#Create-Queue -queueName "My Queue" -isPrivate $true -isTransactional $false
  
# grants restricted access to the queue
#Add-QueueUserPermission -queuePath ".\private$\My Queue" -user 'domain\account'
  
# grants full control to the queue
#Add-QueueUserPermission -queuePath ".\private$\My Queue" -user 'domain\account' -fullControl

Build Projects and Solutions from Windows Explorer with MSBuild 4.0

Copy the following into a .reg file and run. This will give you the ability to Build, Clean, and Rebuild from the Windows Explorer Context menu.

Windows Registry Editor Version 5.00 

[HKEY_CLASSES_ROOT\SystemFileAssociations\.sln] 

[HKEY_CLASSES_ROOT\SystemFileAssociations\.sln\shell] 

[HKEY_CLASSES_ROOT\SystemFileAssociations\.sln\shell\Build] 

[HKEY_CLASSES_ROOT\SystemFileAssociations\.sln\shell\Build\command] 
@="cmd.exe /K \"\"%%windir%%\\Microsoft.NET\\Framework\\v4.0.30319\\MSBuild.exe\" \"%1\" /t:build\"" 

[HKEY_CLASSES_ROOT\SystemFileAssociations\.sln\shell\Clean] 

[HKEY_CLASSES_ROOT\SystemFileAssociations\.sln\shell\Clean\command] 
@="cmd.exe /K \"\"%%windir%%\\Microsoft.NET\\Framework\\v4.0.30319\\MSBuild.exe\" \"%1\" /t:clean\""

[HKEY_CLASSES_ROOT\SystemFileAssociations\.sln\shell\Rebuild] 

[HKEY_CLASSES_ROOT\SystemFileAssociations\.sln\shell\Rebuild\command] 
@="cmd.exe /K \"\"%%windir%%\\Microsoft.NET\\Framework\\v4.0.30319\\MSBuild.exe\" \"%1\" /t:rebuild\""

[HKEY_CLASSES_ROOT\SystemFileAssociations\.csproj] 

[HKEY_CLASSES_ROOT\SystemFileAssociations\.csproj\shell] 

[HKEY_CLASSES_ROOT\SystemFileAssociations\.csproj\shell\Build] 

[HKEY_CLASSES_ROOT\SystemFileAssociations\.csproj\shell\Build\command] 
@="cmd.exe /K \"\"%%windir%%\\Microsoft.NET\\Framework\\v4.0.30319\\MSBuild.exe\" \"%1\" /t:build\""

[HKEY_CLASSES_ROOT\SystemFileAssociations\.csproj\shell\Clean] 

[HKEY_CLASSES_ROOT\SystemFileAssociations\.csproj\shell\Clean\command] 
@="cmd.exe /K \"\"%%windir%%\\Microsoft.NET\\Framework\\v4.0.30319\\MSBuild.exe\" \"%1\" /t:clean\""

[HKEY_CLASSES_ROOT\SystemFileAssociations\.csproj\shell\Rebuild] 

[HKEY_CLASSES_ROOT\SystemFileAssociations\.csproj\shell\Rebuild\command] 
@="cmd.exe /K \"\"%%windir%%\\Microsoft.NET\\Framework\\v4.0.30319\\MSBuild.exe\" \"%1\" /t:rebuild\""

[HKEY_CLASSES_ROOT\SystemFileAssociations\.vbproj] 

[HKEY_CLASSES_ROOT\SystemFileAssociations\.vbproj\shell] 

[HKEY_CLASSES_ROOT\SystemFileAssociations\.vbproj\shell\Build] 

[HKEY_CLASSES_ROOT\SystemFileAssociations\.vbproj\shell\Build\command] 
@="cmd.exe /K \"\"%%windir%%\\Microsoft.NET\\Framework\\v4.0.30319\\MSBuild.exe\" \"%1\" /t:build\""

[HKEY_CLASSES_ROOT\SystemFileAssociations\.vbproj\shell\Clean] 

[HKEY_CLASSES_ROOT\SystemFileAssociations\.vbproj\shell\Clean\command] 
@="cmd.exe /K \"\"%%windir%%\\Microsoft.NET\\Framework\\v4.0.30319\\MSBuild.exe\" \"%1\" /t:clean\""
[HKEY_CLASSES_ROOT\SystemFileAssociations\.vbproj\shell\Rebuild] 

[HKEY_CLASSES_ROOT\SystemFileAssociations\.vbproj\shell\Rebuild\command] 
@="cmd.exe /K \"\"%%windir%%\\Microsoft.NET\\Framework\\v4.0.30319\\MSBuild.exe\" \"%1\" /t:rebuild\""

For more advanced control, try the MSBuild Launch Pad on Codeplex.

Team Foundation Server 2010 Security Practices

A coworker asked me to point him to the location where to find the documents on TFS Security Best Practices and I realized they don’t exist!  There are a number of blog posts with people describing how they have configured TFS, and there are some documents on MSDN that provide the permissions and roles that are available in TFS, but I didn’t see anything that provided a good overview and guidance on setting up and managing TFS security.  So, I will briefly cover the surface area of TFS security, a couple of tools that will help make security management a bit easier, and then some practical recommendations on configuring security.

TFS Security

Managing security in TFS involves managing security to three different components: TFS itself, WSS or Sharepoint, and Reporting Services.  Each of these has different access levels, roles, and permissions, so it isn’t as simple as a single selection.  Out-of-the-box, a team project will be compromised of the following roles within the different application components:

Team Project Roles	Sharepoint Roles	Reporting Services Roles
Project Administrators Contributors Readers Builders	Full Control Design Contribute Read	Content Manager Browser

Straight from the MSDN documentation on the Team Project roles:

• ProjectName\Project Administrators Members of this group can administer all aspects of the team project, although they cannot create projects.
• ProjectName\Contributors Members of this group can contribute to the project in multiple ways, such as adding, modifying, and deleting code and creating and modifying work items.
• ProjectName\Readers Members of this group can view the project but not modify it.
• ProjectName\Builders Members of this group have build permissions for the project. Members can manage test environments, create test runs, and manage builds.

Additionally, access to TFS can be granted at three primary levels – server, collection, and project.  You will very rarely assign permissions at the server and collection level, although the latter is a little more common.  Typically your TFS Admin account(s) and service accounts will be assigned at the server level, and your collection administrators will be assigned at the collection level.  The bulk of the role and permission assignments happen at the team project level.

TFS Security Tools

Fortunately, you don’t have to manage the permissions to all three locations separately.  TheTeam Foundation Server Administration Tool is a free, open-source, tool that consolidates this into a single interface.  The other tool that I have found extremely useful for viewing and managing user workspaces and content is the Team Foundation Sidekicks tool from Attrice(also free).  Note that both of these tools are specific to a team project; you can’t use either to manage the server or collection membership.

Configuring Security

Since TFS is built on top of and integrates nicely with Active Directory, it is strongly recommended to use Active Directory groups to manage access to TFS.  In fact, I’ll go so far as to say unless it is impossible to do so, manage all access to TFS through Active Directory groups.  I think you will find this can be done in almost all of the cases.  This will radically simplify the surface area of what must be managed.  As a practical note, typically a new hire will need to be added to certain A/D groups; why not include the appropriate TFS A/D groups in the “new hire ticket” as well?  In addition, by configuring access to an Active Directory group, granting users the same set of permissions involves simply adding them to that group, rather than duplicating the entire subset of permissions.

• Use Active Directory groups to manage TFS access

The other strong recommendation I would make is that access to TFS should reflect the structure of the project teams.  Since we are remaining with A/D groups, this means my A/D group structure that provides access to TFS should reflect the team and project structure.  I’ll also add that it will be very helpful to create a tree structure of groups so that permissions will roll up.  I will talk more of this as we walk through some scenarios.  My primary push for this is to encourage you to work with your Infrastructure people to get groups defined in such a way that reflects your teams and projects, if possible.  Usually they like simple and easy-to-manage too!

• A/D groups for TFS access should reflect team and project structure.

Let’s cover project collections and team projects.

When would I consider a new project collection?

The primary limitation to keep in mind with team project collections is that the artifacts are not accessible across collections. You cannot access work items, source, builds, etc. across collections. If your team or organization does not integrate with other teams and has very little if any dependencies, a collection may make more sense. However, keep in mind – moving a team project to a different collection is not supported. There are a variety of reasons for this – within each collection, work item IDs, changeset numbers, builds, etc. all start from 1, so you would have major clashes if you tried to consolidate collections. You might consider project collections at a department level – perhaps HR, Internal IT, and Products each have their own collection.

When would I consider a new team project?

A team project can provide a way to encapsulate security, code, and project management activities for a team or project. If you are leveraging Sharepoint and keep the option checked, TFS will create a new Sharepoint site when the project is created. Items across team projects are visible to other team projects within a collection, if security allows. This includes work items, builds, source control, and reports. You might consider team projects when you are creating a product, starting a new project that falls within existing department, or want to track activities that fall within a distinct bucket. Keep in mind that Areas and Iterations can be used within team projects to provide some segregation of activities, so you may not need a new team project if it is just an extension of an existing project or effort.

Now that we have the above two ideas defined and some collection versus team project recommendations, let’s walk through some practical scenarios.  These are based on several different organizations that I’ve been a part of and the work I did in managing TFS access for each organization.  My convention is to use a “TFS.” prefix on the A/D group name so that it is clear that it is for TFS access.  However, this can also be done via OUs in A/D, depending on your Infrastructure preferences.

Scenario 1: Multiple Departments, Multiple Products, Not-Shared within an IT organization

For our first scenario, let’s take an IT department that contains multiple teams, where each team works on multiple, independent internal company products.  It is very unusual for the teams to share resources and thus any sharing is considered a transfer to a new team.  Each team is cross-functional, containing QA, dev, BA, manager, and architect resources.  The access requirements for these resources are the following:

• Architects and possibly team leads should have Project Admin rights, and are the ones responsible for builds.
• Developers should be Contributors.
• QA, BA, and managers should have read-only rights to the source control, but be able to manage work items, iterations, areas, etc.
• The PMO office (executives) want to run reports and read access to source is fine.

We might create a group structure that looks like the following:

A/D Group Name	TFS Team Project Permission	Members
TFS.All	–	All TFS.* groups
TFS.ProjectName	–	All TFS.ProjectName.* groups
TFS.ProjectName.Admins	Project Administrator	Architects, maybe leads
TFS.ProjectName.Developers	Contributor	Developers
TFS.ProjectName.Editors	Work Item Editor	QA, BAs, Managers
TFS.ProjectName.PMO	Reader	PMO office

The Work Item Editor role is a new TFS security group that we will create for the project, with the permissions for that role granting view and edit on work items, iterations, and areas, but read only on source control.  Note that we create a group that contains the other groups – this makes it easy to do team level activity, like email everyone on the team, regardless of role.  If you need to separate the Editor group into separate QA, BA, and Manager groups, that can be easily done as well.

Scenario 2: Multiple Departments, Multiple Products, Shared within an IT organization

For our second scenario, let’s modify one thing: the developer and QA resources within the organization are shared across projects, but the team leads, managers, and BAs are dedicated consistently to a project.  In this case, we might do something like:

A/D Group Name	TFS Team Project Permission	Members
TFS.All	–	All TFS.* groups
TFS.ProjectName	–	All TFS.ProjectName.* groups
TFS.ProjectName.Admins	Project Administrator	Project leads
TFS.ProjectName.Editors	Work Item Editor	Managers and BAs
TFS.QA	Work Item Editor for all dev projects	QA
TFS.Developers	Contributor for all dev projects	Dev, Architects

Once again, you can separate the devs and architects, QA and BAs, if needed.

Scenario 3: Single Department, Multiple Products

For the third scenario, we just have a single department with multiple products (and thus I am assuming it is the same team that works the products).  You can create each product as a separate team project, and in some cases, this can make sense.  The big advantage to having the different products within the same team project is that you can roll everything up under a single project, so it makes resource availability, tracking, and iteration planning simpler.  Since this scenario assumes the same team, the groups don’t need any team or project designation:

A/D Group Name	TFS Team Project Permission	Members
TFS.All	–	All TFS.* groups
TFS.Admins	Project Administrator	Project leads
TFS.Managers	Work Item Editor	Managers
TFS.Editors	Work Item Editor for all dev projects	QA and BAs
TFS.Developers	Contributor for all dev projects	Dev, Architects

Scenario 4: Multiple Teams, Multiple Products, Some Common, Some Team-Specific

For the fourth scenario, let’s take the same idea of multiple teams and products, but vary it slightly by stating that each team manages multiple products, and there are some projects that are common across all the teams.  In this instance, we might create team specific A/D groups and then assign them to the respective product-specific team projects and any common team projects for which they need access.  I’ve found it is helpful to create a team project named Common or Shared that contains the shared artifacts across the teams, so that it is clear that it is shared.  If you want to segregate it further (have multiple shared team projects), consider naming the common team projects with a Common or Shared prefix, such as “Common-Architecture”, “Common-Services”, etc.

A/D Group Name	TFS Team Project Permission	Members
TFS.All	–	All TFS.* groups
TFS.TeamName	–	All TFS.TeamName.* groups
TFS.TeamName.Admins	Project Administrator for team-specific and common projects	Project leads
TFS.TeamName.Developers	Contributor for team-specific and common projects	Developers
TFS.TeamName.Editors	Work Item Editor for team-specific and common projects	Managers, QA, BAs

You can of course separate the common project access into its own security groups, if needed (TFS.Common.Admins, TFS.Common.Developers,…).

Hopefully this helps as you define your TFS security model.

New Computer Modifications

Each time I get a new computer I find myself turning off certain services, uninstalling Windows features, and disabling certain components.  Windows 7’s default installation includes items such as: Tablet PC Components, Windows Media Center, Windows DVD Maker, Windows Media Player, and Windows Gadget Platform.  It also has System Restore and Hibernation enabled, neither of which I use.  The commands below are what I run to turn off or disable the above features (Run in an elevated Powershell command window):

Disable-ComputerRestore –drive "C:"
powercfg –H off
DISM /online /Disable-Feature /FeatureName:MediaCenter /FeatureName:TabletPCOC /FeatureName:WindowsGadgetPlatform /FeatureName:WindowsMediaPlayer /FeatureName:MediaPlayback

You will need to restart your computer after running the above, but after doing so, you'll have more free CPU cycles and memory. Enjoy!

Essential TFS Process Template Modifications

Out-of-the-box TFS 2010 ships with two very solid process templates, and you can install the Scrum 1.0 template and the Scrum for Team Systems 3.0 templates as well.  However, there are two template modifications in the Agile template that are very useful.  The first is to modify the Assigned To field to exclude some of the TFS service accounts and duplicate values.

<FIELD name="Assigned To" refname="System.AssignedTo" >
<ALLOWEDVALUES expanditems="true" filteritems="excludegroups">
<LISTITEM value="[Project]\Project Administrators"/>
<LISTITEM value="[Project]\Contributors"/>
<LISTITEM value="[Project]\Not Assigned"/>
</ALLOWEDVALUES>
<PROHIBITEDVALUES expanditems="true">
<LISTITEM value="Project Administrators"/>
<LISTITEM value="Contributors"/>
<LISTITEM value="Not Assigned"/>
</PROHIBITEDVALUES>
<DEFAULT from="value" value="Not Assigned"/>
<ALLOWEXISTINGVALUE/>
<HELPTEXT>..snip..</HELPTEXT>
</FIELD> 

The other two modifications are to create initial states in both the Task and Bug work item types.  This can be done by adding the states in the work item type editor, editing the workflow to add the states, and then republishing the work items back to the server.  The flow on a bug would be modified to Created > Active > Resolved > Closed and the Task flow would be modified to a flow of Created > Active > Closed.

SQL Server 2012 RTM

SQL Server 2012 went RTM yesterday!  The evaluation is available for free or you can get one of the full versions from MSDN subscriber downloads.  One of the changes I'm excited about is that the Management Studio IDE has been integrated with Visual Studio, so we now have one development IDE for both application and database code.

TFS 2010, Build Definitions, and Powershell

I needed to get a list of build definitions from a TFS 2010 instance as we are planning to migrate to a new server and will not be moving the collection, but only the builds and source files inside the collection. The following Powershell scripts will list out the build definitions for a server:

clear-host

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.TeamFoundation.Client")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.TeamFoundation.Build.Client")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.TeamFoundation.WorkItemTracking.Client")


$tfsCollectionUrl = "http://{server}:8080/tfs/{collection}"
$server = new-object Microsoft.TeamFoundation.Client.TfsTeamProjectCollection(New-Object Uri($tfsCollectionUrl))
$buildServer = $server.GetService([Microsoft.TeamFoundation.Build.Client.IBuildServer])
$workStore = $server.GetService([Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItemStore])

$workStore.Projects | ForEach-Object {
Write-Host ("Project: "+$_.Name)
$buildServer.QueryBuildDefinitions($_.Name) | select-object {$_.Name, $_.ContinuousIntegrationType} | format-table
}