Designing Applications to Use Federated Database Servers

Updatable distributed partitioned views support having groups of Microsoft® SQL Server™ 2000 servers cooperate in processing the database workload of the largest, multi-tier Web sites. While each server is administered independently, the instances of SQL Server 2000 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 to determine which member server has most of the data required by the SQL statements the application must generate to satisfy the user request. For more information, see Designing Federated Database Servers.

In a multi-tier Windows DNA architecture, a system is implemented in these tiers:

• Users services tier. A set of thin clients that focus 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 encapsulate the business logic of the organization. The business services tier uses the next tier for any permanent data storage that needs to be done.
  
  
• Data services tier. A set of components, such as SQL Server databases, that can store data in a permanent medium. This is also called persisting the data.

In Windows DNA, the business services tier is designed as a set of COM+ components running on application servers. This allows Microsoft® Windows® 2000 Network Load Balancing to distribute the user requests evenly across the business tier. Because any user request can be processes on any application server, the business components must have some mechanism for routing the SQL statements they generate to the appropriate member server. 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 store the routing rules in a persistent store, such as SQL Server 2000 or Windows 2000 Active Directory, and having 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 needs to access the partitioned data. For example, in a system accessing customer data partitioned on customer ID, you could:

• Create a routing rules table recording which keys are maintained on each member server.
  
  
• Create a data routing business component that takes either one 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.