Multiple instance in one server

Question

Could you please help to explain why one might need multiple instances of SQL Server on the same server? What is a good point to use separate instances? Why wouldn't one use two databases in one instance instead? At any point is it a good idea to have separate multiple instance in the same server?

Answer 1

The idea of having multiple instances on one server can have various reasons.

1. SQL Server License Costs

You can have multiple instances all running on one (big?) server and not have to pay large amounts of licensing fees.

Disclaimer
This depends on your licensing model, your license agreement and probably requires additional clearance by your certified reseller / licensor. Do not take this statement for granted. Your costs/licenses may vary. No guarantee given.

2. Upgrades / Service Packs / Cummulative Updtes / Hotfixes

Having multiple instances on one (big?) server, allows you to patch the individual instances to different levels. You can have once instance running Service Pack 1 and another instance running Service Pack 2. And on another instance you could even test drive the hotfix you're not sure it will work well.

3. Permissions / SQL Server Roles / ....

Having multiple instances allows you to assign the sysadmin server role to individuals, without giving them the ability to destroy all the databases if they would be located on one instance. You have a separation between databases of one customer and another customer.

4. Windows Server Licenses

You can save on Windows Server licenses, because you only require one server license for multiple SQL Server instances.

Disclaimer
This depends on your licensing model, your license agreement and probably requires additional clearance by your certified reseller / licensor. Do not take this statement for granted. Your costs/licenses may vary. No guarantee given.

5. Individual IP Addresses for Each SQL Server Instance

You can setup the individual SQL SErver instances to only respond to one IP address which in turn is linked to a CNAME/ALIAS. e.g. you could have your Windows Server running on 10.0.0.1 and each of your SQL Server instances running on 10.0.0.2 ... 10.0.0.5 which in turn would have their own CNAME:

10.0.0.1   hostname
10.0.0.2   hostanme-alias1
10.0.0.3   hostname-alias2

Each instance is then only reachable via the IP address and Port 1433 which in turn allows for IP to IP firewall configuration.

See my question Configure SQL Server Analysis Services (SSAS) to run on same IP as SQL Server instance for an example of how you could configure multiple instances. Or the question Multi-instance SQL Server Standard Editon MaxDop settings where I show how we setup multiple insances at our shop.

Answer 2

It depends entirely upon requirement. if you have only one environment and multiple databases then one instance is good in terms of performance. Only one SQL service will run and let decide to SQL Server to use CPU, memory effective way.

If you decide to use two instances and perform the high activity on both instances and parallelism occurs then resource contention will occur, memory will be divided in both instances etc.

Recommend to use a single instance with multiple databases.

Answer 3

There is a lot of scenarios when you want to do it, few samples:

1. You need to create database with the same name for multiple environments (development, testing, etc.).
2. Different teams needs a full dba privileges and you want to create separate sandboxes for them.
3. Support agreement with vendor software requires a dedicate instance for their product.

Remember that one instance installed means one service running on the server.