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

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

Setup LAB: 04 Setting up a Storage Server


Whenever we setup a Cluster in a LAB environment, often the biggest challenges in the whole process is, how to configure the storage server. A user reported that, the configuration steps mentioned on my blog for the FreeNAS server is no more valid for the latest version of the software. With Windows Server 2012, we get the Storage server functionality built into the Windows Operating System. In this post, we are going to build a storage server using Windows Server 2012 R2 evaluation software.

Installing the iSCSI Target Service

To install the iSCSI Target Service, Open the Server Manager, Click on Manage, and select Add Roles and Features. Select Role-based or Feature-based Installation on the Select Installation Type page, choose the list of servers where we want to deploy this service. On the Server Roles page, expand File and Storage Services, expand File and iSCSI Services and select iSCSI Target Server. Click on Install, in the next page to start the iSCSI Target Server installation. 

image[3]

Create the volume using File and Storage Services

I have already added 3 disks to the server. Open Server Manager, go to File and Storage Services and select Storage Pools. Click on the Tasks as highlighted below, and select New Storage Pool.

image

Specify a name for the storage Pool, in my case I have given a name as Local SAN and select the list of disks you wanted to add into this storage pool. The confirmation page of this Storage Pool creation is shown below. Click on the Create button to start the storage pool creation.

image

Once the storage pool is created, let’s create a virtual disk from this Storage Pool. Click on the Task button,below Virtual Disk and select New Virtual Disk wizard. In the Storage Pool selection page, select the newly created storage pool. Provide a name for the virtual disk, (optionally) along with a description of the virtual disk. Select the storage layout (simple/Mirror/Parity) and Provisioning type (Thin/Fixed) along with size of the disk. Click on Create to create the virtual disk. Select the checkbox on the result page to start the New volume wizard.

image

Once the New Volume wizard started, select the newly created disk on the Server and Disk page. Specify the size of the volume, assign drive letter (if any, else use as a mount point), mention the allocation unit size, volume level (if any) and create the volume.

image

Since the volume is now created, in the next post we are going to configure the iSCSI Target software to share the disks for the other servers.

Happy Learning Smile !!!

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

Availability Group Listeners Creation Issue


I must admit, that I was facing this issue since a while. I was trying to setup a lab to have a hands on with AlwaysOn feature; however the Availability Group Listeners creation was failing. As I started looking into the SQL Server error log & observed the below error message.

Error: 19476, Severity: 16, State: 4.
The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect
. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator.

From the System event log, below error was reported:

Log Name:      System
Source:        Microsoft-Windows-FailoverClustering
Event ID:      1194
Task Category: Network Name Resource
Level:         Error
User:          SYSTEM
Computer:      NODE01.SKGLAB.LOCAL
Description:
Cluster network name resource ‘AGDemoTest_AGDemoTest’ failed to create its associated computer object in domain ‘SKGLAB.LOCAL’ during: Resource online.
The text for the associated error code is: A constraint violation occurred.

Please work with your domain administrator to ensure that:
– The cluster identity ‘WINCLUSTER01$’ has Create Computer Objects permissions. By default all computer objects are created in the same container as the cluster identity ‘WINCLUSTER01$’.
– The quota for computer objects has not been reached.
– If there is an existing computer object, verify the Cluster Identity ‘WINCLUSTER01$’ has ‘Full Control’ permission to that computer object using the Active Directory Users and Computers tool.

While I was reading the above error message, the highlighted text (in blue) caught my eyes. I remembered that, if the virtual computer object was not prestaged, by default, it used to create the computer object in the default Computers container in AD (till Windows Server 2008 R2). A little search over internet points me to a post on the PFE Platform blog, which states that starting Windows server 2012 (& onwards), all the virtual computer objects are created in the same OU, where the cluster computer object resides. So in my case, the cluster computer object was unable to create the virtual computer object in the same OU due to lack of permission, which in turns failing the Availability Group Listener creation . As suggested in the above reference post, after providing the necessary permission, I was able to create the availability group listener.

Hope, this may help someone. Happy Learning Smile !!!

Posted in AlwaysOn, How To, Just Learned, Setup LAB, SQL Server, SQL Server Configuration, SQL Server HA, SQL2012, TestLab | Tagged , , , , , , | Leave a comment

Removing a SPN Entry


At times, we may require to remove a wrongly created SPN entry. The syntax for removing a SPN entry is:

setspn.exe -D “SPN entry, which needs to be removed” “Service Account or Server Name”

Over the weekend, I was working on my lab to simulate an issue, while I observed that the SPN registration was failing on one of my test server. To fix the issue, I had to remove the SPN entry. I used the below command to remove the SPN entry as it was configured for the SQL server service account.

setspn.exe -D “MSSQLSvc/KRISHNA03.SKGLAB.LOCAL:1433” “SKGLAB\SQLSvc-SQL2012”

Happy Learning🙂 !!!

Posted in SQL Server | Tagged , , , , , | Leave a comment

SQL Server is consuming more than 17000 handles !!!


Well, these days, this is a topic of discussion most of the time & when this evening I encountered this again, I thought of writing this post for my future reference. While analysing a slow performance issue on a server, a Windows colleague reported that ‘SQLServr.exe is using more than 17000 handles which is causing the server slowness. I am sure most of us, have faced this issue at some point in time.

Over the past few years, I refer to the following blog post, which provides a nice explanation around troubleshooting handle leak issue. According to the above reference post, SQL Server service may consumes 30,000 handles on a 32-bit platform while the exception limit is 50,000 handles on a 64-bit platform. Apart from that, the post also demonstrate how to identify such problems using Sysinternals tools. As always, I have shared the reference article with my colleague and informed him that the SQL Server handle usage is within limit & suggested him to look into other area. Hope, we’ll get a fix for this issue Winking smile.

Happy Learning Smile !!!

Posted in How To, SQL Server | Tagged , | Leave a comment