SQL Server Scale-out: design, implementation, and Maintenance (2)-Distributed partitioned views

In order to allow friends to have a concept of distributed partitioned views, but also for the sake of facilitating the later content expansion, we first look at the following diagram:

Before you tell a distributed partitioned view, it is important to differentiate it from the partitioned tables and indexes that we are familiar with.

First, the distributed partitioned view is a scaled-out scenario (scale out), whereas a partitioned table is a vertically scaled scenario (scale up). By using the distributed partitioned view, we can put the data on multiple DB instances (or servers), and using partitioned tables, we can put the data in the database on different physical files on the same server. Partitions in a partitioned table are in the same database, and the partitions inside them cannot span databases or cross servers.

Partitioned tables are partitioned into multiple filegroups, which can exist on the same physical disk or on different disks. In addition, indexes in partitioned tables can be distributed according to the partition. When the query optimizer evaluates a data request, and the request involves using a partitioned table, the optimizer can optimize the request to find the appropriate data only on the appropriate partition. ( more about the partition table, you can refer to our site published in the "Understanding of the Section table" series of articles )

Although it is said that we are talking about distributed partitioned views, in fact, in SQL Server 2005 (and later versions), but also support "local partitioned View", here is a relative concept. Because of the introduction of partitioned tables after SQL Server2005, many times, the "local partitioned View" is only used to be compatible with previous versions of the database. Now we're going to replace the "local partitioned View" directly with a partitioned table.

When we use partitioning, there are two main purposes:

1. Separating the current data of history

2. Use it to divide the data into different scopes, such as dividing the data in a large customer table by the alphabetical order of the user's name, such as A-f,g-n,o-z, into three areas.

In fact, when we split the data in the scale-out scenario, there are some similar partitioning operations, but when using partitions, it is not easy to partition data on a database, because the data is now split into different database instances, even on different servers, at this time , we need to consider some of the distributed scenarios, as follows:

So far, some concepts of distributed partitioned views have been said, and friends may have been eager to know how to do it, so I'm going to slow down here. Let's take a look at some important things related to it first.

Assuming that the distributed view is now established, the next step is to use it.

Now, let's talk about the principle of implementing distributed view and the knowledge about distributed query.

How distributed Partitioned views work

OLE DB providers are used in distributed queries and distributed partitioned views to communicate information between database instances.

The data in the remote database is accessed using linked server technology (linked server). A linked server is actually an object that resides in a DB instance. The query engine is built on OLE DB, so the mechanism is the same, whether retrieving data from the current local database storage engine or retrieving data from the storage engine of a remote database. If you know a little bit about the internal structure of the database, it's very easy to understand the problem:

Friends see the graph above, the query engine (also known as the "relational Engine") accesses the access method through the OLE DB interface provided by the storage engine. Here, the OLE DB provider can access the Remote Storage engine's rhetorical method.

SQL Server Scale-out: design, implementation, and Maintenance (2)-Distributed partitioned views