SQL Performance Optimization Summary (continued)-architecture Adjustment

Source: Internet
Author: User

SQLPerformance Optimization Summary (continued)-Architecture Adjustment

 

Several bloggers are very interested in this topic. Today, we can continue to summarize the topic.

 

From SQL Server2005 to SQL Server 2008 and the current SQL Server2012, Microsoft does not seem to have launched Load Balancing components, such as Oracle RAC technology, cannot achieve load balancing. Because of the high concurrency and horizontal scaling of databases, many users may consider porting to the Oracle platform and adopting RAC to solve the problem. This is a very difficult process, and the cost is believed to be huge. Some 3rd-party solutions have also been considered. For example, Moebius is not applicable due to many restrictions. So we can only use the existing MS technology. We will briefly introduce several techniques of the ms SQL platform:

 

Failover Cluster

Microsoft Cluster Server (MSC) is a technology to improve availability. When a Server fails, you can switch to another Server for a certain period of time to continue providing application services, and Server Load balancer cannot be implemented.

SQL Server 2005Images and snapshots
This technology uses images to improve availability and uses the snapshot service to provide data access services.

Images are a concept that everyone is familiar with. snapshots are new features introduced in SQL Server 2005. A snapshot is a clone of a database at a certain time point. It usually takes several seconds to generate a snapshot, but no data is copied during snapshot creation. After a snapshot DB is created for the image database, the snapshot DB can provide users with data servers. However, because it is a snapshot, the real-time performance is poor, so you need to consider whether it is suitable for some real-time applications. If you want to distribute the load on the primary data server and backup storage, you need to create snapshots in stages.

Copy and subscribe
SQL Server provides Replication technology to enable read/write splitting. There are several methods, such as snapshot, log, and composite. We will not repeat them here because they are not used in this example.

 

Data Server Architecture adjustment:

As the company's applications gradually increased several years ago, there was already a system performance problem. At that time, the hardware for data servers had been upgraded once, but due to the increasing number of systems in recent years, once again reached the critical point, but the current data server is basically a top-level configuration in general enterprise applications. If you upgrade the hardware, it will take a lot of money. After analysis, in fact, some functions of the existing system do not need to update data, but only need to read data (some reports, queries, etc ), therefore, we decided to adjust the existing Server architecture, perform read/write splitting, and transfer some functions that can be satisfied only by reading to other servers to share the pressure on the existing primary data servers.

 

The original data server architecture is that a common client directly connects to the data server, such:

 

Adjusted data server architecture:

 

In fact, the current architecture is not the best solution. The use of replication technology should be the most appropriate in the current Case. First, the minimum amount of modification to the system can be achieved by changing the data connection. Second, the replication technology is relatively mature compared to the current image + snapshot solution. However, this solution was not adopted due to a previous fault, till now, we are still angry. (The TMD company is not talking about politics.) After the data server architecture is adjusted, some client-side read-only functions can be directly connected to the image server, however, mirroring is a data fault tolerance solution. If you provide an image server to update data, unexpected situations may occur (no tests are available, so do not comment ), what should we do if we want to provide data services to clients? SqlServer2005 provides the snapshot function. After a snapshot DB is created, it can be added, deleted, and modified just like accessing the original database. At the beginning of snapshot DB creation, there is actually no data, but it only records the changed information, for more information about the principles of snapshot databases, I will not describe them here.

This solution has a disadvantage. After a snapshot DB is created, the user's access to the snapshot DB is equivalent to the original DB at the time when the snapshot DB is created.

For example, if the original DataBase is named DB1 and the snapshot DataBase is created at a.m. And its name is DB2, the user can access DB1 at a.m.. the snapshot is just like the name suggests.

 

For the following two reasons, we need to constantly delete the old snapshot DB and create a new snapshot DB to provide data access services.

 

  1. Client users always want to access the most real-time data
  2. The number of snapshot databases cannot be too large, because increasing the snapshot DB will increase the server load (because it needs to synchronize the different data between the source DB and each snapshot DB)

 

There is a time difference between the data provided by the snapshot DB for user access and the data on the original server. We set it to 10 minutes. This time difference is acceptable for general application users.

 

After migration, a large number of reports or queries to the backup storage have been migrated, which consumes server resources (mainly logical reads, which is the bottleneck of server operations), effectively reducing the pressure on the master data server, it turns out that the effect is quite obvious. After the above efforts, the pressure on the primary data server is reasonably shared to other servers that are not very important, and load balancing has been achieved in a sense. At this point, the adjustment of the Data Server architecture has come to an end, and the specific SQL optimization will be introduced later.

 

To be continued.

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.