patternsqlMinor
Database mirroring, "WITH NORECOVERY"
Viewed 0 times
databasenorecoverywithmirroring
Problem
Recently I have started to implement database mirroring and faced with some trouble. According to Books online, full database backup and
The mirror database must be in the RESTORING state for mirroring to
work. When preparing a mirror database, you must use RESTORE WITH
NORECOVERY for every restore operation. Minimally, you will need to
restore WITH NORECOVERY a full backup of the principal database,
followed by all subsequent log backups.
But when I start mirroring, I receive following exception:
"Database "MyDB" cannot be opened. It is on the middle of
restore(Microsoft SQL Server, Error 927)
Can anyone explain, whether I misunderstood something ?
T-Log backup must be restored in the mirror instance in WITH NORECOVERY mode:The mirror database must be in the RESTORING state for mirroring to
work. When preparing a mirror database, you must use RESTORE WITH
NORECOVERY for every restore operation. Minimally, you will need to
restore WITH NORECOVERY a full backup of the principal database,
followed by all subsequent log backups.
But when I start mirroring, I receive following exception:
"Database "MyDB" cannot be opened. It is on the middle of
restore(Microsoft SQL Server, Error 927)
Can anyone explain, whether I misunderstood something ?
Solution
Create endpoints on all servers:
For a witness server, change
Run the first
Notice that it is pointing to the principal server in the command, but you are running it on the mirror.
Then run the next two commands on the principal server, pointing the
If you are setting up a witness for automatic failovers, use
CREATE ENDPOINT EndPointName
STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED ALGORITHM RC4)For a witness server, change
ROLE = PARTNER to ROLE = WITNESS in the witness's endpoint.Run the first
SET PARTNER command on the mirrored server:ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:PortNumber'Notice that it is pointing to the principal server in the command, but you are running it on the mirror.
Then run the next two commands on the principal server, pointing the
SET PARTNER at the mirror:ALTER DATABASE DatabaseName SET PARTNER = N'TCP://MirrorServer:PortNumber'
ALTER DATABASE DatabaseName SET SAFETY OFF --OFF=async, FULL=syncIf you are setting up a witness for automatic failovers, use
SET SAFETY FULL above instead of SET SAFETY OFF and then run this command on the principal:ALTER DATABASE DatabaseName SET WITNESS = N'TCP://WitnessServer:PortNumber'Code Snippets
CREATE ENDPOINT EndPointName
STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED ALGORITHM RC4)ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:PortNumber'ALTER DATABASE DatabaseName SET PARTNER = N'TCP://MirrorServer:PortNumber'
ALTER DATABASE DatabaseName SET SAFETY OFF --OFF=async, FULL=syncALTER DATABASE DatabaseName SET WITNESS = N'TCP://WitnessServer:PortNumber'Context
StackExchange Database Administrators Q#148668, answer score: 4
Revisions (0)
No revisions yet.