December 13, 2006 at 10:39 am
Hi All,
Right - For some reason my boss doesn't want a cluster - but we want an SQL setup that will allow automatic failover, so I've been looking into DB Mirroring.
Has anybody got a MS CRM 3.0 system with a mirror on the back end?
We've also been looking at external load balancing hardware. So if server A is busy the request would be sent to server B - but this would need some sort of two-way mirror / replication. Is that even possible?
Any ideas? Or is clustering in fact the best solution?
Matt.
December 13, 2006 at 4:59 pm
looking at external load balancing hardware. So if server A is busy the request would be sent to server B - but this would need some sort of two-way mirror / replication. Is that even possible?
Mirroring is one way only...
You can write the data to Princepal which is mirrored to Partner...
you can't write to partner but you can read from partner.
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
For two-way best approach will be replication..
MohammedU
Microsoft SQL Server MVP
December 14, 2006 at 8:35 am
Going back to mirroring... what happens if the Witness fails?
December 14, 2006 at 8:44 am
If the principal and mirror server are still able to see each other the loss of the witness will not affect the principal server availability.
December 14, 2006 at 2:05 pm
To be able to "Read" the mirror requires creating a database snapshot of the mirror database which requires the Enterprise Edition. Not worth the cost in my mind if the db snapshots are the only thing you are getting out of it. Plus it would be red only, so all queries that perform some sort of update, delete, or insert would still have to hit the original server. I agree that merge replication sounds like the best solution for you.
December 14, 2006 at 2:23 pm
Using 2005, you could also use per-to-peer replication to keep both databases in sync.
December 14, 2006 at 10:35 pm
According to BOL in reference to P2P replication: All participating databases should contain identical schema and data
He doesn't want identical data.
December 15, 2006 at 1:41 am
The "plan" so far is: SQL 2005 DB mirroring - with a witness so automatic failver can be used. (Reporting will be done from snapshots on the mirror.
However the automatic failover only switches the SQL servers round - the main CRM application will still be looking for the principle.
So in conjunction a load balancing box programmed to switch the CRM application from looking at the principle server to looking at the mirror if the principle fails.
There must be a better way - but P2P replication looks so much more complicated to setup. Is that true?
December 16, 2006 at 4:28 pm
Using ADO.Net 2.0 is a better option than a load balancing box. ADO.Net 2.0 is mirroring aware. You can specify the mirror instance in the connection string and it will automatically switch new and active connections to the secondary connection if the mirror fails over. It will handle the switchover cleaner and quicker.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply