With the Team Foundation Server 2017 now available, TFS administrators will be planning to upgrade their existing TFS installations to this new version. As admins plan this activity, we wanted to discuss an important TFS database schema improvement that is rolling out with TFS 2017.

What is the change? With TFS 2017, the test results generated from automated and manual testing will be stored in a more compact and efficient format, resulting in reduced storage footprint for TFS collection databases. With testing in Continuous Integration (CI) and Continuous Deployment (CD) workflows gathering momentum, this change will translate to meaningful SQL storage savings to customers whose automated test environments generate thousands of test results each day.

What is the impact of this change? A new schema for test results with TFS 2017 means existing test result data must be migrated when you upgrade your TFS server to the new version. Given the scale of data migration, you will encounter longer-than-normal upgrade times depending on the amount of test data you have in your TFS collections. For most small to medium sized TFS collections (under 100 GB), the impact will not be noticeable. Your upgrade will take few hours longer. However, if the test result data in your TFS collection is more than 100GB, then you must plan for a longer-than-usual upgrade window.

How do we reduce the time taken to upgrade to TFS 2017? Here are the guidelines that will help you reduce the TFS upgrade window time:

• Lesser the data to migrate, faster the upgrade. We recommend cleaning up old test results in your system by configuring test retention policy. Details about the retention policy are available in this blog: https://blogs.msdn.microsoft.com/visualstudioalm/2015/10/08/test-result-data-retention-with-team-foundation-server-2015 and the steps to configure retention are available in the documentation. Note that retention does not clean up test results instantaneously. The retention policy is designed to gradually free up space by deleting test results in batches to prevent any performance impact on your TFS instances. As such, make sure you configure retention right way, and have a buffer period of few weeks with retention enabled before you upgrade.
• If you cannot wait to the test retention policy to gradually clean up test results, you have a second option of cleaning up test results just before the upgrade. You need to install TFS (TFS 2017 or later) and then run the TFSConfig.exe tool to clean up test results. Note that you need to run tool against TFS collections when they are offline, during the window after installing TFS but before starting the upgrade wizard. Most importantly, remember to configure the test retention policy even after cleaning up test results using the TFSConfig.exe tool to prevent unbounded growth of test result data in future.
• It’s recommended to try out the upgrade on a pre-production environment, before upgrading your production TFS instances. Given that pre-production environments typically have lower hardware capacity than production environments, upgrade may take longer on pre-prod environments when compared to production environments. Make sure you have a backup of your TFS collection databases when the production instances are upgraded.
• If you still see prolonged upgrade times, reach out to us either via customer support or drop a mail to devops_tools@microsoft.com and we’ll be glad to help.

What kind of gains can we expect with the test result schema improvements? The gain varies depending on your mix of automated v/s manual testing – more the number of test results, implying higher the frequency of test execution, higher the gains. We observed a 5x-8x reduction in storage used by test results with the new schema across various TFS collections we tested. For the Visual Studio Team Services account used by the TFS development team itself, the Test Result footprint reduced from 80GB to 10GB after upgrading to the new schema. Owing to a reduced data footprint, we have also achieved modest performance gains with this new schema.

What is the impact for teams using Visual Studio Team Services? For Visual Studio Team Services accounts, the data will be migrated to the new schema in a phased manner. The migration will be transparent to users without any interruptions or down time. Basically, you won’t notice any change in the way you run tests or analyze test results.

What are the improvements that make the new schema for test results more efficient? The existing test result storage employed a flat schema design which was motivated by manual testing scenarios with Microsoft Test Manager. This design was extended to automated testing as we added capabilities like Lab BDT with XAML and on demand automated testing with MTM. As adoption of automated testing in Build (Continuous Integration) and Release (Continuous Deployment) grew, we witnessed sizable growth test result data. In many TFS collection databases, where customers are invested heavily in automated testing, we observed that test results storage was, by far, the largest user of storage space. With this update we are optimizing the schema for automated testing by moving from a flat schema to normalizing the schema. The new schema has an automated test case reference object that stores all test meta data like test method name, container, priority, owner, etc. – data that does not change with each test result. The test results table will contain only the fields that change with each test result like outcome, start date time, duration, machine name, etc. and point to the automated test case reference for meta data. With these redesigned tables, we have significantly reduced data duplication and eliminated the numerous indexes that existed in the flat schema, yielding the new schema 5x-8x efficient in terms of storage space.

If you have any questions or need any help, please drop us a mail at devops_tools@microsoft.com.

Thank you, Manoj Bableshwar – Visual Studio Testing Tools team