Not able to start SQL Server Services, what may be the reasons and how will you troubleshoot?

There are some basic reason if you not able to start the SQl Server Service:

1)     Master db files are not found on their location or corrupted

2)     Instance specific binaries or related binaries are either corrupted or disk is not available.

3)     Registry may be corrupt;

4)     Permissions may be messed up

5)     Antivirus may block the services to start

6)     Memory is not sufficient on server to allocate to SQL Server that time.

You can Troubleshoot the issue using following way:

1)     Check the SQL Server error log if created in instance root directory in Log folder

2)     Application and System logs in event viewer log

3)     Check resource (Memory and CPU) utilization on server that time.

4)     Check if relevant disks where SQL Server binaries located are attached/accessible from server

Whats a TUF File and where its located in sqlserver ?

Transaction Undo File: which is created when performing log shipping to a server in Standby mode.

And this will be on standby server.

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)