SQL Server: Why we need multiple instances in production environment?

Installing multiple copies of SQL Server on a single machine is not uncommon but it is not recommended in production environment. The copy of SQL Server installation is called as an instance. Multiple instances are installed when we need multiple environments such as development and test, and when we do not have multiple physical machines.

Although it is not recommended to have multi-instance setup in production, there are instances where we install multiple instances in a single server. We install;

• when we have different types of application that require different versions of SQL Server. If we do not have different servers, two different versions can be installed side-by-side using multiple instances.
• when we need different security configurations for different application. Assume that one application needs to connect only with Windows Authentication and should not allowed Mixed Authentication, and another application requires both. This can be solved by installing multiple instances.
• when we need different level of services for databases. There are some instances which need different SLAs (Service Level Agreement) for different databases.
• when we need different collation set up for databases. Even though it is possible to have different collations for databases in a single instance, tempdb can be configured only with one collation. This leads us to have multiple instances if required.
• when we need different server level configurations for specific application. We may have an application that needs standard settings but another that needs something like optimize for ad hoc workloads enabled.