The high level of performance is an immediate requirement in current application database systems. In this scenario, to manage an application as 'high-available' and establish a load balancing of processing load for each task across multiple servers is called Federated Servers. Adding a federation of database servers to a highly-available system offers the flexibility to manage the resources for mission-critical processes efficiently.
The application system that is always available (high-availability) is a key ingredient for the success of every enterprise which establishes a federation of database servers, thereby distributing the processing load across a group of servers by horizontally partitioning the data in an SQL Server database. These servers are managed independently, but cooperate to process requests on the database.
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.
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 betweenRANGE RIGHTandRANGE LEFT. CREATE PARTITION FUNCTION [dbo].[PF_SalesMonths ](datetime)asRANGE RIGHT FOR VALUES('20110201','20110301','20110401','20110501','20110601','20110701','20110801','20110901','20111001','20111101','20111201');3. The following list represents how an underlying table would be partitioned:
Partition
1
2
12
Values
SalesDate<February 1, 2011
SalesDate>= February 1, 2011 AND SalesDatel<March 1, 2011
SalesDate>= December 1, 2011
4. Create a partition scheme that specifies the placement of partitions of a partition function on file groups:
CREATE PARTITION SCHEME PS_SalesRange AS PARTITION PF_SalesMonths TO ([PRIMARY],[FEBRUARY],[MARCH],[APRIL],[MAY],[JUNE],[JULY],[AUGUST],[SEPTEMBER],[OCTOBER],[NOVEMBER],[DECEMBER])
5. The scheme defines the function of where to put the partition data. This step enables the partition of the
SalesHistorytable based on the Date by month range.6. Create a table or index using the defined partition scheme.
CREATE TABLE SalesHistoryPartitionedTable ( federated serversapplication, designingTransactionIDint PRIMARY KEY, SalesDate DATETIME, ProductIdint) on PS_SalesRange(ID)
These steps can be achieved using SQL Server Management studio:
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:
Differentiate the list into
SELECT, UPDATE, INSERT, andDELETEcategoriesOrder the list in each category by the frequency of execution
If the SQL statements reference the stored procedures, then use the base
SELECT, INSERT, UPDATE, andDELETEstatements from the stored procedureIf 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:
The original table is replaced with several smaller member tables
Each member table will have the same number of columns matching as the original table
Each column will have the same attributes as the column in the original table where the attributes, such as data type and collation must be matched
The process of federated servers is implemented with the design architecture of the User/Business/Data services tier.
The initial tier of User Services is 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.
So the Business Services Tier consists of 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 has to be performed.
The final stage of storage is the Data Services Tiers, such as SQL Server databases, which can store data in a permanent medium. This is also referred to as persisting the data.