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.

image

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 Smile !!!

Advertisement
Posted in How To, Just Learned, SQL Server, SQL Server Log, SQL2012 | Tagged , , , | Leave a comment

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.

image

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.

image

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.

image

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 Smile !!!

Posted in AlwaysOn, Clustering, How To, Setup LAB, SQL2012, TestLab | Tagged , , , , , | Leave a comment

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.

image

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.

image 

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

image

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.

image

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.

image

 

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

Happy Learning Smile !!!

Posted in AlwaysOn, Clustering, How To, Setup LAB, SQL2012, Storage Server, TestLab | Tagged , , , , , , | 1 Comment