Database Mirroring vs SQL Server FailOver (Performance)

Hi there.

I′m running some tests in a database with Mirroring and without Mirroring. As expected there is a performance hit using Database mirroring.

The tests i′m running are just simple functions inserting and updating the database, and then counting the number of sucedeed inserts and updates in a time interval.

My question here is: What if i use Sql Server failover mechanism?

I know that failover time will increase and management is more complex, but what can i expect in terms of performance ?

Database mirroring performance will vary depending on many factors. I assume you have been using Mirrroring in high protection mode which ensures all writes are synchronously made to both databases. This has a larger impact on throughput than the high performance asynchronous mode. The failover mechanism should not have any impact on the performance of you database workload since it's basic purpose is to re-direct the connection to the mirror database when the principal fails.

|||

I think you missunderstood me. My question isn′t very clear, sorry for that.

I′m using High Availability mode (with witness).

I want to know if, in fact, i should expect better performance when using SQL Server Failover Clustering(with shared disk array).

Thanks in advance.

|||

If you are asking if you are getting better performance (throughput) using database mirroring or Failover clustering, then the answer is Failover clustering will have better peformance.

In fact, the cluster performance should be about the same (if not exactly the same) as a stand alone server.

Failover times are another matter, though.

|||

I also was thinking that cluster performance would be the same, when comparing to a stand alone server. But i′m not very deep into this mechanism and needed an answer from someone with "know how" in the area.

Thanks for your help Mark.

|||Database mirroring is useful technology for disaster scope. You can have two failover clusters in two geographics positions and database mirroring help you data be in consistent state (sorry for my english :( )|||

To be clear, failover times for the database mirroring can be in the seconds, because the instance of SQL is up and running and constantly redoing as much it can.

For failover clustering, for a failover to happen, the instance on the new node has to start up and then run recovery on the database before it is opened. This generally takes 10s of seconds and even could take place on the order of minutes.

Thanks,

Mark

|||

Fail-over clustering will perform better than database mirroring (in synchronous mode), since the Principal has to wait for the Mirror to commit transactions. This makes you dependent on the bandwidth and latency of the network connection between the two databases, and on the disk IO on the Mirror.

DB Mirroring will fail-over much faster than fail-over clustering, so it is better for routine maintence.