For very large databases, consider using federated database servers to balance the processing load across multiple SQL 2000 or 2005 servers. This technique horizontally partitions SQL 2000 or 2005 data over one or more SQL Servers, allowing the client application to send SQL statements to the server in the federation having most of the data required by the statement. This way, the query load is spread over multiple SQL 2000 or 2005 servers.
Federated database servers works best for databases that can be naturally partitioned over multiple servers. For example, if the data can be segregated by product line or geographical location, then the data can easily be separated over multiple databases in a federation.
Here’s is a federated database might work. Let’s say that a company has customers in North America, South America, Europe, and Asia, and that the data stored for every customer, no matter where they are located, is identical. One option would be to horizontally partition the data based on continent. In this case, four different SQL Servers would be needed in the federation, each storing its respective data.
In almost all cases, when a query is run against the customer data stored in the federated SQL Servers, the data will all be related to a specific continent. Because of this, only one SQL Server will be hit with the query, not all of the SQL Servers. And assuming that customers in all continents will be queried, the separation of customers by continent on different SQL Servers will spread the queries among all of the servers, allowing greater performance and scalability. [2000, 2005] Updated 11-15-2005
*****
To get the optimum performance out of federated SQL Server 2000 and 2005 database servers and distributed partitioned views, careful design of the partitions is critical. To reach high performance, federated database servers must balance the processing load across all the federated servers.
This can best achieved if the application can send the Transact-SQL statements to the member server having most, if not all, of the data required by the statement. If this is not done, and if SQL Server has to query against three or more of the SQL Servers in the federation, then overhead increases and the benefits of the federated database are greatly reduced.
One way to help achieve the goal of minimizing the number of SQL Servers in a federation being hit by a single query, is to place related data (the data most likely to be used together in a query) on the same member server. The goal should be to have 80% or more of the necessary data for a query on a single member server. This way, no more than 20% of the data has to be accessed from other member servers. [2000, 2005] Updated 11-15-2005
*****
To get the most out of federated SQL Server 2000 and 2005 database servers, the servers should ideally be connected via a SAN (System Area Network). If they are not, then some of the benefits gained from federated database servers may be lost due to network latency. If a SAN is not feasible, then the next choice would be to use a Gigabyte network (card and switches) to connect them. [2000, 2005] Updated 11-15-2005
*****
When creating linked federated servers in SQL Server 2000, as part of setting up distributed partitioned views, ensure that the network link between the federated servers is as fast as possible. For best performance of the distributed partitioned views, the servers should be connected at 100Mbs (full duplex) or faster, and connected to the same switch. [2000, 2005] Updated 1-14-2005
*****
When creating a distributed partitioned view in SQL Server 2000 or 2005, set the “lazy schema validation” option true for each linked server participating in your distributed partitioned views. This acts to optimize performance by ensuring that the query processor does not request meta data for any of the linked tables until the data is actually needed from the remote member table, reducing overhead. For example, to set this option to true, run this command for at each linked server:
sp_serveroption ‘server_name’, ‘lazy schema validation’, true
where ‘server_name’ is the name of the linked server.
[2000, 2005] Updated 1-14-2005
*****
When creating a distributed partitioned view in SQL Server 2000 or 2005, set the “collation compatible” option true for each linked server participating in your distributed partitioned views. This option helps to optimize performance by telling SQL Server to assume that all character sets and collation sequences in all the linked servers are compatible with the local server. This, in effect, allows SQL Server to send comparisons on character columns to the provider, instead of performing this task locally. This helps to better distribute the workload among all of the federated servers, boosting performance.
To set this option, run this command:
sp_serveroption ‘server_name’, ‘collation compatible’, true
Of course, don’t make this setting if all the character sets and collation sequences for all of the federated servers are not identical. [2000, 2005] Updated 1-14-2005
*****
To help boost the speed of queries that run against federated databases, consider doing the following, if possible, in your queries:
· Try not to use any data conversion functions.
· Try not to use the TOP clause in the SELECT statement.
· Try to avoid using any columns are of the bit, timestamp, or uniqueidentifier data types.
[2000, 2005] Updated 1-14-2005
]]>