Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

DNS solution for sql server running in named instance or different port Expand / Collapse
Author
Message
Posted Tuesday, July 7, 2009 10:53 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 11:18 PM
Points: 184, Visits: 3,825
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.
Post #748728
Posted Tuesday, July 7, 2009 11:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #748739
Posted Tuesday, July 7, 2009 1:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, August 29, 2014 1:51 PM
Points: 21,644, Visits: 15,317
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #748837
Posted Monday, July 13, 2009 6:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 20, 2012 11:23 AM
Points: 153, Visits: 148


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
Post #751988
Posted Monday, July 13, 2009 7:24 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 11:18 PM
Points: 184, Visits: 3,825
@ 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.

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

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.
Post #752022
Posted Monday, July 13, 2009 8:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 12, 2014 12:57 PM
Points: 2,285, Visits: 4,225
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
Post #752060
Posted Monday, July 13, 2009 5:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #752397
Posted Friday, April 9, 2010 1:52 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 10:36 AM
Points: 4, Visits: 99
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.
Post #900809
Posted Friday, April 9, 2010 2:03 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
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
Post #900814
Posted Friday, April 9, 2010 2:52 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 12, 2014 12:57 PM
Points: 2,285, Visits: 4,225
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
Post #900846
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse