Unable to access SQL Error Log using SSMS

Recently my laptop was upgraded to Windows 10. Today while I was trying to access the SQL Server error log on my express instance (by executing xp_readerrorlog), I received the below error.

Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
Error log location not found.

When I expanded the SQL Server instance using SSMS, and expanded Management and select SQL Server Logs, no error log files were listed there. Although when I tried to open the SQL server error log using Notepad, I was able to read through the error log. Since the error was related to Loopback Connection, I suspect the issue might be related to network configuration.

Started looking at the SQL Server Configuration Manager, checked the Protocols used by the SQL Server instance. Both TCP/IP and Shared Memory was enabled. However, when I checked the SQL Native Client Configuration, I observed that the client protocol Shared Memory was in disabled state.

Once the Shared Memory client protocol is enabled, I was able to open the SQL Server logs using SSMS (both from GUI and using T-SQL query). Issue fixed.

Hope this may help someone. Happy Learning !!!

Setup LAB: 06 Installing Windows Failover Cluster to setup AlwaysOn

In the last post, I had configured the iSCSI target service to share the disk between two servers. I am going to use those two servers as Windows server Failover Cluster nodes and setup AlwaysOn availability group using that.

To begin with, I initialized the disk on one server using Computer Management, brought it online, formatted with NTFS partition and assign a drive letter (Q) before taking it offline. On the other server, I brought the disk online to test that the disk is accessible on both the server.

To configure AlwaysOn, both the servers, Node01 and Node02, should be part of the same Windows Server Failover Cluster. I have installed the Failover Cluster role on both of these servers and configured a heartbeat network between these two servers. Open Failover Cluster Manager from the Tools menu in Server Manager to start the failover cluster Validate Configuration wizard. This will check whether the all the necessary pre-requisites are met to begin the failover cluster installation.

Once the validation completed successfully, click the checkbox next to Create the cluster node using the validated nodes to start the failover cluster installation. Make sure, the account you are using to install this Failover Cluster should have Create Computer Object and Read all Properties permissions on the OU. To minimize the future effort, I have created a user group, which will have the above permissions on the OU and added my account to the above group. In case, you are pre-staging the Failover Cluster Name in the AD, you should have Enable Computer Object permission.

Once the Failover Cluster created successfully, I have performed failover to test that the failover is working fine. Next, I am going to install stand-alone SQL Server instance on each of the cluster nodes, Node01 and Node02. The installation of SQL Server will be performed using slip-stream method (more on that later). Once the installation is completed, in the next part, I will start the AlwaysOn Configuration.

Happy Learning !!!

Setup LAB: 05 Configuring the iSCSI Target

In the previous post, I had installed the iSCSI Target service and create the volume using File and Storage Service. In this post, let’s configure the iSCSI Target Service. To begin with, open Server Manager, expand File and Storage Service and select  iSCSI. Select New iSCSI Virtual Disk from the Tasks sub-menu to start the wizard.

Select the location for iSCSI virtual disk and provide a Name for the iSCSI virtual disk along with the Description. Specify the Size of the disk and select the Disk Type. Select New iSCSI Target, specify the iSCSI Target Name, add the Servers which need to access these drives.

 

On the Enable Authentication page, select the type of authentication and proceed towards the confirmation page to create the iSCSI disk.

Connecting to the iSCSI disk using iSCSI Initiator

Now that I have created the disk using iSCSI Target, let’s connect to the disk using iSCSI Initiator software from the servers Node01 and Node02. To begin with, open Server Manager in Node01 and select iSCSI Initiator from the Tools menu.

As shown in the above screenshot, the disk is available on the server Node01. Open Computer Management, expand Storage and select Disk Management. Now we can see the newly added disk on the server. Follow the above steps on Node02, to connect the disk. Once the disk is connected, it will appear in the Computer Management.

 

As the disk configuration is completed, in the next part we’ll start with Windows Server 2012 R2 Failover Cluster setup.

Happy Learning !!!