September 21, 2008 at 10:29 pm
Hi,
Love to get thoughts from SQl Server experts on this.
I currently have a busy website where my main db is replicated to two additional dbs using one way transaction replication. I am on SQL Server 2005. I have all updates and inserts going to the Publisher. In cases where I need the user to be able to see what he/she just entered, I have those queries also going against the Publisher so they are guaranteed to see their change. I have a load balancer in front of the subscibers and I only query data from them.
Now to me this is scary because if my publisher is down, my website is down until I bring up my log shipped backup. My thought now is to merge replicate three servers with a load balancer in front so any server can go down and still be up. However, my understanding is that if the Publisher goes down I am still stuck with data not moving between servers since the other two are merely subscribers and no Publisher is picking up the changes.
Is there a way to configure three servers with replication which allows me to scale out to X sql servers with all of them logically as peers? So if one goes down we still function?
thanks in advance.
Rob
September 24, 2008 at 2:36 pm
It seems to me as though you might consider a failover cluster. Availability seems to be your concern. You can always Cluster your Pub and scale out to many more subscribers.
Just an idea.
September 24, 2008 at 4:07 pm
If you don't want to look at clustering - consider Database Mirroring instead. You could replace your log shipped database with a mirror, setup a witness for failover and modify your connection strings to include the alternate. This way, if the primary goes down - the mirror takes over automatically.
I am not sure of all of the setup requirements involved with replication - but I have seen a few presentations on setting this up, so I am sure it can be done.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
September 25, 2008 at 8:10 am
I think mirroring with a Witness is the solution you require.
Utilising a third server as a witness allows for automatic failover.
All DB connection strings need to have an additional parameter of 'Failover Partner = MyMirrorDB'
The application will then automatically try the Mirror if the Principal Data Source is not available.
This works well, we have implemented it.
N.B. Mirroring requires SQL2005 SP2. The RTM support was very basic.
September 25, 2008 at 8:27 am
Mirroring / clustering the publisher will help you keep things going, but it's a more expensive solution.
What about merge replication? How much data are we talking about and how often does any particular row change? I mean if most people enter data for "their" client or division and then read from all rows, merge might work. If the schema isn't too large, it's not that hard to get all the publications up and updates can hit any client.
If you like your current scheme, I might look at mirroring, less expensive than clustering and still with automatic failover if your clients have the SNAC client.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 25, 2008 at 8:32 am
Good thought. Part of my dillema is actualty scaling the updates. Since I need users to be able to insert and immediately query it back, those queries are against my main db where the updates take place.
Mirroring is a good idea for failover and I very well may do that.
Biggest question is scaling the updates. One thought is using Websphere queing and have updates go to the queue and that inturn update all dbs. So I dont even need replication and all servers can stand on their own as equals.
September 25, 2008 at 8:41 am
I've been testing merge replication but since it is still Publisher/Subscriber, if my Publisher goes down then synchronization stops until I rebuild the replication. Or is there a way to do it?
September 25, 2008 at 1:21 pm
If you are replicating / synchronizing between 2 machines (simplest case), and one machine is down, what do you have to replicate / synchronize with?
I'm looking through the glass darkly, and not understanding what it is you are really asking. In general, whenever a synchronized system becomes temporarily unavailable, there is always a lag when it becomes available again until synchronization is 100%. How long that takes depends on number and complexity of transactions plus the way the two systems are interconnected.
September 25, 2008 at 1:28 pm
With merge, it's publisher/subscriber, but not really. If one server goes down, the other one still has all the data it should (or that's committed). If you have a load balancer, the users don't know that one is down, although things might be slow.
Once you bring the 2nd one up, it will start receiving data from the other one, which might take time, so perhaps you want it out of the load balancer situation until it recovers.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 25, 2008 at 1:35 pm
My thinking was if I was doing 3 servers. Sorry I was not clear.
If I do 2 servers, merge replication vs mirroring? I guess with merge replication I have failover as well as scaling to 2 servers anyway.
Mirroring I have failover but can only use one at a time.
September 25, 2008 at 1:44 pm
rob,
I am assuming that here there is a mix of two problems.
From one you are trying to achieve a load balancing between three servers. From another you want to add some HA.
If that is true and you want to use three servers, then did you take a look on a peer-to-peer replication?
You can load balance read requests to all three servers.
You can limit update requests based on certain criteria (range, for example), so that certain updates would go to a certain servers only.
September 25, 2008 at 1:58 pm
Yes I am looking to marry fault tolerance with scaling in one solution
So many possibilities. Right now I have two in mind.
I was thinking about Queuing as well but WMQ is crazy expensive. Could use MSMQ. Thought is to have all updates go to a queue and an app update all three servers so all are on equal footing.
Or I could go with Two servers load balanced with merge replication and then replicate one of those to additional query databases. As long as 2 db servers is all I need for a while then that would work.
With this scenario, can I have two servers publish to one server? So if I take one down then other will continue to publish?
September 25, 2008 at 2:34 pm
With this scenario you do not need MSMQ. You will have all three servers being the same (from end user prospective of view).
Each server will publish to two other.
If one of them will go down, the rest two will continue publishing and accumulating changes, so that when the third will come back online, all three will synchronize.
I would assume that the "biggest" problem will be to create a "dynamic" redirection which will check if a particular server is online.
September 25, 2008 at 9:01 pm
So set up merge replication with each server being publisher and subscriber for each one? Wont that cause a lot of conflicts?
September 26, 2008 at 5:08 am
rob,
that is where I suggested to "load balance" updates based on some "range values".
In other words
IF SOMECRITERIA IS TRUE AND SERVER1 IS ONLINE UPDATE SERVER1
IF SOMECRITERIA IS TRUE UPDATE SERVER1 IS OFF UPDATE SERVER2
This kind of "dynamic" update layer is easy to build in your WEB application and you will avoid conflict resolutions.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply