TABLE OF CONTENTS
Installing several SQL Server instances on the same Windows server (instance stacking) looks attractive: one operating system, one server, sometimes one license. It’s also the first idea when different applications need a different collation, a different version or their own sysadmin rights. But instances sharing the same hardware can end up with one consuming the other’s resources, and patches and restarts hit all of them at once.
This article covers the real benefits and risks of instance stacking, how to manage multiple instances when they can’t be avoided, and the alternatives.
Thinking about consolidating your servers?
Aryasoft measures your workloads and determines the best consolidation model at instance, database and VM level, along with the licensing impact.
Why Install Multiple Instances?
- Different server settings: when the server collation, tempdb configuration or sp_configure settings conflict between applications.
- Separation of permissions: when an application team wants sysadmin rights and must not reach other databases.
- Different versions: when an old application supports only an older SQL Server version.
- Cost: one Windows license and one server to manage.
On licensing: when a physical server or virtual machine is licensed per core, you can run any number of instances in that operating system environment. Details are in our licensing guide.
The Risks
- Memory contention: without a limit, every instance tries to take all available memory. When two instances want the same memory, one squeezes the other, and the operating system comes under pressure too.
- Shared CPU and disk: a heavy report or index maintenance on one instance slows down the critical application on the other. Disk latency affects all instances.
- A shared maintenance window: a Windows patch or server restart hits every instance at the same time.
- A single point of failure: a hardware or operating system problem stops all applications together.
- Harder troubleshooting: when someone says “the server is slow”, finding which instance is responsible takes longer.
- Port and network management: named instances use dynamic ports; static ports have to be planned for firewalls and connection strings.
If Multiple Instances Can’t Be Avoided
Divide the memory
Set max server memory for every instance. The total across all instances should stay below physical memory, leaving enough for the operating system and other services. You can also give the critical instance a floor with min server memory.
-- Run on each instance separately; size the values for the workload
EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure N'max server memory (MB)', 40960; -- example: instance 1 on a 64 GB server
RECONFIGURE;Warning
If two instances without max server memory run on the same server, one squeezes the other’s memory at peak times and paging starts. It’s the most common performance problem we see with instance stacking, and it’s often noticed only as one instance being slow.
Assess the CPU
In most cases, setting MAXDOP separately for each instance is enough. If a critical instance’s CPU must be strictly protected, process affinity can be used; but it also stops idle cores from being used by the other instance and needs careful planning.
Separate disks and TempDB
Where possible, place each instance’s data, log and TempDB files on separate volumes; at the very least, don’t share TempDB.
Static ports and documentation
Assign each instance a static port, write firewall rules accordingly, and document which application lives on which instance.
Alternatives
| Option | When it fits |
|---|---|
| One instance, many databases | When applications can agree on the version and server settings; the most efficient and easiest model to manage |
| Separate virtual machines | When isolation, separate maintenance windows and guaranteed resources are needed |
| Instance stacking | When there’s a different collation, version or permission requirement and resources can be divided carefully |
| Containers | For fast, isolated setups in development and test environments |
Tip
In production, the default choice is one instance per operating system, with many databases if needed. Choose instance stacking only when there’s a concrete need (collation, version, permissions) and resource sharing is planned; on development and test servers it’s perfectly fine.
Seeing the Instances on a Server
# All SQL Server Database Engine services on this server
Get-Service -Name 'MSSQLSERVER', 'MSSQL$*' -ErrorAction SilentlyContinue |
Select-Object Name, Status, StartTypeTurning off unused instances and services (Reporting Services, Analysis Services, Browser) frees resources and shrinks the attack surface. Details are in our configuration guide.
Related Reading
SQL Server Consulting
Plan consolidation by measurement
Aryasoft’s senior DBA team measures your workloads and resource usage, then determines and implements the best consolidation model at instance, database or virtual machine level, together with its licensing impact.
