September 28, 2010 at 12:18 pm
Hi,
We’re trying to identify the best method to create a read-only database with high availability It our production environment we have a clustered database with lots of reads and every ten minutes is updated with writes. We experienced performance issues during the updates and when we failover the server the database goes into recovery mode for 45 minutes.
We are planning therefore to setup a read-only database to separate the reads and the writes. What we need is the best method to create the read database, keep it up to date and provide high availability. Right now I’m looking at transactional replication however I’m not sure how to provide high availability to the read database (subscriber). It doesn’t look like if we use two subscribers we can auto-failover from one to the other, we may be able to do this in code.
Some general info:
Read only database must have minimal downtime
Can’t be more than a few minutes out of sync
Is this the best approach? Should we database mirror or another mirror/replication method instead?
September 28, 2010 at 2:33 pm
We too were facing the same issue. Now We have replication set up between our publisher and two subscribers. When one subscriber goes down for any reason, we run a script that will change all ODBC in all webservers and also change the web.config file. That will mean couple of minutes to hours of down time depending on when the failure happened.
But that is not going to help if the distribution server goes down.
I did write an article about how I leveraged the power of Resource governor to solve this issue.you can read it here[/url].
-Roy
September 28, 2010 at 2:52 pm
I would suggest peer to peer replication.
September 28, 2010 at 3:10 pm
Thanks for the feedback but I don't think script will work for us given our concern for HA.
I don't think peer-to-peer would work since the point is to separate the writes from the reads. I assume we couldn't keep the activities separate and lose the control over when the read database would be written to.
Is using Microsoft NLB (network load balancing) between the subscribers a good option?
September 28, 2010 at 3:20 pm
Why not cluster your replicated DB?
-Roy
September 28, 2010 at 3:50 pm
This http://msdn.microsoft.com/en-us/library/ee523927.aspx is a good resource that provides a whole bunch of HA planning and implementation information (covering replication, mirroring, log shipping etc)
September 28, 2010 at 3:55 pm
If you already have a cluster, I would create a reporting instance on the other node and set up either just transactional replication, or asynchronous mirroring and create a database snapshot every hour (if data doesnt need to be up to the minute).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply