Real world pros/cons with creating multiple instances.
What are your recommendations for creating multiple instances on the same machine? Do you have real world experience (pros vs cons)? Does it matter is you are running 2008R2 or 2012R2? Traditionally we only create one instance on each machine. In the near future I want to create multiple instances on a machine (to reduce our monitoring tool licensing costs). Good? Bad? Ugly?
If money isn't the issue, always go for the single instance. Then we get to the real world. If we are talking vmware and multi instance machines. First of all you should take a look at your overall SQL licensing model. If you licence every SQL vm with four standard cores and you have a number of these vm's, it might be cheaper to license 2-3 hosts with enterprise core licenses and run as many sql vm's on these hosts as you can fit in. So if you decide on licensing the hosts, then you can still go with single instance machines and still reduce costs since SQL core licenses are one of the most expensive licenses. Now, the real real world for smaller companies. I do run multi instance, multi sql version servers. For instance (haha) I have customer with a vm that has SQL 2005, SQL 2008, SQL 2008R2 on the same vm. Optimal? No. Is it working? Sure is. Have I had any problems because of multiple instances? No. Also have customers with 4 instances on one vm, its up and running and I haven't had any problems in 5 years with it. Do not get me wrong, you need to be proactive with these kinds of multi instance vm's. Tune your databases, can't afford having a database blasting thousands of queries per minute without making sure the proper indexes are there. CPU overload will kill you.. The trick is to populate these multi ins(t)an(c)e vm's with the right kind of databases. I do not create a mix of Sharepoint, Biztalk and random 24/7 databases all up in one vm. Choose wisely! So is it possible and does it work, it sure does. Don't tell the SQL superstar dba's though. More complex, yup sometimes. A word on licensing regarding SQL monitoring tools. Since you are almost always paying per instance and not per server. You won't gain anything here. That basically leaves the Windows OS, backup software licensing and other monitoring tools. You probably won't save that much money here to justify a consolidation project. So in conclusion, do not just go and do it. If you see that it saves enough money and the end users won't be affected by a big performance hit, do it.