Blog has moved

I've now moved to http://akawn.com/blog and will no longer be using or updating this SQL-Kevin blog.
Cheers
Kevin

Error: 18456, Severity: 14, State: 58

Recently I was alerted to the following error that was caused when a legacy application was attempting to use an ODBC connection to connect to a SQL Server.

Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.
Error: 18456, Severity: 14, State: 58.

The SQL Server was definitely using Mixed Mode Authentication.

The lack of the SQL Login name and incorrect authentication mode being reported implied that the application connection to SQL Server was being interpreted by SQL Server incorrectly.

After a couple of various tests, the solution in this case was to set the ODBC connection for the application to use Named Pipes.

Configure Database Mirroring to use a specific network card

The following article demonstrates how you can reconfigure your existing database mirroring to use a specific network card. Both the principal instance and mirroring instance servers have 2 network cards.

• The principal instance is currently using 10.10.10.16
• The mirror instance is currently using 10.10.10.15
• Both are communicating over their local port of 5022

As shown on the principal instance, by default, the ip_address used for the mirroring endpoint is not specified so it will use any network card.

The steps to set the principal to only use the ip address 10.100.10.16 and the mirroring instance to use only 10.100.10.15 are listed below. Please note only 1 database is mirrored in the demo.

1.   Remove database mirroring for the database.

2.    Alter the Mirroring endpoint on the principal instance to use local network card 10.100.10.16. I'd suggest that it would be better to assign a DNS entry for the ip address and use that instead of hard coding an ip address.

 

3.   Verify that the ip_address of the Mirroring endpoint is now showing the ip address specified.

4.   On the mirror instance, alter the Mirroring endpoint to use local network card 10.100.10.15.

5.   Verify that the ip_address of the Mirroring endpoint is now showing the ip address specified.

6.   On the mirror instance set the partner to be the principal instance database.

7.   Finally, on the principal instance set the partner to be the mirror instance database.

You should now see mirroring using the specified network card.

I found that I had to remove database mirroring to get this to work, as the ALTER DATABASE command did not allow me to specify a new partner address while database mirroring was in use. The error given is shown below:

Auto Update Statistics and Auto Update Statistics Asynchronously Investigation

Below are my findings into the Auto Update Statistics and Auto Update Statistics Asynchronously database options.

What I discovered using profiler was:

Scenario 1:

• Auto Update Statistics = True
• Auto Update Statistics Asynchronously = False

This resulted in the relevant table statistics being updated as part of the user’s session before the user query starts retrieving data. This is the default database setting.

Scenario 2:

• Auto Update Statistics = True
• Auto Update Statistics Asynchronously = True

This resulted in the relevant table statistics being updated by a system session (not the user's session) after the user query has completed.

Scenario 3:

• Auto Update Statistics = False
• Auto Update Statistics Asynchronously = True

This resulted in no statistics being updated. This was interesting as it means that if Auto Update Statistics is set to False, it does not matter that Auto Update Statistics Asynchronously is set to True as the statistics will not be auto updated.

The code used to test this was:

-- Create the test table

CREATE TABLE [dbo].[t1](

      [c1] [bigint] IDENTITY(1,1) NOT NULL,

      [c2] [varchar](50) NULL

) ON [PRIMARY]

GO

-- Insert first data row into table

INSERT INTO dbo.t1

            (c2)

VALUES      ('x')

GO 

-- Stats are only created once you add the where clause

SELECT *

FROM   dbo.t1

WHERE  c1 < 5 

-- Use the below to show when stats were last updated

SELECT OBJECT_NAME(OBJECT_ID)          AS TABLENAME,

       NAME                            AS STATS_NAME,

       STATS_DATE(OBJECT_ID, STATS_ID) AS STATISTICS_UPDATE_DATE

FROM   SYS.STATS

WHERE  OBJECT_NAME(OBJECT_ID) = 't1' 

-- Insert several rows to the table

INSERT INTO DBO.T1

            (C2)

VALUES      ('x')

GO 1000

/*

When you run the below the stats will be updated and this is the data I captured in Profiler. Specifically the EventClasses Auto Stats, Lock:Acquired, Lock:Released, SQL:StmtStarting, SQL:StmtCompleted, Performance statistics and all columns.

*/

SELECT *

FROM   DBO.T1

WHERE  C1 > 5 

SQL Server 2008 R2 upgrade perf-ReportServer-rsctr.dll error

While performing an in place upgrade from SQL Server 2008 to SQL Server 2008 R2 I received the following perf-ReportServer-rsctr.dll error.



Process Explorer revealed that WmiPrvSE.exe was using the file.

To resolve this issue you could try stop the 'Windows Management Instrumentation' service.

Create Test Windows Clusters using Microsoft iSCSI Software Target 3.3

Creating Test Windows clusters has got a whole lot easier now that Microsoft iSCSI Software Target 3.3 is freely available to download.

It can be found here http://www.microsoft.com/downloads/en/details.aspx?FamilyID=45105d7f-8c6c-4666-a305-c8189062a0d0.

VirtualBox http://www.virtualbox.org is a good option to host your VMs as I have found this software to be the simplest and quickest way to get my Test environments built.

The iSCSI Initiator Users Guide for Windows 7 and Windows Server 2008 R2 can be found here: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=00d7c33c-93cc-4449-8d5e-3fd12d820b56

SQL Cluster Install 'Network binding order' warning

When installing SQL Server 2008 on a cluster you may get a 'Network binding order' warning.

To identify why the setup is reporting this error:

1. Open your ..\Setup Bootstrap\Log\..\Detail.txt file
2. Navigate to the 'Network binding order' rule section

Below shows that 'Local Area Connection* 9' was top of the list, when it should have been 'public'

The problem is that sometimes you cannot see the device or cannot get the correct order despite using http://support.microsoft.com/kb/955963.

So what can you do?
1. Run regedt32
2. Navigate to 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Tcpip\Linkage'
3. Right click on 'Bind' and select 'Modify'

4. Move the correct Device (identified in Detail.txt) to the top of the list and that is it.