As I explained before, I am working on an upgrade to my company's back-office software suite. Part of this upgrade has been a migration from a legacy database technology (BTrieve) to a more modern one (MS SQL 2005). The changes are obviously stark and glaring, to say the least.
In any case, about 2 years ago, when we first embarked on this upgrade journey, we had investigated, with the help of some consultants, what technology we would be using to provide for high-availability (HA) and high-security of our data and application. At this time, many of the available SQL Server 2005 technologies were presented, including database mirroring, transactional replication, server clustering, etc.
In particular, database mirroring at first looked very promising. Early on, however, we received some advice from one of our highly-respected consultants that fully led us to conclude the mirroring was entirely unsuited for our needs. Several reasons were cited by this individual, the details of which are, at this point, long-forgotten, but suffice it to say that we fully dismissed database mirroring as a viable option for us and did not pursue it one bit after that.
We went head-long down the path of transactional replication with queued updateable subscribers, providing for high security of our data and the ability to point at any available database in the population of participating servers for up-to-date data. This was all working quite well for us until, unfortunately, very late in the game.
Once we began pushing our full production load of transactions on the server, the performance level of this technology became very suspect. In fact, during one test, when we "failed" our application over to the "subscriber" database for primary services, it was quite apparent that the "queued updateable" portion of this technology was woefully inadequate. Each minute that passed, we were falling 30-50 seconds FURTHER behind in transactions, meaning that we could, for all intents and purposes, never switch back, since the data would be horribly outdated. This was, for us, an absolute show-stopper.
What were we to do? We called in experts. We scoured blogs and websites for any assistance we could muster to see what we could have configured wrong to make the performance so poor. But during that effort, I took some to think outside the box and do some "re-investigation" of other technologies.
Database mirroring came back into my view, with fresh eyes this time. I realized that the reasons we had dismissed it in the first place were misunderstandings and misrepresentations. After a few days of research and trial, we moved our efforts to database mirroring.
The option we chose for now was the High Security mode of operation, foregoing the need for the Witness server for now (it can always be added in later). Configuration of the mirrored pair took all of about 3 hours (most of which was recovering from an operator error on my part).
The best part is that the performance of the mirroring is spectacular, even under our load (about 10,000 transactions per minute all day, every day, with peaks that exceed 15,000). We have been able to fail over and back whenever necessary without any degradation of our services whatsoever. The built-in monitoring tools are mature and very relevant.
Overall, my feeling is that when replication "grew up" in the Microsoft SQL Server world, it became mirroring.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment