A blog by Jose Barreto, a member of the File Server team at Microsoft.
All messages posted to this blog are provided "AS IS" with no warranties, and confer no rights.
Information on unreleased products are subject to change without notice.
Dates related to unreleased products are estimates and are subject to change without notice.
The content of this site are personal opinions and might not represent the Microsoft Corporation view.
The information contained in this blog represents my view on the issues discussed as of the date of publication.
You should not consider older, out-of-date posts to reflect my current thoughts and opinions.
© Copyright 2004-2012 by Jose Barreto. All rights reserved.
Follow @josebarreto on Twitter for updates on new blog posts.
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.
Read more at http://msdn2.microsoft.com/en-us/library/ms143531.aspx