SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DNS solution for sql server running in named instance or different port


DNS solution for sql server running in named instance or different port

Author
Message
Satheesh E.P.
Satheesh E.P.
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 3888
Friends,

Recently we had a server migration from one to another centre. The Servers in the target centre is in different vLan and the IP address is to be changed but we had retained the same host name. But some application is referring to the IP address and we happen to modify those application. It has become a challenging task since most of them are desktop application and we have to redeploy in all the machines.

So, I requested my development time to use only the host name in all the applications. But still it's not a better method. For any reason, if I move a database from one to another server then also the applications should be modifed to change the host name.

So I planned to use different DNS for each database like dbname.domainname.com pointing to the particular database server IP and give it to the development team. The problem here is I have my production box to run with different port number. So I used to connect to the server as ServerName,Port. If it is DNS then dbname.domainname.com,port. The port is not unique in all the servers. So in this case still the applications should be modified to change the port. Is there is any way to point the DNS to a specific port or any other solution for this problem ?

Thanks in Advance.

---------------------------------------------------
Thanks,
Satheesh.
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23282 Visits: 5314
I don't believe the each database name as a DNS entry can work, there really is no connection between the DNS name and the database itself. If you have SQL Browser running you generally don't have to know the port..

As I think about this, if the Database name doesn't change then you could create a DNS entry for each Database (or particular grouping, like an application where its DBs are ALWAYS together..), but it would really only point at the serverthat that database resides on and not the database itself..

You could build some kind of repository, where an application checks to see what server a database it is looking for is on. You could even handle things like DEV, TST, and PROD by making your client app awre of its configuration and assuming one of the environments it it isn't told explicitly..

DNS does not generally reference ports, sometimes you can make some assumptions about a service based on its name like you know that a URL that starts with www is for the WWW service on port 80. I'm not sure if there is any metadata you could add to DNS to accomplish this, but if you did you would tie your apps VERY closely to a particular DNS setup and that raises alarms for me.

Your idea of dbname.secondleveldomain.tld can work provided the db names don't change on the server. And if you have SQL browser running you should not need to know ports.

Clear?

CEWII
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64981 Visits: 18570
We use a similar setup for the explicit purpose of being able to easily move databases and instances from one server to another should the need arise.


I would recommend that you (for the sake of sanity) standardize ports being used - each instance has a specific port. For example applicationinstance1 would have port 1463 in dev, qa and production. This can be configured even after the instance has been in use for some time.

Ports are not typically used in DNS names, nor are databases. DNS typically ends at the instance level.

I would start using the dns,port# as a standard procedure in the code and then you can specify dbname in your constrings.

By specifying the port#, you also ensure that you will connect should the Browser service is turned off. We turn it off in our environments intentionally.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Fernando-235287
Fernando-235287
Mr or Mrs. 500
Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)

Group: General Forum Members
Points: 527 Visits: 148
Hehe w00t

Adding a new question here.

I am in the process of consolidating all my servers. Over the years there were many SQL Servers installed and we ended up with a bunch of single server per application (what a waste of time, licenses, resources, maintenance, etc, etc, etc)

I will get better and powerful machines to get all move into the new machines. No problem in doing that, but the applications (hundreds of them) use the current server names.

Can I create DNS, install instances on my destination servers, and point the DNSs to my newly installed instances? To avoid all application code changes? I can not create DNS to databases as there are quite a few databases per server.

Which way to go? Named instances and create DNS for each instance? or just ask the developers to make their changes in the code to point to the new server (either default or named instance)?

Thank you for your input w00t
Satheesh E.P.
Satheesh E.P.
Mr or Mrs. 500
Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)Mr or Mrs. 500 (521 reputation)

Group: General Forum Members
Points: 521 Visits: 3888
@ Elliott W and CirquedeSQLeil

I got the problem. My sql server instance is installed in the port 1434. Since the sql browser is reserved to use the port 1434 and as sql server is running on that port there is no use if the sql browser is running.Crying

Somewhere this mistake had happen and I am looking for a remedyBlink

Right now, I am unable to change the port of sql sever from 1434 to some other port. Since many critical application is pointing to the server using that port. If I change the sql browser to some other port, will I be able to connect to the sql server instance without specifying the port?

@ CirquedeSQLeil

I would recommend that you (for the sake of sanity) standardize ports being used - each instance has a specific port. For example applicationinstance1 would have port 1463 in dev, qa and production. This can be configured even after the instance has been in use for some time.

Can you please provide more explanation or reference?

---------------------------------------------------
Thanks,
Satheesh.
Carl Federl
Carl Federl
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6706 Visits: 4352
To insure that the clients can connect only specifying the DNS Alias and do not need to specify the SQL Server Name, IP or port, here is a list of the steps needed to implement:

1) Set up the DNS Alias.
2) Determine what ports are going to be used for each SQL Server instance. Registered Ports are those from 1024 through 49151 and Dynamic and/or Private Ports are those from 49152 through 65535. The registered ports number ranges should not be used for named SQL Server instances as a future conflict is possible. Reference http://www.iana.org/assignments/port-numbers
3) Reserve the port numbers so that no other program requests a dynamic port assigned to your SQL server. "How to reserve a range of ephemeral ports on a computer that is running Windows Server 2003 or Windows 2000 Server" http://support.microsoft.com/kb/812873/
4) "Configuring SQL Server Named instances to use a fixed port" http://technet.microsoft.com/en-us/library/ms345327(SQL.90).aspx. This will require a SQL Server restart.
5) Map the DNS aliases to the SQL Server instance name using the SQL Server 2005/2008 Configuration Manager but with SQL Server 2000, use the SQL Server Network Utility. This utility can only be run on the server hosting the SQL Server.

SQL = Scarcely Qualifies as a Language
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23282 Visits: 5314
I believe that SQL browser listens on UDP port 1434 not TCP port 1434. That makes a difference.

I think the moral of the story is "don't let SQL decode what ports it listens too, always take control.."

CEWII
lauer_Christopher
lauer_Christopher
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 111
This is exactly what I need! I would like to have a DNS point to an instance name in such a way that when the DNS is used the instance name or port is not needed (database name in the connection string is expected). I have a instance JV6DBD1\SQL2005Developer and a SQL Alias that points to this instance named "SQLAlias2005". I also have a DNS named db.Lauer2005.domainname.com that resolves to my SQL Server IP address, how do I link the DNS to the SQL alias (SQLAlias2005)? My forehead is getting very flat with the all banging I have been doing!

Currently I am trying to get it to work on my desktop (have three instances running; SQL2000Developer, SQL2005Developer and SQL2008Developer). If I can get this to proof out then we will move the idea to our fail over clusters (we have three and all three clusters have two SQL Servers installed, one default one instance). I would like to make it so the six DNS’s all function the same, i.e., you don’t have to append the instance name on three of them.

Thank you o so very much for any detailed help you can provide to me (a SQL DBA) and my network admin counterpart.
Elliott Whitlow
Elliott Whitlow
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23282 Visits: 5314
Here is the problem. DNS resolves a name db.domainname.com to an address 10.1.5.50, this does not imply any port or anything else. Given this, DNS alone cannot do what you are asking. However, if you have multiple instances on a box you MIGHT be able to assign an IP address to the box for EACH instance and configure that instance to listen to the default port 1433 on that IP address alone and on the main box ip address at a specified port. I think that then you could reference the server by then DNS name or by servername\instancename. It requires a fair amount of manual setup and management but IS doable. The key is that each instance is hard coded to an ip address and port.

Did that make sense?

Coincidently, I think this was covered further up in the chain..

CEWII
Carl Federl
Carl Federl
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6706 Visits: 4352
how do I link the DNS to the SQL alias (SQLAlias2005)?

You do not resolve the SQL Server instance name with DNS but instead use the SQL Server utilities as explained under step 5:

5) Map the DNS aliases to the SQL Server instance name using the SQL Server 2005/2008 Configuration Manager but with SQL Server 2000, use the SQL Server Network Utility. This utility can only be run on the server hosting the SQL Server.



SQL = Scarcely Qualifies as a Language
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search