February 16, 2010 at 12:06 pm
For this post let's assume I have 2 servers: Server A and Server B
Server A will have 3 Databases: DB1, DB2, DB3
Server B will have 3 Databases: DB4, DB5, DB6
What the big whigs are requesting is that Server A mirror to Server B and vice versa. This means making each server a Mirror and a Principal. I can't find anywhere where it specifically can't do it, but there is just something nagging at me that says this isn't a good idea?
Thanks in advance!!!
February 16, 2010 at 12:12 pm
SuzDBA (2/16/2010)
For this post let's assume I have 2 servers: Server A and Server BServer A will have 3 Databases: DB1, DB2, DB3
Server B will have 3 Databases: DB4, DB5, DB6
What the big whigs are requesting is that Server A mirror to Server B and vice versa. This means making each server a Mirror and a Principal. I can't find anywhere where it specifically can't do it, but there is just something nagging at me that says this isn't a good idea?
Thanks in advance!!!
Mirroring is at the database level, not the server level. To accomplish what you are trying to do, you would need all six databases on each server, mirror them appropriately, then set then make DB1, DB2, DB3 on Server A the principal databases for those db's, and DB4, DB5, and DB6 on Server B the principal databases for those db's.
Question, do the databases "talk" to each other? (ie use dstributed transactions?)
February 16, 2010 at 12:59 pm
what are you trying to achieve here? Why do you need databases on both servers as principal databases?
EnjoY!
February 16, 2010 at 1:16 pm
GT-897544 (2/16/2010)
what are you trying to achieve here? Why do you need both servers as principal?EnjoY!
Servers are not principal, databases are. Database Mirroring is at the DATABASE level, not the SERVER level.
February 16, 2010 at 1:20 pm
Lynn Pettis (2/16/2010)
GT-897544 (2/16/2010)
what are you trying to achieve here? Why do you need both servers as principal?EnjoY!
Servers are not principal, databases are. Database Mirroring is at the DATABASE level, not the SERVER level.
Typo I agree!:-)
February 16, 2010 at 1:51 pm
Is clustering not an option for you? that is at instance level.
Gethyn Elliswww.gethynellis.com
February 16, 2010 at 2:20 pm
SuzDBA (2/16/2010)
For this post let's assume I have 2 servers: Server A and Server BServer A will have 3 Databases: DB1, DB2, DB3
Server B will have 3 Databases: DB4, DB5, DB6
What the big whigs are requesting is that Server A mirror to Server B and vice versa. This means making each server a Mirror and a Principal. I can't find anywhere where it specifically can't do it, but there is just something nagging at me that says this isn't a good idea?
Thanks in advance!!!
You can do what's being asked for, but most likely there's a better solution. What business problem is this meant to prevent/resolve?
- 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 16, 2010 at 2:25 pm
A good first question to ask, then, are Server A and Server B identical; same configuration, memory, disks, etc?
February 16, 2010 at 2:49 pm
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.
February 16, 2010 at 2:54 pm
If you have the kit, then a two node multi-instance (Active/Active) cluster could be the way to go, That said each individual node needs to be able to run both instances in the event of a failover.
Gethyn Elliswww.gethynellis.com
February 17, 2010 at 5:54 am
we used to do this on SQL2000 logshipping pairs (would also work with SQL2005 mirroring). Rather than an active\passive setup with one server being purely for failover we had some databases active on server A and some active on server B, logshipping to their respective partners.
We did this a) to spread the load B) so we did not have servers sitting there idle.
Its a little harder to keep server level objects in synch but perfectly doable. One thing to note in active/passive mode you only have to license one server, if both support live databases both need licensing.
---------------------------------------------------------------------
February 17, 2010 at 7:23 am
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?
- 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 7:38 am
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.
February 17, 2010 at 7:39 am
I dont think that mirroring will solve the performance issues as all the transactions need to be moved to the mirror anyway.
You would be better splitting the databases between the servers without the mirroring,
or doing some analysis to see what is causing the performance issues as new hardware is usually the last solution to take.
February 17, 2010 at 8:04 am
if you are logshipping you get i/o spikes as you load the logs, particularly write times on the drive holding the log files. Outside of those times logshipping has no affect.
The thing to do is spread out when the logs are restored and possibly only restore outside core hours. Perfectly acceptable as a DR solution as long as the logs are copied across and readied to be loaded.
With mirroring not quite the same. I will see if I can get the stats on a mirrored pair but I expect the load will be lower as all the mirror is doing is replaying transactions, it doesn't have to do the i/o or cpu to derive the data that goes into the final transaction. i.e. if a one row update required a table scan to get the info for that update, the table scan activity is not reflected on the mirror?
And of course selects are not written to the log and most databases are weighted toward read activity so all that load is not on the mirror.
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply