Designing 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. Microsoft® SQL Server™ 2000 shares the database processing load across a group of servers by horizontally partitioning the SQL Server data. 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 extremely 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 called 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.I 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.
| 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 containing most of the data referenced by the statement. |
While the goal is to design a federation of database servers to handle a complete workload, you do this by designing a set of distributed partitioned views that spread the data across the different servers.