In all SQL server failover clusters in our environment, It came to our notice that the SQL Server Agent is spamming the Application eventlogs under event viewer below entries every second in the Active node.
[sqagtres] LooksAlive request.
[sqagtres] CheckServiceAlive: returning TRUE (success)
It was observed that this incident started to occur after the scheduled windows patching.
Microsoft had identified this as due to a code defect in Sqagtres.dll. To resolve this issue, we would have to patch the SQL instance with Cumulative update 3 for SQL Server 2008 R2 SP2 as mentioned in below KB.
The hotfix download is available at http://support.microsoft.com/kb/2754552
If you wanna add a shared disk to the two nodes of a cluster to be built inside a VMware, Follow below steps.
- If your VMware is pretty Old, Then shutdown both the virtual machines to be configured as cluster.
- Then from inside VMware virtual center, right click one of the virtual machine and select ‘edit settings’.
- Create one SCSI control with sharing allowed among multiple virtual machines.
- Now click on Add and select Hard Disk.
- Select to create new virtual disk, provide required size, thin provisioning unchecked and to store with the virtual machine. And in advanced section, select the value for Virtual node (1:x, where 1:0 was selected as virtual node in step 3 and the x can have any value which is unoccupied. Since this SCSI control was newly created, all the nodes will be vacant. So here x can have any value between 0 and 15). Complete the wizard and now you have disk created and mapped to one virtual machine.
- Right click the same virtual machine once again and choose ‘edit settings’. And then click on the Hard Disk we just added. Now in a box at the right top corner, you will get the disk file location and name.
- Next use the tool putty (Let me know if you would like to know what putty is) and connect with the ESX host where the two virtual machines are located and also the disk we created in step 5 will be located.
- Using basic linux commands, navigate and find the Disk file and run below command to make the Disk file Zeroed (All bits on that disk are reset in this process).
- vmkfstools -w /filepath/filename.vmdk
, Continue reading…
At first I thought starting SQL Server in Single user mode is easy in Windows 2008 fail-over cluster. But I was wrong, If I just add ‘;-m’ in start-up parameter and starts it, the SQL server will start in single user mode via cluster. But we wont be able to log in. The cluster service logs in before us. And so we might see an error like below.
Login failed for user ‘Domain\user’. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: xx.xx.xx.xx]
Now what do we do? Below is how I overcame this problem.