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

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  December 19, 2005 at 12:20 pm

  #610406

  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

  December 20, 2005 at 7:45 am

  #610574

   

  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