Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dowit
Starting Member
2 Posts |
 Posted - 2008-06-06 : 10:37:40
|
| Hi everyone,A relatively simple question but I have no been able to find an answer.We have a SQL Server mirror Active/passive with High availability. So the usual Witness with failover.However, we are using the Mirror server for reporting and letting the users query without limit. We sometimes have problems (slowness) with the Principle server and I'm wondering the following.If the mirror server in high availability mode is maxed out will this affect the principle server because it takes longer to commit the data on both servers?It seems obvious but I would like a clear response.Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-06 : 12:41:40
|
| That's why we use asynchronous database mirroring. With synchronous database mirroring, you've got a two-phase commit.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-06 : 13:37:18
|
| How can you use Mirror server for Reporting purposes? It is in Norecovery mode. You must have created Database snapshot but that is performance overhead.Yes we have tested and proved that Database Mirroring with automatic failover is not good for High OLTP large database Because as Tara said it is two-phase commit.You can use Transactional Replication for Reporting which is best. |
|
|
|
dowit
Starting Member
2 Posts |
Posted - 2008-06-07 : 02:49:43
|
| tkizer: management was scared to go high performance (losing the two phase commit) due to the potential loss of data in a failover scenario. We're thinking of going with an active/active cluster to get the best of all worlds.sodeep: Correct, we have a snapshot pointing to the mirror in non recovery mode. Our principle server seems to be suffering due to the crazy queries we are getting on the snapshot on the mirror. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-07 : 09:53:28
|
| For high OLTP environment,you should go for Clustering and Asynchronous Database Mirroring for offsite Disaster recovery. You should use transaction replication for reporting purposes. We tried with Database Snapshots but didn't get benefit in Reporting purposes. |
|
|
|
Donald Murphy
Starting Member
5 Posts |
Posted - 2008-07-24 : 12:12:35
|
| dowit,DB snapshots only hold the changes made since the snapshot was created. So any queries that use data that hasn't been changed reference the original database. So, unless there are a lot of changes, you are still going to see a performance hit on your production database.Also, Active/Active clustering isn't going to give you the same redundancy as mirroring if that is what you are implying. Maybe I'm misunderstanding what you mean by "the best of both worlds".Do the users creating the reporting queries need realtime access to the data? Or, can they use data that is a little stale? (like 1 day old) |
|
|
|
|
|
|
Mirror busy makes Principle slower?