February 20, 2009 at 7:39 pm
Recently we had a situation where one of our database servers moved from machine A to machine B. This meant the SQL Server returned "machine B" to SELECT @@SERVERNAME. Of course, this required the odbc.ini on our Solaris Application server to be modified. As luck would have had it, the App admin was elusive and the production schedule was closing on.
As the network administrator was available, and since machine A could be taken off the network, I suggested we could "throw in" a DNS alias of "machine A" for "machine B" (perhaps not a good way of saying it). This suggestion drew severe criticism from the SQL DBA. I would like to know from the experts here what the ill-effects are of this approach.
Of course, I would share the DBA's arguments, but honestly I did not feel they were genuine and I would want to know what the "unbiased" experts on this forum have to say about this proposed solution.
Thanks in advance!
February 21, 2009 at 12:05 am
There has been a big debate about this among a number of engineers at my company, myself included and I think there are times when it is ok and times when its not. It is obviously ok if you are running a sql cluster because the network name resource in a cluster is essentially a dns pointer managed by clustering services.
If you are running something like database mirroring though and perhaps your failover partner property in your connection string never works for some reason (classic ASP was notorious for this, although I believe this was patched later) but you may want to use a dns pointer so that your applications can connect to whatever the principal server is.
Another situation would be if you are running transaction log shipping to a standby server, and you want the ability to failover to that server with minimal effort and no need to change connection strings in your applications, you could use a dns pointer here as well. if the primary server goes down for some reason and you have to fail to the standby, you can update the dns pointer to point to the standby server.
These are only a few situations where you might want to do it, in addition to just protecting yourself from something like the situation that you had, but there are also downsides to it. In the interest of starting a discussion with multiple people, I will allow some of those things to come from others.
What I can say is that you are definitely not the first or last person to think of this idea, and it definitely has its place in certain situations, but it can also be problematic. When you have an environment that needs to have minimal downtime, it is usually best to spring for some extra hardware, set up a high availability solution and code your applications ahead of time to deal with downtime situations.
February 21, 2009 at 1:43 pm
Ok so it does not sound like network alias for the server would have been such a bad idea after all??? in fact, let me ask it another way -
If a server has a network name "B.DOMAIN.COM" and a new SQL 2005 was installed and configured on this machine - which means SELECT @@SERVERNAME and SERVERPROPERTY('SERVERNAME') return "B", would throwing in a DNS alias of "A.DOMAIN.COM" for machine "B.DOMAIN.COM" allow applications to connect to the server by specifying "A.DOMAIN.COM"?
February 23, 2009 at 7:15 am
Anyone??? Anyone????
February 23, 2009 at 8:46 am
Anyone at all????
February 23, 2009 at 10:56 am
leonp (2/21/2009)
Ok so it does not sound like network alias for the server would have been such a bad idea after all??? in fact, let me ask it another way -If a server has a network name "B.DOMAIN.COM" and a new SQL 2005 was installed and configured on this machine - which means SELECT @@SERVERNAME and SERVERPROPERTY('SERVERNAME') return "B", would throwing in a DNS alias of "A.DOMAIN.COM" for machine "B.DOMAIN.COM" allow applications to connect to the server by specifying "A.DOMAIN.COM"?
That would work, you can see it in action if you add a dummy hosts entry on your workstation and try connecting to the server through ssms.
[edit]Keep in mind you will still get "B" when you select @@SERVERNAME[/edit]
February 23, 2009 at 11:30 am
appreciate it
February 23, 2009 at 2:09 pm
I just went through changing ip on dns to new 64 bit SQL 2005 box. The only problem we had was Windows authentication to a share. There was something in event log about kerberos key not matching the machine against they were authenticating against. We ended up using the new DNS for drive mappings. Otherwise SQL appears to be working ok.
February 23, 2009 at 2:13 pm
Thank you ...
February 24, 2009 at 5:19 am
Persnally, I think having a level of indirection between your applications and the hardware is a good thing. All SQL Server machines should be given a DNS alias, and that all connection strings should use the alias.
This allows you to move the SQL instance to another server without having any impact on the application. You may only plan to do this once every 3 years when the hardware is refreshed, but even at this frequency I think using DNS aliases is worthwhile. It means that when you have to do an unexpected server rebuild, or decide to consolidate SQL instances, there is no application impact.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 24, 2009 at 6:48 am
I've used the DNS aliases with great success.
Why I like them:
1. As already stated, you can move your DB without changing any application.
2. Absolutely use it with test databases. Since these tend to get moved/cloned more often than a production database (especially when virtualized), administration of test systems is just that much easier.
3. More people on staff know how to work with DNS than know how to change server names & IPs, so it's easier to find someone to make a DNS change when the main server admin is on vacation (or at night/weekend).
4. For documentation, project plans, and task assignments, it's easier and better understood to say "change DNS entry from x to y" than it is to write about the details of server name and IP changes.
February 24, 2009 at 6:59 am
Thanks again .. that makes perfect sense ....
P S - how "Anon" is Anon?
February 24, 2009 at 12:10 pm
I'm with Ed Vassie - in the past I have used nothing but FQDNs and aliases for SQL Servers , application databases and web and application servers. This allowed us to completely revamp the IP address structure and range, rebuild/copy/rename servers (application, web and SQL) without any impact to user applications since all connections were made using DNS aliases !
The only thing you have to remember is that there is local DNS caching that occurs. usually an IPCONFIG /FLUSHDNS does the trick on the clients after a DNS change on a server. On the server you's need IPCONFIG /FLUSHDNS & IPCONFIG /REGISTERDNA first !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
February 25, 2009 at 5:34 am
One potential issue with DNS aliases is getting them changed. At my old place we had servers in GB and US (with no clustering), and wanted the applications to fail over from GB to US (or v-v) in a problem situation. Although all we needed to do to get the application connections working was to point the DNS alias at the required server address, company politics made this a bit of a struggle.
Initially our Windows people wanted to keep control of changing the aliases. This meant we had to raise a ticket and wait until they did the change. We had a 15-minute target to get everything (i.e. everything!) up and running at the other side of the Atlantic from the time management said start. Getting the DNS aliases changed sometimes took 20 minutes or more of this 15-minute window.
We then got approval to apply a SLA to the DNS alias change process. Changing the aliases was one step out of about 40 that needed to be done to complete the failover, and we gave the Windows admin people 2 minutes from the time we picked up the phone to ask for the change until they confirmed it was complete, DNS caches had been flushed, and the aliases were useable at the new location.
Within a week or two the DBAs were given all the Windows authorities needed to change the aliaes that applied to SQL Server. We wrote scripts to change the Alias and flush the DNS cache. In the end this whole process took maybe a minute to complete, with most of the time waiting for the DNS cache flush to complete.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 25, 2009 at 6:12 am
Nice to know again ...
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply