February 11, 2014 at 5:17 pm
Hi All,
I have few questions regarding Database mirroring.
I am just comparing log shipping and database mirroring. In log shipping, everything is handled via sql agent jobs i.e. "LS_" jobs which takes backup, copy backup and restore it on secondary server. This is what happens in case of log shipping.
On the other hand, in Database Mirroring, the log records or log stream is sent to the Mirror server and thus maintaining a hot standby database for Principal database.
I have below questions regarding database mirroring.
1) Who is responsible for moving those transaction log records from Principal database to mirror database? I mean, who or which thread is going to read those log records on Principal and move to Mirror database ? I just know the fact that communication happens via endpoints but who is responsible for reading and transferring those log records to the partner server?
2) Secondly, why can't I have the database which is participating in database mirroring as SIMPLE recovery model? In Log shipping we take transaction log backups and there is a reason of why we need to put the database in FULL recovery model but if I consider DB mirroring, sql server is reading the transaction log directly and if that is the case why can't I just have database in SIMPLE recovery model? Why should I need the database to be in Full recovery model for database mirroring?
3) Thirdly, on the Mirror side, which thread / who is responsible for constant replaying those log records/changes to the Mirrored database? In log shipping I know for the fact, constant restore of log backups does the replay on the Secondary server but in case of database mirroring , who does this ?
4) Finally, Can I have a different database name for my Mirror database? Are there any known implications doing so?
Thanks in Advance.
February 12, 2014 at 1:38 am
Oracle_91 (2/11/2014)
2) Secondly, why can't I have the database which is participating in database mirroring as SIMPLE recovery model? In Log shipping we take transaction log backups and there is a reason of why we need to put the database in FULL recovery model but if I consider DB mirroring, sql server is reading the transaction log directly and if that is the case why can't I just have database in SIMPLE recovery model? Why should I need the database to be in Full recovery model for database mirroring?
Because only in full recovery model are all operations fully logged. Minimally logged operations, since they don't log redo information, don't put enough into the log to allow the operation to be replayed on the mirror
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2014 at 1:54 am
Got it. Thank you very much Gail.
Any comments on questions 1 & 3? I think I have asked the repeated question. I was reading the database mirroring yesterday and these are some questions which made to ask my self.
Appreciate if anyone can provide there insight on this.
Thank you.
February 12, 2014 at 5:30 am
Oracle_91 (2/11/2014)
1) Who is responsible for moving those transaction log records from Principal database to mirror database? I mean, who or which thread is going to read those log records on Principal and move to Mirror database ? I just know the fact that communication happens via endpoints but who is responsible for reading and transferring those log records to the partner server?
This link details the following
Database mirroring session operation
Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible.
Oracle_91 (2/11/2014)
3) Thirdly, on the Mirror side, which thread / who is responsible for constant replaying those log records/changes to the Mirrored database? In log shipping I know for the fact, constant restore of log backups does the replay on the Secondary server but in case of database mirroring , who does this ?
See above!
Oracle_91 (2/11/2014)
4) Finally, Can I have a different database name for my Mirror database? Are there any known implications doing so?Thanks in Advance.
This link details the following, so no, you can't use a different name!
Database mirroring Pre reqs and Restrictions
•When you are creating the mirror database on the mirror server, make sure that you restore the backup of the principal database specifying the same database name WITH NORECOVERY.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 12, 2014 at 5:51 am
Thank you Perry.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply