Enterprises and DBAs are often faced with choosing between:
Single instance that host all databases and Multiple instances with each having subset of the databases
Here is a kicker: an enterprise customer is building a new database server that should host many databases serving multiple applications. They approached you asking about two strategies to setup the server but they are undecided. What are the differences between these two options above?
You are to present the comparison in a table showing the differences between both options from every aspect. The table should also include information pertaining to performance, maintenance, security, administration, monitoring, etc. The explanation should detail what to expect when implementing either of these options.
|Points of Comparison||Single Instance||Multiple Instances|
|Multiple environments for testing and development||This is hard to achieve using a single instance. To achieve this, we have to create copies of the databases.||It is easy to achieve this here. We can designate different instances for development and testing environments without affecting resource usage for the production environment.|
|Administrative ease||It is easier to administer a single instance for security privileges and resource reasons.||Not easy to manage multiple databases scattered across multiple instances in terms of security privileges and resources. If a security issue occurs in one instance, there will be difficulties tracking it down.|
|Licensing cost||It is cheaper here in terms of disk, money, expertise, and support.||More expensive as each instance needs its own license. For example if you set up a database engine instance and an SSIS instance, this is considered two instances and will require two licenses.|
|SQL server versions' backward compatibility||Not quite achievable here||This facilitates isolation of data and access paths|
|Demo||It is not recommended to use single instance for demo. Better to use separate instance for this purpose.||Makes it easy to use different instance for demo purpose. We could also use virtual servers for added advantage of using the same license under data center edition.|
|Performance||Speed of interaction between databases is much faster due to shared memory and does not require linked-server objects. Less maintenance cost and single configuration point generally ensures overall performance.||Interaction between databases in different instances requires linked-servers and is generally slow. Each instance will have its own system database, this and more adds to the high maintenance cost. Also complicated configuration and poor maintenance could degrade performance over time.|
|Monitoring||Having common standards and monitoring is an added advantage.||This is not easy with multiple instances. As any issue encountered is much more difficult to tackle.|
|Database Security and Logins||If multiple databases are installed on a single instance, they would all use the same tempDB thus decreasing performance. Also there is need to provide added layer of security among databases in the same instance.||This gives us the room to spread load across two or more tempDBs. Having databases in different instances ensures they don't interfere with each other and provides good security out of the box.|
|Memory||In a single instance, there is full- shared memory pull. This means that if we are to run two or more instances, memory will be shared among these instances. Consequently in terms of memory usage including CPU and I/O operations, single instance is better. Interaction between two databases does not require linked-server objects as well.||There is high resource contention in this environment because the same resource is shared across multiple instances. This could potentially lead to deadlocks if not properly managed. Interaction between databases in different instances requires the creation of linked-server objects which takes up more memory spaces as well.|
From the look of this comparison table, it seems that the first choice - Single instance that host all databases - is better, at least for a lot of enterprises especially medium to small scale enterprises. However, for some enterprises particularly the big ones, there might be a need for the second option of multiple instances with each having subset of the databases.
More from @sqldibia