Understanding Federated Database Servers

To achieve the high levels of performance required by the largest Web sites, a multitier system typically balances the processing load for each tier across multiple servers. SQL Server shares the database processing load across a group of servers by horizontally partitioning the data in a database. These servers are managed independently, but cooperate to process the database requests from the applications; such a cooperative group of servers is called a federation.

A federated database tier can achieve very high levels of performance only if the application sends each SQL statement to the member server that has most of the data required by the statement. This is referred to as collocating the SQL statement with the data required by the statement. Collocating SQL statements with the required data is not a requirement unique to federated servers. It is also required in clustered systems.

Although a federation of servers presents the same image to the applications as a single database server, there are internal differences in how the database services tier is implemented, as shown in the following table.

Single server tier

Federated server tier

There is one instance of SQL Server on the production server.

There is one instance of SQL Server on each member server.

The production data is stored in one database.

Each member server has a member database. The data is spread through the member databases.

Each table is typically a single entity.

The tables from the original database are horizontally partitioned into member tables. There is one member table per member database, and distributed partitioned views are used to make it appear as if there was a full copy of the original table on each member server.

All connections are made to the single server, and all SQL statements are processed by the same instance of SQL Server.

The application layer must be able to collocate SQL statements on the member server that contains most of the data referenced by the statement.