Prerequisites
For a mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server.
The two partners, that is the principal server and mirror server, must be running the same edition of SQL Server. The witness, if any, can run on any edition of SQL Server that supports database mirroring.
For a mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server.
The two partners, that is the principal server and mirror server, must be running the same edition of SQL Server. The witness, if any, can run on any edition of SQL Server that supports database mirroring.
The database must use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, bulk operations are always fully logged for a mirrored database. For information about recovery models, see Recovery Models (SQL Server).
Verify that the mirror server has sufficient disk space for the mirror database.
sample error if Database not Full Recovery
Database Mirroring
cannot be enabled because the database may have bulk logged changes that
have not been backed up. The last log backup on the principal must be
restored on the mirror. (Microsoft SQL Server, Error 1475)
The
mirror database has insufficient transaction log data to preserve the
log backup chain of the principal database. This may happen if a log
backup from the principal database has not been restored on the mirror
database. (Microsoft SQL Server, Error 1478)
The
remote copy of database has not been rolled forwarded to a point in time
that is encompassed in the local copy of the transactional log. .
(Microsoft SQL Server, Error 1412)
1. Additional Login on SQL Service & restart service
if you not setting on this step. you got Microsoft SQL Error 1418
The server network address "TCP://indserver.xxxx.local:5022" can not be
reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)
2. Backup Full Database on Principal Instance
3. Backup Trancaction Log Database In Principal Instance
4. copy database full & transaction to Mirror Server
5. Restore Full Database & Transaction log to Mirror Instance
6. Mirroring Without Witnes on Principal Instance
7. Testing Manual Failover
8. Manual Failover
9. Log Failover
9. Log Failover
From Server01 Proccess from Principal to Mirror
2021-10-01 11:22:27.42 spid26s This instance of SQL Server has been using a process ID of 2280 since 9/30/2021 8:17:31 PM (local) 9/30/2021 1:17:31 PM (UTC). This is an informational message only; no user action is required.
2021-10-01 11:24:13.13 spid29s Database mirroring is inactive for database 'teguhth'. This is an informational message only. No user action is required.
2021-10-01 11:24:18.47 spid29s The mirrored database "teguhth" is changing roles from "PRINCIPAL" to "MIRROR" because the mirroring session or availability group failed over due to manual failover. This is an informational message only. No user action is required.
2021-10-01 11:24:28.92 spid55 Starting up database 'teguhth'.
2021-10-01 11:24:37.41 spid55 Bypassing recovery for database 'teguhth' because it is marked as a mirror database, which cannot be recovered. This is an informational message only. No user action is required.
2021-10-01 11:24:44.81 spid29s Database mirroring is active with database 'teguhth' as the mirror copy. This is an informational message only. No user action is required.
From Server02 Proccess Mirror to Principal
2021-10-01 11:24:13.55 spid35s Database mirroring is inactive for database 'teguhth'. This is an informational message only. No user action is required.
2021-10-01 11:24:18.05 spid35s The mirrored database "teguhth" is changing roles from "MIRROR" to "PRINCIPAL" because the mirroring session or availability group failed over due to failover from partner. This is an informational message only. No user action is required.
2021-10-01 11:24:25.09 spid16s This instance of SQL Server has been using a process ID of 536 since 9/30/2021 8:21:39 PM (local) 9/30/2021 1:21:39 PM (UTC). This is an informational message only; no user action is required.
2021-10-01 11:24:33.05 spid32s Starting up database 'teguhth'.
2021-10-01 11:24:38.46 spid32s 2 transactions rolled forward in database 'teguhth' (5:0). This is an informational message only. No user action is required.
2021-10-01 11:24:39.42 spid25s 0 transactions rolled back in database 'teguhth' (5:0). This is an informational message only. No user action is required.
2021-10-01 11:24:39.42 spid25s Recovery completed for database teguhth (database ID 5) in 1 second(s) (analysis 357 ms, redo 379 ms, undo 662 ms.) This is an informational message only. No user action is required.
2021-10-01 11:24:44.33 spid35s Database mirroring is active with database 'teguhth' as the principal copy. This is an informational message only. No user action is required.
run command on Mirror Database
alter database teguhth set partner off
restore database teguhth with recovery
11. Sample error please change login to user sa / priledge to sql server
solution
No comments:
Post a Comment