MSSQLWIKI

Karthick P.K on SQL Server

Archive for March, 2009

Installation of SQLserver2008 cluster fails on windows2008.(The group or resource is not in the correct state to perform the requested operation. (Exception from HRESULT: 0x8007139F)

Posted by Karthick P.K on March 26, 2009

Installation of SQLserver2008 cluster might fail on windows2008 with error mentioned below

The cluster resource ‘SQL Server’ could not be brought online.
Error: The group or resource is not in the correct state to perform the requested operation. (Exception from HRESULT: 0x8007139F)

 

Root Cause

This problem occurs because of a new security feature named Loopback check functionality. By default, loopback check functionality is turned ON in Windows and the value of the DisableLoopbackCheck registry entry is set to 0 (zero).
http://support.microsoft.com/kb/957097/

With this feature being turned ON: windows do not allow NTLM authentication if we try to access server from Local server using a name which is not its Net-Bios name (or) IPAddress.

When SQL Server Agent is started, SQL Agent resource access the SQL Server using SQL  VirtualServer name and hence we do not allow NTLM. So the SQL Server Agent would fail and the SQLServer Agent Resource creation would also fail.

SQL Server resource will fail to come Online because, IsAlive check will be done using NTLM Authentication i.e: Cluster service startup account resolves as NT AUTHORITY\ANONYMOUS LOGON when connecting to SQL Server for IsAlive check and the connection fails.

We will not get in to this issue if startup account of SQL Server has permissions to read and write SPN’s.

After the installation fails you will see the SQL Server resource is created but not the SQL Agent resource.

There are three ways to resolve this issue.

Option 1

1. After the failure, create the SPN’s manually using SetSPN tool (or) Configure SQL Server service to create SPNs dynamically for the SQL Server instances (Refer KB: 811889)

Example for creating SPN’s manually:
SETSPN -A MSSQLSVC/VSName.XX.XX.EDU:1433
SETSPN -A MSSQLSVC/VSName.XX.XX.EDU

2. Bring the SQL Server Resource online.

3. Create the SQL Server Agent resource type.

{
To add the sql server agent resource type execute the below command:

cluster restype “SQL Server Agent” /create /DLL:sqagtres.dll .Once done we got the
update that the Resource type ‘SQL Server Agent’ created.
}

4. Create SQL Server agent resource manually.

We need to make sure that the newly created SQL server Agent resource have the virtualservername and Instance name .

To add this property go to “failover cluster management” ==>SQL Server Agent Resource==>Properties==>properties
check for the two parameters (virtualservername and Instancename) and fill in the
details.

}

5. Change configuration reg_dword values of all components to 1 in below registry path

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLX.MSSQLSERVER\ConfigurationState

Option 2

1. Do a Complete uninstall of failed installation (or) Configure SQL Server service to create SPNs dynamically for the SQL Server instances (Refer KB: 811889) and move to Step 3.

2. Create the SPN’s before we do the installation. —

Example:
SETSPN -A MSSQLSVC/VSName.XX.XX.EDU:1433
SETSPN -A MSSQLSVC/VSName.XX.XX.EDU

Note:Beginning with SQL Server 2008, the SPN format is changed and new SPN format does not require a port number Refer: http://msdn.microsoft.com/en-us/library/ms191153.aspx

3. Then install the SQL Server on cluster

Option 3 (Recommended)

1. Disable the authentication loopback check by setting the DisableLoopbackCheck value in
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa registry subkey to 1.
To set the DisableLoopbackCheck registry entry to 1, follow below steps on all nodes of cluster.

a. Click Start, click Run, type regedit, and then click OK.
b. Locate the following registry path:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
c. Right-click Lsa, select New, and then click DWORD Value.
d. Type DisableLoopbackCheck, and then press ENTER.
e. Right-click DisableLoopbackCheck, and then click Modify.
f. In the Value data box, type 1, and then click OK.

2. Restart the system.

3. Do complete uninstall and re-run the setup(or) Follow the steps from step2 in option 1.

 

Note:

1. We will encounter above error if we are installing the named instance of SQL Server and SQL Server browser is in stopped state. 

2.  If you have installed SQLServer 2012 (Denali) and uninstalled it on same cluster. You might encounter above issue. Refer below link for   details.

https://mssqlwiki.com/2012/01/31/sql-server-resource-fails-to-come-online-is-alive-check-fails/

Regards

Karthick P.K

Posted in SQL Cluster Setup, SQL General, SQL Server Setup | Tagged: , , , , , , | 8 Comments »

Using DMVs to find out the index usage history- SQLServer Index Usage

Posted by Karthick P.K on March 10, 2009

SQLserver 2005 ships with a set of DMVs that can help you identify the missing indexes for your workload, Analyze the effectiveness of the existing ones and help find out index fragmentation.

Using DMVs to find out the index usage history

Over a period of time, you could create a lot of indexes on your tables and modify existing ones. However, in SQL 2000, you couldn’t estimate how effective were each of these indexes. Poorly design indexes could lead to performance overhead instead of enhancing performance.

In SQL 2005, you can query the sys.dm_db_index_usage_stats DMV to find out the indexes that have NEVER been used since the last start of SQL Server. You can use the following query to find that out:

 1: select object_name(i.object_id) as ObjectName,

 

 2: i.name as IndexName, s.user_updates, s.user_seeks, s.user_scans,

 

 3: s.user_lookups

 

 4: from sys.indexes i

 

 5: left join sys.dm_db_index_usage_stats s

 

 6: on s.object_id = i.object_id and i.index_id = s.index_id and s.database_id =

 

 7: <dbid>

 

 8: where objectproperty(i.object_id, 'IsIndexable') = 1 and

 

 9: -- index_usage_stats has no reference to this index (not being used)

 

 10: s.index_id is null or

 

 11: -- index is being updated, but not used by seeks/scans/lookups

 

 12: (s.user_updates > 0 and s.user_seeks = 0

 

 13: and s.user_scans = 0 and s.user_lookups = 0)

 

 14: order by object_name(i.object_id) asc

 

In the output, you will ALL the indexes that have never been used by any sort of workload on your server, since the last start of SQL Server.

For indexes that have NEVER been used (either for a SELECT or a DML statement), all columns will be NULL

For indexes that have NEVER been used (for a SELECT), but had to be updated due to a DML statement, the user_updates column will be >0, while other columns will be 0. It is these indexes that could cause severe performance overhead for your DML statements and might be worth dropping.

Give ample time for SQL Server to get exposed to all the workload after a restart, before running this query.

Using DMVs to find out missing indexes

When the query optimizer generates a query plan, it analyzes what are the best indexes for a particular filter condition. If the best indexes do not exist, the query optimizer generates a suboptimal query plan, but still stores information about these indexes. The missing indexes feature enables you to access information about these indexes so you can decide whether they should be implemented.

For more information on how to use this feature, please visit the following link

http://msdn2.microsoft.com/en-us/library/ms345417.aspx

 

Using DMVs to find out index fragmentation

The sys.dm_db_index_physical_stats dynamic management function replaces the DBCC SHOWCONTIG statement.

You can learn more about using this DMV to identify fragmentation, correcting it and possibly automating this activity for your server by visiting the following link:

http://msdn2.microsoft.com/en-us/library/ms188917.aspx

Regards

Karthick P.K

Posted in Performance, SQL General | Tagged: | 1 Comment »

 
%d bloggers like this: