Tuesday, February 3, 2015

SQL Server Database Mirroring... Error Msg 1412

I had the craziest problem happen recently.  Not that it shouldn't have happened, but the error return that I received from SQL Server did not indicate the problem very clearly at all.  I spent two days reading and researching... only to find a very obvious problem, that never should have happened.

A little background.... A customer of mine had a v2008 R2 SQL Server where 5 databases were mirrored, using Database Mirroring.  The server was old and over-used, so they bought a new server, dedicated for the mirror, high performance, no witness.  To implement, I needed to break the current mirroring session for each of the 5 databases, and build it back up on the new server.  I have listed my steps here briefly, and then provided further detail for each following the list.
  1. Turn off mirroring on PRINCIPAL
  2. Drop Endpoint on Mirror
  3. Drop Database on Mirror (Won't do this until after the new mirrored instance is defined)
  4. Create FULL backups of databases and logs, copy to MIRROR server
  5. On MIRROR restore database xxx WITH NORECOVERY
  6. On MIRROR restore database log xxx WITH NORECOVERY 
  7. Re-use endpoint on PRINCIPAL  (Not dropping/recreating it, just re-using the same endpoint)
  8. Create new endpoint on MIRROR (mimicking the PRINCIPAL endpoint) 
  9. On MIRROR, create partnership to PRINCIPAL
  10. On PRINCIPAL, create partnership to MIRROR
  11. On PRINCIPAL, set partner safety off (Thus, High Performance mode)

-- #1. Turn off mirroring on PRINCIPAL
ALTER DATABASE xxx SET PARTNER OFF;

-- #2. Drop Endpoint on MIRROR 
DROP ENDPOINT endpointname;

-- #3. Drop Database on Mirror (It's a placeholder. Do not do this until new mirror is good.)
DROP DATABASE xxx

-- #4. Create FULL backups of databases and logs (I backed up to the Mirror Server via UNC)
-- full db backup
BACKUP DATABASE xxx
TO DISK = '\\MirrorServer\E$\TEMP\xxx.bak' 
WITH FORMAT
GO
-- full log backup
BACKUP LOG xxx
TO DISK =  '\\MirrorServer\E$\TEMP\xxx_Log.bak'  
WITH FORMAT
GO                                 

-- #5. On MIRROR restore database xxx WITH NORECOVERY
RESTORE DATABASE xxx
FROM DISK 'E:\temp\xxx.bak'
WITH 
MOVE 'xxx_dat' TO 'E:\MSSQL\DATA\xxx_Data.mdf'
MOVE 'xxx_log' TO 'F:\MSSQL\Log\xxx_Log.ldf',
NORECOVERY;

-- #6. On MIRROR restore database log xxx WITH NORECOVERY
RESTORE LOG xxx
FROM DISK = 'E:\temp\xxx_Log.bak' 
WITH NORECOVERY;  

-- #7. Re-use endpoint on PRINCIPAL
CREATE ENDPOINT endpointname
AUTHORIZATION [DOMAIN\sqlservice]
STATE = STARTED
AS TCP (LISTENER_PORT= 5023, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED ALGORITHM RC4)        

-- #8. Create new endpoint on MIRROR
CREATE ENDPOINT endpointname
AUTHORIZATION [DOMAIN\sqlservice]
STATE = STARTED
AS TCP (LISTENER_PORT= 5023, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED ALGORITHM RC4)

-- #9. On MIRROR, create partnership to PRINCIPAL
ALTER DATABASE xxx SET PARTNER = N'TCP://PrincipalSrvName.DOMAIN.Local:5023' 

-- #10. On PRINCIPAL, create partnership to MIRROR
ALTER DATABASE xxx SET PARTNER = N'TCP://MirrorSrvName.DOMAIN.Local:5023'

-- #11. On PRINCIPAL, set partner safety off 
ALTER DATABASE xxx SET PARTNER SAFETY OFF;


Basically, that's it. I tested the process successfully twice in my own environment, but with two KEY differences.  First, my Principal and Mirror were two named instances on the same physical SQL Server, whereas the Customer had two physically separate SQL Servers.  Secondly, I used the MOVE statement in the RESTORE in step #5.  I used this in my own testing successfully, so I didn't think that I needed to avoid the MOVE when actually doing it in production. *WRONG*WRONG*WRONG* If I could type bells and whistles here, I would.  I used the MOVE for two reasons:  

1. Because I built a new server for the Customer, with a completely different directory path structure.
2. Because step #4 of the example in this reference does not say we 'MUST USE IDENTICAL PATHS'.  Rather, the reference says that 'IF the paths differ....'.  For that statement alone, I thought that the effort could be done with different directory paths on the Principal and Mirror.  At least in my implementation, that was definitely not the case.  

This became clear to me at step #9.  All steps completed successfully to that point, but step #9 failed immediately with this error:

Msg 1412, Level 16, State 0, Line 1
The remote copy of database "xxx" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

This is what I meant by the unclear error return.  Not just the less than intuitive return, but BOL's instructions for preparing the database(s) for Mirroring actually gives an example using the MOVE statement. It was not until several days later, and an assist from one of my SQL buddy gurus, that I realized this failure was being caused by different directory paths on the Principal vs the Mirror.  

I very humbly explained the problem to the Customer;  The Server Team rebuilt the box, and I repeated all of the above efforts WITHOUT the MOVE statement in Step #5.  The directory path structure on the Mirror mimicked the Principal, and I completed the new mirror deployment successfully.

No comments:

Post a Comment