April 5, 2011 at 9:53 am
I have three SQL servers available, but currently only one is active and used (and has high load).
The two other servers are updated through transactional replication.
I have moved some of the applications read-actions to one server, but high loads stay, because lot's of updates happen and not all reads can easily by moved from one to another.
So I thought of the following solution and please tell me the cons and pro's.
1. I enable Network Load Balancing (NLB) and these three servers are the hosts.
2. I open port 1433 to this NLB cluster.
3. The NLB cluster will evenly load and filtering will be in single mode (so the reuest from one IP stays at the connected server within the session).
4. The data of the three servers are replicated with transactional replication.
5. Conflicts with the PK should be looked at, but can be solved.
6. The application is connecting to the NLB's virtual IP on port 1433.
7. If one server is down the NLB will route the traffic only to the running servers.
8. If one server comes back into the NLB, the replication will update the missing data.
Is this a solution that can be done or is it totaly impossible (and why) ?
April 5, 2011 at 11:32 am
Replication is not instantaneous. Is the delay acceptable?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2011 at 12:09 pm
I think the delay will be between 5 - 20 seconds, depending on the load.
That's acceptable for our solution. Better then the delays the high-load is causing now.
April 7, 2011 at 12:40 am
Is there someone having such configuration or maybe planning to ?
Lot's of questions about loadbalacing and fail-over out there.
This configuration would mean you have AND the load-balancing on SQLServer AND the direct failover AND you can add up to 99 servers !
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply