Several misunderstandings in SQL Server AlwaysOn

AlwaysOn has been released for a long time since SQL Server2012. Recently, When I consulted some customers, I was often asked if AlwaysOn should be used, from the customer's perspective, it seems that AlwaysOn is a cure for all diseases, but there is actually no cure for all diseases. So here I will talk about common mistakes in AlwaysOn.

 

1. AlwaysOn can achieve load balancing.

The answer is no. AlwaysOn can handle read-only loads under certain conditions (requires modifying the front-end application), but Server Load balancer cannot. In SQL Server, if you want to achieve Server Load balancer, you can consider the following two aspects: complex architecture and application modification:

Scalable shared database

This feature allows multiple SQL Server instances to connect to a shared read-only storage so that the report service can Scale-Out, but can only expand the read-only load, as shown in topology 1.

 

Figure 1. scalable shared database

 

Peer Replication

Peer-to-Peer replication allows updating each vertex in the node. However, peer replication has strict restrictions, this includes considerations for the updatable database scope of each node, conflict handling, network bandwidth requirements, O & M personnel levels, and data loss, figure 2 shows the typical topology.

Figure 2. Peering replication topology

 

Distributed View

To put it simply, the distributed view distributes data to multiple nodes and summarizes the data through the view. This solution requires a lot of modifications to the program, which is troublesome.

 

SQL Server Service Borker (SSB)

Speaking of this solution, I once suffered a lot from it. The implementation of this scheme is too complex, and the application end needs to make a lot of modifications to avoid messages. If you do not have a professional DBA, you can find yourself.

Consider third-party solutions

SQL Server has never had a native Server Load balancer solution. If you do not have strong strength or use products provided by third-party vendors that cannot modify code, you can consider a third-party solution, I know a company in China, grui trend (http://www.grqsh.com/) specializes in load balancing solutions on SQL Server. I spoke to their database consultants at an event held by Microsoft. The level is good.

 

 

2. AlwaysOn is a Share-Nothing solution.

It's only half done. In fact, AlwaysOn includes two solutions. AlwaysOnFailover Cluster Instance can be seen as an upgraded version of the SQL Server failover Cluster, the upgraded features include more flexible failover policies and the ability to store TempDB locally. This is a shared disk solution.

The other part is that AlwaysOnAvailability Group is a Share-Nothing solution, which can be regarded as an upgraded version of the previous image, but four copies can exist simultaneously (eight in SQL Server 2014) and can be read-only.

 

3. AlwaysOn uses a group of databases as the granularity to execute cross-database transactions for reorganizing databases.

Not allowed. Although the availability group is based on multiple databases, the data updated in the transaction is not allowed to involve multiple databases in AlwaysOn.

 

4. Each node in AlwaysOn must be on the physical machine.

Error. In fact, the WSFC of AlwaysOn can also be in the virtual environment.

 

5. The performance of the AlwaysOn availability group is much higher than that of the image.

This is also a common misunderstanding. It may be related to Microsoft's promotion of AlwaysOn. Some customers I have consulted have suffered from what Microsoft claims as AlwaysOn, but in fact AlwaysOn is based on images, if your network or I/O performance is faulty, the performance of the AlwaysOn availability group may also be faulty.