• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 566
  • Last Modified:

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.
0
25112
Asked:
25112
  • 4
  • 4
  • 3
  • +1
3 Solutions
 
TempDBACommented:
Yes, every instance has their own ip address. All the instances in a node will be having different ip addres..
0
 
strickddCommented:
Every machine will have one or more IP addresses. It all depends on how many network cards there are. Each instance on a machine will be bound to an IP address and run on a specific port.
0
 
25112Author Commented:
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?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
TempDBACommented:
You can only get ip address from os. You can use xp_cmdshell with ipconfig. The link has nicely written procedure for it.

http://www.sqlmag.com/article/tsql3/dynamically-obtain-a-sql-server-s-ip-address-in-t-sql-code
0
 
25112Author Commented:
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 ?
0
 
TempDBACommented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
Also, you may have interest in these server property values:

SELECT SERVERPROPERTY('MachineName'), SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
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.
0
 
25112Author Commented:

thanks Kevin.. can you please just repharase the following
"These can all use the same SQL port of be bound to different ports."
0
 
Kevin CrossChief Technology OfficerCommented:
Just like the IP address, you can have all the SQL traffic on the same TCP port or on different ports, especially if using dynamic ports. Because of firewalls, most folks tend to use a specific port so that rules can be made to allow traffic or not. When multiple requests come in on the same IP address and port, SQL resolves the instance by the instance name. ;) Therefore, that point just went along with the fact that you do NOT need to host on different IP addresses. Whether you should is up to your needs and environment.
0
 
25112Author Commented:
good answer and explanation indeed!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now