SQLSERVER mirroring VS Log shipping

Question

Hello, I need some help understanding mirroring for the follwoing example.

primary server in FL Mirror server in germany

My application is doing an insert into the table for the FL system

CASE 1 : The mirror server is down -- network issue -- I assume that the insert will be written to the Transaction log on the principal -- It will not be commited to disk What will happen if someone tries to query the FL database.Will they see the last transaction[insert]? When SQL server perform a query, does it look at both the DB and tlog?.

CASE 2: If mirror server is down for 2 days. Then I guess the transation log will continue to grown. Can you explain how this will affect the response time of the appplication

CASE 3 : If mirror is down for while (week). Is it better to break the mirroring. Also, would this means that I have take a full backup of the DB again in order to reconfigure mirroring

Answer 1

You haven't specified what kind of mirroring, so i will assume high safety with automatic failover

CASE 1 : Principal will be in a 'disconnected' state. Transactions will be committed to disk on the principal, but not the mirror (obviously). The transactions will remain in the 'active' part of the log and will not be backed up. i.e. You will see your transaction log grow and the *log_reuse_wait_desc* column in sys.databases will be MIRRORING. The FL database will remain offline and be in a disconnected state. You will not be able to query it unless you use something like FORCE_SERVICE_ALLOW_DATA_LOSS to bring it online at which point you've broken your mirror (although the principal doesn't know it yet & will continue to hold logs)

CASE 2: The transaction log will keep growing in accordance with your autogrow settings. This is the usual case with autogrowing logs, you will have some overhead everytime you get an autogrow and potentially end up with lots of virtual log files. Probably best to set autogrow to something reasonable so it's not growing in 50MB increments.

CASE 3: It depends on how much data change you've had changed compared to the size of a full database backup that will need to be copied between sites to re-init mirroring. In SQL Server 2008 you have options like log compression which means you can stuff more transaction down the wire with less bandwidth (if you're using it)