High Database Availability: SQL Server horizontal scaling: design, implementation and maintenance (2)-distributed partition view (I)

SQL ServerHorizontal scaling: Design, implementation and maintenance (2)-distributedPartitionView (top)

We have released someDatabaseExtendedArticleMany of my friends expressed great interest and expressed their pain points. With everyone's voices, our team once again sorted out and wrote this series of articles.

In this article, we will introduceDistributed partition ViewHow to design and implement it also describes how to use the distributed partition view in practice, and finally describes some scenarios and advantages and disadvantages of the distributed partition view.

To enable our friends to have a concept for the Distributed partition view and to facilitate the subsequent content, let's take a look at the following figure:

Before talking about the distributed partition view, it is necessary to distinguish it from the partition table and index that we are often familiar.

First, the distributed partition view is a scale-out solution, while the partition table is a scale-up solution ). By using the distributed partition view, we can place data on multiple database instances (or servers). Using a partition table, we can place data in the database on different physical files on the same server. Partitions in the Partition Table are all in the same database, and the partitions in the table cannot be cross-database or cross-server.

Partition tables are partitioned into multiple file groups. These file groups can exist on the same physical disk or on different disks. In addition, indexes in a partitioned table can also be distributed based on partitions. When the query optimizer evaluates a Data Request and uses a partition table in the request, the optimizer can optimize the request to find the appropriate data only in the appropriate partition. (For more information about partition tables, refer to the "deep dive into a partition table" series published on our site.)

 

Although this article describes the distributed partition view, in SQL Server 2005 (and later versions), it also supports the "local partition View", which is a relative concept. Because partition tables have been introduced since SQL server2005, "Local partition View" is only used for compatibility with the previous database version. Now we directly use a partition table to replace the "local partition View ".

When we use partitions, there are two main purposes:

1. Separate historical current data

2. it is used to divide the data into different ranges, such as the data in a large customer table in alphabetical order of username words, such as A-F, G-N, O-Z, divided into three areas.

In fact, when we split data in the scale-out scheme, we also perform operations similar to partitions. However, when using partitions, this is not as simple as partitioning data in a database, because the data has been split into different database instances or even different servers. At this time, we need to consider some distributed situations as follows:

 

 

So far, I have also talked about some concepts of distributed partition views, and some friends may be eager to know how to implement them. So, let's take a look at it. Let's take a look at some important content related to it.

Suppose that the distributed view has been created, and the next step is how to use it.

Next, let's talk about how to implement a distributed view and knowledge about distributed queries.

 

 

Working principle of distributed partition View

In Distributed Query and distributed partition view, ole db providesProgramTo implement information communication between database instances.

The linked server technology is used to access data in a remote database ). A linked server is actually an object in a database instance. The query engine is built on ole db. Therefore, whether it is to retrieve data from the current local database storage engine or from the remote database storage engine, their mechanisms are the same. If you know a little about the internal structure of the database, you can easily understand this problem:

 

As shown in the figure above, the query engine (also known as the "relational engine") accesses the database through the ole db interface provided by the storage engine. Here, the ole db provider can access the back-question method of the remote storage engine.

 

To be continued!