Default Instance and Named instance using the same environment SQL 2008

  • I've inherited a sql server setup. I've run across a situation. It seems to be two separate instances of sql server available. The default serverA and a named instance ServerA\A. If you view the instance from SQL Server Configuration Manager the only Instance available is ServerA. If you query sys.servers then SERVERA\A returns. I can connect to both instances and see the Same databases. While connected to both instances If I create a login in ServerA, I cannot see the login in ServerA\A immediately. If I reconnect the login appears. This is causing some issues for the development team. I'm thinking my only alternative is to uninstall then reinstall the default instance. Unless someone has a better suggestion

  • 1. What version of SQL Server is each instance?

    2. When you look at services.msc, are 2 instances showing up with MSSQLSERVER service? (i.e. is one analysis services or SSRS or something)

    3. Why would you expect a login you create on 1 instance to show up on another?

    Jared
    CE - Microsoft

  • 1. What version of SQL Server is each instance?

    2. When you look at services.msc, are 2 instances showing up with MSSQLSERVER service? (i.e. is one analysis services or SSRS or something)

    3. Why would you expect a login you create on 1 instance to show up on another?

  • 1. What version of SQL Server is each instance?

    Both ServerA and ServerA\A are 2008 Sp1

    2. When you look at services.msc, are 2 instances showing up with MSSQLSERVER service? (i.e. is one analysis services or SSRS or something)

    No just the one instance is showing under services(ServerA default instance). It it reads exactly like the configuration manager just ServerA. They both show as database engines.

    3. Why would you expect a login you create on 1 instance to show up on another?

    If they were actually separate instances I would not expect to see the login. I was curious to see what happened if I made a change to the ServerA instance. Creating the login was non intrusive to what everyone else was doing on the instance.

  • What do you see in the registry settings for the SQL Server?

    If you use regedit

    HKEY_LOCAL_MACHINE

    SOFTWARE

    MICROSOFT

    MICROSOFT SQL SERVER

    (look for folders matching "ServerA" and "ServerA/instance"

    Are there two.. and also how about Binaries pointing same spot? (e.g. Setup folder of registry settings)

    --------------------------------------------------
    ...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

  • I see ServerA\A which I believe is the named instance.

  • There is only one entry for ServerA\A

  • You mention in the registry you see server A\A instance

    Do you see 2 instances? (default and named)

    example in registry might look

    Microsoft SQL Server

    |

    --- MSSQL10.MSSQLSERVER (default)

    |

    --- MSSQL10.NAMED_INSTANCE_A (you serverA\NamedInstance

    --------------------------------------------------
    ...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀

  • Hmm... What is the name of your named instance?

    Jared
    CE - Microsoft

  • go into sql server configuration manager and check the alias section. Someone has setup an alias for the named instance probably

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • The named instance is in the registry. An alias does not exist. I apologize for not mentioning this earlier.

  • what does the following registry branch show

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Still didn't answer my question. What is the name of the named instance?

    Jared
    CE - Microsoft

  • The Connect To key does not exist. I have a last connect key which list the named instance.(SQLDev\Dev)

    BTW I did not give the actual server names I gave an example. ServerA (default) ServerA\A (named)

    The actual name sqldev(default) SQLdev\dev(named)

  • What you have is a Named, default instance, and only one instance on the server.

    During installation it is possible to give the default instance a name.

    In SSMS both names (ServerA and ServerA\A) are addressable and point to the same instance.

    Ron

Viewing 15 posts - 1 through 15 (of 17 total)

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