July 7, 2009 at 10:53 am
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.
July 7, 2009 at 11:13 am
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
July 7, 2009 at 1:47 pm
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[/url]
Learn Extended Events
July 13, 2009 at 6:48 am
: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:
July 13, 2009 at 7:24 am
@ 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 remedy:blink:
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.
July 13, 2009 at 8:07 am
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
July 13, 2009 at 5:50 pm
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
April 9, 2010 at 1:52 pm
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.
April 9, 2010 at 2:03 pm
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
April 9, 2010 at 2:52 pm
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
April 12, 2010 at 2:20 am
so .... picture this ..
Physical server name TheServer IPaddress 172.16.6.10
The instance TheServer\TheInstance listening at port 2072 (double check its usage if you actually want to use this particular port number !)
The DNS alias TheSQLInstance mapping to 172.16.6.10
Where in the server configuration tools of SQL2005 do you map TheSQLInstance to the sqlserver instance to be used (TheServer\TheInstance) and how ?
Please add a screenshot. I must be missing a click.
If I'm correct the goal of this should be that your applications can connect using a connection string like this:
Data Source=TheSQLInstance;Initial Catalog=myDataBase;Integrated Security=SSPI;
(so without actually providing a port number or instance name by any client side setting )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 12, 2010 at 3:19 pm
With the SQL Server Configuration Manager open, expand "SQL Native Client Configuration", right click the 'Aliases' in the tree, and choose 'New Alias'. There are 4 items to be entered:
1.Alias Name: TheSQLInstance
2.Port number: 2072
3.Protocol .TCP/IP
4.Server : 172.16.6.10
SQL = Scarcely Qualifies as a Language
April 12, 2010 at 3:24 pm
I think that kind of defeats the purpose of what was being asked.
CEWII
April 13, 2010 at 12:38 am
Carl Federl (4/12/2010)
With the SQL Server Configuration Manager open, expand "SQL Native Client Configuration", right click the 'Aliases' in the tree, and choose 'New Alias'. There are 4 items to be entered:1.Alias Name: TheSQLInstance
2.Port number: 2072
3.Protocol .TCP/IP
4.Server : 172.16.6.10
Having this configured at client side works (as long as the port number doesn't change)
But having his at server side, doesn't work. Client side, that server alias is pingable using TheSQLInstance, but SSMS will not connect.
(I tested it on a Win2003 domain using an alias as well as using a c-name)
So you would need to provide this alias at all clients. :unsure:
( That's what I want to avoid. So, I'll (still) have clients provide the instance name )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 31, 2011 at 7:41 am
We recently migrated 25 large data warehouse DB's from a single default instance of SQL Server (2005) to a single new, named instance of SQL Server (2008 R2 sp1). Let's call them:
SQLServerOLD2005 >>> to >>> SQLServer2008R2\myInstance
Now being asked to set up an alias for the old SQLServer -- to allow any applications, references etc. that are currently pointing to SQLServerOLD2005 to be dynamically redirected to the new SQLServer2008R2\myInstance.
Is this s 2 step process?
1. CREATE a DNS ALIAS
2. CREATE a SQL Server ALIAS (using the SQL Server Configuration Manager's - SQL Native Client 10.0 Configuration, Alias's definition (which requires Alias Name, Port No, Protocal, Server)
I anticipate standard feedback -- "manually update all your connections in those applications to point to the new SQL Server instance" -- but my Mgmt still wants to install the Redirect via DNS.
thoughts?
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply