Single instance vs Multiple instances in SQL Server

The Database Engine sits at the core of the SQL Server components which operate as a service on a machine and they are frequently referred to as an instance of SQL Server.

It is very important decision to choose single instance or multiple instance in database designing based on business requirement, environment set up, cost & budgeting, app/DB size etc. factors.

An instance is nothing but known as a collection (copy of the server files, databases and security credentials) of SQL Server databases run by a single SQL Server service which can have many databases on there and each instance can be started or stopped individually.  .

If there are multiple server installations on single machine they are identified by instance name. Key facts to divide SQL Server into multiple instances are –

• Very well protected from one another
• They don't interfere with each other
• Each instance has own databases, logins, security, etc.
• Multiples instances give us the ability to spread load over more than one TempDB.

Multiple instances of SQL Server on a single physical machine are a great way to have separate environments - both based on the versions (and editions) of SQL Server we might need to support, but also in terms of the use of the instances - development vs. testing vs. demo vs. whatever-else-you-might-have.

But if we manage multiple instances on the same server/machine then also overall CPU and IO performance will degrade for h/w memory demand among multiple instances.

Single instance Vs multiple instances of SQL Server

We can differentiate both instances by observing the below table:

Comparison of single instance to multiple instances SQL Server installation
Key Points	Single instance	Multiple instances
Databases, logins, security and applications	If we are using multiple databases in a single instance means all databases will use the same tempDB and decrease the database performance. We need to provide extra securty layer between databases.	Multiples instances give us the ability to spread load over more than one TempDB. Databases are stored in separate SQL intances which are more secured and very well protected from one another. They don't interfere with each other. Allows to run different application databases at different SQL Server Service Pack
Memory usage	A single instance provides a fully shared memory pool. Memory usage is better in the single instance. We need better memory usage in terms of CPU,IO and overall performance tuning then we can go with single instance. If you run three instances, your available RAM will be divided among all three.	Every instance to a server will create contention for resources which reduce the capabilities of your instances. Also in multiple instance, they all instances use the different tempDB database which also hit the performance somehow/time. Base memory allocations for SQL server are occupied by each instance hence we are shortchanging the maximum available memory for each SQL instance. Multiple instances can resolve the memory issues for large databases.
TempDB disk usage	It is easier to maintain single instance TempDB disk usage. Multiple databases will use the same tempdb which slow down the performaces.	If you can also place the two TempDB data and log files all on separate disks, even better. It brings multiple TempDBs into play is a nice and required external storage also.
SLA differences		Better
CPU usage	better than multiple instances and all the resources are available for single instance.	With multiple Instances we need to set max memory of each Instance very carefully. And whenever a failover happens specially in Active Active Cluster then your node must be capable of running the load of the those Extra Instances.
IO	As standard can only address 64 or 128 gb (depending on version) of RAM per instance.	Multi instance makes sense as the new instance can then address another set of RAM.
Ease of administration	Easy to administrative task  for security privileges and resources on single instance.	Multiple Instances adds extra admin overhead. We have the complication of administering the security privileges and resources on each instance not to mention if you get an issue with performance then it may be harder to track down the culprit. Each instance will have its own set of binaries, so if you need to support different versions, this will do the trick.
Overall performance	Sufficient, balanced and appropriate configuration can easily maintain which is the key for right output and best performance.	Each instance will get its own system databases including tempdb.
Multiple test and dev environments	Hard to maintain for various environments. We need to create multiple copies of requested databases.	Multi-instance approach to great effect when supporting separate DEV and TEST/Staging environments on the same server. Throw a handful of Pre-Prod, Dev or Test instances on a server, and take an empirical look at production workloads before I/O and RAM are over committed throughout a 24x7 workload.
Cost of licensing	Cheaper than multiple instances in term of money, disk, RAM, core licensing, expertise, support etc.	Each instance in production needs its own license, so if you set up one instance with the relational database and another with SSAS that is two licenses and cost where both on the same instance is a single license.
Different SQL Server versions for backward compatibility		Multiple named instances facilitate isolation of data and access paths.
Demo purposes	Not recommonded for the demo purpose and always use separate instances for demo purpose.	If you plan on moving one instance to another server, then a multiple instance deployment on a single machine makes sense. But, with the capabilities of virtualization today, putting each into its own VM provides advantages including licensing under the data center edition.
Monitoring	Standards and monitoring are keys to high performance systems within single instance.	Multiple instances is a nightmare from a performance standpoint. Multiple instances make issues 100s of time harder to troubleshoot.

Most important thing, it depends on your requirement. But I believe that single instance is always better because we can easily handle the cluster node and use all the available resources (money, disk, RAM, core licensing, expertise, support, etc.) within the single instance.

Conclusion

We can understand that it is case to case base decision to choose single instance or multiple instances. There is not any hard core rule to define instances on server. If we need to separate security between databases and applications or divide test and dev environment either or require different SQL Server versions for backward compatibility then we should go with multiple instances. The last technologies encourages multi-threading and multi instances. Some time we are put in position to consolidate our SQL servers. This means to put all of them in the same machine.