Msg 11004; Mirroring online database migration 2005-2008 R2, witness artifact

Question

Good Morning all,

Recently performed an online migration form SQL Server 2005 to SQL Server 2008 R2 in a multi-instance environment via witnessed mirroring (three server solution).   Because mirroring isn’t backward compatible, one can go from 2005 to 2008 but not from 2008 to 2005, the fail over sequence was manhandled to protect the 2005 database as a fall back (as it turned out unnecessary fallback).  Method employed was to stopped the 2005 SQL Service instance, forcing a fail over.  With the 2008 instance(s) now principal, completion was to set the partner off (e.g. http://technet.microsoft.com/en-us/library/ms189112.aspx).

The glitch is the witness still has references to each of the database.  Meaning, the How to Remove Database Mirroring document might have missed an important precursor step, to set the witness off after fail over and before setting the partner off (I added a note to the above document).

The witness retains pointers to the 2005 instance databases, whose server has since been re-provisioned resulting in DNS errors on the witness server:

• Database mirroring connection error 2 'DNS lookup failed with error: '11004(The requested name is valid, but no data of the requested type was found.)'.' for 'TCP://Server.Domain.local:port'.

My search for a way to clean up the witness instance has been fruitless (there are only so many times one can read setting the partner off terminates mirroring before they give up the hunt).  Being one to cut my losses I’ll likely re-build the witness instance, but, I reasoned, surely there must be a mechanism to clean up the witness?  Also, what about the next poor sole who buys into the ending mirroring by setting partner off only step and who might not want to re-build the witness instance (community service)?

Thoughts, suggestions are always appreciated.

Wishing everyone a fantastic New Year.

 

Answer 1

Sequence should be

1. first you must upgrade witness server instance

2. Upgrade mirroring server to 2008 and make failover

3. Upgrade old Principal server to 2008

Then it should work as normal - 

---

http://uk.linkedin.com/in/ramjaddu

Answer 2

Peja, happy new year.  Thank you for the response but it did not answer the question. 

The question simplified: how to remove the artifact from the witness?

 

#1, the witness was/is already a 2008 R2, e.g. no upgrade (I did not specify the version of the witness, sorry).

#3, the old server's name no longer exists BUT the witness retains reference too it (hence the DNS errors).

 

Unfortunately not knowing one needed first clip the reference to the witness (set witness off) before the partner (set partner off) appear to have denied me the ability to inform the witness that it is no longer needed for the mirroring configuration.

Put it another way; I suspect this isn't proper behavior for the witness as once the partner setting on the principal has been set to off the witness is essentially no longer applicable (witness too what?).  Perhaps not being able to communicate with the former principal has influenced the witness's retention of it, but this still isn't proper as the witness was witness to the instruction of setting the partner off on the new principal (e.g. terminating mirroring).

 

Answer 3

Update, the following did not resolve the glitch either (e.g. re-build the instance is the only plausible fix, added a bug related to the issue):

Had thought because the original principal host is no longer available a solution would be to fake it.  To do so created an entry in the HOSTS file of the witness host server (could have done this with DNS but did not want the rest of the enterprise to have the reference point).  The entry needs to point to a host with SQL instance that one could freely create and destroy endpoints on (refereed to here as Fake01).

On the Fake01 instance systematically create an end point to match the original principal.  To determine the ports needed employed the following query on the witness:

 

select principal_server_name

  from master.sys.database_mirroring_witnesses

 GROUP BY principal_server_name;

 

Suppose the first end point was port 5053, simply created an endpoint for database mirroring on Fake01 employing the same security measures that where originally employed (for example the certificate) on that port.

Ideally the witness will correct its self as it communicates with Fake01 and agrees the database does not exist, hence witnessing is no longer needed.  The reason I suspect this did not work is instance (Fake01) does not contain a reference point for the mirroring configuration (mirroring_GUID/Family_GUID) hence when the Witness connects to the port it cannot confirm or deny the configuration.

Conclusion; giving up as it appears there is no orderly way of removing artifacts from the witness.  The solution employed is to remove the instance and re-build it (the most disruptive cleanup method).  This was not a desirable outcome in a production environment...

Hope this helps others who stumble upon this feature...

PS, noted another with similar issue:

http://social.msdn.microsoft.com/Forums/is/sqldatabasemirroring/thread/f3088009-4e2e-4695-b2ad-5c4a2cc55f69