This is what I have been asked today on my interview for a SQL Server DBA position:
It is an active - active cluster configuration. The primary node is in NY and the other node is in Rio. The NY server is failed and the failover cluster took over the activities. But the Rio server is becoming overloaded because of the additional activities. What will you do to solve the overload problem?
Please help as I am a newbee for the clustering concept.
1 Answer 1
GTT,
You stated 'SQL Server' so I assume you mean MS SQL Server. The reasons it can be overloaded is because that single node is now handling the additional instances and that puts load on the shared resources (Memory, Cpu, Cpu threads, not disks though, etc.). There are several ways of resolving this:
1-If you're going active-active, you should follow the N+1 rule so you would want 3 servers in the cluster. This way if 1 fails over, if you setup the preferred nodes properly, it'll go to the empty node and you can finish your maintenance on the original node and the nodes aren't overloaded.
2-Setup resource groups per cluster, you should do this on an active-active implementation anyways. You can limit the amount of RAM and set the CPU affinity (how many cpu cores the instance will use). By doing this you can adjust the usage of the limited resources in advance, so the other instance which might not be as critical won't hog all the resoruces.
3-How do they feel about the other server being so far? Distance increases latency which will have a direct impact on the customer receiving their queries, especially if best practices aren't being used and extra data is being generated (NO COUNT off, SELECT * instead of just what they need, etc.). You should also check the wait times to see if the network is now a issue too. For all we know the bottleneck is just the network and everything is fine.
This answer would satisfy me if I was interviewing you. Good luck. Have you been studying hard on SQL Server?