Designing applications to use federated servers

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.

Getting ready

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.

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:

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 ] ] )
[ ; ]

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','20110301','20110401','20110501','20110601','20110701','20110801','20110901','20111001','20111101','20111201');
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

Note
If no RANGE option is mentioned, then RANGE LEFT is the default.

Partition	1	2	12
Values	SalesDate<February 1, 2011	SalesDate>= February 1, 2011 AND SalesDatel<March 1, 2011	SalesDate>= December 1, 2011

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])

The scheme defines the function of where to put the partition data. This step enables the partition of the SalesHistory table based on the Date by month range.

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, and DELETE categories
Order 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, and DELETE statements from the stored procedure
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:

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

How it works...

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.

Tech Concepts

Programming languages

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

Microsoft SQL Server 2008 R2 Administration Cookbook

By : Satya Shyam K Jayanty

Microsoft SQL Server 2008 R2 Administration Cookbook

By: Satya Shyam K Jayanty

Overview of this book

Designing applications to use federated servers

Getting ready

How to do it...

Note

How it works...

Microsoft SQL Server 2008 R2 Administration Cookbook

By : Satya Shyam K Jayanty

Microsoft SQL Server 2008 R2 Administration Cookbook

By: Satya Shyam K Jayanty

Overview of this book

Designing applications to use federated servers

Getting ready

How to do it...

Note

How it works...

Confirmation

Buy this book with your credits?

Submit Your Feedback

Create a Free Account To Continue Reading

Sign in to activate your 7-day free access