When to use a Named Instance

Question

When to use a Named Instance

Glenn Owens

SSCrazy

Points: 2099
More actions
December 19, 2005 at 7:10 am

#166492

This is a question of strategy.
What is the criteria in determining when to use a named instance(s) versus lumping all databased under the same (default) instance? I understand that the system databases (including tempdb will be unique, non-shared resources). I also suppose that if the database environment is very dynamic (one in which the number of databases increases/decreases quickly - then you could separate static dbs versus dynamic soas to manage backup jobs, etc more easily using generic maintenance plans. Aside from these obvious characteristics I'm not sure that I would know when to implement a new named instance.
Glenn

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

It looks like you've covered many of the reasons when to use a named instance. Other reasons could be security/permissions reasons where you want the SQL Server service account to have different local or network permissions. Another reason would be if you need to have 2 databases with the same name on the same server. You can only do this by having a named instance. In my opinion, you create a named instance for any of the reasons you or I have mentioned when you can't allocate a second SQL Server to do the job.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

iccEC SSC Rookie Points: 30 More actions · Answer 2

Since I'm minded for performance tuning these days and that we are talking about Named instances , etc... What is the actual performance difference if we compare (all these on the same physical SQL server):

- Doing a SELECT from a table that's in the same database and instance to that table that it JOINs to.

- Doing a SELECT from a table that's in another database but in the same instance to the table that it JOINs to.

- Doing a SELECT from a table that's in another database *and* in another instance to the table that it JOINs to.

The reasoning for this realy would be to have containers for supplier catalog lists that I want to save, but since they might not be used a lot, I would put them elsewhere as to not bear "weight" on my own base.

Has anyone experimented with this yet?

Eric