In this article, we will explore the concept of Distributed Partitioned Views in SQL Server. Distributed Partitioned Views are designed to provide scalability in environments that require a high degree of data distribution. However, it is important to note that they do not offer high availability like clustering solutions.

Distributed Partitioned Views are based on the concept of horizontal partitioning, where rows in a table are divided into distinct sets based on the values in key columns. The goal is to load balance the distribution of queries by placing each range of values in a separate partition residing on a separate SQL server. This collection of servers with partitioned data is known as a federation.

When implementing Distributed Partitioned Views, it is crucial to carefully choose the ranges that reflect data access patterns. By doing so, the majority of queries can be satisfied by accessing a single partition only. For example, if a table contains data related to geographical locations, the location code can be used to determine the partition to which a particular row belongs.

Partitioned views provide transparent access to data residing in separate partitions by combining all rows from all partitions and making them appear as a single table. These views can be read-only or read/write, with the latter requiring SQL Server 2000 Enterprise Edition or SQL Server 2000 Developer Edition.

Before implementing partitioned data and federated servers, careful database design is essential. It is important to determine the optimal placement of data to ensure related data is stored together. This can be achieved by storing foreign keys referencing rows in a local partition on the same server. Alternatively, existing tables can be partitioned based on observed data access patterns.

Once the distribution of data across partitions is determined, replicas of each table should be created on each server. Each table should have a CHECK constraint that enforces the uniqueness of all partitioned data sets, ensuring that each row belongs to only one partition.

In addition, each server participating in the federation needs to be added as a linked server. This can be done using SQL Enterprise Manager or by running the sp_addlinkedserver stored procedure. If integrated security is not used or if credentials differ between servers, mappings between local and remote server logins need to be specified.

Finally, a distributed partitioned view can be created on each federated server, combining all rows using the UNION ALL statement. This view provides data transparency and improves query performance for accessing data from a single partition.

It is important to note that there are specific requirements that need to be met for Distributed Partitioned Views to work properly. For example, each SELECT statement in the view definition must refer to a single table for the view to be updateable. A full list of rules can be found in the SQL Server Books Online.

While creating partitioned views may seem straightforward, it is crucial to understand the distribution and access patterns of the data before implementing this solution. Proper data distribution is key to the efficient operation of Distributed Partitioned Views.

In the next article of this series, we will explore log shipping as a method to increase SQL Server availability.