October 7, 2011 at 5:26 pm
We have production and standby servers. The production and standby servers are both clustered servers. They both have different SQL Network Cluster names; however, the instance name is the same. What options are available when users are complaining that they have to change their application or odbc connections, etc to use the standby SQL Network name during a failover? If they have 100 workstations with an ODBC connection defined on it to the production instance, what is a good way to get the workstations to connecto to a standby server with minimal effort.
We changed DNS the other day for this type of thing thinking this would work. The user said that the production SQL Network name was all over the place within the application. He wanted us to change DNS instead, so we did. His application ended up not being able to connect to the standby databases and windows accounts couldn't login to the SQL instance either. However, I could login with the SA account. I took the SQL cluster offline and back online thinking maybe it was confused. That didn't work either. Then the SQL cluster instance wouldn't come up. We ended up backing out of everything and changing DNS back. Then SQL clusters instances came up.
In DNS they changed the Prod SQL Network IP and Name to be the Standby SQL network IP and Name, and they changed the Standby SQL network IP and Name to be the Prod SQL Network IP and Name.
Thanks everyone for your help.
Patti
October 7, 2011 at 9:13 pm
I think you are looking at the right path with DNS. I'd suggest changing to using DNS Aliases (CNAMEs) as mentioned in this blog post by Allen Kinsel, http://www.allenkinsel.com/archive/2010/01/using-aliases-in-sql-server/. You might also want to look into mirroring and coding the applications to support mirroring.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 8, 2011 at 6:46 am
Think it's not a real cluster when you have different network names. What was the reason for using different network names for SQL resources? You can install a new sql server resource with the same network name of the primary production server.
October 8, 2011 at 6:55 am
parissa_bakhshi (10/8/2011)
Think it's not a real cluster when you have different network names. What was the reason for using different network names for SQL resources? You can install a new sql server resource with the same network name of the primary production server.
I think we misunderstood the original post. It is not 2 servers in a cluster with 2 network names it is 2 clusters in different locations, so they have to have different names to have them online at the same time. The issue is how to fail over from one cluster to another cluster with minimum service interruption.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 8, 2011 at 7:08 am
Oh, so if this is the problem, I don't think using CNAME solves the problem. To find the problem , let's try step by step. when you changed the names, make sure that the name resolves properly. For this purpose, after changing records in DNS server, on your developer's PC do this in command prompt:
ping "your ActiveServerNetworkName"
It should show you the IP of Active node. let's have the result, then we'll continue.
October 8, 2011 at 7:37 am
parissa_bakhshi (10/8/2011)
Think it's not a real cluster when you have different network names. What was the reason for using different network names for SQL resources? You can install a new sql server resource with the same network name of the primary production server.
Network names are unique in the windows domain!
2 instances of SQL Server whether in the same Windows cluster or not must have unique network names. Only if they are in separate domains is this possible
Use a CNAME DNS record and not an A (host) record to alias the virtual network name
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 9, 2011 at 5:51 am
Hey all, I just got a chance to see your replies and thanks for taking a look.
We do have 2 domains. One domain is in one state and the other is in a another state. One SQL Network name ex: SITESQLAPP and the other is XSITESQLAPP. Both have the same Instance: SITESQL\SQLInstance and XSITESQL\SQLInstance. Each state has their own network and domain.
I do have mirrroring configured as well. With the issue we had last week, we did find out that the application has ADO .NET connections so we are looking to see if we can modify that connection string to have both SQL Network names and instances included. However, we have many other applications that don't with the same SQL setup. We have large IT buildings in both of these states where we keep all of out equipment with the intention of using them as a production and standby in case of a disaster.
October 10, 2011 at 7:14 am
As I know , for GEO clustering, you should use VLANs between sites, you can do this using site-to-site VPN. In this way you have a single domain between sites. Then no problem. What Microsoft recommends is this. But may be you can find tricks to overcome such problems.
My recommendation is that for disaster recovery, do not use database mirroring or clustering, because mirroring have limitations and to have a standard cluster you must pay a lot! The recommended way is replication or log shipping.
No come to CNAME. CNAME is a pointer to A record. So in either way, if you use CNAME or A recore, you shall change the orriginal A record IP address.
October 10, 2011 at 7:34 am
parissa_bakhshi (10/10/2011)
So in either way, if you use CNAME or A recore, you shall change the orriginal A record IP address.
this is incorrect.
If i have a DNS host (A) record for
MyHugeServer.WhackingGreatDomain.com 10.10.10.10
And i now create a CNAME record of
ItsNotHugeReally.WhackingGreatDomain.com
aliasing
MyHugeServer.WhackingGreatDomain.com
The CNAME record does not affect the IP address assigned to the host (A) record.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 10, 2011 at 7:43 am
Hey Perry, since the IP address doesn't change does that mean I would have to reconfiure mirroring and anything else that I have configured that uses the host name to then use the IP address instead, and all the users would modify their ODBC connections on their workstations to use the alias name?
October 10, 2011 at 8:00 am
I would choose DNS aliases too.
We use them without any problems for our production environment to have an opportunity to migrate databases between servers without the need of client's reconfiguration. There will be no problems to use them for fail-over switch.
Perhaps you will need to flush client's ipconfig cache to make things work after failover. But this is the thing that can be solved without major problems.
October 10, 2011 at 8:31 am
Abrukovsky, Did you use them on clusters?
October 10, 2011 at 10:14 am
Patti Johnson (10/10/2011)
Abrukovsky, Did you use them on clusters?
No, but I don't see any problems here. If we used clusters we would probably use aliases as well.
October 10, 2011 at 10:20 am
Patti Johnson (10/10/2011)
Hey Perry, since the IP address doesn't change does that mean I would have to reconfiure mirroring and anything else that I have configured that uses the host name to then use the IP address instead, and all the users would modify their ODBC connections on their workstations to use the alias name?
mirroring would use the real fully qualified computer names. The alias would be used purely for the app to redirect to the correct location
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 10, 2011 at 10:31 am
Okay guys...where are the aliases define? In SQL Server or in DNS? This is where I am getting confused. I've been searching everything, reading everything I can find. I just don't know for sure with my situation.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply