I must be forthright, I've dedicated far too much mental energy to a full consideration of Germanic Goddess Jordan Carver as we move closer to Oktoberfest.

This has caused me to be slack in my duties of continuously patching sql server.  I had decided to "sit and wait" while SP3 for SQL 2008 finished it's CTP and not apply any more cumulative updates.

Of course that has now caused me to be burned by two bugs, both of which are patched in either a Hotfix or a cumulative update.

The first was a security patch for something with an XML Editor, I love when other people find my unpatched vulnerabilities in sql server, makes me feel really good.

http://support.microsoft.com/kb/2494089

http://technet.microsoft.com/en-us/security/bulletin/ms11-049

The other was an issue with a Microsoft Cluster, the remote registry service kept stopping.  If you haven't noticed you can't connect to cluster administrator if this service is not running, and you can't remotely monitor Perfmon counters without this service.  Of course this is not a sql server problem, so I sent it to the engineers, the engineers were non too pleased and after much needling on my part they did begin to try and figure it out... they were nice enough to show me that sql server was causing this service to fail and that I did not have it patched to the correct level, argh, i love when an engineer points out that we don't have sql patched correctly and it's causing the service I put a ticket in for to crash.  Absolutely lovely. 

http://support.microsoft.com/kb/2159286

Also be advised that without the remote registry service running, the sql server cumulative update will fail.  You can research the message endlessly or just start the remote registry service, set it to restart every time it fails and try again.  The exact error message is the following message:

SQL Server Setup failure.
------------------------------

SQL Server Setup has encountered the following error:

Failed to retrieve data for this request..

I don't know why, but this simple request for SOX Compliance took me a while to find. There is a function that retrieves login properties, LOGINPROPERTY, duh! Of interest is the PasswordLastSetTime.

It also has many other useful properties, see: http://msdn.microsoft.com/en-us/library/ms345412(v=SQL.100).aspx . It now becomes very easy to drop into any query, below is one of many sox queries we run that is now required to include this:

A good day of catching some green sunfish in the back pond.

Aaron with a monster fish:

Thomas has a live one !

EJ Fights this one, but reels it in.

I finally found a plow for my Ford 4000 tractor, I'll take pictures once I have it hooked up and working...

Every few days I have a flock of Turkeys come through the property, usually they are pretty far back, but recently they have been really close, if they come this close to the house after Sept 15th, one or two of them will be in the freezer...

   The boyz are back in town !

1. EJ 3rd Grade
2. TJ 2nd Grade
3. AJ Kindergarten

Strength in Numbers.

The sql server error log, starting with sql 2005 +, began giving us some very descriptive reasons for the login failure. This detailed reason was not always returned to the client for security reasons, but the SQL DBA could look in the error log and get more information on the error.

DBA's are never satisfied as the more information is enough to help, but not completely. Recently we started receiving the login state 16 on a TFS (Team Foundation Server) SQL Server. State 16 means that incoming user does not have permission to log into the target database. Damn, it'd be nice if the error message would tell you the target database it was trying to login to !

Out of desperation we promoted the login to a sysadmin. The login state 16 error continued.

This is when we clued in that the target database must just not exist. Sure enough a comparison with our other TFS SQL instances showed a missing database.

Once we restored that missing database, the error went away. Frustruating to troubleshoot this particular login state, when if the error message were more clear (by including the name of the target database) we could have fixed this in 10 minutes. The TFS administrator was clue-less, and was unable to determine that there was even an error happenning, or what service was generating the error. We never did resolve that. So remember state 16 may not just be permissions to a database, it may be a missing database.

Found this happy fellow by our window.  A very pretty spider.  Don't kid yourself, it's bite is not going to feel good.

http://mdc.mo.gov/discover-nature/field-guide/black-and-yellow-garden-spider 

changing the port number for a sql server cluster should be a pretty straight forward task.  Most Clustered sql servers will be mission critical, hence the clustering; this also means it will be properly secured behind a firewall.  This means you will need to run the SQL Server instance on a static port.  In some instances you can not choose the port that SQL chooses for you.  In these instances you will need to change the port number for SQL Server.

There are several resources out there on how to do this (why Microsoft didn't make choosing this as part of the install, or an option in cluster manager ), but all of them referenced using a command line program "Cluster Res", unfortunately this errors out on Windows 2008 R2 and SQL 2008 (r2 as well), with a "can not find the file specified".

For Windows 2008 R2 and SQL 2008 you need to run "Cluster . Resource" from the command line.

The basic directions are:

1.  Take the SQL Server instance off line.

2.  cluster . resource "SQL Server (<InstanceName>)" /removecheckpoints:"HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER"

3.  Open SQL Server Configuration Manager (ensure to "Run as Administrator"), Make the appropriate changes.

4.  cluster . resource "SQL Server (<InstanceName>)" /addcheckpoints:"HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLSERVER"

5.  Bring the SQL Server Instance back on line

6.  Verify in the sql server error log that the instance is using the correct port.

Easy stuff, that Microsoft should have made easier.

How to change SQL Server parameters in a clustered environment when SQL Server is not online (http://support.microsoft.com/kb/953504)

http://sqlserverpedia.com/blog/sql-server-bloggers/sql-server-configuration-time-bombs/