Pros/Cons between Default vs Named Instances

  • My situation is that our network admin has come up with a new, much more complex naming convention for our company's servers.  They contain dashes and the names are sometimes quite long.  I am flexable enough that I can type in the extra characters, but it does cause some problems when referencing one of the servers with a funky name...but I digress.  The main problem is that I feel I am ceding some control of my SQL environment to the network admin.

    In the past I have always simply used a default instance of SQL because the machine name was not an issue.  Now that it is, I am considering using named instances of SQL so that I have more control over my SQL environment.  The challenge is that I am not familiar enough with the differences between the two.  I am specifically interested in knowing if having named instances makes it harder for app developers to connect to my servers, though any and all information about this topic would help me.

    Unfortunately, I don't have the resources to simply throw a named instance out there and see what happens without knowing what I'm getting myself into (otherwise I would).  I have been looking around online for posts regarding and benefits or complications arising from using a named instance of SQL over the default instance, but have not found anything that satisfies my needs.

    I would like some ammo to give to management that would convince them to allow me to try a named instance of a SQL server instead of using the default name like we always have.  Likewise, if someone feels that I am being irrational and should just deal with it, I would like to hear your views as well.

  • Hi Greg,

    If you're interested in named instances because the server name is long, you'll probably be disappointed to know that a named instance is identified by a name with the format computer_name\instance_name.  So applications will have to specify that to connect.

    Greg

    Greg

  • Thanks for the response, Greg.  That pretty much bursts my bubble and is exactly what I needed to know.  I guess my next question becomes:

    Does anyone have a +5 Wand of Network Admin Brainwashing that I can borrow?  Though perhaps that one belongs in a different forum...

  • Just lost a long post on this, and no time to recreate it now.

    I have never been successful with named instances because I always ended up having problems with third-party software not connecting.  It's hard to get them to change their ways.  Developers can be told how to connect, and they connect.  Now, getting them to change existing code is another matter.

    I seem to remember a problem with dashes in server names.  I believe it was when I was installing SQL Server 2000 on a cluster, and the second node could not deal with the dash.  Sorry, but my memory is fuzzy on this, if anyone can provide details, please do.

    I would also say "just deal with it"... they aren't your machines any more than they are the sys admins... if they're being jerks, and you respond by being a jerk, you can cause more trouble than you'd ever want.  I mean, you COULD say that you don't like the admins having "rights" on your db (which they do), and you COULD start removing privs from SS, closing accounts, rename sa, restricting privs... and next thing you know you've got a war   (not to mention the possibility of locking yourself out of your "own" database).

    Just remember "ownership" means work... if you cede some control to them, at some point you will also cede some work to them.  I don't see that as a bad thing.

    Thank-you,
    David Russell
    Any Cloud, Any Database, Oracle since 1982

  • I have also seen issues with 3rd party/vendor software having connectivity issues with named instances. If the application can handle it, then there is really no difference between a default instance and a named instance of SQL Server on a server providing there is only one instance on it and you are using the default port number. Yes, you gotta type more, but usually it's a one time thing for connection strings, registry entries or, ughh, ini files.

    There is a work around to get a shorter name.Its creating an 'alias' on the client machine. But lots of clients mean lots of manual intervention unless your network admins are adept at SMS rollouts. Also, it's another 'moving part' in the application that can be broken.

    The only time I can see named instances as a benefit is when one has multiple SQL Server instances on a large and powerful server.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • It seems that the concensus is to use default instances when possible.  Though most of our apps are web based, it does seem that named instances will not alleviate the issues I'm concerned with (mainly the network admin having control over how the SQL servers are named.  Between David's and Rudy's comments, I think I'll choose to stick with the named instances for the sake of not wanting to start a 'turf war' as well as the fact that some apps may not be able to handle it.

    Thanks for the suggestions all.

  • By the way ... if anyone has a spare "+5 Wand of Network Admin Brainwashing" that they would like to part with I'd be glad to take it off of their hands. At present I do not need one but having one in your tool box can only be an attribute in being prepared !

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • It's not just 3rd party apps, either. Microsoft CRM 3.0 until just recently only supported default instances. You still have to jump through hoops to get a named instance working compared to how it should be. And don't even get me started on collation. Egads.

    *puts network admin hat on*

    Now, as far as naming convention for servers go, realize that network admins are typically responsible for more servers than you are. My team deals with this same issue... the developers don't like out cryptic naming scheme for servers. However, they aren't the ones trying to keep the whole server inventory up and secure, either. My team's cryptic naming scheme allows us at a glance to identify either what group or application owns the server, what environment it rests in, whether it's physical or VM, and what it's primary purpose is. For servers not at our operating location, we have that, too. So before being so hard on your network admin, you may inquire as to why he has his/her naming convention. Now, if said person can't show you reasonable justification, make it a +5 keen greatsword of network admin smiting instead. But otherwise, you may need to cut him/her a break.

     

    K. Brian Kelley
    @kbriankelley

  • In my experience, the best way to address this is to get your network admin to set up a "virtual" IP address and name.

    This is basically just a pointer that points to the actual server/IP. This way you can perhaps pick a more reasonable name while not interfering with the networks naming standards... An additiona perk is that if you migrate your instance to a new server, you just re-point the virtual name to the new server.

  • Note: This is a very old thread.


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • Still, it's another answer that someone may run across and benefit from. Some of the "old" problems are very relevant even today. I never mind it when someone adds some new information even to a decade old post... heh... especially when the team I'm working with is currently having the same problem. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

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