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

The Federated Server is made up of two systems; a 'program'—to access data and a 'database
structure'—to store the data. Similarly, to achieve the high levels of performance required by a
client-server application or a website on a 24/7 basis, then a multi-tier system is needed that
can typically balance the processing load for each tier across multiple servers.

By design, SQL Server features can share the database processing load across a group
of servers by horizontally partitioning the data in a database. These servers are managed
independently, but cooperate to process the database requests from the applications; for
example, a cooperative group of servers is called a federation.

Designing and implementing structured storage within the database-which is called
partition-is the first step to planning a federated database design. To accomplish the
concept of database partition, we should create the underlying objects, such as partition
function and partition scheme. The partition function handles the mapping of the rows of a
table to the set of partitions based on certain column values, which are called partitioning
columns. A partition scheme handles the mapping of each partition specified by a partition
function to a file group.

The important choice for partitioning column values can be determined by the extent to
which the data is grouped logically. Grouping by date is an adequate approach for managing
subsets of data. For example, the SalesHistory and SalesHistoryArchive tables are
partitioned by the TransactionDate field. Each partition consists of one month, which
enables the SalesHistory table to maintain the year's worth of transactions as current and
the SalesHistoryArchive table to maintain the data older than one year. By partitioning
the tables in this way, the database engine can offer the scalability to transfer the old data
quickly and efficiently.

How to do it...

Once we have decided the partition function and partition scheme, the implementation of the
partitioned table or index is the key step. The steps for creating a partitioned table or index
using Transact-SQL are as follows:

1. Create partition function (rules) that specify how a table or index is partitioned:

CREATE PARTITION FUNCTION partition_function_name(input_parameter_
type)
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( [boundary_value [ ,...n ] ] )
[ ; ]

2. From the previous syntax, the key value is RANGE , you have to choose between

RANGE RIGHT and RANGE LEFT . CREATE PARTITION FUNCTION [dbo].[PF_
SalesMonths ](datetime) as RANGE RIGHT FOR VALUES ( '20110201' , '20110
301' , '20110401' , '20110501' , '20110601' , '20110701' , '20110801' , '20110
901' , '20111001' , '20111101' , '20111201' );