SQL Server – Single Instance vs. Multiple Instances

Single Instance

Pros

• Only one instance needs to be administered on the single machine.
• There is no duplication of components or processing overhead, such as having to run multiple database engines on the same computer. This means that the overall performance of a server with a single instance may be higher than a server running multiple instances.
• A single instance of SQL Server is capable of handling the processing growth requirements of the largest Web sites and enterprise data-processing systems, especially when it is part of a federation of database servers.
• Performance – One instance, one server, Always. The reasons have to do with the SQL Server memory manager and CPU scheduler architecture, it really works best if it has the whole box to itself and nothing else runs on the box. ‘Partitioning’ of resources (max server memory, affinity mask) solves some problems and introduces more.
• Security – As If one SQL Server runs into problems and a third-party needs to get involved with troubleshooting, you can give them OS-level permissions without worrying about what they’ll do to the other SQL Servers installed on the box.
• SQL Server be sure to patch all of the instances, not just the default or other instance. Many of the patches need to be applied per instance and not per server.

Cons

• Open

Multiple Instances

Pros

• Server consolidating is one of trending reason to have multiple instances of SQL Server on to a single server.
• When you must support different systems that have to be securely isolated from each other, such as when a service owner has a large server and must create a separate instance of SQL Server for each customer.
• When you need to support multiple test and development databases, and the most economical configuration is to run these as separate instances of SQL Server on a single large server.
• Save the cost of Server Operating System Licenses.

Cons

• Each instance will fight over resources (i.e. CPU, Memory, etc.) impacting performance of all instances.
• Confusion among users, which instance to use.
• Less problems with application compatibility. Some apps just aren’t compatible with named instances of SQL Server
• If one SQL Server runs into problems and a third-party needs to get involved with troubleshooting, you can give them OS-level permissions without worrying about what they’ll do to the other SQL Servers installed on the box.

Bottom Line:

Multiple instances of SQL Server running on the same machine is an easy way to lose performance and scalability. Often when addressing tuning issues, we focus on what is happening inside of a SQL Server and we miss the forest of instances sitting on the same box. The recommended configuration for most production databases servers is to use a single instance of SQL Server with multiple databases.