Having same server name for principal and mirror server, does it work?

Question

Having same server name for principal and mirror server, does it work?

winston Smith

SSCoach

Points: 19497
More actions
April 23, 2014 at 5:21 am

#284805

I have a mirrored database from server SRV-SQL01 (principal) to SRV-SQL02(mirror).
Users/Developers access SRV-SQL01 via a DNS alias called "App-SQL".
Developers have started using synonyms, meaning to access a table they use the synonym [App-SQL].[dbName].[schema].[tablebame]
This doesn't work though as @@servername returns SRV-SQL01, and not App-SQL (which is the DNS Alias).
A request came in to rename the server using:
sp_dropserver <old_name\instancename>;
GO
sp_addserver <new_name\instancename>, local;[/I]
My problem is that if I do that on the principal, I will have to do it on the mirror to ensure everything works in the event of a failover, and I don't know if I can establish a mirror between two sql servers who have the same name internally. I can always use the actual server name but I wanted to check to see if there are any complications?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 1

Don't think that will work. The instances involved in mirroring need to be able to tell each other apart. Plus if you use a monitoring server how would it tell the difference between the principal and the mirror? Additionally if apps connection strings are setup to support mirroring I would imagine they would get confused as well.

Joie Andrew
"Since 1982"

winston Smith SSCoach Points: 19497 More actions · Answer 2

Joie Andrew (4/23/2014)
Don't think that will work. The instances involved in mirroring need to be able to tell each other apart. Plus if you use a monitoring server how would it tell the difference between the principal and the mirror? Additionally if apps connection strings are setup to support mirroring I would imagine they would get confused as well.

I was going to try setting up mirroring against the physical servername. I think i really need to test this out ( im just having trouble procuring an environment at the mo)

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

It won't work. The SQL Server instance will have the Windows host name (outside of a cluster), even with a named instance. Rules of Windows hosts mean no two duplicate names on the network.

Your developers should not use @@servername to check things. They should be working cross database only, with 3 part, not 4 part, names.

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 4

Your developers should not use @@servername to check things. They should be working cross database only, with 3 part, not 4 part, names.

I agree with Steve. I have a mirror that is setup with two different machines (each with a unique name, or course) and I have a DNS entire for a "virtual" server name that our application connections to. If we need to fail over we make a DNS change and they are now connected to the new principal. Why do your developers want to use 4-part naming?

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Matt Crowley SSCarpal Tunnel Points: 4163 More actions · Answer 5

For Database Mirroring (now deprecated), there was an attribute you could add to an ADO.NET connection string for Failover Partner. This would cause the client to try to connect to the primary server, and if it was not there, try to connect to the failover partner.

In Always On, there is a similar ADO.NET connection string attribute called MultiSubnetFailover. If the Availability group fails over to a node in the same subnet, the same IP address will be re-registered with the network switches, and the applications don't know much better (although all old connections would be severed). The MultiSubnetFailover option allows a connecting application to make note of all potential IP addresses, and it can re-connect to any one of them in the event of a failover (although, again, running queries will get killed).

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 6

Not sure I would count DB Mirroring out just yet. I don't actually see it on the SQL Server 2014 deprecation list. Plus with AlwaysOn you are required to have a cluster, and most likely an AD domain. Much simpler setup with db mirroring.

Also, wasn't Log Shipping deprecated at one time and then brought out of consideration for deprecation? I thought I remember seeing that somewhere at one time, but cannot seem to find it now.

Joie Andrew
"Since 1982"

Matt Crowley SSCarpal Tunnel Points: 4163 More actions · Answer 7

i don't recall log Shipping getting deprecated then rehabilitated. i can see that happening, since you can develop a home grown Log Shipping solution with a little trouble.

The mirroring deprecation actually happened in SQL 2012 Here is the Deprecated Feature list. Database Mirroring is listed under High Availability. It could very well have been returned in SQL 2014.

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 8

Nevermind. I found it. Still deprecated.

Deprecated Database Engine Features in SQL Server 2014

Joie Andrew
"Since 1982"

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 9

Log shipping isn't deprecated, though I suppose the wizard and jobs could be at some point. However LS does something different than AlwaysOn/mirroring/other HA stuff. Even if it were, you could write scripts/jobs, or use third party products like Red Gate's SQL Backup[/url], to do this.

Disclosure: I work for Red Gate.