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. 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 storing all your data in a database is that it simplifies data management because you don’t 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. 

Tip

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, see SQL Server Collation 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 Server. 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.

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 report reader 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 service account for Team Foundation Server, the report reader 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 Accounts required for installation of Team Foundation Server.

Tip

Even if you are logged on with administrative credentials, you might have trouble accessing Report Manager or the https://localhost/Reports sites unless you add these sites as Trusted Sites in Internet Explorer or start Internet Explorer as an administrator. To start Internet Explorer as an administrator, choose Start, All Programs, open the shortcut menu Internet Explorer, and then choose Run as administrator. For more information, see the Microsoft Web site.

See Also

Concepts

SQL Server Reporting Services Roles