1.1

• Use parallel, data-aware procedures to run the logic for the query on the member that stores some or all of the
data (to minimize data distribution hops). The procedure should execute multiple queries and then join the
results using application code. Using Data-Aware Stored Procedures on page 153 provides more information.
• Split the query into multiple queries, and perform the join in the application client code.
Example: Adapting a Database Schema for SQLFire
If you have an existing database design that you want to deploy to SQLFire, translate the entity-relationship
model into a physical design that is optimized for SQLFire design principles.
The goal is to identify tables to partition or replicate in the SQLFire cluster, and determine the partitioning key(s)
for partitioned tables. This generally requires an iterative process to produce the most optimal design.
Procedure
1. Read Guidelines for Adapting a Database to SQLFire on page 51and the preceding sections under Designing
vFabric SQLFire Databases on page 49.
2. Evaluate data access patterns to define entity groups that are candidates for partitioning. See Step 1: Determine
the entity groups on page 52.
3. Identify all tables in the entity groups. See Step 2: Identify the tables in each entity group on page 52.
4. Identify the "partitioning key" for each partitioned table. The partitioning key is the column or set of columns
that are common across a set of related tables. See Step 3: Define the partitioning key for each group on page
53.
5. Identify the tables that are candidates for replication. You can replicate table data for high availability, or to
co-locate table data that is necessary to execute joins. See Step 4: Identify replicated tables on page 53.
Guidelines for Adapting a Database to SQLFire
Follow these guidelines for designing a SQLFire database or adapting an existing database to SQLFire:
• Focus your efforts on commonly-joined entities. Remember that all join queries must be performed on data
that is co-located. In this release, SQLFire only supports joins where the data is co-located. Co-located data is
also important for transaction updates, because the transaction can execute without requiring distributed locks
in a multi-phase commit protocol.
• After you locate commonly-joined entities, look for parent-child relationships in the joined tables. The
primary key of a root entity is generally also the best choice for partitioning key.
• Understand the trade-offs associated with different partitioning and co-location strategies. The steps that
follow describe how to evaluate a customer order management system.
This example shows tables from the Microsoft Northwind Traders sample database.
51
Designing vFabric SQLFire Databases