How to use SQL Server to scale databases horizontally

Generally, scale up SQL Server databases rather than scale out ). It is easy to scale vertically: Increase hardware, processing capabilities, memory, disks, and network speed. The principle is that the database is still running on one server, but the processing capability and resources of the server are increased. This method is very expensive, but very simple and straightforward.

Cloud technology

Sometimes, the easiest way is to hand over the problem to someone else. Microsoft's Windows Azure cloud service includes a cloud-based SQL Server version of SQL Azure, which is not technically meaningful for horizontal scaling because it is an Infinite vertical scaling method. Therefore, you do not need to make major changes to your application to migrate to Azure. In fact, you only need to migrate your application to SQL Azure and pay for storage, processing, and data transmission. These are all paid services, but you do not need to worry about expansion issues.

Copy

Native replication of SQL Server is a solution that supports horizontal scaling. It depends on how databases are created and used. You only need to copy multiple database copies on multiple servers, and then direct different users to each server. This method is usually most suitable for users with geographically dispersed data. For example, users in the Asia office use Server 1, while users in the North American office use Server 2. each server has a complete copy of the data and copies all modifications made to the partner server.

This method does not support automatic load balancing, and is most suitable for users who only use a portion of data. In other words, if Asian users only need to edit the data related to their offices-for example, the data is mostly from Asian customers, replication can ensure that other database copies also contain copies of these records. If all users need to edit the complete dataset, replication becomes complicated because SQL Server must edit the same data on different servers while supporting users.

SQL Server's merge replication can handle such conflicts, but you must perform custom merge programming, which means that your developers must develop some algorithms, determine who gets the editing permission when the user accesses data concurrently. Customer applications also need to add programming so that they not only submit data changes to the database, but also cyclically check whether these changes are overwritten by other concurrent users. The user also needs to re-train, because the client application may prompt: "The data you are programming has changed. You need to check again to see if your edits are still valid. "

Federated database

Another important method of horizontal scaling is union. In this way, you can divide the database into multiple servers. Vertical segmentation saves different rows in the same table to different servers. At the same time, geographic partitioning is the most common method: Save all Asian data records on one server, while all European data is saved on another server. This method is different from overall replication: the server at each location does not have a complete database, but only has data at that location. By implementing a distributed partition view of SQL Server to form a complete table, you can browse a Data View of "union" or combination. Horizontal segmentation stores the table fields on different servers, so each server works together to form a combined table.

The creation of these databases is not simple, and involves an overall operation. You must have detailed information about data access and usage to achieve proper deployment. In addition, you need an SQL Server database architect who should fully understand these technologies, analyze your business conditions, and be able to correctly create these components.

In some cases, the implementation of this horizontal extension requires little change to the client application. This is especially true for applications that are designed to use views and stored procedures for data access. These elements are only abstracted in the background and will not change on the client. However, these applications are not common. Generally, to implement horizontal scaling, you must modify the client program to separate the client from the background structure.

Horizontal scaling is not simple

There is no doubt that the implementation of SQL Server horizontal scaling is very complicated-this is one of the reasons why cloud database systems such as Azure are popular. In addition, some third-party vendors can help achieve horizontal scaling technology without relying entirely on the native features of SQL Server. You need to understand these methods and data access and usage, so that you can select the method that best meets your requirements.