title: Best approach for loading balancing SQL Server 2016 Database and Reporting Services Loads
body: <p>So I'm working on a small .NET project that has 4 Production servers: </p>
  
  <ol>
  <li><em>Web</em></li>
  <li><em>App</em></li>
  <li><em>Database</em> ((SQL Server 2016 Enterprise)</li>
  <li><em>Reporting</em> (Sql Server Reporting Services 2016)</li>
  </ol>
  
  <p>After a recent outage, we installed a secondary replica SQL Server DB instance on our <em>Report</em> server as a High Availability Cluster. We intend in the near future to install SSRS on our original <em>Database</em> server, so we have complete failover if either server goes down.</p>
  
  <p>I think there is an opportunity to more efficiently utilize the computing resources of both servers through load balancing database connections and SSRS loads.</p>
  
  <p>Database load balancing ideas:</p>
  
  <ol>
  <li>Write connections will have to be directed to the primary node in the cluster, but read-only connections can go to either, with an increasing preference for the secondary replica as the write connection load on the primary database node increases.</li>
  <li>Database calls can originate from SSRS and from my .NET application, and well be sent to the High Availability Listener.</li>
  <li>Database operations tend to be I/O heavy, and relatively light-medium CPU intensive.</li>
  </ol>
  
  <p>SSRS load balancing ideas:</p>
  
  <ol>
  <li>Report requests have 2 stages: data retrieval, and UI rendering. Both components could (theoretically) be individually load balanced to either server.</li>
  <li>SSRS Report rending (not including data retrieval, but including any grouping and data processing within SSRS) is relatively medium-high on CPU utilization, and minimal IO.</li>
  </ol>
  
  <p>I know there are load balancing solutions available that can load balance database calls and SSRS in isolation, but are there any products/solutions that can load balance database and SSRS loads together? That is evaluate the <strong>total</strong> load on each server and select the best server to execute each additional request the load balancing solution receives?</p>
  
  <p>Here is a quick diagram:
  <a href="https://i.stack.imgur.com/M6NXG.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/M6NXG.png" alt="enter image description here"></a></p>
  
  <p>In this picture, the SSRS load balancer and High Availability Listener are both on the <em>App</em> server. The primary database node is on top, labeled as 'Database 2', and the secondary replica is on the bottom sever in the diagram, labeled as 'Database 1'. Both database servers have SSRS installed.</p>
  
  <p>-PS: We are currently budget limited to just these 4 Production servers, and we're trying to make the best use of these as we can :) These servers are all VMs, and the database servers have 4 vCPUs and 64 GB of RAM. The App Server also has 4vCPUs and 32 GB of RAM.</p>