March 28, 2011 at 10:15 am
I'm working on creating a DR environment that matches up with a clustered environment I have in production. The new environment is a stand alone machine named after the node in the cluster. Call it ND01. Somehow the SQL Server is called SQL03. There is an additional IP address & alias showing up in the cluster administrator called SQL IP Address1 (SQL03) and SQL Network Name (SQL03). I'm guessing that this is what is allowing the SQL Server installed on ND01 to actually be called SQL03. Where it gets strange is that SQL03 in production can be referenced as either SQL03 (as if it was the default instance) or SQL03\SQL03 (as if it has a named instance of SQL03).
Does anyone have any idea how this is being managed? Unfortunatly the person who set it up is long gone, and the person who currently supports it has no idea how it was done either.
Thanks
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
March 28, 2011 at 12:42 pm
Kenneth
what does the following return when executed against the instance
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ComputerName,
CASE (SELECT SERVERPROPERTY('ISCLUSTERED'))
WHEN 1 THEN 'VirtualName = ' + CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(25))
WHEN 0 THEN 'Not Clustered'
END AS VirtualNetworkName,
ISNULL(SERVERPROPERTY('Instancename'), 'Default') AS InstanceName
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 28, 2011 at 1:03 pm
Perry Whittle (3/28/2011)
Kennethwhat does the following return when executed against the instance
SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ComputerName,
CASE (SELECT SERVERPROPERTY('ISCLUSTERED'))
WHEN 1 THEN 'VirtualName = ' + CAST(SERVERPROPERTY('MachineName') AS NVARCHAR(25))
WHEN 0 THEN 'Not Clustered'
END AS VirtualNetworkName,
ISNULL(SERVERPROPERTY('Instancename'), 'Default') AS InstanceName
I get the following:
ComputerNameVirtualNetworkNameInstanceName
ND01VirtualName = SQL03SQL03
Thanks for your help with this one.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
March 28, 2011 at 1:42 pm
Windows 2003 or 2008 cluster?
What does this return
SELECT SERVERPROPERTY('Servername')
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 28, 2011 at 2:00 pm
Perry Whittle (3/28/2011)
Windows 2003 or 2008 cluster?What does this return
SELECT SERVERPROPERTY('Servername')
Windows Server 2003 R2
SQL03\SQL03
Thanks again
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
March 28, 2011 at 2:31 pm
One of my server guys pointed out that the SQL03 instance is on port 1433. Could that be causing the behavior I'm seeing? I've never put a named instance on 1433 so I can't be certain thats it.
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
March 28, 2011 at 2:48 pm
check the following registry key and post the values
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
Also check this for the port number in use by the instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Tcp\IPAll
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 28, 2011 at 3:16 pm
Perry Whittle (3/28/2011)
check the following registry key and post the valuesHKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
Also check this for the port number in use by the instance
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Tcp\IPAll
Name: SQL03.domain.company.com
Type: REG_SZ
Data: DBMSSOCN,sql03.us.aegon.com,1433
The other key also shows 1433
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
March 28, 2011 at 3:36 pm
ok so you have a TCP alias setup as SQL03 which points to SQL03,1433 or SQL03\SQL03 does this make sense?
Open sql server configuration manager and go to SQL Native Client Configuration -> Aliases
you'll see it there
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 31, 2011 at 7:49 am
Yes, and thanks for all of your help. I finally tracked it down about the time you posted your last post.
The way I have it is that if there is no default instance and you have a named instance with a port of 1433 you can connect with either servername or servername/instancename. I would be curious to see what would happen if I set up a server with a default instance and a named instance and set the named instance to port 1433.
Thanks again
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply