A debate that is common among DBAs and possibly Architects, is whether should an organization use the what-so-called “SQL Instance Stacking” in order to deploy more than one SQL instances under the same OS, usually a Windows Server system.
Let’s just see the advantages and disadvantages listed below:
Pros
Benefits Consolidation as several systems can be consolidated under one centralized Windows Server/SQL Database Server.
Benefits System Resources Availability as by consolidating servers, resources might get released and available for other purposes (CPU/RAM mainly).
Benefits Licensing for both Windows and SQL, as you practically reduce the deployed Windows and SQL installations to one per platform.
Benefits OS Patching as there would be only one Windows system to be patched against x former ones.
Benefits SQL Patching as the SQL Patching tool will identify all instances and will ask to include them in the patching sequence. Of course, this would partially be feasible for all instances as they might have different maintenance windows etc.
Cons
Complicated Maintenance Planning in terms of rebooting the system, as DBAs have to get approvals from all Application/Data Owners of all instances for a specific (yet common to all) maintenance window and reboot activity.
Security level is not the ideal (out of the box) when requests for accessing the server will start coming through (especially if some Databases are related to 3rd party vendors that might request RDP sessions to the server), or when the standalone servers where in different VLANs with different security policies defined by the Security administrators.
Possible performance issues that could arise if certain activities occur on one or more instances (i.e. backup, massive data entry for ETL etc).
Comments