Extending SQL Server outward to achieve higher scalability

This article describes the two options for extending the database system outward to achieve higher scalability: horizontal data partitioning and vertical data partitioning.

When I mention scaling out the database system, I'm actually just talking about splitting or dividing the database system so that you can use those parts and assign them to a separate database server. This allows you to spread the processing power across many servers to accommodate ever-expanding growth.

However, additional features and functionality require a higher degree of complexity. An outward-expanding database is difficult to design or manage. Before you successfully implement an outward expansion of a database system, you must address a number of difficult business and technical challenges.

This article describes the two options for extending the database system outward to achieve higher scalability: horizontal data partitioning and vertical data partitioning.

Horizontal Data Partitioning

With horizontal data partitioning, the database structure does not change in the database instance. Typically, the only difference between databases is that the instance contains different data.

Developing a middle-tier application that allows data to be properly maintained between instances is characteristic of this solution. It may be difficult to decide how to divide data between database instances. Data allocation methods are critical because we need to distribute data loads more evenly across different servers. Without this average segmentation, you will not be able to gain any benefits from scaling out the database, because most of the IO for queries or transactions still occurs on only one server.

Since all data is no longer saved on a single server, it is more difficult to execute queries on all data when necessary. Fortunately, you can create a distributed partitioned view to connect data between two server instances, making the data look like it's stored on a single server. There are special views that use the attached server and the constraints established on the datasheet to effectively return data, as if you were querying only data on a single server. (I'll discuss the distributed partitioned view in detail in next week's article.) )

Also, it is worth mentioning that if the servers are in different geographic locations, their performance may be affected. In this case, you might want to replicate the data between the instances so that each server has complete data, but only one piece of data is maintained.

Vertical Data Partitioning

Vertical data partitioning involves dividing database tables into different database instances that are saved on different servers. Each server is typically assigned to complete a special task. This allows the IO in those tables to be split. This type of segmentation depends on logically dividing the system into many parts so that the parts can operate independently. This is necessary if there is a minimum amount of interaction between instances required for transaction processing.

For example, if your database system maintains sales, marketing, and advertising data, it is best to split the tables into individual database instances, preventing them from sharing IO on the same server. You may also need to deal with the two systems that share some of the same data (such as customer data). With the ability to split these business functions, you can extend the database environment to improve system efficiency when necessary.

You can take steps such as using interconnected tables and views on each server so that instances can view data from other instances. Doing so reduces the amount of extra computing required by the application layer to decide where to find the data it needs. You need to ensure that the application layer has the necessary logic to decide which server to store the data on.

Modify

You can make changes to the horizontal and vertical data partitioning method to better suit your environment. For example, you can use a service-oriented architecture to allocate processing power between servers, apply replication to ensure that database instances are kept in close sync with each other, and apply these techniques together.