Load balancing MSSQL

Exclusive offer: get 50% off this eBook here
Implementing NetScaler VPX™

Implementing NetScaler VPX™ — Save 50%

Leverage the features of NetScaler VPX™ to optimize and deploy responsive web services and applications on multiple virtualization platforms with this book and ebook

$16.99    $8.50
by Marius Sandbu | April 2014 | Enterprise Articles Web Development

This article by Marius Sandbu, author of the book Implementing NetScaler VPX™, gives an idea on setting up a load-balanced Microsoft SQL Server running on 2008 R2.

(For more resources related to this topic, see here.)

NetScaler is the only certified load balancer that can load balance the MySQL and MSSQL services. It can be quite complex and there are many requirements that need to be in place in order to set up a proper load-balanced SQL server.

Let us go through how to set up a load-balanced Microsoft SQL Server running on 2008 R2. Now, it is important to remember that using load balancing between the end clients and SQL Server requires that the databases on the SQL server are synchronized. This is to ensure that the content that the user is requesting is available on all the backend servers. Microsoft SQL Server supports different types of availability solutions, such as replication. You can read more about it at http://technet.microsoft.com/en-us/library/ms152565(v=sql.105).aspx. Using transactional replication is recommended, as this replicates changes to different SQL servers as they occur.

As of now, the load balancing solution for MSSQL, also called DataStream, supports only certain versions of SQL Server. They can be viewed at http://support.citrix.com/proddocs/topic/netscaler-traffic-management-10-map/ns-dbproxy-reference-protocol-con.html. Also, only certain authentication methods are supported. As of now, only SQL authentication is supported for MSSQL.

The steps to set up load balancing are as follows:

  1. We need to add the backend SQL servers to the list of servers.
  2. Next, we need to create a custom monitor that we are going to use against the backend servers.
  3. Before we create the monitor, we can create a custom database within SQL Server that NetScaler can query.
  4. Open Object Explorer in the SQL Management Studio, and right-click on the Database folder. Then, select New Database, as shown in the following screenshot:

  5. We can name it ns and leave the rest at their default values, and then click on OK. After that is done, go to the Database folder in Object Explorer.
  6. Then, right-click on Tables, and click on Create New Table. Here, we need to enter a column name (for example, test), and choose nchar(10) as the data type. Then, click on Save Table and we are presented with a dialog box, which gives us the option to change the table name. Here, we can type test again.
  7. We have now created a database called ns with a table called test, which contains a column also called test. This is an empty database that NetScaler will query to verify connectivity to the SQL server.

Now, we can go back to NetScaler and continue with the set up. First, we need to add a DBA user. This can be done by going to System | User Administration | Database Users, and clicking on Add. Here, we need to enter a username and password for a SQL user who is allowed to log in and query the database.

After that is done, we can create a monitor. Go to Traffic Management | Load Balancing | Monitors, and click on Add. As the type, choose MSSQL-ECV, and then go to the Special Parameters pane.

Here, we need to enter the following information:

  • Database: This is ns in this example.
  • Query: This is a SQL query, which is run against the database. In our example, we type select * from test.
  • User Name: Here we need to enter the name of the DBA user we created earlier. In my case, it is sa.
  • Rule: Here, we enter an expression that defines how NetScaler will verify whether the SQL server is up or not. In our example, it is MSSQL.RES. ATLEAST_ROWS_COUNT(0), which means that when NetScaler runs the query against the database, it should return zero rows from that table.
  • Protocol Version: Here, we need to choose the one that works with the SQL Server version we are running. In my case, I have SQL Server 2012.

So, the monitor now looks like the following screenshot:

It is important that the database we created earlier should be created on all the SQL servers we are going to load balance using NetScaler. So, now that we are done with the monitor, we can bind it to a service. When setting up the services against the SQL servers, remember to choose MSSQL as the protocol and 1433 as the port, and then bind the custom-made monitor to it. After that, we need to create a virtual load-balanced service. An important point to note here is that we choose MSSQL as the protocol and use the same port nr as we used before 1433.

We can use NetScaler to proxy connections between different versions of SQL Server. As our backend servers are not set up to connect the SQL 2012 version, we can present the vServer as a 2008 server. For example, if we have an application that runs on SQL Server 2008, we can make some custom changes to the vServer. To create the load-balanced vServer, go to Advanced | MSSQL | Server Options. Here, we can choose different versions, as shown in the following screenshot:

After we are done with the creation of the vServer, we can test it by opening a connection using the SQL Management Server to the VIP address. We can verify whether the connection is load balancing properly by running the following CLI command:

Stat lb vserver nameofvserver

Summary

In this article, we followed steps for setting up a load-balanced Microsoft SQL Server running on 2008 R2 remembering that using load balancing between the end clients and SQL Server requires that the databases on the SQL server are synchronized to ensure that the content that the user is requesting is available on all the backend servers.

Resources for Article:


Further resources on this subject:


Implementing NetScaler VPX™ Leverage the features of NetScaler VPX™ to optimize and deploy responsive web services and applications on multiple virtualization platforms with this book and ebook
Published: April 2014
eBook Price: $16.99
Book Price: $27.99
See more
Select your format and quantity:

About the Author :


Marius Sandbu

Marius Sandbu is a Consultant, Advisor, and Trainer working at the Value Added Distributor (VAD) Commaxx in Norway. He has worked with Microsoft technology for over nine years and has been awarded an MVP title from Microsoft because of his great dedication to the Microsoft community. He is also a board member of the local Microsoft technology user group and has spoken at many public events at both Microsoft and other events. He has always had a high interest in technology. Over the past few years, he has taken over 30 certifications in different areas of technology, and also had a role within Microsoft as an Infrastructure Ranger. He is also a certified Microsoft trainer and has held different courses on System Center and Windows Server. As an experiment to improve his learning skills, he started blogging in 2012 and now has over 2,000 visitors to date. He also contributes to Born To Learn, which is a Microsoft community website for training and certification.

Books From Packt


 Getting Started with Citrix® CloudPortal™
Getting Started with Citrix® CloudPortal™

Getting Started with Citrix® Provisioning Services 7.0
Getting Started with Citrix® Provisioning Services 7.0

Citrix® XenApp® 6.5 Expert Cookbook
Citrix® XenApp® 6.5 Expert Cookbook

Getting Started with Citrix XenApp 6
Getting Started with Citrix XenApp 6

Getting Started with Citrix XenApp 6.5
Getting Started with Citrix XenApp 6.5

Getting Started with Citrix VDI-in-a-Box
Getting Started with Citrix VDI-in-a-Box

Citrix XenApp Performance Essentials
Citrix XenApp Performance Essentials

Instant Citrix XenApp [Instant]
Instant Citrix XenApp [Instant]


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software