Designing Applications to Use Federated Database Servers
Updatable distributed partitioned views support having groups of SQL Server 2005 servers cooperate in processing the database workload of the largest, multitier Web sites. While each server is administered independently, the instances of SQL Server 2005 on each server use distributed partitioned views to share the work. A group of autonomous servers that cooperate to share work is called a federation.
You build federated database servers by creating databases on each server and then partitioning tables across the databases. Each original table is split into a member table on each member server. Each member table has a subset of the rows from the original table; the original table is partitioned horizontally across the member tables. When designing a federated database system, partition all the tables so that all related data is located on the same member server.
One result of partitioning tables across a set of federated database servers is a set of data routing rules. An application can match some piece of data it can infer from user requests against the data routing rules. In doing this, it can determine which member server has most of the data required by the SQL statements that the application must generate to satisfy the user request. For more information, see Designing Federated Database Servers.
In a multitier architecture, a system is implemented in the following tiers:
- User Services Tier. This is a set of thin clients that focuses on managing the application user interface. The user services tier calls the next tier to perform the business functions needed to support user requests.
- Business Services Tier. A set of COM+ components that encapsulates the business logic of the organization. The business services tier uses the next tier for any permanent data storage that has to be performed.
- Data Services Tier. A set of components, such as SQL Server databases, that can store data in a permanent medium. This is also referred to as persisting the data.
The business services tier is designed as a set of COM+ components running on application servers. This allows Microsoft Windows Network Load Balancing to distribute the user requests evenly across the business tier. Because any user request can be processed on any application server, the business components must have some mechanism for routing to the appropriate member server the SQL statements that they generate. The business components must be able to match some piece of information in the data received from the client against the data routing rules to determine what member server should process the request.
A flexible mechanism for implementing data routing in the business services tier is to store the routing rules in a persistent store, such as SQL Server 2005 or Windows Active Directory, and then have the business components retrieve them at run time. You can code a COM+ component that will match keys against the routing rules to determine which member server would most efficiently process the query. This COM+ routing component can then be called by any other COM+ component in the business services tier that has to access the partitioned data. For example, in a system accessing customer data partitioned on customer ID, you could do the following:
- Create a routing rules table recording which keys are maintained on each member server.
- Create a data routing business component that takes either a single key value or the starting and ending keys of a range of key values as input. The COM+ component would read the routing rules table, compare the input key or key ranges against the key ranges recorded for each member server, and then return the name of the member server having the best match to the calling component or application.
- Code the general business services tier components or applications to always call the data routing component when executing an SQL statement referencing the partitioned view. The business component will use the server name returned by the data routing component to select the database connection on which to execute the SQL statement.
This method requires no changes to application code if the partitioning of the data is changed. The data routing rules can be changed while the applications are running.