April 6, 2011 at 2:00 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 6, 2011 at 2:20 am
Please don't post multiple threads for the same question.
No replies to this thread please. Direct replies to http://www.sqlservercentral.com/Forums/Topic1088738-291-1.aspx
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 6, 2011 at 2:22 am
This setup is also possible for SQL2008, so please comment on the above thread.
April 14, 2011 at 4:02 pm
you can setup peer-to-peer nodes but do not get carried away with that. There is a limit in the number of peers that can be setup
The main problem is that you need to know what is causing the High CPU.
If is the "reads" then you may solve the problem like this but if it is the writes you will have to rethink your app.
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply