How to extend a database horizontally with SQL Server

In general, people choose to vertically expand (scale up) SQL Server databases rather than scale-out (scale out). Vertical scaling is easy: adding hardware, processing power, memory, disk, and increasing network speed. The principle is to still run the database on a single server, but increase the processing power and resources of the server. This method is expensive, but very straightforward.

Using cloud Technology

Sometimes the easiest way to do this is to leave the problem to someone else. Microsoft's Windows Azure cloud service contains a cloud-based SQL Server version of SQL Azure. This is not technically a horizontal extension, because it is an infinite vertical extension method. Therefore, moving to Azure does not require a major change to your application. In fact, you only need to migrate your application to SQL Azure and then pay for storage, processing, and data transfer. These are fee-based services, but you don't need to worry about scaling up any more.

Copy

SQL Server Native replication is a solution that supports scale-out, related to how databases are created and used. You only need to replicate multiple copies of the database on multiple servers, and then point different users to each server. This approach is usually best suited for geographically dispersed users, such as those in the Asian Office using server 1, while the North American Office user uses server 2. Each server has a full copy of the data and replicates all modifications to the partner server.

This approach does not support automatic load balancing and is best suited for situations where users are fixed to use only a subset of the data. In other words, if Asian users only need to edit data related to their offices-for example, primarily Asian customers-replication can ensure that copies of those records are also included in other database replicas. If all users need to edit the full dataset, replication becomes somewhat complex because SQL Server must edit the same data at different servers while supporting the user.

SQL Server merge replication can handle this conflict, but you must do some custom merge programming, which means that your developers must develop algorithms that determine who gets edit permissions when users access data concurrently. Client applications also need to be programmed to commit data modifications not only to the database, but also to iterate over whether those modifications are overridden by other concurrent users. Users also need to be retrained because the client application may prompt: "The data you are programming has changed." You need to double-check to make sure your edits are still valid. "

Federated database

Another important lateral extension method is the union. In this way, you can divide the database into multiple servers. A vertical split saves different rows of the same table to a different server. At the same time, geographical partitioning is the most common approach: All Asian data records are saved on one server, and all European data is saved on another server. This approach differs from overall replication: servers in each location do not have a complete database and only have data in that location. By implementing a SQL Server distributed partitioned view to form a complete table, users can browse a "federated" or combined view of the data. Horizontal segmentation saves the fields of the table on different servers, so each server works together to form a combined table.

The creation of these databases is not simple, it involves a whole operation. You need detailed information about data access and usage before you can achieve the right deployment. In addition, you will need a SQL Server database architect who understands these technologies, analyzes your business situation, and creates these components correctly.

In some cases, implementing this horizontal extension is a small change to the client application. This is especially true for applications that are designed to use a large number of views and stored procedures for data access. Because these elements are only abstract in the background, they are not changed on the client. However, these applications are not common; Generally, implementing landscape scaling requires modifying the client program to separate the client from the background structure.

Scaling horizontally is not easy

There is no doubt that implementing horizontal scaling of SQL Server is complex--one of the reasons for the popularity of cloud database systems such as Azure. In addition, there are third-party vendors that can help implement scale-out technology without having to rely entirely on the native features of SQL Server. You need to work on these methods and understand how data is accessed and used so that you can choose the method that best fits your requirements.