Q) Is there a good reason for having more than just a single (default) instance of SQL Server on a server? Does there have to be a default instance or can you just have named instances? Can you later change which instance of SQL Server is the default instance and what benefits are there for being the default instance?
Before SQL 2000, all we had was a single instance, unnamed. The reason to have the named instances is to provide more flexibility for hosting multiple applications while providing more isolation.
There’s no real feature-related benefit in using just the default instance, othere than the fact that you can get to it with a shorter connection string. There’s no special feature that works only on the default instance or only on a named instance.
Note that there’s no performance advantage in having multiple instances. SQL Server is already multi-threaded by default and will leverage multiple CPUs and lots of memory with a single instance. In fact, it should be faster to have a single instance than having two or more instances on the same hardware. The other instances will require additional resources, since they’ll load another copy of SQL Server software and have copies of the query plans in memory.
On the argument for multiple instances, there’s que question of isolation. It’s possible for an application to store information in the system databases, like error codes in the master or agent jobs in the msdb. This has a potential for conflict. If you’re a hoster and you provide a SQL database with every IIS site your set up, named instances make perfect sense.
By the way, SQL Server 2005 can have up to 50 instances when it’s not clustered, 25 when it’s clustered.