25112
asked on
IP of SQL instance
does the SQL Server instance have an IP address? or does it only in a cluster situation?
how does the above change if there are multiple instances in one machine.
how does the above change if there are multiple instances in one machine.
TempDBA
Yes, every instance has their own ip address. All the instances in a node will be having different ip addres..
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
so I understand that each instance will have a distinct IP.. so obviously SQL instance need not be the same as the machines.
how can we find/see the IP of an instance from tsql code or from ssms?
how can we find/see the IP of an instance from tsql code or from ssms?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i tried that.. but it works most of the time.. (matches with ipconfig address).. but sometimes does not bring anything back (both on cluster and non-cluster also).. sometimes it is wrong..
no way to check from Sql server folders or settings?
will Each instance on a machine have an distinct IP address ?
no way to check from Sql server folders or settings?
will Each instance on a machine have an distinct IP address ?
No, there is no way to check from SQL Server.
Yes, every instance listens to different IP addres..
If still you don't get your answer, you can raise an attention, that will be taken care from experts.
Yes, every instance listens to different IP addres..
If still you don't get your answer, you can raise an attention, that will be taken care from experts.
That is not entirely true. You can have multiple instances running on the same server with a single IP address. Each instance will take the form of:
x.x.x.x\instance_name
In the case of the default instance, you can address via the host name or x.x.x.x (i.e., IP address). These can all use the same SQL port of be bound to different ports. It just depends on the implementation. In a cluster situation, what is going on is that the nodes are independent instances that can run independently if the database files are local, but that can also host a clustered IP address and instance name whose database may be on a shared drive within a cluster.
x.x.x.x\instance_name
In the case of the default instance, you can address via the host name or x.x.x.x (i.e., IP address). These can all use the same SQL port of be bound to different ports. It just depends on the implementation. In a cluster situation, what is going on is that the nodes are independent instances that can run independently if the database files are local, but that can also host a clustered IP address and instance name whose database may be on a shared drive within a cluster.
By the way, from SQL you can use SELECT @@servername. With the host name, you can typically find the IP address using normal nslookup type tools.
Also, you may have interest in these server property values:
SELECT SERVERPROPERTY('MachineNam
http://msdn.microsoft.com/en-us/library/ms174396.aspx
The first returns the virtual server name in a clustered environment.
The second returns the physical host name that is currently the active node in the cluster for a given instance of SQL server.
SELECT SERVERPROPERTY('MachineNam
http://msdn.microsoft.com/en-us/library/ms174396.aspx
The first returns the virtual server name in a clustered environment.
The second returns the physical host name that is currently the active node in the cluster for a given instance of SQL server.
ASKER
thanks Kevin.. can you please just repharase the following
"These can all use the same SQL port of be bound to different ports."
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
good answer and explanation indeed!