June 26, 2012 at 11:51 am
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
June 26, 2012 at 12:55 pm
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
June 26, 2012 at 1:29 pm
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?
June 26, 2012 at 1:34 pm
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.
June 26, 2012 at 2:42 pm
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 😀
June 26, 2012 at 2:54 pm
I see ServerA\A which I believe is the named instance.
June 26, 2012 at 2:55 pm
There is only one entry for ServerA\A
June 26, 2012 at 3:02 pm
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 😀
June 26, 2012 at 3:02 pm
Hmm... What is the name of your named instance?
Jared
CE - Microsoft
June 27, 2012 at 3:10 am
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" 😉
June 27, 2012 at 4:40 am
The named instance is in the registry. An alias does not exist. I apologize for not mentioning this earlier.
June 27, 2012 at 4:55 am
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" 😉
June 27, 2012 at 5:18 am
Still didn't answer my question. What is the name of the named instance?
Jared
CE - Microsoft
June 27, 2012 at 11:50 am
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)
June 27, 2012 at 12:15 pm
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