SQL Server 2012 – Splitting Servers for Load Balancing

Architectureload balancingsql serversql-server-2012

We have a Windows 2012 VM running SQL Server 2012. There are around 20 databases with a total size of 5.83 TBs. There is 64GB of RAM and 16 logical processors on the box.

The server is extremely busy with a lot of users hitting the databases with queries during 7 – 6 window and a lot of jobs hitting it in the evening after 6. Most of the jobs run integration server packages that are stored in MSDB. On top of all this there is snapshot replication on one dB and backup/restore on 2 other databases every night.

Now, there is a need to load balance on the server, we have been talking on a couple of points which are below:

  1. Separate into two servers, A & B, A only runs jobs and B is for users, every night back databases from A and put them on B.
  2. Always On Availability Groups with readable secondary.
  3. Separate Integration Services and database engine into 2 servers. We have earlier separated SSRS and SSAS from database engine.
  4. Have two servers with heavy load dBs on one server and other dBs on other.

Which approach would you guys go with?

Approach 1 & 2 — are kind of automatically out of the picture as users need read/write access. They not only read data from the databases but also write the data in their own schema.

Approach 3 — separate SSIS & database engine — I am against this as I have noticed that IS does not take more that 2GB of memory at its heaviest load and not to forget that even though we separate IS & engine the IS will still be hitting the databases and will be locking tables which users are trying to read. I agree that putting it on different server will allow IS to have its own processors and memory but whatever little performance we see will be offset by transferring data over the network.

Approach 4 — is seeming most feasible with taking 1/4th of busy databases on other server and keep 3/4th of less busy one on same server.

I have allotted 50GB to the database engine and left 14GB for OS and other processes.

Best Answer

Community wiki answer:

Have you considered just migrating the VM to a bigger host and giving it more cores and RAM?

How to move a VM to a new host and assign additional resources is dependent on your hypervisor, and is something whoever manages your VMs can do. For Hyper-V see: Windows 10 and Windows Server 2016 PowerShell - Move-VM

Choosing one of the four options mentioned in the question, go for approach 4 - keep it simple.

Related Question