Is it possible to create a second instance on SQL Server 2005 on same system?

Question

I would like to know if it's possible to create another instance of SQLServer 2005, to have 2 instances running on the same system ? thanks

Answer 1

Sure. One is the default instance, the other(s) is(are) a named instance.

This really does belong on serverfault.

Answer 2

Yes it is, that is what the Instance name is used for, when installing make sure you don't pick the default instance name but specify your own.

You can then connect to the instance using <SERVERNAME>\<INSTANCENAME> instead of just <SERVERNAME>

Answer 3

No one has mentioned that this is done by doing a second install, from install media, of whatever version of SQL Server you want your additional instance to be. You don't do it by opened SSMS and making a new instance. You install the product again, and it makes a new install directory, a new SQL service, a new agent service, etc.

Answer 4

You need to run the installer again, then choose a "named instance". You can install as many instances of SQL Server on the same machine as you like.

Answer 5

Definitely you can. To add to the other answers, a few things to watch out for when using multiple instances:

• TCP/IP port assignment - each instance is a separate process, and therefore needs to be run on its own port. (A single SQL Instance will happily run on 1433) The "proper" way to do this is to have your instances use dynamic port assignments, then make sure the SQL Browser service is running. The problem you run into with this approach (in a secured enterprise environment anyways) is it creates some headaches for your firewall administrators because they need to have a wide range of ports open to your SQL Server. The alternative is to run each instance on a static port.
• Patching/Updates - make sure that both instances get any patches/updates you apply. This should be pretty easy because the patches are instance aware (you need to make sure all instances are checked when running through the patch installation) but I have seen people mess this up.