Getting Started with SQL Server 2008 R2 - Microsoft SQL Server 2008 R2 Administration - page 33

The partitioned table approach is a common solution. The pattern of data and the relationship
of such tables are important to route the federation.
To accomplish this task, it is essential to develop a list of the SQL statements that will be
executed by the application during typical processing periods. The process of developing a list
can be accomplished as follows:
F Differentiate the list into SELECT , UPDATE , INSERT , and DELETE categories
F Order the list in each category by the frequency of execution
F If the SQL statements reference the stored procedures, then use the base SELECT ,
INSERT , UPDATE , and DELETE statements from the stored procedure
F If you are partitioning an existing SQL Server database, you can use SQL Server
Profiler to obtain such a list
The frequency of such SQL statements can be determined to sustain a reasonable estimate
for an OLTP system where distributed partitioned views work best. Such systems are
characterized by having individual SQL statements that retrieve relatively small amounts of
data when compared to the types of queries in a decision support, or an OLAP system.
When each SQL statement references a small amount of data, just studying the frequency of
each statement yields a reasonable approximation of the data traffic in the system.
However, many systems have some groups of SQL statements that reference a lot of data.
You may want to take the additional step of weighing these queries to reflect their larger
data requirements.
The routing rules must be able to define which member server can most effectively process
each SQL statement. They must establish a relationship between the context of the input
by the user and the member server that contains the bulk of the data required to complete
the statement.
The applications should be able to take a piece of data entered by the user, and match it against
the routing rules to determine which member server should process the SQL statement.
High-level performance cannot be achieved simply by deploying the Federated Server
process; the database design and table normalization is essential to keep up the
performance and scalability.
This is referred to as collocating the SQL statement with the data required by the statement,
it is the key to design a partitioning scheme to be clear about what data belongs to each
member table. The partitioning scheme can be designed as follows:
F The original table is replaced with several smaller member tables
F Each member table will have the same number of columns matching as the
original table