What all things are mirrored in Data mirroring High protection mode? ex. table structre, login, updated data???

Question

I wanted to know, what all things are mirrored in data mirroring in sql server 2005?

like 1. data updated in primary servers database

     2. data inserted in primary servers database

 

Answer 1

Normally, while a database participated in Database Mirroring session, it sends all the transactions from the principal database to mirror database. Thus, it include all the data changes.

However, while operating in High Protection Mode, both the Principal and Mirror database may not in complete sync with each other, so a switch over may causes data loss. What it means, when the transaction is written to the t-log file of principal database, the same will be sent to mirror database log as well; however, since its operating in high protection mode, the principal database does not wait for an acknowledgement from the mirror database. So we can not say, whether the transaction is harden to the t-log of the mirror database.

Hope, this may help.

---

SKG: Please Marked as Answered, if it resolves your issue.

Answer 2

HI Vaibhav,

  All DML (Data change) Operation's (Insert, delete and Update) will be sent from Principal Db to Mirrored Db in Mirroring, by extracting Phyiscal log (Only DML operation, not other like select) from Principal Db and sending to Mirrored Db via endpoints in secured manner.

 

Note: Required login's and users for operating application or User at mirrored server needs to created mannaully even (fixing mismatch user) .

Answer 3

Normally, while a database participated in Database Mirroring session, it sends all the transactions from the principal database to mirror database. Thus, it include all the data changes.

However, while operating in High Protection Mode, both the Principal and Mirror database may not in complete sync with each other, so a switch over may causes data loss. What it means, when the transaction is written to the t-log file of principal database, the same will be sent to mirror database log as well; however, since its operating in high protection mode, the principal database does not wait for an acknowledgement from the mirror database. So we can not say, whether the transaction is harden to the t-log of the mirror database.

Hope, this may help.

 

---

 

SKG: Please Marked as Answered, if it resolves your issue.

Hi Sudeepta,

As you mentioned that "since its operating in high protection mode, the principal database does not wait for an acknowledgment from the mirror database. So we can not say, whether the transaction is harden to the t-log of the mirror database." I am not agree with this. What you are trying to say is possible in High-Performance Mode of Database Mirroring. In high protection mode always the principal database will have to get acknowledgment from mirror database.

 

Answer 4

Hello Sanjeev,

Yes, I was talking about High Performance Mode. Sorry, by mistake, I put is as High protection mode...

Thanks for pointing out.

---

SKG: Please Marked as Answered, if it resolves your issue.

Answer 5

thanks everybody....

actually I wanted to know whether

1. if a new table is created on Primary will be mirrored?

(means DDL statements are inserted in Transaction log..are they sent for mirroring or not?)

Answer 6

Hi Vaibhav,

   Yes, New table will be created in the mirror, even if u create new file (seconadry data files) also it will be created.

Answer 7

Thanks Sudhakar...

So everything that is inserted in Transaction Log will be mirrored...

means...

1. DDL statements

2. DML statements

2. DCL statements

all....

am I right???