SQL Server horizontal scaling: design, implementation and maintenance (3)-distributed partition view implementation

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

In the previous articleArticle, We have already mentionedDistributed partition ViewThe linked server technology is used for implementation, and the implementation principle is also roughly described.Internal MechanismSo far, our series aims to practice and analyze the use cases of various technical solutions.

In general, we often use SQLProfilerFor the linked serverTroubleshootingAndPerformance Optimization. In SQL Server 2005 and later versions, the event for viewing the working status of the linked server is enhanced. Through profiler, we can see that ole db calls otherDatabaseServer Information.

Query Execution Process

In fact, the distributed partition view transmits a part of the query tree generated in a query to a remote database. It should be easy to think. In SQL Server 2000, the startup command for a query and the command for retrieving data from a query cannot be executed in parallel. In SQL Server 2005 and later versions, Command Initiation can be performed in parallel, and the connection pool of the remote database and the I/O used for overlapping operations are optimized. However, data cannot be obtained by executing commands in parallel.

It may not be clearly described here. Here is a simple example:

Suppose there is a query using the linked server, then the generated query tree (Note: Here is the query tree, which is the logic syntax tree, notExecution Plan) Let's assume that:

Qq20121128081422.png(18.17 K) 11/28/2012 8:16:20 AM

 

The red part in the figure indicates that the query tree needs to be executed on the remote database.

After the query tree is generated, it needs to be executed. At this time, the query tree will be split into different remote databases. At this time, in 2005, assume that the preceding query tree needs to connect four different remote databases. At this time, you can start four query commands in parallel, then these commands pass their respective query tree structures over. Then these query trees will be handed over to the remote database query engine for optimization and then executed. Then, the remote database will send the data back, however, at this time, the four remote databases cannot transmit data at the same time, but they can only be sent one by one.

In addition, in this process, the SQL Server ole db will send the "Statistical Information of the retrieved data rows" to the remote SQL Server to help the remote databaseQuery ProcessorThis allows a statistical query processor to perform cost estimation and optimization for the same query and local query remotely, and select the most effective overall query plan.

 

 

 

Distributed partition View

To implement the distributed partition view, we need to use the linked server. To establish the linked server, we can use a lot of Methods: directly use the graphical SSMs interface, and then create it in the Object Browser; use the sp_addmediaserver stored procedure.

In SSMs, we can easily declare the name of the database instance to be connected and set the server type to SQL Server. When using SSMs to create a linked server, many configurations use the default value. However, when using sp_addmediaserver, We need to configure relevant parameters, especially to set svrproduct, the value of this parameter must be SQL Server.

 

The following is an example created using a stored procedure,CodeAs follows:

 

-- On instance
Use Master
Go
Execute Sp_addmediaserver ' Serverb ' , N ' SQL Server '
Go

--On instance B
UseMaster
Go
ExecuteSp_addmediaserver'Servera', N'SQL Server'
Go

 

 

Then, we need to create the same data table in each database, because the example we demonstrate now is to split the data in a table on different servers (of course, we can also store tables originally in a database in different databases ). When we split a table into different databases, we need to set a condition for splitting. This is similar to the partition solution used in the Partition Table of SQL Server 2005, however, the partition here is distributed. For example, for the customer table, we use customerid as the shard column, as shown below:

 

 

-- On instance
Use Salesdb
Go
Create Table Salesschema. customers1to4 (
-- Partitioning Column
Customerid Integer Primary Key
Check (Customerid Between 1 And 49999 ),
-- Other columns...
)

-- On instance B
Use Salesdb
Go
Create Table Salesschema. customers5to9 (
-- Partitioning Column
Customerid Integer Primary Key
Check (Customerid Between 50000 And 99999 ),
-- Other columns...
)

 

-- On instance
Use Salesdb
Go
Create Table Salesschema. customers1to4 (
-- Partitioning Column
Customerid Integer Primary Key
Check (Customerid Between 1 And 49999 ),
-- Other columns...
)

-- On instance B
Use Salesdb
Go
Create Table Salesschema. customers5to9 (
-- Partitioning Column
Customerid Integer Primary Key
Check (Customerid Between 50000 And 99999 ),
-- Other columns...
)

 

The above code should be well understood: Put the user of customerid from 1 to 49999 in database instance A, and put the user of 50000-99999 in B.

After the data is distributed, we need to create a viewProgramThe code for hiding the internal implementation is as follows:

 

-- On instance
Use Salesdb
Go
Create View Allcustomers As
Select * From Salesdb. salesschema. customers1to4
Union All
Select * From Serverb. salesdb. salesschema. customers5to9
)

--On instance B
UseSalesdb
Go
Create ViewAllcustomersAs
Select * FromServera. salesdb. salesschema. customers1to4
Union All
Select * FromSalesdb. salesschema. customers5to9
)

 

The following describes the security settings. This is the time to go. For more information, see

 

 

[Free trial] scalable SQL Server Architecture Design and Practice series (1)

[Free trial] scalable SQL Server Architecture Design and Practice series (2)-Why should we expand the database?