DNS solution for sql server running in named instance or different port
-
:w00t: :hehe:
It all depends on how your application connects to your data server. We just completed a consolidation project and had to use a mix of the DNS pointing to the new instances and the SQL Server Network Utility to set up the alias.
of course you have to have the alias defined in SQL Server (Configuration Manager) At that point, I did not include any port. Then the DNS resolved the name, no redirect at all, but ... I think they want to play 150% safe.
For those apps connection via ODBC or otherwise, I use the SQL Network utility and set the alias at each server being used by the application executable to open their stuff (run cliconfg.exe, it is an enbedded utility in all Windows boxes, workstations or servers) I then, add the alias and all our apps (web base or fat clients were able to connect without any issues) Consolidation went from 85 servers to 14 and all of them are working properly. Good luck
:w00t: :hehe: 😎
-
Unfortunately you can't do it completely transparently. Keep in mind DNS ONLY addresses the IP address of the server, thats about 90% of your problem, you can get the clients pointed to the correct server. But without the named instance name, you can't get it to the right SQL instance.
As another poster pointed out you could add a client alias on each machine but that means you still have to touch each machine.
There is a trick but I'm not sure you want to go down that path, provided there isnt a default instance on the machine currently using port 1433 you can force the named instance to use the default port and in that case the DNS will handle the redirection.
CEWII
-
Having encountered a similar scenario to those outlined in this thread I'm trying to follow the steps given and getting hopelessly lost. I work as a programmer for a small local council that doesn't have an actual DBA, hence I or the other programmer get tasked with anything relating to our databases. We have perhaps 50 or 60 one click applications of various sizes (from single form data entry to full document keeping or building application systems), all of which include pointers to our current SQL server which is a default 2005 instance. We also have a separate server running 2005 for payroll and a third for finance which we hope to consolidate onto the same server but we'll be moving them in the future. We have a new virtual server setup with SQL 2008 Enterprise, some of the databases have been copied across for testing, I just need to get it so that the current applications won't all fall over when we switch to it.
The concepts outlined here seem to be exactly what we're after but without a proper background they just don't give enough detail for me to make them work for me. If we say the current machine is for example 'CNLSQL' with IP 192.1.1.50 and we want it to go to 'CNLSQL01V\CNLSQL' what DNS entries do I need to make? Is the existing AHost name for CNLSQL the same entry that will be needed for the new instance (in which case no changes need to be made to dns, just turn off the current machine) or should something be changed there? Then in SQL Server Configuration Manager I think it should look something like this?
This is what I tried on the weekend just gone but as soon as I set "Listen All" to no, I couldn't start the service. When I left it as yes I was actually able to connect to CNLSQL01V\CNLSQL as CNLSQL through SQL Server Management Studio on the virtual server that was hosting it but not from anywhere else.
Any advice would be gladly welcomed before we resort to blowing away the current virtual machine and creating a new one called CNLSQL as a direct replacement.
-
DNS ONLY (REPEAT ONLY) handles name resolution, it does NOT handle port assignments which are important for named instances.
What this means is that if you are current pointed at an instance that is running on the default port (1433) and you want to use DNS to point at a new machine using a named instance that would not be on the default port you have a problem that simply cannot be solved with DNS. With that said though there are some tricks that *may* be able to be used until you can fix it properly, but even those can only go so far.
The first question is that you mentioned Enterprise Edition, are you using fail-over clustering? If not then we have some options.
You can use DNS to say that old machine OldSQLMachine1 and OldSQLMachine2 is now NewSQLMachine1, where you get into trouble is named instances which are rarely on the default port (and never without some assistance).
Aliases are only useful for clients, and I wouldn't suggest using them in this case because it will complicate things further AND they have to be added to EVERY client machine which is a maintenance nightmare.
But it sounds like you have several servers you are consolidating onto a single server and each will have its own named instance, is that right?
CEWII
-
I don't believe we are using fail-over clustering on SQL Server (though I'm not entirely certain how to check). We have 3 different servers at the moment and ideally we would like to have them all running on the same box but retaining the current name for the connection strings so that we don't have to go through and attempt to update all those simultaneously with putting the new machine live. I'm working based on the assumption that they would need to be separate instances in order to connect with three different connection strings?
-
No. Nothing in DNS will help with the port referencing.
You could try an app rewrite such that the app uses a central config file rather than deploying that config to each desktop.
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 -
If it was just 'the app', that would be a viable solution. But it's not, there are 60+ one click .net apps that all have multiple connections to the main sql server as well as third party applications that are setup for the current server. There are also some old access databases that are outside of our control (IT that is, they were built and maintained by other departments without consulting us first) but which at least have the backend in SQL now. Then there is the dozen or two SSIS packages which again point at the server. The other two sql servers are far less spread throughout the organisation and for those two it is plausible that we could change the connection strings, for the main server I don't believe that would be possible without extensive downtime which simply wouldn't be acceptable to the rest of the organisation.
If I can't find a way to set up a pointer somehow using DNS and SQL configuration manager to redirect the current server to the new instance (or any other way someone can suggest, it doesn't have to be DNS if there is another solution) then as previously mentioned it will be a case of creating a new virtual server with the same name as the current one and use the default instance again. Detach all the databases from the current server, copy them across to the new shared volume, turn off the current server, connect the new server to the network and the shared volume, attach all the databases, and go from there. That would mean I can't use the copy database wizard (couldn't have both machines online at the same time with the same name of course) and would have to go through and fix all the user logins manually after the event as well as all the jobs and SSIS packages. It would also mean working around our attempt at finally standardizing server names and attempting to do things as best we can rather than simply making it work, but a working cludge is better than a nonfunctional machine.
-
faulcon (10/17/2011)
If it was just 'the app', that would be a viable solution. But it's not, there are 60+ one click .net apps that all have multiple connections to the main sql server as well as third party applications that are setup for the current server. There are also some old access databases that are outside of our control (IT that is, they were built and maintained by other departments without consulting us first) but which at least have the backend in SQL now. Then there is the dozen or two SSIS packages which again point at the server. The other two sql servers are far less spread throughout the organisation and for those two it is plausible that we could change the connection strings, for the main server I don't believe that would be possible without extensive downtime which simply wouldn't be acceptable to the rest of the organisation.If I can't find a way to set up a pointer somehow using DNS and SQL configuration manager to redirect the current server to the new instance (or any other way someone can suggest, it doesn't have to be DNS if there is another solution) then as previously mentioned it will be a case of creating a new virtual server with the same name as the current one and use the default instance again. Detach all the databases from the current server, copy them across to the new shared volume, turn off the current server, connect the new server to the network and the shared volume, attach all the databases, and go from there. That would mean I can't use the copy database wizard (couldn't have both machines online at the same time with the same name of course) and would have to go through and fix all the user logins manually after the event as well as all the jobs and SSIS packages. It would also mean working around our attempt at finally standardizing server names and attempting to do things as best we can rather than simply making it work, but a working cludge is better than a nonfunctional machine.
So long as you don't have named instances and port differences, you can use DNS - but the original issue in this thread was around named instances and different ports.
Setup CNAMEs in DNS as long as you have not changed the port number or as long as you are not using named instances.
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 -
:w00t::hehe:
I initiated this trend a couple of months back due to the fact that we were moving forward with our consolidation project (went from 88 servers to 14) We use aliases, and some other technics to accomplish the purpose of our project.
So far all is going great. A couple of things to have in mind. The instances do not use 1433 port at all, each instance has set dynamic ports to allow the apps to connect to any port available at the time the connection is established (as soon as you start the installation of SQL using Name Instances, it does not select 1433 to be use and you will be able to set the ports as you want, though)
Memory and CPU have to be set per instance to allow the server to work as needed and don't keep to much processes in cache (BOL has some information about this settings)
Do your due diligence before staring to move things around, SQL is very easy if you follow best practices. Good luck
:hehe::w00t:
-
Yes, there will be named instances, 3 of them eventually (unless we go to the fallback in which case there's no DNS or aliases or anything, it would effectively be a straight upgrade of the existing box retaining the same server name). For a named instance you automatically have a different port as I understand it since only one instance can be on the default port and that's generally the default instance? This is basically what I'm trying to do as asked earlier in the thread:
Fernando-235287 (7/13/2009)
: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:
AND
ALZDBA (4/12/2010)
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 )
I tried working through the suggestion given here:
Carl Federl (7/13/2009)
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.
But I can't seem to get it to work either, I don't have enough knowledge of either DNS or SQL Server configuration to be able to translate from this quick answer to the full solution.
-
Fernando-235287 (10/17/2011)
:w00t::hehe:I initiated this trend a couple of months back due to the fact that we were moving forward with our consolidation project (went from 88 servers to 14) We use aliases, and some other technics to accomplish the purpose of our project.
So far all is going great. A couple of things to have in mind. The instances do not use 1433 port at all, each instance has set dynamic ports to allow the apps to connect to any port available at the time the connection is established (as soon as you start the installation of SQL using Name Instances, it does not select 1433 to be use and you will be able to set the ports as you want, though)
Memory and CPU have to be set per instance to allow the server to work as needed and don't keep to much processes in cache (BOL has some information about this settings)
Do your due diligence before staring to move things around, SQL is very easy if you follow best practices. Good luck
:hehe::w00t:
Yes, this is exactly what I want to do and I want to follow the best practices, I just don't know how. Can you tell me how you've made it work?
-
:w00t::hehe:
Forgot to ad the following, there is an utility that comes with all Windows systems that will allow to set the aliases on each machine, but that is time consuming and we found a way to push the entry to the registry (that is what the utility does) as a regular distribution to your servers (patches, service packs, etc)
Type cliconfg.exe, it opens the utility and there you can set up the alias. OK good luck now
-
There is "sort of" a way to do this, but it is gonna take some work. You can assign an IP address to each instance and force it to use port 1433 on that IP and then use DNS to point at THAT IP. If you are running on a cluster then you can't do this.
Its kind of a kluge but maybe..
CEWII
-
Well the virtual machine is running on a hyper-v cluster, but that's not the cluster you're talking about, correct? I'm fairly certain there is no sql server cluster setup though I am not sure how to check this to make absolutely certain.
Since we only have the three servers to consolidate I expect it will be much quicker to setup individual IP addresses and a DNS pointer for each of those three rather than going through all the individual applications. I can't quite get the steps to work though on the instance I installed to test with:
testinstance (what I want to use in the connection strings) is the alias, the database is instance sqltest on server escsql01v
That IP2 address is the only enabled one, Listen All is set to No because I only want it to listen on the set IP, correct? But if I do that I can't start the service. I'm sure there's something basic I'm overlooking here but without a solid foundation in SQL server or DNS I'm basically just taking guesses at what sounds logical to me for the details.
-
faulcon (10/17/2011)
Well the virtual machine is running on a hyper-v cluster, but that's not the cluster you're talking about, correct? I'm fairly certain there is no sql server cluster setup though I am not sure how to check this to make absolutely certain.Since we only have the three servers to consolidate I expect it will be much quicker to setup individual IP addresses and a DNS pointer for each of those three rather than going through all the individual applications. I can't quite get the steps to work though on the instance I installed to test with:
testinstance (what I want to use in the connection strings) is the alias, the database is instance sqltest on server escsql01v
That IP2 address is the only enabled one, Listen All is set to No because I only want it to listen on the set IP, correct? But if I do that I can't start the service. I'm sure there's something basic I'm overlooking here but without a solid foundation in SQL server or DNS I'm basically just taking guesses at what sounds logical to me for the details.
I am talking about failover clustering which is different than a hyper-v cluster. Before we look at the multiple IP solution what is the error in the errorlog?
CEWII
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply