How can I tell that SQL Server mirroring is actually working?

Rob Rudloff
Rob Rudloff used Ask the Experts™
on
Hi.  We have two databases mirrored on identical servers.  This is not set up for "automatic" failover.  There is no witness server.
When we set up mirroring, everything seemed to work -- no indication that the setup of mirroring failed, and as you can see in the attached image, everything seems to be OK.
Is there any way to tell if the mirroring is actually working?  
The reason I ask is that we set it up once, everything worked fine.  We set it up again (after a failover test) and it seemed to setup OK, but the mirror was not getting any data, and the log file on the primary server kept growing (I assume it was waiting for the mirror to grab the transactions).
-- thanks
clip-image002.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Did you try looking at the database mirror monitoring..
Rob RudloffIT Development Specialist

Author

Commented:
Yes, I did indeed look at Monitor, but even then, it says that the mirroring is working, but the "history" only shows that "0 KB" was "unsent".  

I was hoping I could see something like "100 KB" was "sent".   Even with the Mirror Monitor, I can see that X amount of data was sent and received.  If a user creates a new record, I really can't see anything happen on monitor.

I did disable the mirror server, and saw that there was a history record of "1380 KB unsent", and then the next record was "0 KB unsent", so I guess that's proof that mirroring is working?


mm2.jpg
mm1.jpg
One More way to check is Create a database snapshot on top of the mirrored database and see for the records...in the database
Rob RudloffIT Development Specialist

Author

Commented:
I tried to do a snapshot and got "Database Snapshot is not supported on Standard Edition" ... oh well ...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial