What are some of the pros and cons of installing multiple SQL Server isntances on the same server? What is the best practice on this?
asked
Best Practise on Multiple SQL Server Instance Installation on The Same Server
We use it here with no real issues. In one example we have a single system with an OLAP and an OLTP database on different instances and replication between them.
In another we have reduced the number of SQL servers to maintain by bringing lots of systems together onto a single instance and we are now moving from that to a new 2008 instance on the same h/w. So for the next year or so we are having 2005 and 2008 on the same box. The actual load isnt any more or less, its just shifting from one instance to another. When we reach a tipping point I will reverse the settings re RAM, CPU etc to be biased towards the 2008 instance as it takes on more load.
I think one of the main points to look for is, are the underlying/shared resources up to the task of running the load - CPU, RAM, HDD (and HDD configuration)?
What is the alternative - all systems on the same instance? If so then another instance will cost you very little (comparatively) in its own resource usage but its another license to buy.
What would you want to achieve by having an extra instance - securtity? That would certainly be more configurable with two instances.
The major reasons to place multiple instances on a single server are, as stated by my esteemed peers - security, consolidation and reduced licensing costs.
Security isolation between instances eases fears of inadvertant data access betwixt Clients.
Consolidation reduces hardware costs and semi-centralizes management (one SSRS console/one server login). Rather than having under-utilized servers sitting there eating energy you have fewer servers maximizing ROI.
$$$$ :)
On the negative side you do increase the resource contention that may arise. MAX MEM needs to be reviewed/adjusted, placement of data/log files on disks needs review, CPU and network requirements also need to be analyzed.
Generally... installing multiple instances is OK. It does depend on your particular scenario. Plan, Test, Review, Repeat!
Best practice is "It Depends"
Cons - multiple instances require more resources to be shared between the servers.
Pros - this kind of setup can provide a good division between databases for different clients (for example).
Just a small thing to add to what Fatherjack said:
The best practice is really to separate instances onto separate disk groups for high volume applications. It is far more often that you will see a SQL Server that is busy because of IO than you will see one that is busy because of CPU. Following that as a general rule leads to separating out the IO to separate disk groups for improved concurrency.
Of course, this does depend on your workload, and nothing can take the place of specific analysis of what the workload on the server is. So... 'It depends'.
+1 - I started thinking about mentioning all this and then forgot to add it before I hit 'send'. If you are starting on new server then yes certainly config HDDs to match intended SQL installation.
There is a lot of good advice here, and it is all worth looking at.
Personally, I frequently do this for testing, especially with different versions. I have had 2005 and 2008 sitting side by side on test machines on many instances. I have never had an issue (unless you count the fact that one must be a named instance, but I generally don't see that as a problem.)
As mentioned above there can be some security benefits to having two instances sit side by side in production and it can help reduce licensing costs, but you must think this through carefully if you are going for that. Remember that unless encryption is used there are ways for someone who is a system administrator to effectively bypass most sql server security and that in the default configuration a system administrator is automatically a sql server administrator. You can reap great security benefits using multiple installs, just be sure you realize exactly what those benefits are and where they hit their limitations.
Also, remember that another option is to run virtual machines with installs of Sql Server. I have not tried this myself, but I have read accounts of it working quite well for some people, especially in test environments.
