SQL Server third-party load balancing scheme in the era of big data----Moebius test

I. What is covered in this article (Contents)

1. What is covered in this article (Contents)
2. Background (contexts)
3. Architecture Principles (Architecture)
4. Test environment (Environment)
5. Installing Moebius (Install)
6. Moebius Test (testing)
1. Load Balancing test (load balancing testing)
2. High-availability Testing (Failover testing)
3. Data security testing (security testing)
7. Summary (Summary)

two. Background (contexts)

A few days ago in a SQL Server MVP Song (Song) article "The Database Cluster Technology ramble" saw the grey trend on SQL Server load Balancer product Moebius, the database knows: There is a RAC on Oracle, MySQL also has a corresponding scheme (for reference: MySQL builds the amoeba series), and on SQL Server until the SQL Server 2012 version of AlwaysOn arrives, Microsoft does not provide a load balancing scheme, I'm looking for a Moebius test version from Song to test, and here's the process for me to test.

Three. Architecture principle (Architecture)

(Figure1:moebius for SQL Server logical schema diagram)

Four. Test environment (Environment)

Operating system: Windows Server R2

Database version: SQL Server 2012

Server a:10.0.0.1

Server b:10.0.0.2

Virtual ip:10.0.0.15

Five. Installing the Moebius (install)

The installation of the Moebius is simple and easy to install on the server with the SQL Server engine, the next step in the installation process. There is no more talking here.

Here is my test environment, two virtual machines, IP 10.0.0.1 and 10.0.0.2, the operating system and SQL Server versions are Windows Server 2012 and SQL Server 2012, respectively. After the installation is complete, right-click the database in the Management Studio management tool and find the Moebius menu in the pop-up menu, as shown in Figure2.

(Figure2:moebius for SQL Server 2012)

When the installation is complete, open the Configuration Manager interface, as shown in Figure3.

(Figure3:moebius for SQL Server 2012)

Adding 10.0.0.1 and 10.0.0.2 two servers to the cluster, it can be noted that the Moebius is database-granular, which is more flexible than the instance, as shown in Figure4.

(Figure4: Setting up a database)

After the databases of 10.0.0.1 and 10.0.0.2 Two servers are added to the cluster, the virtual IP and port are established, the virtual IP is 10.0.0.15, the port is 5000, and then all the front-end application connections are made through the virtual IP, without regard to the backend architecture at all. You can decouple the front end from the backend, as shown in Figure5 and Figure6.

(FIGURE5: Set virtual IP)

(Figure6: Setting connection properties)

After the setup is complete, the cluster is configured, as shown in Figure7.

(Figure7:moebius for SQL Server 2012)

six. Moebius Test (testing) (i). Load balancing test (load balancing testing)

Once the cluster has been built, you can start testing the cluster. The first is the load balancing test. Load balancing test method is to use the pressure test tool, and then view the two instance of the Profiler, according to my consulting Song, the load balancing algorithm is the default based on the two servers in the past time collected performance indicators for analysis, priority to the query to the low-load server, However, when the cluster was built, there was no historical data, according to the principle of the average distribution. Here is the result of my load balancing test using sqlquerystress, as shown in Figure8. I opened 100 threads, each thread looping 10 times, to make a very simple query.

(figure8:sqlquerystress pressure test)

The resulting results, as shown in Figure9, Figure10, can be seen from the graph: the load is basically distributed evenly to two servers (because the test tool each query will have set Statistics IO on and time on, so the load should be 100 threads * 10 cycles).

(Figure9:profiler tracking information)

(Figure10:profiler tracking information)

by Figure9 and Figure10, it is possible to see that the load is evenly distributed among the two servers in the cluster.

(ii). High-Availability Testing (Failover testing)

The next step is to test for high availability. High-availability testing I focused mainly on the speed of failover switching. First, I drive 100 threads, each thread looping 20 times, running load balancing on the cluster, as shown in Figure11.

(figure11:sqlquerystress)

Figure11 probably performed for 20 seconds, at which point I executed again and, during execution, forced the 10.0.0.1 SQL Server service to shut down in the cluster, as shown in Figure12.

(figure12:sqlquerystress)

We see that some of the transactions that have been sent to the 10.0.0.1 server have failed and rolled back to the front-end application, removing the time it took to stop the service, and all queries by 10.0.0.1 and 10.0.0.2 load balancer execution, to just 10.0.0.2 the time spent executing alone, failover switching time within 10 seconds, this speed has been almost flat with the image of SQL Server.

At this point, looking at the Moebius Cluster Manager, the 10.0.0.1 server has been taken offline, and the virtual IP has drifted to 10.0.0.2, as shown in Figure13.

(Figure13:moebius for SQL Server 2012)

(iii). Data security testing (security testing)

After the situation described by FIGURE13, there are only 10.0.0.2 a server is in a live state because Moebius uses the share-nothing architecture, so it should be possible to use redundant data to prevent data loss, thus ensuring data security. At this time I set up a new form on the 10.0.0.2 demo. and restart the 10.0.0.1 database service, and back online in Moebius, as shown in Figure14, Figure15.

(Figure14:moebius for SQL Server 2012)

(Figure15:moebius for SQL Server 2012)

In the process of online, there is a step to recover the differential data, after online we look at the 10.0.0.1 database, the demo table has been restored when the difference data is automatically synchronized, as shown in Figure16.

(Figure16:demo table)

Seven. Summary (Summary)

With a simple test of Moebius above, Moebius does implement load balancing, high availability, and secure data for SQL Server. I am amazed at the ability of a company to implement a load balancing scheme like Oracle RAC in the country, and if the result remains the same in a complex database environment, it will be of great value to large companies that use SQL Server. I have a chance to do some more complicated tests.

SQL Server third-party load balancing scheme in the era of big data----Moebius test