SQL Server offload - advantages and disadvantages

SQL I/O disk performance is the greatest capacity limiting factor for report servers.

  • In NAM 2018, the NAM Server is the report server.
  • In DC RUM 2017, the CAS and ADS are report servers.

Do not offload the SQL database from the report server machine to another server unless the effective available I/O on the other machine is at least the same as that of the local hardware.

This means that

  • The other server offers faster disk I/O speed
  • The benefits of this faster disk I/O are not lost to multiple servers competing for resources and bandwidth

SQL offload typically requires hosting the report server database on a shared SQL Server installation. If multiple servers compete for resources and share bandwidth, any benefits of faster disk I/O may be lost. For example, do not offload the SQL system to an external server that offers 50% faster access if it will be used by more than two fully loaded report servers.

We do not take into account disk access by the report servers, because their Java processes make very little use of disk access, so full HDD I/O bandwidth remains available to SQL.