CODEWITHSUNDEEP
sql-serverazure
Juha Palomäki
Juha Palomäki

Reputation: 27062

Load balancing queries to SQL Server instances

I have a read-only SQL Server database which my application is querying. The database is hosted on SQL Server Express, which is running in Azure Virtual Machine. All queries executed are very similar.

Now I would like to add more virtual machines there and do load balancing between them. The goal is both increased performance and high availability (if one server goes down, I would like to get the queries to remaining server).

What is the proper way to do the load balancing? My application is a .NET application.

I could configure the virtual machines as cloud service. In this case Azure can do load balancing between them on TCP connection level. The other option I can think of is to do this on application level, selecting in round-robin fashion the server to connect to.

Upvotes: 3

Views: 694

Answers (1)

Andrew Loree
Andrew Loree

Reputation: 264

The words SQL Express and Performance or High Availability don't go together. Likewise, you are going to be reinventing the wheel for features that the higher SQL editions already provide.

With regards to performance, you are limited to 1GB of memory with SQL Express, which is tiny. Number one rule for SQL performance is keep the data in memory, and avoid going to disk. If you can't do that within one SQL instance, having multiple instances will not likely improve things sufficiently, just delay the eventual disk churn. You would need to use a higher edition, which increases memory limits, and more importantly, includes several other features for helping with scale out (e.g. replication, Availability Groups, log shipping...)

If your data sizes are that tiny and fit into 1GB memory, just install SQL Express on each app server. You stated your database is read-only, so you are already going to have to develop your own method of deploying said database (e.q. scripting) to multiple instances. Keeping the SQL Express instance local to each app server, you can skip TCP altogether, and get some efficiency gains by using shared memory for talking to SQL, and your need for High Availability is somewhat moot. You have a one-to-one app to SQL instance architecture.

Now your problem becomes front-end load balancing. DNS round-robin if your application is truly stateless, or use a sticky/session-aware proxy load balancer.

Upvotes: 1

Related Questions