Share via


Understanding SQL Server and SQL Server Reporting Services

You can manage Visual Studio Team Foundation Server more easily if you understand SQL Server, SQL Server Reporting Services, and how they both interact with Team Foundation Server. If your deployment also utilizes SharePoint Products, you will also need to manage the dependencies between SharePoint Products and Reporting Services. For extensive documentation about SQL Server 2008, see this page on the Microsoft Web site: SQL Server 2008. This topic focuses specifically on the interactions between SQL Server, SQL Server Reporting Services, and Team Foundation Server.

Interactions between Team Foundation Server and SQL Server

The logical data tier for Team Foundation depends on SQL Server and consists of several SQL Server databases. By default, these databases have the following names: 

  • TFS_Configuration, which contains all the configuration information for the resources for Team Foundation Server

  • TFS_Warehouse, which is the database for all reports in Team Foundation Server 

  • TFS_Analysis, which is a multi-dimensional database that stores the aggregated data from team project collections

  • **TFS_**CollectionName, the individual databases for team project collections (each collection has its own database).

This logical tier can be located on one or more physical servers, and it can be distributed across a SQL Server cluster of servers.

The following diagram illustrates the logical architecture of a deployment of Team Foundation Server that is integrated with both SQL Server Reporting Services and SharePoint Products:

Database relationships with SharePoint Products

One advantage of this aspect of Team Foundation Server architecture is that all data that you need to restore an Team Foundation Server deployment is stored in these databases. You do not have to back up individual client computers. If you are familiar with backing up SQL Server databases, you will find backing up and restoring Team Foundation Server databases equally familiar. 

Warning

Team Foundation Server requires that collation settings are case insensitive, are accent sensitive, and are not binary. If you want to use an existing installation of SQL Server with Team Foundation Server, you must verify that the collation settings meet these requirements. If they do not, installation of Team Foundation Server will fail. For more information, download the installation guide for Team Foundation from the Microsoft Web site, and see "Collation Setting Requirements for Team Foundation Server."

SQL Server must be installed on a server (or servers) that has the appropriate trust levels configured between it and the server (or servers) that hosts the logical Team Foundation application-tier.

Interactions between Team Foundation Server and SQL Server Reporting Services

SQL Server Reporting Services is considered part of the logical application tier for Team Foundation. However, Reporting Services does not have to be installed on the same physical server as other logical aspects of that application tier, such as SharePoint Products. For more information, download the installation guide for Team Foundation from the Microsoft Web site.

When you configure user and group permissions and group membership in Team Foundation Server, you must also manually configure role membership and permissions appropriately for those users and groups in Reporting Services. For more information, see SQL Server Reporting Services Roles.

In addition to configuring role membership and permissions in Reporting Services, you must also manage the service account that Team Foundation Server uses to communicate with the report server. This account is frequently referred to as the data sources account for Reporting Services, or TFSREPORTS. Like the TFSSERVICE service account, the TFSREPORTS service account must be a member of a workgroup or domain that is trusted by every computer that will connect to Team Foundation Server. For more information, see Team Foundation Server Services and Service Accounts and Change the Service Account or Password for SQL Server Reporting Services.

Important

Even if you are logged on with administrative credentials, you might have trouble accessing Report Manager or the https://localhost/Reports site on a computer that is running Windows Server 2008 or Windows Vista. You might need to add those sites as Trusted Sites in Internet Explorer or start Internet Explorer as an administrator. To start Internet Explorer as an administrator, click Start, click All Programs, right-click Internet Explorer, and then click Run as administrator. For more information, see the Microsoft Web site.

Interactions between SQL Server Reporting Services and SharePoint Products

If your deployment utilizes both Reporting Services and SharePoint Products, there are additional dependencies that you will have to consider before the reports and dashboards for Team Foundation Server will appear correctly. The considerations are different if you are using Windows SharePoint Services 3.0 or Microsoft Office SharePoint Server 2007. For more information, see Interactions Between SharePoint Products and Team Foundation Server and Service Accounts and Dependencies in Team Foundation Server.

Using Reports After You Upgrade Your Server

If you upgraded your deployment of Team Foundation Server from a previous version, you might need to perform extra steps to view previous reports or to upgrade the types of reports and dashboards that are available for your team projects. For more information, see Locating Reports After the Upgrade to Team Foundation Server 2010, Add SharePoint Features to an Upgraded Team Project Portal, Updating an Upgraded Team Project to Access New Features, and Adding Dashboards and Reports to Upgraded Team Projects.

See Also

Tasks

Add a Report Server to Your Deployment

Add a Report Server to a Team Project Collection

Add SharePoint Features to an Upgraded Team Project Portal

Add Reports to a Team Project

Concepts

Team Foundation Server Architecture

SQL Server Reporting Services Roles

Roles in SharePoint Products

Planning and Tracking Projects

Other Resources

Managing Permissions

Managing Server Configuration with TFSConfig