March 20, 2009 at 8:54 am
We are planning our migration from 2000,2005 - 2008 this year. (wish me look LOL)
We need to provide DR so will be placing both servers in different locations and will be using Mirroing as our prefered failover.
Instead of having a meaty server with 4x4way processors and 32gig Ram sitting in standby waiting for failover, Is they anything preventing us from using each server in an Active active state simlar to clustering.
Example
Server 1.....................................Server 2
SQL databse 1 (principle)...............SQL databse 1 (Mirror)
SQL databse 2 (Mirror)...................SQL databse 2 (principle)
SQL databse 3 (principle)................SQL databse 3 (Mirror)
SQL databse 4 (Mirror)...................SQL databse 4 (principle)
This way they both share the workload and both still offer failover in a DR solution.
Any thoughts, Can this work or not? what are the downsides?
Due to the 2 geographic locations, And budget we have ruled out clustering.
We have a fast link between sites and will be using fast SAN storage
March 20, 2009 at 1:21 pm
mirroring is at the database level so yes an instance can be principal for some databases and mirror for others. I do a simialar thing but with log shipping.
seems to me you make better use of your hardware this way, of course it does mean extra cost that both servers must be fully licensed as active servers.
slight complication when applying patches cannot use rollout technique to minimise downtime, mirroring will have to pause for a while.
---------------------------------------------------------------------
March 20, 2009 at 2:00 pm
Hey thanks for that George,
I did forget about the extra license issues but making full use of both the servers will be worth it in the long run.
Does any one else have this kind of setup?
how does it perform for you?
Is their an official name for this type of Active mirroring?
March 22, 2009 at 5:10 pm
The other thing you should note is since your secondary server will be at a distance from your main data-center if you make that server active for any database the travel time for queries increases and you will notice performance impact.
Second point, when you are using Mirroring with two servers in different location in high availability the servers will have a lag time in commit transaction unless you use high protection...
High Availability Mode:
User submits a transaction ...
Server A (Principle) writes it to log..
Server A sends it to Server B...
Server B writes it to log..
Server B hardens it (commit) .. sends okay back to Server A...
Server A (commit) .. sends message back to User saying all good.
So if distance travel between two node is high latency then you'll have to take that into account...
High protection mode, Server A will not wait from a response from the Server B if it was successful or not. But then you have a possibility of data lose.
I run mirroring with over 80 databases from single server without issues (other then *wonderful* 3rd party vendor code) .. it runs very nicely :).
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 23, 2009 at 4:37 am
Mohit, you might have your terminology non-standard. see http://msdn.microsoft.com/en-us/library/ms191456.aspx
Mohit (3/22/2009)
The other thing you should note is since your secondary server will be at a distance from your main data-center if you make that server active for any database the travel time for queries increases and you will notice performance impact.Second point, when you are using Mirroring with two servers in different location in high availability the servers will have a lag time in commit transaction unless you use high protection..
I presume you mean safety off (asynchronous) or high performance mode?
High Availability Mode:
User submits a transaction ...
Server A (Principle) writes it to log..
Server A sends it to Server B...
Server B writes it to log..
Server B hardens it (commit) .. sends okay back to Server A...
Server A (commit) .. sends message back to User saying all good.
this is safety on (synchronous) or high-safety mode
High protection mode, Server A will not wait from a response from the Server B if it was successful or not. But then you have a possibility of data lose.
again this is high performance mode
cheers
george
---------------------------------------------------------------------
March 23, 2009 at 11:16 am
Urg. Correct; sorry must have been running low on caffeine. Sorry for the mix-up ..
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply