February 17, 2010 at 8:07 am
Lynn Pettis (2/17/2010)
GSquared (2/17/2010)
SuzDBA (2/16/2010)
The business purpose revolves around the fact that right now the application does not perform optimally and the load of all databases on 1 server would be too much. We balance the load between the two and then mirror to beteen the two.I was thinking that Principal and Mirror were at the server level so now that is not a problem.
I'm not sure that splitting it up that way will actually solve that issue. I haven't tried it, so I'm not certain on this, but if both servers have to handle all transactions fully (which is what's done in mirroring), then it's more likely to just put the full load on each. Mirroring A to B and B to A means all transactions on either one have to be done on both. I don't see that reducing the load on A (assuming that's the current primary).
Log shipping might accomplish what you're looking for, if one of the servers gets databases that have lower activity than the other server. Again, I'm not sure, but it would be a slightly lower impact because the log restores would cause periodic spikes and lulls in activity instead of a steady stream of activity (like mirroring).
Does that make sense?
That could be dependent on the ratio between reads (select) and writes (insert/update/delete). If there are more read only operations this may help.
That's true.
Would definitely need to be tested.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 17, 2010 at 11:39 pm
Mirroring, and HA/DR in general, are very advanced and complex topics. I highly recommend you get a professional in to ensure you a) understand what your objectives really are and b) configure a system that successfully meets those objectives.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 18, 2010 at 4:50 am
I went back and looked at performance stats for an active\passive server pair doing some mirroring and some logshipping.
the load on the failover is a fraction of that on the primary (10% or less)
---------------------------------------------------------------------
February 18, 2010 at 5:23 am
The problem with this scenario isn't the load that the mirrors put on the server. The problem is what happens if you have to fail over all databases to the same server? You're right back where you started with a server that can't handle the load.
But yes, you can do it, and no the load won't be the same as it was when they were all online on the same server.
February 18, 2010 at 5:30 am
Lynn Pettis (2/16/2010)
Servers are not principal, databases are. Database Mirroring is at the DATABASE level, not the SERVER level.
Actually, the terms "principal" and "mirror" are used to describe both the databases invovled in mirroring and the servers that host these databases. This has become common practice due to the fact that people rarely use a server to host databases in both roles.
How Database Mirroring Works
The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners
February 18, 2010 at 5:54 am
Regardless, the point I was trying to make was that mirroring occurs at the database level, not the server level. Clustering is done at the server level.
February 18, 2010 at 6:14 am
Robert Davis (2/18/2010)
The problem with this scenario isn't the load that the mirrors put on the server. The problem is what happens if you have to fail over all databases to the same server? You're right back where you started with a server that can't handle the load.But yes, you can do it, and no the load won't be the same as it was when they were all online on the same server.
Agreed, users\management should be made aware that performance might suffer in a DR scenario. If they accept that as quid pro quo for cheaper hardware outlay then I don't see it should preclude this setup. Hopefully you wouldn't run in DR mode for too long!
---------------------------------------------------------------------
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply