Running Multiple Instances of SQL Server on One Machine
If you can afford the licenses, it often makes sense to run more than one instance of SQL Server on the same machine; especially if those instances are not highly utilized and you have a more powerful machine.
In order to run multiple instances you will either have to have virtual operating systems running under which your SQL Server service will run, or you will have to instantiate named instances of SQL Server on a single operating system.
When using multiple operating systems, each virtual OS will have its own IP address. As a result, SQL Server can run using the standard port 1433. When you used a named instance, you can set a static port for each instance (as long as the ports are different), or SQL Server will dynamically assign a port to each instance.
Some folks have observed that the dynamic port is the default method for SQL Server 2012, http://jeffreypalermo.com/blog/how-to-configure-sql-server-2012-for-remote-network-connections/. I’m not sure if that is totally true, since this example using a named instance, and the behavior is similar to that I have experienced elsewhere.
I find that the virtual machine allows you more separation of the instances. However, you give up some computing power to the host virtual operating systems. If you need separation for departmental reasons as well, then the virtual operating system is a good way to go and get more full utilization of your hardware.
Many of you are more experienced in this scenario than I am. Share your hard learned lessons with comments below, or drop me an email at btaylor@sswug.org to get into the conversation.
Cheers,
Ben