July 24, 2008 at 5:57 am
We're wanting to add a third server to our currently two way mirrored DB. This SQL3 machine will be used for reporting by clients - it's not imperative that it's up to the minute updated (twice daily is fine).
Our thoughts so far are to throw away the mirroring, changing to merge replication between SQL1 & SQL2, with both SQL1 & 2 running snapshot replication to SQL3.. :crazy:
From the testing I've done so far, merge seems quite slow compared to mirroring and I'm not 100% convinced that the two snapshots will work correctly, possibly with one overwriting the changes the other has made, due to the speed of the merge repl. :unsure:
Do you have any thoughts on what we're trying to achieve? I'm going to try testing the setup with SQL1 & 2 mirrored still, with both snapshotting to SQL3 with it as the distributor, which MS seems to say is possible, but we'll see 🙂
July 24, 2008 at 1:20 pm
Is SQL3 just reading data, no updates?
If so, I wouldn't use Merge (as you say, there's alot of overhead, the Merge advantage from my experience is managing changes done on both sides of the replication).
I'd use Transactional replication, which is much quicker, to get data to another server.
July 24, 2008 at 2:09 pm
Well, SQL1 & 2 need to be kept up to date, back and forth, but yes, SQL3 looks best to receive it's (one way) data via transactional.
However I've had a go at mirroring between 1 & 2 and transactional to 3 and that looks even better! Some more testing needing to be done, but it's looking very promising so far..
July 24, 2008 at 7:44 pm
dogers (7/24/2008)
Well, SQL1 & 2 need to be kept up to date, back and forth, but yes, SQL3 looks best to receive it's (one way) data via transactional.However I've had a go at mirroring between 1 & 2 and transactional to 3 and that looks even better! Some more testing needing to be done, but it's looking very promising so far..
Really? mirroring allows updates/changes to only the primary.
* Noel
July 25, 2008 at 2:41 am
You're right - although I should add some background 🙂
1 & 2 are currently mirrored in an effort to make a high availability database. They now want to add a third SQL Server for external reporting purposes. We thought dumping mirroring and going completely to replication would be the best way, but it's looking not, right now..
July 25, 2008 at 3:11 pm
Ah ... that's different.
Yes, you could use mirroring as high availability; an independent distributor server and a separated subscriber for reporting that will stay working even with mirroring failover.
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply