How many IP addresses are required for 3 nodes cluster?

2 IPs required per node (Private and Public)

1 Windows Cluster

1 per SQL Instance + MSDTC

         MSDTC was must in Windows Server 2003 if used by instance but Windows 2008         onwards it can use from local service on node while for best practice it should be separate for each instance.

What is look alive and is alive in QSL server technology?

When an instance of SQL Server is running in a Clustered Environment, Microsoft Cluster Service performs two checks to confirm if the SQL Server resource is still available. One is LooksAlive and the other one is IsAlive check.

Looks Alive:  It’s a basic check in which the Cluster service queries the Windows Service Control Manager to check if the SQL Server service is still running. By default this check happens every 5 seconds.

Is Alive: IsAlive check the Cluster Service connects to the SQL Server instance with the help of c:\windows\system32\sqsrvres.dll and runs SELECT @@SERVERNAME against the instance. This check does not check if the user databases are online or not. It just checks the SQL Server instance availability. This check happens every 60 seconds by default.

If SQL backup job is taking long time, how will you troubleshoot it?

Ans: I would check all the components involved while taking backup means Disk IO on source (where database files resides) and destination, CPU utilization and the N/W utilization as well if the backup destination is on network location. Additionally, what else is running on server at same time which may impact the backup? E.g. rebuild index, bulk copy or insert, log backup (perhaps; need to verify), latency to complete checkpoint process and ghost cleanup task (need to verify). I will check the following to monitor relevant components involved in backup process:

Disk IO: Average disk queue length, Average sec/read, Average Disk sec/write (Object: Logical Disk)

CPU: Processor Time (Object: Process), Processor Queue Length (Object: System)

Network: Bytes Total/sec, Bytes Sent/sec, Bytes Received/sec, Current Bandwidth (Object: Network Interface)