Should We Install Multiple Instances of SQL Server?

Considering the resource utilization of the Sql server, we recommend not installing more than one installation on the same machine. However, you can decide according to your business needs.

Instance stacking is the technique of installing multiple SQL Server Instances on the same Windows machine. For example, you may have a virtual machine or server named SQLPROD1:

• SQLPROD1 – the default Instace of SQL Server, for example, SQL 2016
• SQLPROD1SQL2014 – for our old applications
• SQLPROD1SharePoint – because it needs to be on its own Instance
• SQLPROD1development – for our QA and testing

Benefits of Instance Stacking Technique

Lower SQL licensing costs – you only need to pay for one license and even the Standard Edition allows you to install dozens of Instances on the same Windows base.

Lower Windows licensing costs – you only need to pay for one Windows.

Easier Windows Patch – since you only need to install one operating system.

Disadvantages of Instance Stacking Technique

Performance tuning is much harder – all Instances share the same CPU, memory, network, and storage. While SQL Server offers tricks like affinity masking and memory tuning to mitigate the first two, it has no answer for the second two. A backup on one Instance will degrade the performance of other Instances no matter how much tuning work you do. If none of the Instances are performance sensitive, that’s no big deal – but how often does that happen? And how do you know what the “right” memory or CPU settings are? It requires so much human work and experimentation that it only really makes sense when you have plenty of free time per DBA per server.

Much more difficult reboot scheduling – you need to get all Instances to agree on a specific time for all customers to patch Windows.

Security challenges – sometimes there are terrible people who insist on being able to RDP into the Windows Instance hosting their database. If they insist on being the system administrator on the entire box, they can make changes that harm other running Instances.

Alternative: Virtualization

When you think about breaking a single server into smaller pieces, consider virtualization instead. It is a great default place for new SQL Servers.

Every SQL Server deserves its own Windows instance. Yes, this means higher license costs – you need to license SQL Server Enterprise Edition at the hardware host level and then you can place as many virtual machines as possible on the host.

Then, each virtual machine gets its own performance management, patching programs, and security. Plus, a surprise bonus: every virtual machine, even the smallest ones, gets all the features of Enterprise Edition.