- TFS2010: Warehouse and Job Service Administrator Reports
-
The new TFS Administration Console will show you very basic information about warehouse & cube processing. However, it doesn’t show you anything about the queued & executing jobs. These are both gaps that I hope we address in the shipping product in the future. In the meantime for any real warehouse or job status information you have to hit the web services. Since the web services don’t allow you to invoke them using a web browser from a remote computer, you’re forced to logon locally to the server and run them from there.
The smarter option is to download yourself a copy of WebServiceStudio, which allows you to invoke web methods with complex parameters from your remote workstation. No more logging onto servers!
Here are the WSDL EndPoint’s that you’ll need. Copy them in, change the server name, select ‘Get’, select a method from the tree, select ‘Invoke’, (ignore the exception dialog if you get one the first time), select ‘Invoke’ again and there you have it.
Now, the problem with either reading the XML from the web browser (argh! my eyes!), or using WebServiceStudio is that there’s lots of nested information which makes it difficult to find out what’s going on. Fortunately with a fairly simple report and the XML Data Source that SQL Reporting Services provides, we can make it much, much nicer.
Introducing the first two TFS2010 Administration Reports
As I run the Pioneer dogfood TFS server day-to-day, I notice opportunities for tools to help make TFS administrator’s lives easier. Here’s the first two reports to give you a better insight as to what is happening on your server and allow you to investigate problems without having to mess around with web services.
Download & Setup
- Open a browser an browse to your SQL Reporting Services root site. For example: http://tfsserver/Reports/
- Create a new folder and name it ‘Status’
- Create two new shared data sources with the following properties:
- Download the ZIP attachment of this post (TFS2010AdministratorReports.zip)
- Extract the contents to a temporary folder on your workstation
- Select ‘Upload File’ from the reporting services manager
- Browse to the temporary folder where you extracted the reports, select ‘OK’.
- Repeat for the other report
- Try it out!
- Get somebody else to try it out and make sure permissions are set correctly.
Warehouse Status Report
The first part of the report shows you the overall status, similar to the ‘Reporting’ tab in the Team Foundation Administration Console. This is a quick an easy way to find out if an Incremental or Full processing is in progress. It will also show you any errors (like warehouse schema conflicts) in the ‘Last Run’ column.
The second part of this report is useful after an upgrade or when the warehouse needs to be rebuilt manually. It shows you each of the data adapter sync jobs for each collection and their current status. During normal operation, these will run very quickly as data changes in the operational stores, so you’ll probably always see them as “Idle”. It will also show you any errors from previous job executions in the ‘Last Run’ column.
Job Status Report
The first part of this report shows you the job definitions for the instance and the interval they’re set to run on. This is useful for checking to see if a job has somehow been disabled or changed.
The second part of this report shows you the job history. Let me explain each of the fields:
- Job ID – Every job in the system has a unique id.
- Job Agent ID – Every AT/Job Agent has a unique id. This field is useful if you have multiple application tiers and you want to know which one the job executed on so that you can investigate the event log or performance counters.
- Result – This is an integer that represents the result of the job. 0 = Succeeded, 1 = PartiallySucceeded, 2 = Failed, 3 = Stopped, 4 = Killed, 5 = Blocked, 6 = ExtensionNotFound, 7 = Inactive, 8 = Disabled
- Queued Reason – This is an integer that represents the way a job was scheduled. 0 = No reason to execute the job, 1 = Job has a schedule, 2 = Queued manually, 4 = Queued manually while already in progress, 8 = Queued due to previous result (Blocked, Inactive)
- Start Time – The local time that the job started executing on a job agent
- End Time – The local time that the job finished executing on a job agent
- Duration – The number of minutes that the job was executing for
- Result Message – Jobs are able to log a status message when they finish executing. Most jobs don’t, since they execute very often and there’s no need to log anything if the job succeeded.
There’s a few little visual styles that I added to allow you to glance the report and find problems:
- Hover over a job result message to see the full text
- Job results that failed have their error printed in red
- Jobs that took longer than 30 minutes have their duration in red
- Jobs that took longer than 10 minutes have their duration in orange
Filters
There is a lot of noise when dealing with jobs, so I added some parameters to help you filter out the noise.
- Unscheduled jobs - are jobs that have a definition, but no schedule set. Examples: “Create Team Project Collection”, “Delete Team Project Collection”, “Service Team Project Collection”, “Provision Attach Team Project collection” and “Team Foundation Server On Demand Identity Synchronization”.
- Successful jobs - are jobs that have a job result of ‘0’
If you find these useful, leave me a comment or drop me an email. I’d love suggestions for them or for any new TFS2010 Administrator reports.
- TFS2010: Invoking TFS web services using PowerShell
-
In this blog post, I’m going to show you how to invoke the Visual Studio 2010 Team Foundation Web Services remotely using Windows PowerShell 2.0.
There are some TFS administrative functions that can only be performed remotely using the TFS ASMX web services. Over time, there will be powertools and functionality in the command line tools and TFS Administration Console that allows you to access these. In the meantime though, we have to create our own scripts and utilities for doing this.
PowerShell 2.0 is installed by default in Windows 7 and Windows Server 2008 R2. It includes a new cmdlet called “New-WebServiceProxy”. Using this cmdlet, we can create an in-memory web service proxy for the TFS web services and use it to invoke some useful web methods.
Viewing currently executing requests
1. Open Powershell 2.0 from the Start menu.
2. Create the web service proxy by copying this command and replacing your server name in the URL. The UseDefaultCredentials is required so that we are authenticated to the web service.
$tfsadmin = New-WebServiceProxy –UseDefaultCredential -URI http://tfsserver:8080/tfs/TeamFoundation/administration/v3.0/AdministrationService.asmx?WSDL
3. Invoke the QueryActiveRequests web method, expand the ActiveRequests property for each item returned and then format some of the properties (User, Method, etc) as a table (ft).
$tfsadmin.QueryActiveRequests($null, "False") | %{ $_.ActiveRequests } | ft StartTime,UserName,MethodName,RemoteComputer
This is a very simple example which should be enough to get you started. Some of the other Administration web services you might want to explore are:
Where possible, you should use the Microsoft.TeamFoundation object model since that is the officially supported API. An example for modifying the TFS registry is included in this post.
- TFS2010: What happens to subscriptions after an upgrade?
-
Both TFS2008 and TFS2010 allow users to create subscriptions to events. These can be Work Item events, CheckIn events and others. They are created via the ‘Project Alerts’ option in Team Explorer or Web Access, or the ‘Alerts’ feature in the Team Foundation Power Tools. An administrator can also run bissubscribe.exe on the Application Tier to setup web service/SOAP subscriptions.
A few people have asked me what happens to subscriptions when a server is upgraded from TFS2008 to TFS2010, so here’s how it works:
Upgrade from TFS2008 and Attaching a Team Project Collection to an existing instance
- Subscriptions & alerts that users have configured get preserved as-is
- The only difference is that we’ll now store the subscriber by the internal TF identity GUID instead of the SID. This is transparent to the user.
- For any subscription with a subscriber who is in the service accounts group, we replace the subscriber with the service group (e.g. ‘DOMAIN\tfsservice’ turns into ‘[CollectionFoo]\Service Accounts Group’)
- We remove SOAP subscriptions which have endpoints that end in “/VersionControl/v1.0/Integration.asmx” or “/WorkItemTracking/v1.0/SyncEventsListener.asmx” or “/Warehouse/v1.0/warehousecontroller.asmx”. These are internal subscriptions used in previous versions to keep areas, iterations and identities in sync.
- During TFS2008 upgrade or when you attach a new Team Project Collection (TPC) we replace the url prefix of SOAP subscription which ends in “/WorkItemTracking/v1.0/Integration.asmx” with a token that gets filled in at runtime with the actual public url to the TPC
TPC Move
This is the scenario that you detach a TPC from one instance and attach it to another instance
- Subscriptions & alerts that users have configured get preserved as-is. They are stored in the collection’s database, so they move with the collection.
TPC Split
- Subscriptions & alerts that users have configured get copied to the split collection.
- When you split a TPC, you will want to make sure you clean out the subscriptions or at least warn users that they may receive duplicate alerts.
- TFS2010: Object Model API changes after Beta 2
-
Based on feedback that we received both internally and externally we decided to make a rather significant change to the TFS client object model. This change centers around the TeamFoundationServer, TeamFoundationApplicationInstance and TeamFoundationServerBase classes and their supporting factories.
The History
First some background. In Orcas and Whidbey the TeamFoundationServer object was used as the starting point for working with the Team Foundation client object model. In TFS 2010 we now have Team Project Collections and the Team Foundation Configuration Server and unfortunately the TeamFoundationServer object is no longer specific enough to describe the end point that you are working with.
To combat this originally we decided to repurpose the TeamFoundationServer class for talking to Team Project Collections and introduce a sibling class called TeamFoundationApplicationInstance for talking to what was at the time called the Application Instance and has since been renamed to the Configuration Server. We also introduced a base class called TeamFoundationServerBase to contain all of the shared code between the TeamFoundationServer and TeamFoundationApplicationInstance classes.
Martin Woodward describes this old model in his first TFS 2010 API By Example post.
The Changes
Since we made that original change, we have gotten feedback both internally and more recently externally that these objects were very confusing. Because of that feedback we decided to make some changes to these objects to make them more intuitive. The main changes that were made are as follows:
- Renamed the TeamFoundationServerBase class to TfsConnection.
- Renamed the TeamFoundationApplicationInstance class to TfsConfigurationServer.
- Introduced the TfsTeamProjectCollection class to replace the TeamFoundationServer class.
- Obsoleted the TeamFoundationServer class.
The Impact
If you have started writing tools that use the TFS 2010 Beta 2 object model, you should anticipate these changes in our final release and allow yourself time to make the changes and test them.
Here are some of the things that have changed throughout this refactoring and some things that you should look for and validate if you are reviewing these changes:
- The TeamFoundationServer class had many constructor overloads, several that took strings and several that took URIs. We have been wanting to get rid of the string overloads (that allowed you to pass a server name or a server url) as part of this effort so the new TfsTeamProjectCollection class does not have string constructor overloads. Instead you need to create this object with a URI just like you have to do with the TfsConfigurationServer object. There is a static helper on the TfsTeamProjectCollection class called GetFullyQualifiedUriForName that will return you the collection URI given a string that either contains the collection name or the collection url. If the collection url is passed in this is essentially a no-op.
- Any public property or method on a public class that either returned a TeamFoundationServer object or took it in as a parameter was deprecated. Along with this, a mirroring property or method was added that simply replaced the TeamFoundationServer type with the TfsTeamProjectCollection type. If there was any doubt as to whether or not that property or method had shipped in Orcas or Whidbey we made the assumption that it had shipped and deprecated it instead of removing it. If we knew that it had been added in the TFS 2010 time frame, we simply removed the property or method that exposed the TeamFoundationServer class.
- With the above refactoring of properties and methods we tended to only rename the property or method name if it was exposed to the public and very wrong. I.e. if we saw GetTeamFoundationServer(), we renamed it to GetTeamProjectCollection().
- The TfsTeamProjectCollection class does have an internal property that exposes a related instance of the TeamFoundationServer class. The TeamFoundationServer object also exposes a TfsTeamProjectCollection instance. These properties are used to support public methods or functions that need a TeamFoundationServer in a backward compatibility scenario.
If you have an addin or tool built against the Beta2 object model and you don’t have the Beta2 assemblies installed, then you’ll get something like the following error:
Team Foundation Error
Could not load type 'Microsoft.TeamFoundation.Client.TeamFoundationServerBase' from assembly 'Microsoft.TeamFoundation.Client, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.
System.TypeLoadException: Could not load type 'Microsoft.TeamFoundation.Client.TeamFoundationServerBase' from assembly 'Microsoft.TeamFoundation.Client, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.
To fix this, you’ll need to contact the addin/tool owner to get them to release a compatible version with the changes mentioned in this post.
- TFS2010: Older clients not able to connect
-
When the Forward Compatibility GDR for Visual Studio 2008 shipped, a change was made to the Team Foundation Server 2010 code that blocks incompatible clients. That means that after TFS2010 beta2, clients that don’t have a forward-compatible patch installed, won’t be able to connect.
For more information on what features will & won’t be available, see the TFS2010 Compatibility Matrix.
When a client connects to TFS, the UserAgent is checked against a list in the TFS registry and if it matches any of these, an error message specific to that client is returned to the user. For example:
Team Foundation services are not available from the server.
Technical information (for administrator):
TF253022: You must update your client with the Forward Compatibility Update in order to connect to the Team Foundation Server that you selected. To obtain this update, go to the Microsoft Web site: http://go.microsoft.com/fwlink/?LinkId=16648
The list of UserAgent’s that is checked is defined in the \Configuration\Application\DisabledUserAgents path in the TFS registry of the instance:
Team Foundation (*.exe, 8.0.50727.<4406) – Visual Studio 2005 without the forward-compat GDR (available closer to launch)
Team Foundation (*.exe, 9.0.21022.*) – Visual Studio 2008 RTM
Team Foundation (*.exe, 9.0.30729.<4172) – Visual Studio 2008 SP1 without the forward-compat GDR
Team Foundation (VSSConverter.exe, 8.*)
Team Foundation (VSSConverter.exe, 9.*)
Teamprise 1.*
Teamprise 2.*
Teamprise*/3.<3* – Teamprise 3.3 introduced basic support for TFS2010
Of course, if you know how to change the TFS registry, you can remove these bans. However, I wouldn’t recommend doing that, because using incompatible clients against the server can cause some strange things to happen. You’re much better off just patching the clients.
- Multi-threaded robocopy for faster copies
-
Windows 7 and Windows Server 2008 R2 include an updated version of Robocopy that includes a multi-threaded copy feature.
/MT[:n] :: Do multi-threaded copies with n threads (default 8).
I’ve been using this for a while now and I’ve been seeing excellent results. Be careful with who you share a switch with though – I managed to saturate my 100Mbit switch so badly that my fellow switch neighbors started getting timeouts. My favorite usage is:
robocopy \\server\share\folder C:\Temp\folder /S /MT /Z
This copies the contents of the remote folder, with 8 threads in restartable mode – great for when you’re on a slow or unreliable link. Also note that the progress monitors don’t seem accurate in multi-threaded mode. It starts copying 8 files at once, but only shows you the progress of 1 of them.
- TFS2010: Troubleshooting SharePoint Dashboards data source problems
-
In the TFS 2010 release, if you have the Enterprise features of Microsoft Office SharePoint Server (MOSS) configured (i.e. Single Sign-On and Excel Services), then you can make TFS Excel Reports available to your users via a web site. This is very compelling since Excel makes PivotTables and PivotCharts easy to use and for users viewing the rendered charts via the web it is painless user experience.
For more information see the Excel Reports topic on MSDN. Here is an example of a working SharePoint Dashboard
However, there are a lot of moving parts required to get all this working. TFS 2010, SQL Analysis Services, MOSS (Single Sign-On Service, Excel Calculation Services). The purpose of this article is not to describe how to get it working, but where to look when it stops working. If you are starting from scratch, then you should refer to the “Checklist: Multiple-Server Installation” topic in the TFSInstall.chm guide for TFS 2010 and the Configure Settings for Dashboard Compatibility topic on MSDN. I’ve been through those steps more times than I can remember and my feedback went into it to help you be successful where I stumbled.
Error: Unable to retrieve external data
In my experience, this is the most common error:
Unable to retrieve external data for the following connections:
TfsOlapReport
The data sources may be unreachable, may not be responding, or may have denied you access.
It can mean a few different things:
- Excel Services cannot connect to Analysis Services or SQL because of a connectivity issue (or it’s trying to connect to the wrong server)
- The credentials returned from the SSO service are incorrect or they don’t have the right permission to connect to Analysis Services / SQL
- The user browsing the website is not not a member of the group that the SSO credentials are allowed to be used by
- The SSO service can’t retrieve the credentials from it’s store
The first thing to do is to check the connection.
- Open one of the Excel reports from the Excel Reports document library in Excel (the desktop application)
- Click on the Data tab in the ribbon
- Click on the Connections button to display the Workbook Connections dialog box
- Click on the TfsOlapReport data connection
- Click on the Properties... button
- Click on the Definition tab of this dialog box
This should show a connection string like the following:
Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;User ID="";Initial Catalog=Tfs_Analysis;Data Source=ASSERVER;Location=ASSERVER;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error
If the connection string is incorrect, then you need to open the Team Foundation Administration Tool on your Application Tier and configure the reporting settings correctly.
The next thing to check is the Single Sign-On configuration.
- Click on the Authentication Settings... button
The SSS radio button should be selected and an SSS ID specified
If an SSS ID (sometimes incorrectly referred to as an SSO ID) is not specified, then you need to open the Team Foundation Administration Tool on your MOSS server and configure an SSS ID.
Troubleshooting data source problems
The best place to start looking is the combined SharePoint and Excel Calculation Services trace logfiles.
- Logon to the server where MOSS is installed
- Open a command prompt as administrator
- Type: explorer C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\LOGS
- Open the workbook in a web browser – you should receive the error “Unable to retrieve external data” error message
- At the command prompt type: net stop sptrace && net start sptrace
- Select ‘Update > Refresh all data connections’ in the excel workbook (below)
- Type: net stop sptrace && net start sptrace
Then if you sort the files in the LOGS directory by Newest to Oldest, the second most recent one should have the Excel Services & SSO tracing in it. That usually has a stacktrace or error message indicating where the failure is. You can also use ULSViewer to look at the log files, however I find Notepad is adequate.
Here’s an example of a successful workbook refresh.
Excel Services Web Front End 6k38 Medium ExcelServerProxy.ExecuteWebMethod: sending request of type OpenWorkbook, to server=http://mossserver:56737/SharedServices1/ExcelCalculationServer/ExcelService.asmx, session=(null), state id=0
Excel Calculation Services 766u Medium ExcelService.LogRequest: starting request of type OpenWorkbook
Excel Services Session 5pbu Medium SessionManager.BeginRequest: There are 1 active users
Excel Services Session 1sxs Medium SessionManager.BeginRequest: Created Session id=23.485TDKRh5qu7zU3Ghay5tQ590.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060, caller=fe80::982:6438:527c:31f1%11
SSO 0 Medium RPC request to ncalrpc_AuthenticateClientBinding()
SSO 0 Medium RPC request to s_GetCredentials
SSO 0 Medium RPC request to GetCredentials()
SSO 0 Medium Cached entry is stale, so will not return credentials
SSO 0 Medium CSsoResourceManager succeeded in getting the connection string
SSO 0 Medium Provider=SQLOLEDB;Extended Properties='Server=SQLSERVER;Database=SSO;Trusted_Connection=yes;App=Windows SharePoint Services;Timeout=15';
SSO 0 Medium User is a member of the group.
SSO 0 Medium Destroying CCommandEx
SSO 0 Medium Destroying CSessionEx
SSO 0 Medium CSsoResourceManager succeeded in getting the connection string
SSO 0 Medium Provider=SQLOLEDB;Extended Properties='Server=SQLSERVER;Database=SSO;Trusted_Connection=yes;App=Windows SharePoint Services;Timeout=15';
SSO 0 Medium RPC request to ncalrpc_AuthenticateClientBinding()
SSO 0 Medium RPC request to s_GetApplication
SSO 0 Medium RPC request to GetApplication()
SSO 0 Medium CSsoResourceManager succeeded in getting the connection string
SSO 0 Medium Provider=SQLOLEDB;Extended Properties='Server=SQLSERVER;Database=SSO;Trusted_Connection=yes;App=Windows SharePoint Services;Timeout=15';
SSO 0 Medium Destroying CCommandEx
SSO 0 Medium Destroying CSessionEx
SSO 0 Medium CSsoResourceManager succeeded in getting the connection string
SSO 0 Medium Provider=SQLOLEDB;Extended Properties='Server=SQLSERVER;Database=SSO;Trusted_Connection=yes;App=Windows SharePoint Services;Timeout=15';
SSO 0 Medium RPC request to s_GetApplicationFields
SSO 0 Medium RPC request to GetApplicationsFields()
SSO 0 Medium CSsoResourceManager succeeded in getting the connection string
SSO 0 Medium Provider=SQLOLEDB;Extended Properties='Server=SQLSERVER;Database=SSO;Trusted_Connection=yes;App=Windows SharePoint Services;Timeout=15';
SSO 0 Medium CSsoResourceManager succeeded in getting the connection string
SSO 0 Medium Provider=SQLOLEDB;Extended Properties='Server=SQLSERVER;Database=SSO;Trusted_Connection=yes;App=Windows SharePoint Services;Timeout=15';
SSO 0 Medium Destroying CCommandEx
SSO 0 Medium Destroying CSessionEx
Excel Services External Data 13l9 Medium ExternalSource.GetExternalKeyAndStateAndMarkUsed: Key exists in cache - waiting for it to be created, sessionId=23.485TDKRh5qu7zU3Ghay5tQ590.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060, externalsource=TfsOlapReport, key=[ts: 12/03/2009 13:04:14.192943, [serviceaccount, en-US, en-US, +0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060, [Oledb, Stored, TFS, False, ], ()] BaseWB: "http://mossserver/sites/COLLECTION/Project/Reports/Bug Progress.xlsx" [0x409] [11/06/2009 19:14:04.000000] [BaseWB ID: 2]] 00000412-f5f3-0da1-0000-000050f7b00b
Excel Services External Data 13la Medium ExternalSource.GetExternalKeyAndStateAndMarkUsed: Key exists in cache - wait was successful, sessionId=23.485TDKRh5qu7zU3Ghay5tQ590.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060, externalsource=TfsOlapReport, key=[ts: 12/03/2009 13:04:14.192943, [serviceaccount, en-US, en-US, +0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060, [Oledb, Stored, TFS, False, ], ()] BaseWB: "http://mossserver/sites/COLLECTION/Project/Reports/Bug Progress.xlsx" [0x409] [11/06/2009 19:14:04.000000] [BaseWB ID: 2]] 00000412-f5f3-0da1-0000-000050f7b00b
Excel Calculation Services 2m5e Information Session opened with workbook http://mossserver/sites/COLLECTION/PROJECT/Reports/Bug Progress.xlsx. [Session: 23.485TDKRh5qu7zU3Ghay5tQ590.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060 User: REDMOND\granth] 00000412-f5f3-0da1-0000-000050f7b00b
Excel Calculation Services 5cag Medium ExcelService.PostProcessRequest: finished request of type OpenWorkbook 00000412-f5f3-0da1-0000-000050f7b00b
Excel Services Web Front End 6k39 Medium ExcelServerProxy.ExecuteWebMethod: request of type OpenWorkbook succeeded, from server=http://mossserver:56737/SharedServices1/ExcelCalculationServer/ExcelService.asmx, session=23.485TDKRh5qu7zU3Ghay5tQ590.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060, new state id=1
Excel Services Web Front End 6k30 Medium ExcelServerProxy.SetReachable: server: http://mossserver:56737/SharedServices1/ExcelCalculationServer/ExcelService.asmx.
Excel Web Access 5cbj Medium InternalEwr.RenderWebPart: Finished rendering internal EWR
And here’s the sorts of messages you’ll get if the workbook cannot be refreshed.
The workbook 'http://mossserver/sites/COLLECTION/PROJECT/Reports/Bug Progress2.xlsx' attempted to access external data using the unsupported provider 'Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;User ID="";Initial Catalog=Tfs_Analysis;Data Source=ASSERVER;Location=ASSERVER;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error'. [Session: 23.3897Bz4UV5Lllg0FPFLZm8k90.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060 User: DOMAIN\user] 00000301-f5f3-0da1-0000-000050f7b00b
Refresh failed for 'TfsOlapReport' in the workbook 'http://mossserver/sites/COLLECTION/PROJECT/Reports/Bug Progress2.xlsx'. [Session: 23.3897Bz4UV5Lllg0FPFLZm8k90.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060 User: DOMAIN\user]
ExternalSource.ValidateConnection: Unable to get a connection: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException: Exception of type 'Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException' was thrown. at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.GetConnection(ConnectionRequest connectionRequest, ExtendedConnectionInfo extendedConnInfo, Credentials credentials, Int64 privateConnectionId, Boolean auditConnection) at Microsoft.Office.Excel.Server.CalculationServer.ExternalSource.TryGetValidatedConnection(Request request, Credentials credentials, ExtendedConnectionInfo extendedConnectionInfo, Boolean shouldReportFailure, Boolean auditConnection, Connection& connectionOut). sessionId=23.3897Bz4UV5Lllg0FPFLZm8k90.5.en... 00000301-f5f3-0da1-0000-000050f7b00b
Hopefully this is enough to lead you in the right direction, but for further help you should try the following:
- VSTS Pioneer TFS2010: SQL Backups
-
(See this summary on the VSTS Pioneer dogfood server and all the other posts.)
Having an adequate backup strategy for your Visual Studio 2010 Team Foundation Server is a very important part of any deployment. I’ll share with you how we are running backups on our Pioneer Dogfood TFS2010 server, but you should refer to the MSDN guidance on How to: Back Up Team Foundation Server for definitive guidance.
To prevent accidental corruption of data, you must synchronize all backups of databases that Team Foundation Server uses, which include databases for SQL Server and SharePoint Products, to the same time stamp.
In the fine print a lot of people (myself included) don’t realize that you must restore all databases to the same point in time otherwise you may not have a functioning system. The stumbling block that we ran into on a recent upgrade dry-run was that the ‘Tfs_Configuration’ database was restored to a point in time that was 3 hours earlier than one of our collection databases. This caused identity information in the collection database to be out of sync with the configuration database and caused the cloned system to be unusable.
The reason this happened is because it takes us ~3 hours to backup our collection database, so the timeline went something like this:
- 12:00 Backup Tfs_Configuration (1 minute)
- 12:01 Backup Tfs_VSTSDF collection (3 hours)
The additional step we were missing in our dry-run steps was to restore the transaction logs for all the databases to a common point in time (a time after the collection database had finished backing up). See Restoring a database to a point within a backup and Performing a Complete Database Restore (Full Recovery model).
Our backup process
SQL backups are performed using stored procedures from Ola Hallengren. See SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization
Essentially a stored procedure is called with a number of parameters from a SQL Agent Job which performs the backups.
The reason these scripts are used instead of standard SQL Maintenance Plans, is because they give us a more flexible and robust backup solution. For instance, the backup job log is written as the job executes & we have the flexibility to exclude certain databases while automatically including all new databases created on the server. See Comparison between SQL Maintenance Plans and Ola's Backup scripts
|
Job Name |
Location |
Schedule |
Retention |
Databases |
|
DatabaseBackup - Weekly FULL |
E:\MSSQL\Backup\Full |
Occurs every week on Wednesday at 12:00:00 AM |
Delete files older than 144 Hours (6 days) |
SYSTEM_DATABASES, USER_DATABASES |
|
DatabaseBackup - Nightly DIFF |
E:\MSSQL\Backup\Diff |
Occurs every week on Monday, Wednesday, Thursday, Friday, Saturday, Sunday at 10:00:00 PM |
Delete files older than 23 hours |
SYSTEM_DATABASES, USER_DATABASES
* Automatically does a FULL backup if a DIFF cannot be performed. |
|
DatabaseBackup - 15min LOG |
F:\MSSQL\Backup\Log |
Occurs every day every 15 minute(s) between 12:00:00 AM and 11:59:59 PM. |
Delete files older than 24 hours |
SYSTEM_DATABASES, USER_DATABASES |
Then we have our IT group’s tape backup process come along every night and backup the SQL backup files to tape for on-site and off-site retention.
Backup Job Alerts
Email alerts are sent to a monitoring email list when each SQL backup job completes (regardless of success or failure). This is done using the SQL Mail functionality.
Compression
Additionally, if you are running SQL Server 2008 Enterprise Edition – you have SQL Backup Compression available to you. This can greatly reduce the size of your backups and the amount of I/O required, therefore decreasing the time it takes to perform backups & restores. The tradeoff is of course, additional CPU usage. See the following SQLCAT Tuning the Performance of Backup Compression in SQL Server 2008 article for more information on this.
- TFS2010: Public Workspaces
-
Public Workspaces is a feature that we built last year mainly for our internal build lab customers. Before TFS2010, if one user tries to use a workspace that belongs to another user on the same machine, they will receive an error message:
No workspace matching *;SecondUser on computer COMPUTER found in Team Foundation Server http://tfsserver:8080.
The Public Workspaces feature removes this limitation of one user per workspace and allows multiple users to use the same workspace mappings on a single machine. Unlocked workspaces are sometimes also referred to as "Shared workspaces", as they can be shared among multiple people.
Additionally, we enabled the ability to change the owner of a workspace, if you don’t want to share a workspace at all, but just reassign it to a different user.
Scenarios
Shared Machines
"I need to setup a shared ProductX enlistment machine; we’d like to use it to run tests, integrations and such."
"Our team has a rotating branch-owner role, so we have no choice but to use a service account."
"We find it very useful to have a shared machine that anyone on our team can use to investigate issues, checkin fixes, etc."
"Is there a way to make a TF enlistment user-neutral, so that anyone who logs into the build machine can open the enlistment, and use TF commands – and those commands will be run as the username of the loggedin user?"
Save disk space
"We need to use it to save disk space on shared machines."
Split personalities
"I have two admin accounts on the box. I use one of them to enlist in the branch and work with TFS. The problem is when I use another one (labaccount vs. personal account) I can login, but I cannot run any tf command because enlistment belongs to the first user only."
Hotdesking
"I'm taking over another dev's machine for a week while he works on another project and he has all the development tools already setup"
Workarounds for TFS2008
Before this feature existed, there were are few different workarounds that people were using:
| Workaround | Description |
| Shelvesets | The various developers pass shelvesets around |
| Run As | The owner starts a cmd shell / VS window with RunAs |
| Shared User Account | Create the workspace using a shared system account, then have all developers use that account on that machine. This has the downside that all changes are checked in as the shared account, rather than the actual user. You can overcome this by using the "/author" flag with the actual username, but only if the shared account has the CheckinOther permission. |
| SUBST path substitution | Use the windows SUBST.EXE "Substitution" utility to map a different drive letter to the same physical path. This tricks TFS into thinking that the workspace doesn't exist. |
Usage
The owner of the workspace should run the "tf workspace" command and select a workspace permission profile from the drop-down box. The default is "private."
- A Private workspaces works the same as a workspace in TFS2008.
- A Public (limited) workspace can be used by any valid user. Only the owner can check in or administer the workspace
- A Public workspace can be used by any valid user. Anybody can check in or administer the workspace as long as they have permissions to the underlying files. The checkins will still be attributed to the person checking them in.
Workspace permissions profiles
| | Private | Public-limited | Public |
| Read | Everyone | Everyone | Everyone |
| Use (most operations -- such as pend changes, resolve, etc.) | Owner only | Everyone | Everyone |
| Check in | Owner only | Owner only | Everyone |
| Administer (change owner, mappings, etc) | Owner only | Owner only | Everyone |
Once a workspace has been made public or public-limited, another user can log onto the machine with the workspace and use the workspace. The other user will need to refresh their local workspace cache manually by using the "tf workspaces /collection:http://server:8080/tfs/collection" command before they will be able to use the workspace (otherwise they will not know the workspace is there -- because they did not create the workspace, and the local workspace cache is per-user).
- VSTS Pioneer TFS2010 Dogfood Server: Statistics October 2009
-
(See this summary on the VSTS Pioneer dogfood server and all the other posts.)
As with the last few months, October was another busy month for dogfooding TFS. Since the Pioneer server has been such a success for Work Item Tracking and Source Control, we’ve really ramped up our efforts to dogfood every single feature as much as we can. The benefits of having everything online really lets us make sure our end-to-end integration is working as well as feel the pain before we share it with our customers. The most notable changes this month are:
- Each of the different business units within VSTS have setup their own SharePoint MOSS Dashboards with bug trends, etc.
- We’re now using Team Build 2010 with Gated Checkin on our part of the tree (we still have to follow the divisional processes of custom nightly build scripts at this stage)
- We enabled Test Case Management features and are now dogfooding the Test Case Management (TCM) features for our major test passes
- We setup and enabled the Lab Management functionality on our server and we’re starting to dogfood that across the organization as well
Here’s the latest stats:
Version Control
- Version Control Users: 622
- Files: 11,053,314
- Compressed File Sizes: 284,480
- Uncompressed File Sizes: 870,228
- Checkins: 29,275
- Shelvesets: 7,249
- Merge History: 10,897,164
- Pending Changes: 13,344
- Workspaces: 1,607
- Local Copies: 382,464,960
Work Item Tracking
- Users with Assigned Work Items: 2,607
- Total Work Items: 284,813
- Areas and Iterations: 2,910
- Work Item Versions: 2,892,664
- Work Item Attachments: 115,501
- Work Item Queries: 5,728
Commands (last 2 weeks in October)
- Work Item opens: 154,788
- Work Item queries: 120,537
- Work Item updates: 40,242
- Checkins: 2,630
- Uploads: 10,440
- Shelves: 4,553
- Downloads: 251,032
- Gets: 41,615
DevDiv TFS Server
On top of enabling all these new scenarios on the Pioneer server, we also managed to get the main DevDiv TFS server upgraded from SQL 2005 to SQL 2008. This was the first step to getting the server to TFS2010. I can’t say it was all smooth sailing, but it looks like we’re through the worst of it now (man, what a fortnight…). The good news is that all of the extra performance optimizations we had to make to cope with our dataset sizes also made it into TFS2010 product.
Although no install guide will tell you, I highly recommend you put on SQL 2008 SP1 CU4 if you’re running a large, busy TFS2008 or TFS2010 server – in the processes of testing the upgrade and after the actual upgrade to SQL 2008, we hit these bugs which the SQL team quickly reacted and fixed for us:
- KB967178 - When you restore a SQL Server 2005 backup file in SQL Server 2008, the operation takes much longer than when you restore the same backup file in SQL Server 2005 (fixed in SQL08 SP1 CU1)
- KB970823 - CPU cost increases suspiciously when the number of threads working on the same index increases (fixed in SQL08 SP1 CU3)
- KB970133 - Backup Log hangs when using compressed backups and the destination drive runs out of disk space. SQL Restart is required (fixed in SQL2008 SP1 CU4)
Now, onto the next Pioneer upgrade.
- TFS2010: Diagnosing Email and SOAP subscription failures
-
Sometimes emails alerts such as "Work Item Changed" will fail to send. Sometimes SOAP (web service) subscriptions will not be called correctly.
To diagnose these, you can look in the tbl_JobHistory table in the Tfs_Configuration database.
If you run this SQL command, it will retrieve the last 10 failed notification jobs:
SELECT TOP 10 DATEADD(hour, -7, StartTime) as StartTimePDT, Result, ResultMessage
FROM [Tfs_Configuration].[dbo].[tbl_JobHistory]
WHERE JobId = 'A4804DCF-4BB6-4109-B61C-E59C2E8A9FF7' AND Result <> 0
ORDER BY StartTime DESC
The JobId GUID is the well-known GUID for the “Team Foundation Server Event Processing” job with the namespace: “Microsoft.TeamFoundation.JobService.Extensions.Core.NotificationJobExtension”
The DATEADD() function converts the date from UTC to local Pacific time.
ResultMessage:
There were errors or warnings during notification delivery. 0/4 emails delivered. 0/6 soap notifications delivered. 1 errors. 0 warnings. Set /Service/Integration/Settings/NotificationJobLogLevel in the TF registry to 2 to see errors and warnings.
To get detailed information about why the job failed, you will need to set a flag in the TFS registry. This is different from the windows registry and probably warrants a blog post on it’s own.
Changing NotificationJobLogLevel setting in the TFS registry using PowerShell
If you have VS2010 & PowerShell installed on your local machine and you have the appropriate permissions on the server, then you can run these steps from there. Otherwise, you will need to logon to a machine that does, or an AT. If you logon to the AT, you will need to run PowerShell as an Administrator.
To change the registry setting, replace the server url & collection name and follow these steps:
- Open PowerShell
- Run the following commands:
# Load client OM assembly.
[Reflection.Assembly]::Load("Microsoft.TeamFoundation.Client, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a");
$collectionBaseUrl = "http://YOURTFSSERVER:8080/tfs/YOURCOLLECTION/";
$tfs = [Microsoft.TeamFoundation.Client.TeamFoundationServerFactory]::GetServer($collectionBaseUrl);
$collectionHive = $tfs.GetService([Microsoft.TeamFoundation.Framework.Client.ITeamFoundationRegistry]);
# Set the setting in the collection hive.
$collectionHive.SetValue("/Service/Integration/Settings/NotificationJobLogLevel", "2");
It’s a good idea to set this back to “0” once you’re done with it, to avoid filling up the job history logs unnecessarily.
Once you have set this setting, the next time the job runs and fails, it will log more detailed information including the exception and stack trace. For example:
There were errors or warnings during notification delivery. 0/0 emails delivered. 0/3 soap notifications delivered. 3 errors. 0 warnings. ------------------------------- Notification not delivered. Notification: WorkItemChangedEvent (DeliveryType: Soap; Address: http://eventsserver/EventHandler.svc) Exception: Microsoft.TeamFoundation.TeamFoundationServiceUnavailableException: Team Foundation services are not available from server http://eventsserver/EventHandler.svc. Technical information (for administrator): HTTP code 415: Cannot process the message because the content type 'application/soap+xml; charset=utf-8' was not the expected type 'text/xml; charset=utf-8'. ---> System.Net.WebException: The remote server returned an error: (415) Cannot process the message because the content type 'application/soap+xml; charset=utf-8' was not the expected type 'text/xml; charset=utf-8'.. at System.Net.HttpWebRequest.GetResponse() at Microsoft.TeamFoundation.Client.TeamFoundationClientProxyBase.AsyncWebRequest.ExecRequest(Object obj) --- End of inner exception stack trace --- at Microsoft.TeamFoundation.Client.TeamFoundationClientProxyBase.ProcessHttpResponse(HttpWebResponse response, Stream responseStream, WebException webException, XmlReader& xmlResponseReader) at Microsoft.TeamFoundation.Client.TeamFoundationClientProxyBase.ExecWebServiceRequest(HttpWebRequest request, XmlWriter requestXml, String methodName, HttpWebResponse& response) at Microsoft.TeamFoundation.JobService.Extensions.Core.TeamFoundationNotificationClient.Notify(String eventXml, String tfsIdentityXml, Subscription subscription) at Microsoft.TeamFoundation.JobService.Extensions.Core.NotificationJobExtension.SendSoapNotification(TeamFoundationRequestContext requestContext, TeamFoundationNotification notification, TeamFoundationIdentityService identityService)
This particular error message indicates that the client was expecting a SOAP 1.1 message, and the server sent it a SOAP 1.2 message.
If your email alerts are failing to send, then you’ll get an SMTP error back that shows you where to start looking.
- TFS2010: SQL Queries for TFS Statistics
-
Brian has been posting TFS statistics from the Developer Division dogfood TFS server for many years. I’ve updated the queries from TFS2008 to work with Team Foundation Server 2010 Beta 2. Here are the statistics for the TFS2010 Beta 2 server that we have been using internally since August 2009.
The queries
With the default permissions in SQL, mere mortals will not have enough access to run these queries. In fact, only TFS administrators should be able to even connect to SQL at all. To run these queries, you essentially need db_datareader rights on the Tfs_Configration and any Tfs_Collection* databases that you want statistics on.
In general, you do NOT want to query the TFS operational databases. The only supported API is the Microsoft.TeamFoundation.* Object Model. If you build applications that read the SQL store directly, you are likely to impact TFS performance and it is likely to break in the next version if the database schema changes. You’ve been warned! (Running these queries are OK periodically though :)
-- Change this to the name of your collection DB. You’ll need to run these queries for each of your collection DBs.
USE Tfs_DefaultCollection
GO
-- Recent Users
select count(distinct IdentityName) as [Recent Users] from tbl_Command with (nolock)
-- Users with Assigned Work Items
select count(distinct [System.AssignedTo]) AS [Users with Assigned Work Items] from WorkItemsAreUsed with (nolock)
-- Version Control Users
select COUNT(*) AS [Version Control Users] from [Tfs_Configuration].[dbo].tbl_security_identity_cache as ic JOIN tbl_Identity as i ON i.TeamFoundationId=ic.tf_id where ic.is_group = 0
-- Total Work Items
select count(*) AS [Total Work Items] from WorkItemsAreUsed with (nolock)
-- Areas and Iterations
select count(*) AS [Areas and Iterations] from tbl_nodes with (nolock)
-- Work Item Versions
select count(*) AS [Work Item Versions] from (select [System.Id] from WorkItemsAreUsed with (nolock) union all select [System.Id] from WorkItemsWereUsed with (nolock)) x
-- Work Item Attachments
select count(*) AS [Work Item Attachments] from WorkItemFiles with (nolock) where FldID = 50
-- Work Item Queries
select count(*) AS [Work Item Queries] from QueryItems with (nolock)
-- Files
select count(*) as [Files] from tbl_VersionedItem vi with (nolock) join tbl_Version v with (nolock) on v.ItemId = vi.ItemId where VersionTo = 2147483647
-- Compressed File Sizes
select (sum(convert(bigint,OffsetTo - OffsetFrom + 1)) / (1024 * 1024)) AS [Compressed File Sizes] from tbl_Content with (nolock)
-- Uncompressed File Sizes
select (sum(FileLength) / (1024 * 1024)) AS [Uncompressed File Sizes] from tbl_File with (nolock)
-- Checkins
select max(ChangeSetId) AS [Checkins] from tbl_ChangeSet with (nolock)
-- Shelvesets
select COUNT(*) AS [Shelvesets] from tbl_Workspace with (nolock) where type='1'
-- Merge History
select SUM(st.row_count) AS [Merge History] from sys.dm_db_partition_stats st WHERE object_name(object_id) = 'tbl_MergeHistory' AND (index_id < 2)
-- Pending Changes
select count(*) AS [Pending Changes] from tbl_PendingChange pc with (nolock) join tbl_Workspace w with (nolock) on pc.WorkspaceId = w.WorkspaceId where w.Type = 0
-- Workspaces
select COUNT(*) AS [Workspaces] from tbl_Workspace with (nolock) where type='0'
-- Local Copies
select SUM(st.row_count) AS [Local Copies] from sys.dm_db_partition_stats st WHERE object_name(object_id) = 'tbl_LocalVersion' AND (index_id < 2)
-- Command Counts
select Command, count(*) as [Execution Count] from tbl_Command with (nolock) WHERE Command IN ('QueryWorkitems', 'Update', 'GetWorkItem', 'Get', 'VCDownloadHandler', 'Checkin', 'Upload', 'Shelve') GROUP BY Command, Application ORDER BY [Application],[Command]
These are the core statistics to get you started. I need to add in some more on Team Build, Web Access, Excel & Project usage, etc. Let me know what other statistics you think would be useful.
If you’d like to share your statistics with me and/or the TFS team, send me a link or email them privately. We’d love to know what sort of usage patterns people are using the product in.
I’m also working on a plugin for the forthcoming TFS Best Practices Analyzer that will allow you to run these queries on a schedule and publish your own TFS statistics. By running it on a schedule, you will be able to track growth in size and usage over time and help you do some capacity planning. Stay tuned for that.
- VSTS Pioneer TFS2010 Dogfood Server: Upgrading
-
(See this summary on the VSTS Pioneer dogfood server and all the other posts.)
One of the major goals of the Pioneer Team Foundation Server is to upgrade it to early builds so that we can get some “bake time” with them and feel confident with what we’re releasing to customers. Since we spun up the server, we’ve done two successful upgrades and we’re already planning the next one:
- July 1st – Initial installation of Beta 1 build
- August 29 – Upgrade to 8/22 build from the VSTS branch
- September 17 – Upgrade to 9/17 build from the Beta 2 branch (yes, the build dropped at 5PM and we used it to upgrade at 6PM)
- November – Upgrade to a post-Beta2 build
The process of testing the upgrades on a copy of our database is just as important as running the upgrade itself. With our interesting dogfood topology of a dedicated SQL server, NLB/multiple application tiers, a dedicated MOSS server, virtual machines and use of virtual DNS names, we find some interesting bugs that we’re able to fix before things are locked down for shipping.
There’s no whitepaper on “How to Dogfood” at Microsoft, so our process for deploying perpetual upgrades is something that just kind of happened. Here’s basically what we do:
Depending on where we are in the cycle and the amount of churn in the code base, we might add another iteration or skip one. What’s most interesting about this process, is that it’s driven by the end date. Based on the product schedule, we know when our windows of opportunity are for upgrading and we work back from then.
Tips for upgrading
Here are some tips to consider when planning your own upgrades:
- Hardware – Get your production & pre-production servers in place early. Getting new hardware or re-configuring existing hardware usually has a long lead time. Make sure that you’ve also got enough storage space.
- Start fresh – If at all possible, give yourself the best chance at success by starting with a fresh box. Sure, plenty of products support in-place upgrades & clean uninstalls – but in my experience, flattening the box and starting fresh lays a good foundation for the future.
- Backups – Organize a copy of your production backups that you can test the upgrade with. Not only does it give you peace of mind that your backups work, it also helps you line up the DBA’s for when it’s time to do the real upgrade.
- Start with the latest OS – If you start with Windows 2008 R2 now, it’s one less OS upgrade (and downtime) you’ll have to do in the future. It also means you’ll be on 64-bit, which is a very good thing.
- Virtualization – We run our 3x Application Tiers as virtual machines with no problems whatsoever. This is great for when we’re installing pre-beta copies of the .NET framework that don’t have clean uninstalls yet. In our last upgrade we just turned off the old application tiers and spun up three fresh ones. You can treat AT’s as throwaways in a TFS2010 NLB topology, since there is no state stored on them.
- Dry-Runs – Run through your plan and make sure everything is going to work. Review it with others and get their feedback.
- Have a test plan – Once you’ve done the upgrade, you need to be sure that everything is back online and working as expected. Checkin, Checkout, Open work item, Save work item, Web Access, Sharepoint, Reports, Cube, Alerts, Monitoring, Builds, etc.
- Do pre-requisites ahead of time – For example, to install SQL2008 you need to have .NET3.5 and Windows Installer 4.5 both installed first and both of these require a reboot. If you plan to do one on the first weekend and the other on the second weekend, it means that you don’t waste time during your more important SQL upgrade waiting for reboots.
- Do one thing at a time – This is an extension of the last point. Let’s say you’re moving from a single-server TFS2008 SP1 on SQL2005 and Windows 2003 to a dual-server TFS2010 on SQL2008 SP1 and Windows 2008 R2. If it were me, I’d take it in steps: upgrade SQL, make sure everything still works, move to dual-server, check again, upgrade to TFS2010. The more steps you try and bundle together, the higher your risk of failure.
Upgrade logistics
I’ve been involved with at least 5 major upgrades this year and here are some things I’ve learnt from running them:
- Book a conference room
- This is the single most valuable thing you can do. Try and get anybody who is actually doing any steps in the upgrade (DBA< IT, Helpdesk, etc) to come to the room.
- Have a spare computer or project your own desktop on the screen. This is useful for synchronizing with others in the room, running ‘ping –t’ when a server reboots and doing “group debugging” sessions when problems happen.
- Setup a “party line” conference call. Invite anybody who wants to “listen in” to join. Invite your manager.
- It’s also useful for anybody who you might need help from to have these details. If things go south, you don’t waste time trying to setup a conference call – it’s already there.
- Setup a live meeting / desktop sharing session. Invite anybody who wants to be a “fly on the wall” to join.
- Sure, you can log a bug that says “when I clicked on X on screen Y, setup blew up” and people might believe you. But if you’re sharing your desktop and other people see it happen, then you have some people to back you up.
- (I’ve been meaning to do a screen recording of our upgrades, but various technology problems have prevented it each time – I think it would be useful for reviewing and improving the process for next time)
- Setup an “on call / escalation” list. Find out who you need to call if you have problems with one of your dependent systems. The last thing you want to be doing is sitting on hold to a L1 helpdesk if your server doesn’t reboot cleanly or something.
- Send at least 3 notifications to users of the downtime
- 1st Notice – Give as much notice as possible so people can plan milestones around the date. Even if it’s not set in stone yet, people would rather know about it and have it cancelled than to find out at the last minute.
- 2nd Notice – 7 days or on the Monday before a weekend upgrade is good for this.
- 3rd Notice – On the day of the upgrade (or 24 hours before if it’s a weekend), send another one.
- Guidelines: Make sure you describe what the impact to users is, any actions they need to take and where they can go to get future status updates and support.
- Provide regular status to stakeholders. Start an email thread and trickle through progress as it happens. By keeping them informed, you buy their trust and their support when things don’t go as planned.
Planning your own upgrade
Brian Keller has a great post on how to Get ready to “go live” with Team Foundation Server 2010 beta 2! He includes two useful documents:
- Overview presentation (.PPTX)
- Detailed checklist (.DOCX)
I’m also going to share with you a copy of the generic deployment plan template that I’ve been using for the Pioneer upgrades.
-
Deployment Plan Template(.XLSX)
Planning, testing and upgrading to these new releases has been a fun experience and it’s a bonus when everything goes smoothly. It’s great to see our experiences influence the product and make it the best release yet.
- VSTS Pioneer TFS2010 Dogfood Server: Statistics September 2009
-
(See this summary on the VSTS Pioneer dogfood server and all the other posts.)
September was a busy month for dogfooding TFS – all of the VSTS team were completely moved across and on 9/17 we upgraded to an almost final TFS2010 Beta 2 build. Here are the numbers from the end of September:
Version Control
- Files & Folders: 10,722,118
- Compressed File Sizes (MB): 263,350
- Uncompressed File Sizes (MB): 773,731
- Checkins: 23,369
- Pending Changes: 55,697
- Local Copies: 303,331,190
Work Item Tracking
It looks like I don’t have the work item tracking stats for September – I’ll have them next month.
Commands (last 2 weeks in September)
- Work Item opens: 113,128
- Work Item queries: 101,203
- Work Item updates: 23,653
- Checkins: 1,855
- Uploads: 6.622
- Shelves: 2,402
- Downloads: 216,452
- Gets: 30,201
Table sizes
One of the interesting metrics that I’m also tracking is how our tables and indexes are growing on the disks. Here’s the top 10 for this month:
| Table | Rows | Reserved (MB) | Data (MB) | Index (MB) | Unused (MB) |
| tbl_Content | 5,915,547 | 277,260 | 275,129 | 4 | 2,127 |
| tbl_LocalVersion | 303,331,190 | 81,562 | 44,789 | 36,574 | 199 |
| Attachments | 63,783 | 11,928 | 11,908 | 13 | 7 |
| tbl_Version | 12,129,564 | 3,418 | 1,031 | 2,369 | 17 |
| WorkItemLongTexts | 2,728,618 | 3,314 | 2,533 | 777 | 5 |
| tbl_VersionedItem | 15,754,176 | 2,983 | 1,598 | 1,382 | 3 |
| WorkItemsWere | 1,239,831 | 2,482 | 1,617 | 863 | 2 |
| tbl_PropertyValue | 33,293,914 | 1,320 | 523 | 793 | 4 |
| tbl_File | 5,717,981 | 722 | 442 | 266 | 14 |
- TFS2010: Installing pre-requisites from the command line
-
On the Team Foundation Server team, there is a new build of the server every night that comes out of our build lab. These vary in quality throughout the development cycle and the QA teams look at them and rate them as “Self Test”, “Self Test (with workarounds)”, or “Self Toast”. The QA & dev guys have lots of fancy automation for deploying nightly builds and testing them out, but I like to stay away from that stuff and use the builds as if I were a real customer. Once a build is declared “Self Test”, I’ll grab a copy and spin it up in a VM to see what’s changed.
The two preparation things I’ve done that have really sped up my installation turnaround times are:
- Create a sysprep’d Windows Server 2008 R2 VHD
- Create a SQL2008 SP1 slipstreamed installation point
Once I had those in place, I then just used these two long command lines below to install SQL & IIS. Of course, none of this is necessary for a “TFS Basic” install, since the installer will drop down IIS & SQL Express for you. See Brian’s blog on TFS 2010 for SourceSafe Users for more details on that.
Creating a sysprep’d Win2008 R2 VHD
Creating a sysprep’d VM is easy. Create a new VM and install a fresh copy of Windows 2008 R2. If you have a domain, join it to the domain and add yourself to the local administrators group (this will be retained after you sysprep & rejoin to the domain again). I don’t usually add the IIS role at this point, since I use these VHD’s for other things. Once you’re happy with it, run the following command:
C:\Windows\System32\Sysprep\Sysprep.exe /oobe /generalize /shutdown
Then take a copy of the VHD and name it something like ‘Win2008R2Sysprepped.vhd’. Whenever you need a fresh machine, just copy this VHD to a new file and away you go.
Creating a SQL2008 SP1 slipstreamed installation point
Follow the instructions for creating a slipstreamed drop on the SQL Server Setup team’s blog. Two things to note when doing this:
- Paths matter – it’s best to pick a folder name and stick with it whenever you use it. e.g. C:\SQL2008SP1\
- You DO have to download the ia64 & x86 SP1 packages & slipstream them as well, otherwise your setup.exe will fail
Once you’ve created the installation point, save it away somewhere – it’s really useful and it saves so much time by not having to do an RTM install followed by a SP1 install.
Installing TFS pre-requisites from the command line
- Boot into your fresh 2008 R2 VM
- Give it a new name, join it to the domain, apply any updates, reboot, etc
- Install SQL2008 SP1 from your installation point:
Setup.exe /q /ACTION=Install /FEATURES=SQL,FullText,AS,RS,Tools /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="NT Authority\Network Service" /RSSVCACCOUNT="NT Authority\Network Service" /ASSVCACCOUNT="NT Authority\Network Service" /AGTSVCACCOUNT="NT Authority\Network Service" /SQLSVCStartuptype=2 /RSSVCStartupType=2 /ASSVCSTARTUPTYPE=2 /AGTSVCSTARTUPTYPE=2 /BROWSERSVCStartupType=2 /SQLSYSADMINACCOUNTS="Builtin\Administrators" /ASSYSADMINACCOUNTS="Builtin\Administrators" /SQMREPORTING=1 /ERRORREPORTING=1 /INDICATEPROGRESS
- Install required IIS components:
- Open a command prompt as administrator
- Type the following command:
ServerManagerCmd -install WAS WAS-Process-Model WAS-NET-Environment WAS-Config-APIs Web-Server Web-WebServer Web-Common-Http Web-Static-Content Web-Default-Doc Web-Dir-Browsing Web-Http-Errors Web-Http-Redirect Web-App-Dev Web-Asp-Net Web-Net-Ext Web-ISAPI-Ext Web-ISAPI-Filter Web-Health Web-Http-Logging Web-Request-Monitor Web-Security Web-Windows-Auth Web-Filtering Web-Performance Web-Stat-Compression Web-Mgmt-Tools Web-Mgmt-Console Web-Mgmt-Compat Web-Metabase Web-WMI Web-Lgcy-Scripting Web-Lgcy-Mgmt-Console
At this point, you’re now ready to run setup.exe from the TFS media. You have a fully configured SQL server and the requires IIS pre-requisites installed.
Other useful SQL 2008 setup.exe command lines
For a TFS “Application Tier Only” install, IIS is one pre-requisite and the “SQL Client Tools Connectivity” is the other one. This is required so that the TFS Job Agent can talk to Analysis Services and tell it to process the warehouse. Here’s how to install just the client tools:
setup.exe /ACTION=Install /FEATURES=Conn /ERRORREPORTING=1 /INDICATEPROGRESS /SQMREPORTING /Q
If you just want to install SQL Server Management Studio on your developer machine:
setup.exe /ACTION=Install /FEATURES=ADV_SSMS /ERRORREPORTING=1 /INDICATEPROGRESS /SQMREPORTING /Q
For more information, see How to: Install SQL Server 2008 from the Command Prompt and available features.
